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.

 

Thanks Erik

Thank God we have a group drive around here tomorrow


---
Ellen

 

Cheers for the headsup and for your continued efforts to keep this site up and running

 

 

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.

 

certainly appreciate the work you've put into this.

 

Erik, will the work you did on the database and queries possibly have solved the multi moderation issues we had previously?

 

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.

 

I understand.

You're making lots of notes Erik

 

104 items on my to-do list and counting.