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

[joshskeety]

It astounds me that VB hasn't thought of this yet..

I saw one VB admin just say.. "Why not move everything to an archive forum (meaning add another forum to your forum called archive)." And I was like.. REALLY? So the vb post table stays JUST as large if you do that, what have you archived? Nothing..

I actually brought this to their attention a few years ago and was basically laughed at. I had nearly a 4 gig post database that made backing up the forum impossible, made searching posts horrible, any repair to the database took HOURS. I am a Systems Engineer and EVERY system we use including just PST's have an archive or some way to relieve a database. This is typical in almost anything that uses a SQL database backend.. Why VB hasn't come up with an easy solution or why no hack has been created to do this is beyond me.

The solution is easy in my mind. Allow a connection to a SECOND database and set up a VB cron job automatically (like in the options section) to 1 in the morning, or something similar to move posts from (X) days from one database to the other. Allow the users table to access that database and use VB Search function to pull up those archives. That way you keep your main database at a minimum, backing up is a cinch, your archive you can backup like once a month MAYBE. Your MYSQL runs better, your forum runs better and repairing or optimizing your tables take 2 minutes rather than 2 hours..

Really, this is something that should of come standard on like VB version 2. The fact that there isn't even a MOD just astounds me.. My forum is pretty big and massive, but what about guys who most likely have post tables that are 20-50 Gig in size.. How do they do it?

I know my solution was to piss off all my users and deleted all our posts from 2004 until 2010.. It was a damn shame to lose all of them, my statistics got all screwed up, the users can't go back and find a post. We had 3 GREAT users who passed away and we couldn't go back to their posts..

If I knew how to code, I would have made this 7 years ago..

[raywjohnson]

The issue with a large MySQL table is when a new record is added (i.e. the table it written to). The table has to be locked before the new record is added. This takes memory and processor power to accomplish.

Reading data from a table is quick and easy (not nearly as memory or processor intensive).

Moving old records (thread/posts) to a second table (that is read only) allows your active table (read/write) to be small, and your archive table (read only) to be as large as you need.

Also, the use of Sphinx Search will make searching the large table simple (and very fast).

It is not the optimal solution, but for those of us on a budget (and cannot afford to pay for additional MySQL servers w/huge amounts for RAM), this works well.

The large table is still unwieldy for many normal operations. A better solution would be to have a table for every year of threads and posts. Or just divide it by size. Say, 1GB max per archive table.

Since I set this up, my forum and server run smooth and efficient. Even as the archive table has grown to 7GB+.

--RayJ

[Zachery]

Actually, that's not an issue with MySQL tables, that is an issue with MySQL tables in the MyISAM format. Innodb has row level locking.

The large post table isn't a big deal.

[raywjohnson]

Yes. Thanks!

I forgot to mention I was talking about MyISAM tables.

When I first looking into changing to Innodb, it required a prohibitive about of RAM (as far as cost goes). I think that has changed, especially with cloud servers, and the cost is now very reasonable. So even for large multi-gigabyte tables, you can afford the RAM needed to run Innodb.

--RayJ

[AusPhotography]

Which version of vB?
We use InnoDB on vB4.2.1; MySQL 5.5 without any issues.

[raywjohnson]

v 3.8

I will be upgrading to v4 (or maybe 5) soon. After I convince the membership. And setup a cloud server (possibly with a separate MySQL server). Then I will use the InnoDB tables.

--RayJ

[joeychgo]

How many posts do you have to get a 7gb post table?

I have 2.5 million posts and the post table is only 1.6gb. I have no issues with how it runs.

[Paul M]

Originally Posted by joshskeety

Really, this is something that should of come standard on like VB version 2. The fact that there isn't even a MOD just astounds me.. My forum is pretty big and massive, but what about guys who most likely have post tables that are 20-50 Gig in size.. How do they do it?

There is absolutely no reason for this to be "standard", and the fact no one has spent a load of time trying to make a Mod certainly doesnt astound me.

I have two forum databases in front of me, both have between 2.1 and 2.5 million posts in them, both Post tables are less than 2GB. To get tables the size you mention would reqire forums with 20/30 Million posts (or more). You could probably only find 2 or 3 of them in existance, and they simply make sure they have the hardware to cope with such large databases. As your forum gets bigger, you need to increase your system environment to keep up.

[joeychgo]

Originally Posted by chikkoo

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.

Originally Posted by raywjohnson

Since I set this up, my forum and server run smooth and efficient. Even as the archive table has grown to 7GB+.

That's why IM confused. The OP is talking 13gb and raywjohnson is talking just his archive is 7gb. Im wondering what is causing these tremendously large DB tables, or if there is something wrong with their setup.

[final kaoss]

Really? Is that really what you think? In my forums niche, we have about 25K posts and the post table size is already at 250+ MB So at that rate it'll hit 2GB at around 200K posts

Originally Posted by Paul M

To get tables the size you mention would reqire forums with 20/30 Million posts (or more).