Multiple MySQL instances on Fusion-io ioDrive

It is known that MySQL due internal limitations is not able to utilize
all CPU and IO resources available on modern hardware.
Idea is to run multiple instances of MySQL to gain better performance on Fusion-io ioDrive card.

Full report is available in PDF

For tests we used tpcc-mysql package, which generates TPCC-like workload on MySQL systems.

• Server hardware: Dell PowerEdge R815
• Storage: Fusion-io ioDrive Duo 640GB MLC. Fusion-io driver version: 2.3.1 build 123; Firmware v5.0.7, rev 101971
• Software: Percona Server 5.5.15
• Client hardware: IBM x3650

Fusion-io ioDrive Duo 640GB MLC card was provided by Fusion-io.

More details on hardware and software configuration are in full report

tpcc-mysql tests were run for following combinations:

2400W, big buffer pool

• 1 MySQL instance, 2400 warehouses (220GB of data), 120GB buffer pool
• 2 MySQL instances, 1200 warehouses (110GB of data) each, 60GB buffer pool each
• 4 MySQL instances, 600 warehouses (55GB of data) each, 30GB buffer pool each

2400W, small buffer pool

• 1 MySQL instance, 2400 warehouses (220GB of data), 64GB buffer pool
• 2 MySQL instances, 1200 warehouses (110GB of data) each, 32GB buffer pool each
• 4 MySQL instances, 600 warehouses (55GB of data) each, 16GB buffer pool each

1200W, big buffer pool

• 1 MySQL instance, 1200 warehouses (110GB of data), 120GB buffer pool
• 2 MySQL instances, 600 warehouses (55GB of data) each, 60GB buffer pool each

1200W, small buffer pool

• 1 MySQL instance, 1200 warehouses (110GB of data), 64GB buffer pool
• 2 MySQL instances, 600 warehouses (55GB of data) each, 32GB buffer pool each

The purpose of different combination of data and memory sizes, was to check
how data/memory ratio affects results.

We used 48 user sessions and we performed 2700 sec long run, gathering data for New Order Transaction each 10 seconds.

That is, for each set of user sessions, we take 270 throughput measurements.

Based on this, we constructed Median Throughput for last 900 sec, to avoid warm-up influence on results.

In conclusion I can highlight:

• Running multiple instances shows good improvement in throughput. 1.4x-1.8x for 2 instances and 1.6-2.4x for 4 instances.
• If you have sharding environment which allows you separate database into multiple instances you may try 2-4 instances setup to get better overall throughput from your MySQL setup

Recently Fusion-io announced new ioDrive2 card with new driver, which promises even better performance for MySQL. I am looking forward to test it.

Disclaimer: This benchmark is sponsored by Fusion-io, but this post is totally independent and fully reflects our opinion.