Foller.me: MySQL Tweaking & Optimization
As I mentioned in the interview with @enked on his website Chidimar.com, I had serious problems with MySQL database optimization on the Foller.me project. The current public stable version (beta-1) is using the MyISAM engine and it’s not holding much data – profiles, locations, geo points for the followers geography.
In the new version (currently dev-1 and hopefully beta-2 in a few days) I changed most of the old tables and added new ones, and using InnoDB this time. You see, it’s not very easy to scan through ~1,000,000 relations for the @mashable account ;) and I bumped into a ~10 second delay before the @mashable profile showed up at Foller.me. Slow query showed that one of the simplest queries caused that slow-mo – it took 6 seconds to execute! The guys at Stack Overflow helped me optimize the query and the two tables I was having problems with, so I came down to ~2 seconds for that query, neat!
Digging further I managed to tune the MySQL server up a little bit (caching, all sorts of buffers, etc – you should definitely take a look at MySQLTuner, it’s a perl script that helps you tune pretty much all the MySQL config) which decreased the query execution time to 1 s. The peeps at Stack Overflow said it’s pretty okay for that kind of query to execute for 3 seconds on over 2 million rows, so I thought that 1 second is final. Phew! :)
Now, think about the MySQL query cache. It doesn’t work in my situation, simply because I shoot UPDATEs and INSERTs at the relations table every five minutes or so (via a cron job), so there actually is a way to perform even higher. Thought of temporary tables, views and triggers (and even stored procedures). Nah.. Simply caching that query would be good, right? I mean if I cache the whole profile for an hour, why wouldn’t I cache the relations result set? Cache the query.. Aha, but I thought slightly further. Why not cache the whole page with memcached? I’ll keep you updated with the results.
Sorry, but commenting on this post is closed. I tend to close comments on articles older that two weeks in order to avoid spam, however, if you still wish to say something, you can always reach me at any time on Twitter:
Tweet to @kovshenin
Sorry for the inconvenience and thank you so much for your patience!