Database maintenance this weekend
#1
Administrator
Thread Starter
Database maintenance this weekend
I'll be taking the site down tomorrow from 10AM till 5PM Eastern time (GMT-4) to do some long overdue and much needed database maintenance work. The database can't be changed while this is going on so the site must come down.
The result should be a faster database (and site) and one that is much easier to manage. Sorry for any inconvenience this causes but it needs to be done to make the site more reliable and faster.
The result should be a faster database (and site) and one that is much easier to manage. Sorry for any inconvenience this causes but it needs to be done to make the site more reliable and faster.
#5
Administrator
Thread Starter
I've completed a fairly lengthy database related optimization which should yield significant improvements. I'm sorry for the downtime but it was unavoidable and necessary to make this work.
First I dumped the entire database contents, some 16M rows, to an export file. I then reconfigured the database server from scratch choosing a configuration suited the hardware we are using.
The database of posts, topics, PMs, members, etc. was setup about 6 years ago based on what we had then. It still worked but but at the time we had much less capable hardware and software to run the database on. Many improvements have occurred in that time but we were trapped under design decisions made in days gone by which didn't take advantage of newer technology.
Once that was done I re-imported the database dump to create an entirely new database. I got that all done by just before 5PM and got the site back online.
In addition to simply dumping and recreating the database I also started to monitor those database operations which were taking an extra ordinary amount of time. Retrieving some topic pages was taking in excess of 30 seconds. I've been experimenting with ways to improve those queries and managed to come up with some very significant improvements on the order of 1000x. Some queries which took 60 seconds have been redesigned and in some cases I broke them down into multiple simpler queries and now complete in just a few milliseconds.
There are still a few problem areas which I will continue to monitor and redesign but I think I got many of the most often encountered performance problems under control (I hope).
Thanks again for your patience. I assure you it was worth the effort.
First I dumped the entire database contents, some 16M rows, to an export file. I then reconfigured the database server from scratch choosing a configuration suited the hardware we are using.
The database of posts, topics, PMs, members, etc. was setup about 6 years ago based on what we had then. It still worked but but at the time we had much less capable hardware and software to run the database on. Many improvements have occurred in that time but we were trapped under design decisions made in days gone by which didn't take advantage of newer technology.
Once that was done I re-imported the database dump to create an entirely new database. I got that all done by just before 5PM and got the site back online.
In addition to simply dumping and recreating the database I also started to monitor those database operations which were taking an extra ordinary amount of time. Retrieving some topic pages was taking in excess of 30 seconds. I've been experimenting with ways to improve those queries and managed to come up with some very significant improvements on the order of 1000x. Some queries which took 60 seconds have been redesigned and in some cases I broke them down into multiple simpler queries and now complete in just a few milliseconds.
There are still a few problem areas which I will continue to monitor and redesign but I think I got many of the most often encountered performance problems under control (I hope).
Thanks again for your patience. I assure you it was worth the effort.
Trending Topics
#8
Administrator
Thread Starter
No. The MM feature recounts the posts each time it moves things and that is an expensive operation. To fix that the code needs to change so that it adds and subtracts the posts it moves from the counts rather than lazily recounting each forum from scratch. I've made a note.