Can't moderate 100,000 posts - crashes forum!

[Head-Nod.com]

Please Help - Spammers have crashed my forum!

I have quite a large forum, with over 15,000 pages indexed in Google. Russian spammers have started targeting my forums with spam bots, registering dozens of new members at a time and firing off posts to every thread simultaneously.

My moderators all quit because they couldn't handle the moderation, and unfortunately I was slow in realising the scale of the problem and reacting. Now my forum has crashed!

The "vb_post" table is about 1.5 Gb and so unstable it corrupts every time I run an operation.

I have over 100,000 unmoderated posts. I want to bulk delete them, but as soon as I click on the "Moderate Posts" tab in the Admin CP it crashes and corrupts the table.

Trying to Repair or Optimize the "vb_post" table under Maintainance also crashes and corrupts the table.

My hosts can repair the table for me by running a mysqlcheck with the -r option from their end, but they have already done this for me 5 times, and are running out of patients. They have told me to reduce the size of my database or they will have to delete it, but every time I try to prune posts it corrupts the table again!

Can anyone help or offer any advice?

If I could only delete all unmoderated posts, and all posts by deleted users, it would free up about half my post table, but I don't know how to do that without it crashing!

Can I manually delete all unmoderated post by running a mySQL command from command line?

Perhaps I could stagger it, by limitting it to deleting 500 records at a time, so as not to overload the server?

Does this have knock-on effects on other tables, (vb_posthash , vb_postindex, wfvb_thread ???). Would I make things worse by maually purging this table?

What do other people do when their post tables reach 1 GB in size?

Please help me, this forum is over 7 years old, and I really don't want to lose everything!

Neil

[nisarg]

Install a backup....and install antispamming plugin

[Head-Nod.com]

Yeah... unfortunately I do not have a recent backup.
The last backup I have is from about 3 years ago... :erm:

Anyone else know if I can manually delete all unmoderated posts from a mySQL command line?

[markbolyard]

Are you on shared hosting, a vps or a dedicated server? If you are on shared or a vps, I would think it's a memory issue. On a dedicated, it could be the same thing but one thought would be, ask your host to increase the amount of memory on your server (even if you have to pay for it. You could always downgrade next month if you can't afford more ram) If not, my other thought would be, download a mysql backup, install a local copy of vbulletin on your computer using xampp or the like and create a "offline" version of your site, make all the changes you need locally then restore to your live site.

--------------- Added 1296055249 at 1296055249 ---------------

BTW, what version of vbulletin are you running and what's your forum URL (can PM if you don't want to display it publicly).

--------------- Added 1296056179 at 1296056179 ---------------


If I could only delete all unmoderated posts, and all posts by deleted users, it would free up about half my post table, but I don't know how to do that without it crashing!


Have you tried the prune function in the admincp under threads and posts? Your post by deleted users (those users should be Guest now), you could prune those, you can prune threads in moderation... something to try.

[Head-Nod.com]

Thank you Mark.

I had actually considered your idea of downloading a backup and creating an offline version to work on. If all else fails, I will try that, although at the moment I am unable to even backup my database without it timing out and crashing.

I am on a shared hosting, cheap $10 per month plan, and the host have been more than patient with me already! I may upgrade for a month while I get this sorted if it helps though. I don't have a lot of cash, but I don't mind throwing $50-100 at it, if it helps me save my database!

Also, I am running version 3.6.8, and just realised that I posted this in the v4 forums by mistake! My forum is currently offline while I sort this out, so I haven't bothered posting a URL.

[markbolyard]

On shared hosting, I think you are going to be out of luck (But, in my experience you should be able to ask your host to create the mysql database for you, and place it in a non public place for you to download it. When you create your local copy of your site, most likely you won't be able to restore that database without timing out, so search google for something called bigdump. It's a script that can restore your database by spreading the dump out over time or by chunks (Read their site about it... I might not be explaining it too well).

You most likely will need to get on a more stable hosting plan... a vps would most likely be minimal...

When you prune, just try and figure a way to do it in blocks, or chunks... DON'T run any sql commands that would delete post, or you will mess up your database and won't recover it. Get that host to create a backup, download it, and make a copy so you aren't working with the only full backup you have. Hope these suggestions help.

[Head-Nod.com]

Thanks Mark, that was very helpful.

I might try upgrading to a VPS first, to see if that gives me enough memory to cary out the purges online, as I have never setup an offline webserver or mySQL DB before, and it sounds like quite a bit of work. Will definately try to obtain a database backup from my hosts though, that was a good tip.

Is there any way to moderate un-moderated posts in blocks, rather than all at once in the Admin CP?

Or to delete all users who have no moderated posts, only unmoderated posts in the moderation queue?

Thanks!

[combs]

Wish there was a way to do this, those of us with large databases trying to prune things are just screwed. I have about 200K spam posts from only 2 months or so and am trying to restore back to before that. Even when I try to prune forum by forum with even with less than 10K posts it just sits there creating dots all day long. Only thing I been able to do is delete all the spam users, but this didnt even delete the posts just made all their posts by guests

--------------- Added 1300894274 at 1300894274 ---------------

Hey man as I just posted i've had the same problem. I finally fixed it. Hope this helps.

1. First DL this plugin https://vborg.vbsupport.ru/showthread.php?t=244707 then use the settings to find the spam users you wish to delete. You can try by register date. I did it by post count and just unclicked users I wanted to keep. Keep in mind if it doesn't work it's because the search range is too broad. For instance I tested on users with 0-500 posts realized I had to shorten my searches from 3-10 posts 20-50 posts and 50-100 100-200 etc.

2. After you finish deleting users all their posts will still be there as guests. To further get rid of them run a SQL Query of this DELETE FROM post WHERE userid = 0.

That will delete all the guests and their posts from your forum.

3. Go to update counters and rebuild mostly everything. Remove Orphan posts and threads firsts though(from same page).

Then you're all set! I tried 100 different solutions and this is only one that worked for me.

[The Rocketeer]

Thanks Combs that was quite helpful, we got almost 50KK + spam posts, automated bots. i followed your steps and it was very easy to rid of the spammers and delete their posts it seems, however just a way to figure out rebuilding counters so their non existent posts titles are not in the forum.