Clover13 Posted November 12, 2022 Share Posted November 12, 2022 Seems my database size has increased significantly and not aligning with traffic increases. Any suggestions on where to review to troubleshoot? Link to comment Share on other sites More sharing options...
Jim M Posted November 12, 2022 Share Posted November 12, 2022 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 Link to comment Share on other sites More sharing options...
Clover13 Posted November 12, 2022 Author Share Posted November 12, 2022 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? Link to comment Share on other sites More sharing options...
Clover13 Posted November 12, 2022 Author Share Posted November 12, 2022 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: forum_posts at 741MB core_reputation index at 448MB core_follow at 99MB Link to comment Share on other sites More sharing options...
Jim M Posted November 12, 2022 Share Posted November 12, 2022 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. Link to comment Share on other sites More sharing options...
Clover13 Posted November 12, 2022 Author Share Posted November 12, 2022 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 "-" Link to comment Share on other sites More sharing options...
Clover13 Posted November 12, 2022 Author Share Posted November 12, 2022 If it helps: Link to comment Share on other sites More sharing options...
teraßyte Posted November 13, 2022 Share Posted November 13, 2022 The index seems way too big compared to the data in the table. Maybe you have some extra/duplicate/useless indexes you can remove? Link to comment Share on other sites More sharing options...
Jim M Posted November 13, 2022 Share Posted November 13, 2022 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) Link to comment Share on other sites More sharing options...
Clover13 Posted November 13, 2022 Author Share Posted November 13, 2022 An optimize on the table knocked it down to 1.1GB Link to comment Share on other sites More sharing options...
teraßyte Posted November 13, 2022 Share Posted November 13, 2022 That's certainly more reasonable already. 😅 Clover13 1 Link to comment Share on other sites More sharing options...
Marc Stridgen Posted November 14, 2022 Share Posted November 14, 2022 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 Link to comment Share on other sites More sharing options...
Clover13 Posted November 15, 2022 Author Share Posted November 15, 2022 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? Link to comment Share on other sites More sharing options...
Marc Stridgen Posted November 16, 2022 Share Posted November 16, 2022 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. Clover13 1 Link to comment Share on other sites More sharing options...
Mercury Forever Posted September 11, 2023 Share Posted September 11, 2023 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? Link to comment Share on other sites More sharing options...
Marc Stridgen Posted September 11, 2023 Share Posted September 11, 2023 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 Link to comment Share on other sites More sharing options...
Mercury Forever Posted September 12, 2023 Share Posted September 12, 2023 Hello @Marc Stridgen, that board on my base weighs 4.7GB. Link to comment Share on other sites More sharing options...
Jim M Posted September 12, 2023 Share Posted September 12, 2023 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? Link to comment Share on other sites More sharing options...
Mercury Forever Posted September 13, 2023 Share Posted September 13, 2023 How do I rebuild indexes? I have not done it. Link to comment Share on other sites More sharing options...
Nathan Explosion Posted September 13, 2023 Share Posted September 13, 2023 Mercury Forever and Marc Stridgen 2 Link to comment Share on other sites More sharing options...
Recommended Posts