• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Composition of MySql Forum Database

Neutral Singh

Well-known member
#1
My forum's database has reached 2GB... but lately it is increasing at an alarming pace where as i have not noticed any significant jump in activity over a long period of time...

Is there a way to see, what comprises of the database... like how much is the size of the images etc.

Is there a way to reduce the database size?

Thank you
 

Anthony Parsons

Well-known member
#3
Is there a way to reduce the database size?
Why? A large database is not an issue, and 2Gb is nothing to worry about. 2Gb is a small database, in the scheme of database sizes. There is no reason you would suffer any performance issue if your setup is correctly optimised and you have enough RAM.
 

Mouth

Well-known member
#4
My forum's database has reached 2GB
Why? A large database is not an issue, and 2Gb is nothing to worry about
Agreed. Mine is 6+ Gb for over 2 million posts.

but lately it is increasing at an alarming pace
Understanding that pace and growth could be the key. Are you using elasticsearch, or searching DB? Have you been installing add-on's that are adding additional DB tables and indexes?
 

Anthony Parsons

Well-known member
#5
I was just reading some stuff yesterday on this, and it seems there is no threshold to determine what even MariaDB issue as recommended default my.cnf configs for small, medium, large and huge databases. One would think such is defined, yet is not.

Talking about it with a couple of different people, a broad agreement could be:
  • Small - up-to 1Gb
  • Medium - 1Gb to 50Gb
  • Large - 50Gb to 1Tb
  • Huge - 1Tb and above
Yet then that is all opinion, still. Ask someone with a 250gb database, they may say its medium in size, as their "normal" could be working with many TB databases. Talk with someone who only knows small GB size databases, they may say they're large.

What I have personally discovered, very little config is needed for databases of several GB -- so to me, I would still call them small and in little need of performance enhancements when running on SSD.
 

xenfans

Well-known member
#6
Discussions: 260,770 / Messages: 753,410 / Members: 24,980

user@omg:~$ mkdir export
user@omg:~$ cd export/
user@omg:~/export$ mysqldump -u omg -p omg > omg.sql
Enter password:
user@omg:~/export$ tar -czf omg.sql.tar.gz omg.sql
user@omg:~/export$ ls -hall
drwxrwxr-x 2 user user 4.0K May 8 09:26 .
drwxr-xr-x 6 user user 4.0K May 8 09:23 ..
-rw-rw-r-- 1 user user 1.2G May 8 09:25 omg.sql
-rw-rw-r-- 1 user user 261M May 8 09:26 omg.sql.tar.gz
user@omg:~/export$

750k posts hobby site is already over a gig.

1.6m posts niche topic is at 1.5g

2.1m posts sports site is at 2,7g

Maybe those numbers help you.
 

jwright

Active member
#7
Discussions: 260,770 / Messages: 753,410 / Members: 24,980

user@omg:~$ mkdir export
user@omg:~$ cd export/
user@omg:~/export$ mysqldump -u omg -p omg > omg.sql
Enter password:
user@omg:~/export$ tar -czf omg.sql.tar.gz omg.sql
user@omg:~/export$ ls -hall
drwxrwxr-x 2 user user 4.0K May 8 09:26 .
drwxr-xr-x 6 user user 4.0K May 8 09:23 ..
-rw-rw-r-- 1 user user 1.2G May 8 09:25 omg.sql
-rw-rw-r-- 1 user user 261M May 8 09:26 omg.sql.tar.gz
user@omg:~/export$

750k posts hobby site is already over a gig.

1.6m posts niche topic is at 1.5g

2.1m posts sports site is at 2,7g

Maybe those numbers help you.
Nothing there screams red flags to me.
 

Neutral Singh

Well-known member
#8
Are you using elasticsearch, or searching DB? Have you been installing add-on's that are adding additional DB tables and indexes?
Nope.

These are my three biggest tables: First one looks a suspect, almost 800MB, can i get rid of it?


Code:
xf_bb_code_parse_cache        ~156,957    InnoDB       utf8_general_ci    768.3 MiB
xf_search_index                          259,395     MyISAM    utf8_general_ci    549.3 MiB
xf_post                                          ~136,447    InnoDB       utf8_general_ci    533.8 MiB
 
Last edited:

xenfans

Well-known member
#12
Nothing there screams red flags to me.
Exactly.
Nope.

These are my three biggest tables: First one looks a suspect, almost 800MB, can i get rid of it?


Code:
xf_bb_code_parse_cache        ~156,957    InnoDB       utf8_general_ci    768.3 MiB
xf_search_index                          259,395     MyISAM    utf8_general_ci    549.3 MiB
xf_post                                          ~136,447    InnoDB       utf8_general_ci    533.8 MiB
You can manage that one, post table seems normal, search is probably fine as well.
As a forum grows, so does it's content and to keep things running and running smoothly, so do the tables around it that help the features like caching, speed, performance, convenience, etc.
View attachment 152176

Uncheck it if you want to drop the use of the table. Then flush the table.

View attachment 152177
Ah there you go. Yeah. that might do the trick.
 

eva2000

Well-known member
#13