Context
Historically, we’ve used the MyISAM storage engine that MySQL offers by default. It is a pretty simple engine, which doesn’t support transactions or foreign keys. It seems to lack stability as well, as evidenced by how often tables would crash and lose or corrupt data.
The other major storage engine for MySQL is InnoDB. We have recently switched a few key tables from MyISAM to InnoDB with surprising performance gains, and this post will explain how.
It’s all about locks
MyISAM has an important characteristic, which is that it locks the whole table when performing a write operation (UPDATE or INSERT). This seriously hampers scalability, as the table will spend more and more of its time locking clients out as we grow in size. This means that as the number of clients grow, our table will get slower and spend more time waiting on locks than executing queries.
This table lock is clearly not appropriate in heavy-update applications such as our games, where players send many AJAX requests to update their virtual horses, fish, or babies. These tables often spent 95 to 99% of their time locking, and the rest executing. Adding cores doesn’t help here, and we had to start scaling horizontally, by partitionning tables.
How we do “sharding”
In order to limit locking times, we use our own sharding technique: splitting a table into many, and touch only one of them at a time only based on the previously-known value of one field or more.
For instance, a “comment” table for a blog-like website would be split in 20 tables: comment_0, comment_1... to comment_19. The article’s ID is used to determine in which table the comments are stored, using the formula Article ID modulo 20.
This technique would guarantee that when a user posts a comment, only one table locks, while another user posting on another article would lock (hopefully!) another table. It works well, except when your design is such that you can’t really partition the table without rewriting a lot of code and completely changing the way the data is accessed, which could mean changing the game too.
In order to simplify our databases and to gain more flexibility, we considered switching to InnoDB. This was decided mainly for one reason: InnoDB doesn’t lock per table, but per modified row. But first, we had to evaluate the possible performance gains.
Configuring InnoDB
We configured InnoDB with an innodb_buffer_pool_size of 1.5 GB, which is enough to hold our InnoDB data in its entirety. Many parameters can affect InnoDB’s performance, and I won’t detail them here. This presentation goes into a lot of details, and has been very helpful to us.
How not to benchmark
Benchmarking database engines like SQLite used to do is meaningless in a web environment. Their benchmarks used to compare the time needed to insert 1000 rows, for example. This is not at all how we use our tables.
To get a fair comparison of storage engines, we used a specific pattern of queries that corresponds approximately to what we can see on our production servers: For 24 queries, 16 are SELECTs, 7 are UPDATEs, and 1 is an INSERT. We used sysbench as well as a custom tool to apply this pattern of queries in a benchmark and measured the results.
Where SQLite used to measure the time spent inserting 1000 rows in a single transaction, we ran our custom benchmark in a highly parallel environment and measured how the different engines reacted. Such a configuration gives a completely different image, and recommends InnoDB instead of MyISAM.
Parallel benchmarks
These tests have been run on a 2M+row table. Numbers are in queries per second.
SELECT using the primary key:
UPDATE a field using the primary key:
INSERT a row:
As you can see, testing with a single thread like in the old SQLite
tests is a big mistake in this case. Doing so would result in MyISAM
performing (seemingly) better than InnoDB, when it is certainly not the case in our production environment and with our usage.
The
number of queries per second could seem to be much lower for some
operations than for others, but these numbers come from the scenario
described above. For every INSERT, 16 SELECT and 7 UPDATE are executed.
Performance gains
We converted a few key tables to InnoDB. We selected these tables among the ones with the longest locking time, using MySQL’s SHOW PROFILE command to sort them by locking time. With all these lock contentions removed, we experienced a large drop in CPU usage, and much faster response times for our largest and most accessed pages.
Here is a measure of instant page speed, on one of the games. Can you tell on which day we turned to InnoDB? (The spikes are during our maintenance process).
Here is a sliding 7-day window of average page speed, with better detail.
This graph comes from a different game which is why the days don’t match. But even with different tables and different access patterns, the gains are still impressive.