Jump to content

Large database handling

Guest alon1234

Recommended Posts

I have seen very large database (over 800MB) of various users.
That takes a tall load on the server from a resource point of view,. specifically on RAM.
I would like to see an Archive option that would split somehow the Posts file into
Posts1, Posts2 etc.. as users don't really read 5 months' worth of messages, but if they run a search or try to do an insert (while there are 300 active concurrent users), the server starts to show high CPU loads.
By providing a mechanism that check periodically on the size of the Posts table, and limiting it to 200MB, and then spilling over to a new table, or running an Optimization that would spill over messages that are active to the new tables, it would mean a huge boost to the system as new Inserts are going to be faster and also Searches would be dramatically faster as most searches will be in the "Current Forums" or in the "Archive Forums".

Instead of upgrading to a very expensive database system such as MS-SQL Server or Oracle,. it would mean that you can still stay in the MySQL world and enjoy fast and speedy systems at a fraction of the cost.


Link to comment
Share on other sites

800MB is not very large for MySQL, but none the less, I'd like to see an option for the admin (not the user) to limit forums searched under the simple or ALL FORUMS search. We should be able to limit by forum and cut off date.


800MB is perhaps not a large forum.... well.. you may be right.. but we started February with only 400MB when we moved the forum to the new server.
So given we are up 400MB in 20 days.. and this is not a dedicated server.. and as I mentioned,. we have nearly 300 concurrent users online,.. CPU load has already been jumping to 10.00 at peek times. So this is only going to get worse from here on.
800MB,.. is just the starting point.. 3 months from now I estimate we will be in the 2GB range.. that starts to be large. Searches on such a size with 300 concurrent users will bring the server to it's knees.
We have other sites using the server and the MySQL (about 60 other websites varing in sizes and all using the database).

Regardless of my server's activity,.. if there was a way to split and provide for a spill over of the posts table, it would certainly allow for multiple large boards to reside at a single server with no dramatic stress on the particular server.

And this will a huge improvement of performance for small servers like we have:

A single P4 not a xeon which works fantastic for small-medium websites.. but is nearing it's limits when it comes to the large database (large in the sense that for a regular website.. a database usage - so far - has been somewhere in the 50-100MB range).

This should definatly be an Admin level setting. Perhaps a spill over based on time (a monthly cut-off? 30 days trailing? some other time factor?).
I personally think a 40 days trailing is plenty. When users scroll all the way to the first post they should find post number one with: "Earlier posts in this thread have been moved to the Archives. Click here to switch to Archives".
There should be a script that checks threads posts dates etc and moves the posts to other tables thereby keeping the current-db very much lean and efficient.

I think this is the better solution rather than getting a new hardware. It is certainly cost efficient and will get rave reviews from users worldwide.

Hmm.. pay $160 one time fee with $20 monthly hosting fees...vs. get a dedicated server at $120 a month?

just my $0.02.

Link to comment
Share on other sites

  • 2 weeks later...
  • 1 year later...

Our ould ASP board, Snitz 2000, actually had an archive like this. There were a topics_a table in addition to the topics table, and a posts_a table in addition to the posts table. Exactly the same, just the archive tables contained the old threads. Admins could archive from the ACP manually, by selecting a date.

(Wasn't without complications though. Once you had archived, you couldn't bring them back, not without making a custom script. And the option to let your search include the archives, didn't work either.)

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...