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.
If log size is same (4 GB) for 1, 2 and 4 instances this is unfair advantage IMHO.
Jordani,
What is unfair in having the same log size?
If the database is shardable wouldn’t it make more sense to put the shards on multiple smaller nodes instead of putting multiple MySQL instances on 1 big server?
The total costs of several small servers would be lower than that of 1 big server, and the performance would be at least as good, if not better.
Andy,
For curiosity I went on dell.com
R815 with with RAID10 on 6 disks and 4 CPU and 160GB of RAM: $10,800
with 2 CPU and 96GB of RAM: $7,300
So it is not cheaper to ran 2x smaller servers.
Also biggest problem is power consumption and rack space, which you pay monthly,
and that will be more costly for 1-3 years of running servers.
Interesting… I recently did some similar benchmarks using sysbench and came up with differing results. Measuring QPS, and skipping transactions, 2 instances yielded about an 8% increase over 1 for an in memory data set and about a 5% increase for a data set 5x the size of the buffer.
For 4 instances logs are 16GB so I think there is more write combining compared to 1 instance with 4 GB log.
If total size of logs is same I’m expecting to see improvement but how much?