EmergencyEMERGENCY? Get 24/7 Help Now!

Multiple MySQL instances on Fusion-io ioDrive

 | October 7, 2011 |  Posted In: Benchmarks, Insight for DBAs, MySQL, Percona Software

PREVIOUS POST
NEXT POST

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.



PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

6 Comments

  • 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?

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.