4.6.12.1 Database Bloating

[filmix]

We  just upgraded to Version 4.6.12.1 from 3.3.4. Most things are working good except for one thing -- the disk space on our server had exploded from 40GB for the old board to now 80GB. We just watched the disk capacity rise from 73GB to 80GB in the span of 12 hours, filling up our 80GB drive and crashing the board. We cleared a little space and restarted the board, but we're seeing a steady rise again. From 95% capacity to 97% in just three hours.

We do not allow content uploading on the board except for profile pictures, most of which were imported from the old board.

Obviously a message board shouldn't need that much disk space. Where is all this content coming from?

Thanks!

Edited April 18, 2022 by filmix

[filmix]

Made an important edit in the first post about content uploading.

[Jim M]

Going from version 3 to version 4 will definitely see an increase of database size just due to different technology, storage engines, etc... How large your database should be would be dependent on many different variables. Are you able to query what exactly is so large in your database?

For instance, if it is the core_log table, this would indicate that there is an error being hit consistently and needs to be resolved. If it is the forums_posts table then this would be expected growth.

 

[teraßyte]

Maybe you have the settings to prune logs disabled? Those can end up taking up a ton of space in the long run. Have a look at your database and check the tables size. Order them by size and post here the names (or a screenshot) of the first one.

[filmix]

Database screenshot attached.

[Jim M]

Those are simply rows and aren't telling the storage consumption (there could be tables with smaller row counts taking more storage). However, I would evaluate your pruning on the Error Logs in ACP -> Support -> Error Logs -> Settings. There are quite a few in there. You may also want to inspect what you're reporting on, you may wish to raise this level to 3 or higher to eliminate storing things where the software is operating per normal (i.e. a user hit a 404 page).

[teraßyte]

The only thing that stands out in that list is ibf_core_error_logs that has a lot of data. I'm guessing the setting to prune those logs is disabled, but other than that those are all "normal" tables to see in that list.

 

EDIT: But yeah, as Jim mentioned that's the rows count and not the table size. You'll have to look at that in phpMyAdmin (or whatever other tool you have access to).

Edited April 18, 2022 by teraßyte

[filmix]

We still have to install phpMyAdmin, but I will check out those log settings for now.

[Adriano Faria]

Could it be the tables created by the Converter (orig_)?

[filmix]

30 minutes ago, teraßyte said:

The only thing that stands out in that list is ibf_core_error_logs that has a lot of data. I'm guessing the setting to prune those logs is disabled, but other than that those are all "normal" tables to see in that list.

 

EDIT: But yeah, as Jim mentioned that's the rows count and not the table size. You'll have to look at that in phpMyAdmin (or whatever other tool you have access to).

That log is not disabled, but it's set to prune after 30 days, and it looks like 99% of the error log entries are for this:

2000 You are not allowed to visit this community.   Ad bots are generating those. I have several IP blocks from known ad spammers banned, and they're trying to get into threads and not succeeding. 23382 pages of error logs going back a month.

Edited April 18, 2022 by filmix

[teraßyte]

1 minute ago, filmix said:

That log is not disabled, but it's set to prune after 30 days, and it looks like 99% of the error log entries are for this:

2000 You are not allowed to visit this community.   Ad bots are generating those. I have several IP blocks from known ad spammers banned, and they're trying to get into threads and not succeeding. 23382 pages of error logs going back a month.

As Jim advised it's best to change the setting to log only errors of level 3 or above then:

29 minutes ago, Jim M said:

Those are simply rows and aren't telling the storage consumption (there could be tables with smaller row counts taking more storage). However, I would evaluate your pruning on the Error Logs in ACP -> Support -> Error Logs -> Settings. There are quite a few in there. You may also want to inspect what you're reporting on, you may wish to raise this level to 3 or higher to eliminate storing things where the software is operating per normal (i.e. a user hit a 404 page).

===

14 minutes ago, Adriano Faria said:

Could it be the tables created by the Converter (orig_)?

Good point. If he converted the database to utf8mb4 before the upgrade all tables are duplicated.

[stu_m]

If you have access to the database via phpmyadmin or something like that then it might be worth looking to see what the largest databases are and working from there 

 

[Marc]

In the first instance, please go to Support in the top right of your admin CP and address any items that are showing there. If there are old tables that need to be removed, this will tell you. Secondly, you need to ensure you are logging only errors that are needed. As mentioned above, this would usually be logging only level 3 and above.

Another item to check would be to go to your system logs within the support area, and ensure you dont have any system errors being logged over and over, which may be causing tables to fill up.

If all else fails, please update your access details on file, and I can take a look to see if there is anything causing you obvious issues there that I can see. You can see how to update your access details here

Https://invisioncommunity.com/4guides/welcome/getting-support-r292/#access

[filmix]

Update.

We deleted the whole database and installed a prior backup, the one from just before we reopened after the big upgrade. We lost that first day's posts after the upgrade.

Last night we got everything set up again, took a snapshot of the disk on the server, backed up the database, and opened the board. The disk usage on the server was a very manageable 36% (out of 80GB), the snapshot was 30GB. The disk storage started rising again as soon as we opened, and it rose to 48% in just five hours. So, we still have a leak.

We closed access the board overnight and the storage flattened out. We reopened this morning and it's rising again.

Marc, updating our access details sounds like a plan. I'll let you know when that's done.

Edited April 20, 2022 by filmix

[Marc]

1 minute ago, filmix said:

 

Marc, updating our access details sounds like a plan. I'll let you know when that's done.

No problem. Let us know and we can see whats happening

[filmix]

Ok Marc, access info is updated. Thanks in advance for your help.

One important consideration here, the board seems to be generating temp files that are not viewable with MySQL/PHPMyAdmin, as if the usage doesn't show up as part of a database.

[Marc]

Please check the access there, as your site requires email login

With regards the temp files, we dont create temp files on your file server in relation to mysql. Your mysql would control things like that.

[filmix]

Info updated. Sorry, an old habit from the old board.

[filmix]

Marc, we're going to close the board just to keep the storage from ballooning. If you need to toggle the board on/off to look at anything feel free to do so.

[Marc]

You have issues on your database instance there. At times (often) when the system queries the database, the server is reporting tables dont exist, such as the topics table, the forums table and more. This is an issue on the database server side, rather than the software, but of course its logging this every time.

The other issue you have is that there is a field named 'choice' which has no default value in the table ibf_core_voters . This is not a default table. So you may be able to remove that column if you are not using that for any 3rd party item. Ensure you take a database backup before you do

[filmix]

Thanks very much Marc!

We made the troublesome 'voters' column NULL and that fixed that functionality.

There are still some dirty queries being made from residual V.3 functions that we can view in the ACP logs. No big deal.

 On the server we changed the MySQL BINLOG settings and sizes are completely manageable now.

Great diagnostic work Sir.

Edited April 21, 2022 by filmix

[Marc]

Very glad we were able to help you get that sorted out for you.