So last night the web server Blog Buzz Machine is hosted on slowed to a crawl. As the site grows I was seeing more and more slowdown out of both the DB and Web servers. I'd been doing a ton of research on MySQL database tuning in the hope of squeezing some more performance out of the 2 little machines I'm using to run the site. I realized that I stupidly created the database for the system using MyISAM for the storage engine. The plus to it was it supported FULLTEXT indexes which would prove useful for the search side of things. Sadly though after much beating up of the database server I realized that the way MyISAM was working, it was locking up one of the main tables for selects, causing any updates or inserts to queue up (causing maximum connection issues) which ended up slowing the server down. I started looking for other options. I started playing with InnoDB about a week ago, it's a bit more like Oracle (with row-level locking/transactions) and I came across several articles on the wonderfull-ness of InnoDB so I figured I would give it a try.
What a change....last night I finished moving the main tables to InnoDB and the difference is like night-and-day. My only gripe is that InnoDB doesn't seem to support FULLTEXT indexes so I'll need to figure out something with that. But hopefully the system as a whole seems a bit quicker, plus we're up to over 475,000 pinging blogs now. This little programming experiment has turned into a whole lot of work!