I was up until 2:00 am last night working on the database. I wanted to wait until the regular 1:00 am full backup completed so there would be something to go back to if I really screwed something up badly.
Using my training in the scientific method that I gained as a physics student at one of the top research universities in the country, I decided that my strategy would be to blindly poke at things and see if anything happened. And I would have to poke hard. If a little is a good thing then surely a lot must be even better. I made some adjustments to three or four settings and restarted the database. Things seemed to be working better than ever. Of course, it's hard to judge because the site always performs well under the light night traffic.
I went to sleep confident that I had accomplished something good. The sound of the pager at 4 am suggested otherwise. With a strange calmness I was able to form a bit of a theory as to why the database was freaking out and back out those changes. The clues were there earlier but at that time I wasn't aware of what they meant.
I was perhaps a little more conservative than necessary, but I waas able to leave in a change that I think will actually make the biggest difference. And with that, I've consumed pretty much all of the collective knowledge I could glean off the internet for tuning MySQL databases.
The only thing left that I can think would make a difference is to sacrifice some up to the second synchronization gurantees for reduced disk access. Dr. Brain may disagree, but I'm not convinced that an fsync() after each and every transaction is really necessary. But even this will become less of an issue when our new database server with its faster striped disks comes online.
With things working once again, I headed back to bed, only to wake up too soon to the ringing of the alarm clock. I pressed snooze for as long as I could, but I needed to be awake and ready to meet Andrej for breakfast this morning. And so I'm not sure whether I will have enough energy to carry me through the new year or whether I will just crash sometime later this evening.
