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.
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
2400W, small buffer pool
1200W, big buffer pool
1200W, small buffer pool
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:
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?