So,
It's been a crazy, crazy year! December has just been
nuts - as evidenced by the fact that this is my first and probably only blog for the month
One definate highlight of December was the Ballistic/CGS Christmas get-together. We managed to get almost everyone in the company here in the same week, have a stack of productive meetings*, and then have a great big party. I got to meet our Advertising guy, Scott Stahley, in-person for the first time - I must have exchanged a thousand emails with the guy, and dozens of phone calls, but had never seen him!
( * Yes, they really exist :P )
On to the bad news, we had a disk fill up rather unexpectedly. You guys have just been submitting too much art!! We've added a new drive with 184GB usable space, which we hope will last a bit longer. Unfortunately, the server that it filled up on is a key component in many of our image-related tasks, so NVArt and CGPortfolio both had oddness while we sorted out the replacement drive. We're waiting on some quotes for our 2008 server purchases... I have some lovely plans for server upgrades I'm hoping we'll get to implement later in 2008
Day by day, CGTalk seems to run slower. Isn't it the way? More people, more posts, more searches, more of everything. We're always looking at improving speeds, and have implemented a huge swag of changes through 2007 to that end - primarily memcache implementations and index optimisations.
Recently, CGTalk posting performance in particular had started to drag... down... slower... and... slower...
The obvious solution was to back out the "low_priority_updates" setting in MySQL; it'd been placed in months ago to improve browsing speed (at the expense of posting speed). Back then, the change to posting speeds wasn't that great, but now it was really noticable. Of course, that immediately made browsing far slower.
With the extra stats information we're collecting now (as well as a good chunk of extra knowledge we've gained through the year) I managed to determine the number one cause of issues was MySQL's full-table lock on the "thread" table. UPDATEs would wait on SELECTs, and SELECTs would queue up behind the UPDATE. Once the queue hit a few hundred connections, everything gives up and goes into maintenance mode.
A quick change to the table to turn it into InnoDB (to switch to row-level locking instead of table-level locking) and everything was suddenly... even worse. I'd tried InnoDB some time ago, but decided against it due to the extra performance problems we'd experienced at the time. Thankfully this time we were more on top of
why the performance was so crap - MySQL's default configuration only allocates 8MB to the InnoDB buffer pool. (Docs suggest up to 50%-80% of system RAM...). While 8MB is fine to run the couple of tables in the CGS Wiki that are InnoDB, it's nowhere near enough to run the thread table too. A quick bump up to 100-200MB and everything is where we are today.
Short version: If you run a decent-sized forum, you can make your thread table InnoDB to alleviate locking issues, but
don't use MySQL default options!
So, performance isn't as good as I'd want. (Here's a secret, it probably never will be - I'm a picky guy
). But posting is now usable, and browsing speeds aren't the ridiculous parody of usability that they had been during some of the tests I'd done. We also shouldn't have any of the "the thread has three pages but I can't get past page two" problems, which were caused by the locking stopping thread postings from completing 100%.
We still have occasional locking issues, mainly centred around the user table and the gallery_entry table; early next year I'll be spending some time working out what settings are best for those.
For now though, folks, have a very Merry Christmas. See you all in 2008, I hope!
