Lessons learned optimizing MySQL

This week I’ve been quite entertained at work optimizing a big MySQL database, 18M rows and about 10Gb of table data for a high traffic web site (11M unique views per month). Not optimizing for perfomance is overkill in this case, so I have been learning and enjoying a lot during these days.

The first thing to note is that database servers are complex beasts (don’t underestimate them) and each and every database provider has its own strengths but also its own caveats.

MySQL logoSooner or later you’ll face a behavior that may seem counter-intuitive and there’s when you must read a lot of documentation. In this case, MySQL Performance Blog and MySQL documentation have been of great help, as well as the now unmantained-but-still-useful Hack MySQL site, which provides great tips very well explained.

Second. As always, doing an 80% optimization consumes 20% of the time and most of it is quite straightforward. EXPLAIN, review results, create index, EXPLAIN again. This way you can do a good job just knowing some indexing basics. Your indexes may be a bit fat, but will do the job.

Then you’ll need to be patient, creating indexes is not always fast, in fact the number, size and heterogeneusy of the columns involved will increase the time it takes to create them. While you wait, the best is to read good books about MySQL or relational databases like: Relational Database Index Design and the Optimizers.

You’ll need to be organized. Creating indexes randomly just looking a couple of slow SELECTs is something, but you must look at the big picture: make an inventory of all the application queries, no matter how unimportant look like because if they attack a big table with a WHERE or an ORDER BY they will perform bad. Fact. This list will help you organize the indexes and see which ones are used and which not and where.

Learn as well what the configuration parameters mean, MySQL has a bunch of them. Just increasing numbers will not always solve your bottlenecks. If you see something strange, there might be a parameter misconfigured and causing bad results.

Sometimes you’ll find yourself frustrated by something that you can’t understand. I found that almost anything in this field has an explanation, even a reasonable one.

Finally, don’t overlook other thinks. Database performance is important but there may be a lot of room to improve in your code, don’t blame MySQL as the root of all problems!

Simple Mini Profiler Glimpse plugin

I recently published on Github a simple small project (miniprofiler-glimpse-plugin) to integrate the results collected by the excellent MVC Mini Profiler created by the people at Stackoverflow with Glimpse, the Firebug for ASP.NET and MVC applications.

Creating Glimpse plugins is really easy (just implement IGlimpsePlugin and decorate it with GlimpsePluginAttribute), implement the methods and reference the plug-in assembly in the project where you are using Glimpse. MEF will do the gluing and the JSON.NET will do its best to deserialize the results in a human readable way.

To use this plug-in, just compile it and reference the assembly in your Glimpse-enabled project. I you have questions, suggestions or bug reports, you can use Github’s own issue reporting system, drop me a line o fork the project and make your improvements.

So, I hope you like it.

Link: miniprofiler-glimpse-plugin [github.org]

Carbon Copy Cloner saved my day

This post is just an agreement to the great people behind Carbon Copy Cloner, a great piece of backup software for Mac.

I trusted Time Machine, and actually it seemed a good program and a very convenient way of making and managing backups but… it does not matter how pretty is if when you need to restore a backup it gives you strange errors.

The whole story is that I wanted to upgrade my Mac hard drive to a bigger one and reading Time Machine documentation, seems that I can use the OSX Installation disk to restore a backup to a new fresh drive. Well, that’s just theory but when doing as instructed I got an “Error -22″ after about an hour restoring… epic fail. Good that I did keep the original drive untouched.

Then I tried donationware Carbon Copy Cloner to create a bootable clone backup of my working hard drive. After a couple of hours I had an external USB that mimicked my original drive. I replaced the hard drive, created a new partition and cloned again the external drive backup to the new one. Now I’m writing this on my upgraded Mac.

So, thanks to Carbon Copy Cloner from Bombich I was able to upgrade without problems. Great job!