Performance and High Availability

Since Heikki Tuuri and Peter Zaitsev’s presentations at the MySQL User Conference 2008, I really wanted to try MySQL 5.1 with InnoDB plugin in production work to see how it compares to my current setups (MySQL 5.0.48 with integrated InnoDB).

First of all, the upgrade to MySQL 5.1.23-rc went without any particular quirks - I did not have to dump and reload tables, a simple mysql_upgrade script run was sufficient to put my DB to MySQL 5.1-readiness.

When starting a new MySQL instance you know that you have to do some warm-up work before getting any indicators - the buffer pool will be empty of indexes and data so most operations in the first hour of uptime will be IO-bound. You can also use clever queries to speed warm-up time as indicated by Peter Zaitsev on his blog.

Workload Characteristics

It’s always good to know that benchmarks and system or software upgrades can give different results depending of whether you are I/O-bound or CPU-bound and of your hardware platform characteristics.

In our particular test case, the workload is mostly I/O bound (available InnoDB buffer size is about 1/8 of exploitable dataset size) and the hardware platform is composed of 2 x dual-Core Xeon 5148 with 16G of DDR, and 8×15k SAS drives in RAID-0 mode (BBU controller, write-back enabled).

Average thread concurrency is always around the 32 threads mark which is good for our benchmark case because we want to see how MySQL 5.1 and InnoDB perform in superior workload conditions. The queries’ workload is mostly read-oriented : we’re in a case of a traditional web 2.0 database with very few writes and lots of reads.

Production test

The first indicator that really interests me in this case is how the database performs with current workload - QPS (Query per Second) is generally a good indicator and is available in many tools like innotop.

• MySQL 5.0.40 + built-in InnoDB: 2.0k QPS avg.
• MySQL 5.1.23-rc + InnoDB plugin 1.0: 1.5k QPS avg.

As you can see there is a serious performance drop with MySQL 5.1 (about 25% less QPS) which corroborates Peter Zaitsev’s slides from the MySQL UC, noting some potential regressions in 5.1 which lead to a noticeable decrease of performance.

Another indicator which was disappointing was the number of SELECT queries per second, which we graph using RRDTool.

For MySQL 5.0 the number of SELECT per second is usually steady around the 1.2k mark.

With this new installation of MySQL 5.1 and InnoDB, this number was no higher than 900k SELECTs, and the database showed signs of thread congestion at some times : performance dropped to half this number during a few minutes before attaining peak again.

Table compression test

After this rather disappointing start, I wanted to try a new feature of InnoDB plugin : table compression. I went for a 8k compressed page size according to Venu Anuganti’s tests and compressed a 6GB table which is one of the most used tables in our application. The table compression rate was interesting at 73% (I used the method recommended by Ken Jacobs at the MySQL UC: gzip a n.ibd file and see if the resulting gzip file is more than 50% compressed)

In production, the results were promising in CPU usage since we switched from an I/O-bound workload to a totally CPU-bound workload.

• Before (no compression) : 30% user CPU, 30% iowait
• After (using 8k compression on the main table) : 87% user CPU, 3% iowait

Impressive numbers! But there was an outstanding problem with compression: some requests greatly underperformed.

Actually, we have in our application, some requests which I will call “external sorts” which are used to build various rankings. i.e. we do this kind of joins:

mysql> SELECT id FROM main INNER JOIN category USING (id) INNER JOIN ranking ORDER BY ranking.total LIMIT XX;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: category
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 750465
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ranking
type: eq_ref
possible_keys: id
key: id
key_len: 4
ref: id
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: main
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: id
rows: 1
Extra: Using where; Using index


The optimizer will usually reverse table execution order because of the ORDER BY clause and will fetch all of the ids in the category table, then do a temporary and filesort to build the final resultset. This will usually work fine with no compression (a few secs) and will be OK on the application side provided that you store the resultset in memcached.

With compression, I guess that the performance problem is derived from the fact that MySQL will not reading adjacent pages of data in the main table, but that it will be forced to fetch and decompress many non-adjacent pages due to the fact that the main table is not sorted by index, but rather by filesort algorithm. I know that filesort + temporary is not very good query design on large pages of data but for us it was a tradeoff which worked without using compression - looks like I’ll have to make more benchmarking tests on filesorts + compressed data to assess if the decrease of performance is at this particular spot.