Composition of MySql Forum Database

[Neutral Singh]

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

 

[xenfans]

Doesn't phpmyadmin list the tables, just sort it on the size column.

 

[Anthony Parsons]

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]

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]

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]

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.

 

[JonathanW]

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]

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?

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

 

[Mouth]

First one looks a suspect, almost 8GB, can i get rid of it?

It's only 800Mb.
https://xenforo.com/community/threads/problem-table-xf_bb_code_parse_cache.116960/

 

[Neutral Singh]

Oops!

 

[Anthony Parsons]

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

 

[xenfans]

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?

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]

Nope.

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

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

check out https://xenforo.com/community/resources/bbcode-caching-tweaks.5398/

Tweaks XenForo bbcode cache to ignore small posts