Malfunctioning forum suddenly eating up space and memory on server

[Sono]

Something really weird is happening with my forum recently. I wonder if you have an idea where to look for the solution? So the situation is: my forum database grows with 1GB per year normally, as member count and posting activity is quite steady. On the 26th of August, according to backups, my database size was standing at 7.7GB, the forum file sizes at about 600MB alltogether. Now, 3 DAYS LATER, the forum file sizes are nearly the same, but the SQL size is: 17GB!!! So the database grew 10GB in 3 days!!! And the memory usage by the forum is bursting over the limits of 1GB of my hosting subscription. I am constantly present at the forum, but there was no such rise in number of newcomers or post count that would be responsible for this large amount of data. I approve every registrant manually, and I never approve suspicious elements. At the moment the forum is closed, because it doesn't even work. My account was suspended by my hosting provider last night due to the burst of memory use. They have unsuspended the account for me so I can correct the errors, but the forum doesn't even work now. It is showing a white blank screen, I guess because there is not enough memory for the scripts to run. In the error logs there is no corresponding message. The hosting team, however, found a suspicious thing: in spite of that the SQL is 17GB at the moment, when you log in to myPHPadmin, the interface reports 105MB as overall size of that database. That is very odd. The team also directed my attention to that the table working incorrectly should be the one named: "pretty_urls_cache". For that, myPHPadmin reports "NULL" as size. I assume you know pretty URL, it is a mod to convert dynamic links to static.

So this is the situation. Last night all I could do was to backup and delete the forum files from the hosting account to stop the burst in memory usage. Once I deleted the files, the memory usage dropped. So how to go on from here? What might have happened that resulted in this problem and what can be done to repair the database and stop the high memory usage?

My forum version is: 2.0.6

[Arantor]

The first one I'd be interested in is the size of smf_log_errors.

2.0.6 is also really old; I was still a member of the dev team in 2013 when that was put together (because I think I was the one who did it), and the current version is 2.0.18 - I suspect patching up to 2.0.18 will actually fix your problem. I'm suspecting your host has upgraded the PHP version and the lack of patching means the patches for later PHP version support aren't present causing every page to create errors in the SMF error log table.

[Sono]

I logged in to myPHP admin to check it, smf_log_errors is empty, no data in it. The interesting however is: now the pretty_urls_cache dropped back to 8.2GB. I did not do anything with it. Last night when the hosting team was investigating the error and they noticed the oddity with that table, they tried to repair it, but they said they were unable to do so. Maybe they did something afterwards or it is just a lack of the forum files that resulted in the size to come back to normal I don't know.

If I copy the forum files back what should I do in case the memory usage peaks again? How to cut that? Should I also run repair_settings.php now as the SQL might have been modified last night?

The PHP version on my Cpanel is 5.4 at the moment.

[Arantor]

Firstly, repair_settings.php is primarily about if the values in Settings.php are wrong - this doesn't sound like the case.

As far as 'burst in memory usage' is concerned... if you remove the files, the forum doesn't work so there won't be any memory usage of any kind. Putting the files back is likely to produce more memory usage but I think you might be wise to have a skim read of the last couple of pages of the Pretty URLs mod's thread - there's discussion in there last I looked on whether it was safe to just empty out that table entirely.

[Sono]

Okay, I write into that topic, we'll se if they have any suggestions.

In case I load the files back and the tendency of abnormal database and memory grow continues, where can I osberve which processes result in it? Is there a way to observe them somehow? As I mentioned the error log in the database is empty at the moment.

[Sir Osis of Liver]

Bloated pretty_urls_cache table is a known problem, I've worked on several forums that were crashed and/or suspended due to the table becoming huge.  Servers may choke on the table, resulting in bad info being reported by phpmyadmin.  I believe there's a setting in the mod that prevents this from happening, but too late for that now.  Empty the table, if you can get the forum running uninstall pretty urls, clean up mod code in .htaccess.  It will likely leave other code behind in forum files, but you should be able to get the forum running.  Best solution is to drop pretty urls tables (iirc there are two), upload clean 2.0.18 files, reinstall themes and mods.

[Sono]

At the moment I am not very familiar with how pretty urls operates, but in case I follow your suggestions, will all the posts and pages remain in my forum, or some will be lost due to the uninstallation of pretty urls?

[Sir Osis of Liver]

Your content is in the database, other than removing the pretty urls tables it won't be affected.  Back up the database before you start.