Help with deleting 2 million posts efficiently in MySQL!

[dreamygirl]

I have a running VB3 forum and really need to delete around 2 million posts from it.

A few weeks ago I was researching and found two different forum owners who had written custom scripts to do this. (They didn't publish the scripts.) However, now I can't find those posts again. I believe those forum owners said they found it was much better to use a shell script to directly acess the database. One of them said the script was not hard to write and was quick to perform the tasks.

I could use any advice on how to accomplish this task. I would like to minimize the downtime.

It seems I would stop mysql, backup the database, run the custom script, then optimize the tables, and put the site back online?

It seems wise to get a custom script instead of simply running SQL commands and dropping anything with an old date from the posts, attachments, polls, and thread tables? Or can I simply do this? I would also want the search index(es) and other parts "slimmed down". (I did not want to purge old users however, especially if they ever made a post... including deleted posts.)

I do want to delete the posts without reducing the users' post counts as well.

Thank you.

[FreshFroot]

Why can't you just turn off the forums. Then do a mass prune?

Deleting 2 Mil posts won't be nice on your server.

I'd probably do it at a very low peak time eg. late over night and just mass prune based off date. The repair and optimize the tables and you should be ok.

[dreamygirl]

I had read it wasn't fast and efficient enough. Can someone else tell me if that will work?

I was getting time outs just trying to delete 1000 posts at once. I know that I have to increase that timeout in the config and maybe on the server (but I am not sure the host will allow that).

[FreshFroot]

Oh right forgot about the timeouts. Most hosts won't let you change it. That is unless your own your own dedicated server. If your on shared or reseller, then most likely no they wont allow it.

Well what you could do is is use phpmyadmin. And delete old threads and limit it to sections. So maybe delete 10,000 at a time or less. I don't know if phpmyadmin has a timeout limit as well.

Then to fix things you could rebuild the thread indexes to optimize.

[dreamygirl]

Right, I don't think I'd have issues deleting them in phpMyAdmin. However, isn't it more complex than just deleting the old posts? Like my first post said... I think I'd be leaving remnants unless I also deleted from the search index table (I think there's two or three of these?), the polls, attachments, posts table, threads table, etc.

Well, the limits in phpMyAdmin may be why the others used shell scripts to access the database. If I can only delete 10,000 at a time, I have to do 200 batches... and that would be 200 times the number of tables. So, a script would be a lot better than sitting here all day waiting and clicking another batch. I could probably do way more than 10,000 though.