Database size significant increases

[Clover13]

Seems my database size has increased significantly and not aligning with traffic  increases. Any suggestions on where to review to troubleshoot?  

[Jim M]

You would need to look at the database to determine which tables these are. If it is your log tables, you will need to review the logs to see what is happening and correct the issue.

[Clover13]

1 hour ago, Jim M said:

You would need to look at the database to determine which tables these are. If it is your log tables, you will need to review the logs to see what is happening and correct the issue.

It appears core_search_index is the culprit.  What would cause it to be more than half of the DB size?

[Clover13]

Addendum, core_search_index is around 5GB+, which isn't particularly large but it does account for more than half of the DB storage and seems to be growing at an incredibly fast pace while activity grows at a much slower pace.  This was particularly noticeable from the last few upgrades where the size of this table has nearly doubled to tripled in the last 9 months to a year. 

The next largest tables are:

1. forum_posts at 741MB
2. core_reputation index at 448MB
3. core_follow at 99MB

[Jim M]

Is the search index the actually size or the overhead size? If the latter, your hosting provider would need to evaluate your MySQL server configuration as it is not performing “garbage collection” effectively.

[Clover13]

1 hour ago, Jim M said:

Is the search index the actually size or the overhead size? If the latter, your hosting provider would need to evaluate your MySQL server configuration as it is not performing “garbage collection” effectively.

Overhead for all tables just shows a "-"

[Clover13]

If it helps:

[teraßyte]

The index seems way too big compared to the data in the table.

Maybe you have some extra/duplicate/useless indexes you can remove?

[Jim M]

May wish to clear the index and have the system rebuild it. Seems quite odd to be that large. (please take backups prior to doing any of that)

[Clover13]

An optimize on the table knocked it down to 1.1GB

[teraßyte]

That's certainly more reasonable already. 😅

[Marc]

Glad to see that reduced the size. Please also check in Support (top right of your admin CP) and ensure you address anything under the mysql section. We do tend to see less issues like this (although it is indeed a hosting issue) when people are using INNODB that is recommended. I'm wondering if you may not be in your case

[Clover13]

On 11/14/2022 at 4:57 AM, Marc Stridgen said:

Glad to see that reduced the size. Please also check in Support (top right of your admin CP) and ensure you address anything under the mysql section. We do tend to see less issues like this (although it is indeed a hosting issue) when people are using INNODB that is recommended. I'm wondering if you may not be in your case

Support checks were all clean.  And MariaDB was setup to use INNODB for the InvisionCommunity tables.  Wonder if it's our heavy usage of Activity Streams that contributes to that particular table sizing if they are related?

[Marc]

Of course, if you have more streams it would take up more space. However, it would not take up that kind of space, and given it reduced when you optimised, that would indeed be garbage collection on your mysql instance that isn't happening properly.

[Mercury Forever]

On 11/13/2022 at 12:42 PM, Jim M said:

May wish to clear the index and have the system rebuild it. Seems quite odd to be that large. (please take backups prior to doing any of that)

Hi @Jim M

I am having some slowness problems and my core_search_index table is of a large size. When you refer to "clean", is it truncating the table?

[Marc]

When you say "Large", what kind of size are we referring to here? If you rebuild the search index, its done from the admin CP, and will indeed wipe that table and rebuild. However if it's the correct size, it will rebuild back to that same size

[Mercury Forever]

Hello @Marc Stridgen, that board on my base weighs 4.7GB.

[Jim M]

42 minutes ago, Mercury Forever said:

Hello @Marc Stridgen, that board on my base weighs 4.7GB.

As Marc mentioned, it’s not so much size but rather is it the correct size? Did you rebuild the search index and did it rebuild to the same size?

[Mercury Forever]

How do I rebuild indexes? I have not done it.

[Nathan Explosion]