Post table is too big, how to reduce or split it?

[chikkoo]

Hi..

My forum has been running for last 7 years, therefore the Post table has grown more than 13 gb. Due to the size of the post table, it is crashing very often, at least once or twice a month.

My server management company suggested me that there should be an in-built function in vbulletin or should be a Mod to move the very old and inactive threads of certain no. of days to move to a different table or database. So that my current table size will become very less.

Does anyone know how to reduce the Post table by moving inactive old threads to a separate database or table.

Thanks for your suggestion to save my forum from crashing every month.

[Paul M]

There is no inbuilt function to do this, you would need to run a few manual queries to copy old threads/posts to "archive" tables and delete them from your live tables. Note that vbulletin would no longer have access to those threads/posts.

[DragonBlade]

Sorry for reviving a months-old thread, but I find myself in a similar predicament here.

However, I'm attempting to modify it somewhat.

I have a 2.2GB InnoDB post table. (We had changed it over from MyISAM after using Sphinx to be able to get rid of the FULLTEXT search.) I find that it is much too large for our little 540 Linode to handle.

Instead, I am planning on creating an "Archive" system, where Mods can toss threads into an Archive forum (and maybe have the Admins able to batch enter them into a new table, unless it would be better to just do it each time a Mod moves a thread there). This new table would be MyISAM, as MyISAM has a higher read rate if I am not mistaken than InnoDB. Since there would not be very many writes to it (only when Mods archive a thread), I would not have to worry about table locking issues, which is why I considered InnoDB in the first place.

I say "this table," but it would actually be two tables: one for a post_archive and one for a thread_archive. It would take the content from the InnoDB "live" tables, insert it into the "dead" MyISAM tables, and kill the shit on the InnoDB "live" tables.

I would have to configure another search index (or dozen, lol) on Sphinx, I would need the two tables, and I would have to replicate the "showthread" and "showpost" pages for the archive... Am I missing anything here?

Also, has there ever been a Mod written for this? I'd be happy to share my steps if not, and my results.

[drjarmin]

Do mySQL db's ever need 'shrinking'? Is there a function to do this? Mine is also growing too big, but not sure why.

Thanks

[mikejp]

We would love such a mod.

The other part that it would have to do, is redirect the users to the archives from the old URL.

Have you started your project? We might be able to assist in it as well if you would like.

Mike

[kris]

chikkoo , did you find any solutions ?

DragonBlade is there any progress in your development ?

We need to find good solution to this problem, big size of my post table keeps me away from adding more mods to my forum.

[Amenadiel]

MySQL 5.1 supports table partitioning. BUT, you can't partition a table with fulltext indexes, so there's the catch.

[raywjohnson]

A possible solution: A second install of vB.

Create a product for both the Live and Archive installs. (but have them use the same database, with different table prefixes)

The Live product:

• daily cron (Scheduled Task) to do the archiving
• have a cutoff (in days) for what threads will be archived (using the date of the last post)
• allow the marking of threads "no archive" to prevent archiving
• ignore archiving of sticky threads

The Archive product:

• daily cron (Scheduled Task) to clean up the archived threads/forums (i.e. build_thread_counters, forums via build_forum_counters and build_forum_permissions)
• lock down most scripts on the arcive install ( only these would need to be accessible: index, forumdisplay, faq, search, image, showthread, showpost, actions, archive, misc )

If you use Sphinx Search, you would have to do a seperate install for that as well.

This would need to be able to detect changes to the thread tables (and post table) due to other products.

To make this work flawlessly, you would need to find a way to allow access to the Archive by members that are already logged into the Live install.

If I had the time, I would see if this could actually be done.

--RayJ

[Marco van Herwaarden]

That would also need a second license.

[Carnage]

My instinct on how to deal with this would be two or more tables

archive_0_post, archive_1_post ... archive_n_post and live_post

Modify your board to make new posts to live_post; periodically move posts from live_post to archive_n_post based on last use (eg if they are in a thread that is updated often leave them there even if they themselves are fairly old)

Create a view called post joining all the archive tables and the live table the view should be updatable so post edits etc will still work. I don't know how sphinx deals with indexes, you may have to add indexes for each table separatly or have an index on the view.

I don't know what the performance will be like as I don't know exactly how mysql deals with views - it may well get worse but in your situation, this would be the first solution i'd look at in detail as it requires the least code edits/loss of functionallity.