Mat Keep’s blog post on InnoDB-vs-MyISAM benchmarks that Oracle recently published prompted me to do some mathematical modeling of InnoDB’s scalability as the number of cores in the server increases. Vadim runs lots of benchmarks that measure what happens under increasing concurrency while holding the hardware constant, but not as many with varying numbers of cores, so I decided to use Mat Keep’s data for this. The modeling I performed is Universal Scalability Law modeling, which can predict both software and hardware scalability, depending on how it is used.
In brief, the benchmarks are sysbench’s read-only and read-write tests, and the server has two Intel SSDs, 64GB of memory, and 4 x 12-core AMD Opteron 6172 “Magny-Cours†2.1GHz CPUs. It is a reasonably typical commodity machine except for the high core count, which is more than I can remember seeing in the wild. The database was MySQL 5.5.7-rc. I am not sure why they didn’t run the GA version of MySQL for this benchmark. Maybe they wrote the paper before 5.5 went GA.
The following are plots of the read-only and read-write scalability models that I generated, based on these benchmarks.
Read-Only Results
Read-Write Results
The model predicts that the server will continue to provide more throughput as the core count climbs into the mid-50s, although the bang for the buck isn’t very good at that point. Also, there appears to be some bottleneck that hits more sharply than the model predicts at high core counts. It would be great if the benchmark were re-run with the same core counts and with sysbench on another machine, instead of taking 12 cores away from MySQL and giving them to sysbench. That way we could test with 48 cores and see what happens. My gut feeling is that the results will not be as good as the model predicts at high numbers of cores. But as Neil Gunther says, this wouldn’t mean the model is broken; it would mean that there is potentially something to fix in the server at high core counts. Without the model, there wouldn’t even be a basis for discussion.
The biggest thing I want to point out here is the dramatic improvement over just a few years ago, when you could “upgrade” from 4 to 8 cores and see a reduction in throughput. Oracle (and Percona, and lots of others) have done great work in the last couple of years making InnoDB scale and perform better on modern hardware.
Hi Baron
This is an excellent post. And I mean, the interesting stuff is indeed in Mat’s paper, but the way it was titled I didn’t pay any attention to it until now. It is great to see a benchmark where no of cores is the x-variable. Thanks to everyone who has made InnoDB perform so well nowadays!
I do wish they’d started their graphs at 1 core instead of 6 cores. I’m a big innodb fan (we use the willies out of it), but I do have a nagging suspicion that on an old-school one core system and a read-only workload, myisam is going to outperform innodb.
Two places where I have historically seen myisam do well is:
1) Low core count boxes
2) Low memory boxes with IO bound read workloads (myisam uses less space on disk = less IOs)
Mind you I’m not objecting to the overall conclusion (innodb == faster in a concurrent environment on modern hardware), just saying that if they started their graphs at 1 those performance lines might cross :).
I wish they’d benchmarked 1-core for a different reason: the Universal Scalability Law requires it as a reference point for everything else, and in its absence I have to do a quadratic regression to estimate it. But oftentimes a 1-core number has such different characteristics than multi-core numbers that I have to ignore it and do a regression anyway.
MyISAM perf. numbers in this WP are BOGUS since they used DEFAULT params for MyISAM (as opposed to InnoDB) in a server with 48 CPU cores and 64GB of RAM (?!?).
As to InnoDB results at high core counts there may be some scalability bottlenecks in Linux kernel affecting InnoDB scaling (there was a recent MIT paper about Linux scalability bottlenecks on 4CPU 48core AMD server; suggested fixes most likely weren’t in Oracle Linux used for WP tests).
The parameters for MyISAM aren’t always as performance-critical since MyISAM relies on the operating system’s cache heavily, whereas InnoDB bypasses that when correctly configured for large amounts of memory. MyISAM’s main problem in this benchmark is likely to be its lack of support for concurrency, at least on the read-write test. But you raise a good point.
As to the Linux scalability problems, indeed, the entire system is involved. Not only the kernel, but the hardware itself doesn’t scale perfectly, although a high-level benchmark such as this doesn’t reveal whether the bottlenecks are in InnoDB, the server, the kernel, the hardware…
Igor,
I’ve had bad luck with myisam under concurrent load on large hardware, even after tuning it (I think) reasonably well. There are (or were) a couple of internal mutexes in the system around things like the key cache that essentially serialized access. That, in turn, led to really poor performance when I had lots of parallel queries all trying to rip data through the key cache.
These were on large 128G boxes with 16G of key cache and about 70G of free buffer space (for OS disk cache). The other 40G or so was app code. Workload was read-mostly and essentially memory mapped (very little io wait).
For our use cases, we got much better throughput on the big boxes with innodb, even for “read mostly” workloads.
Myisam still held its own on some of my ancient 2-4G and 2 core boxes, but on the bigger hardware it didn’t scale up well for us.
I’m sure we could have milked a little more out of it with multiple key caches and whatnot, but it just didn’t make sense in our case when switching to innodb gave us A) better throughput and B) better stability.
Your workload isn’t mine of course, so I don’t want to overgeneralize, just making some comments on what we found.
On MyISAM – This is a very wrong benchmark for MyISAM Performance, It will use LOCK TABLES to “emulate” transactions. This effectively serializes all operations even though most of them are reads and could be run concurrently.
Having said that I would not expect MyISAM which is in MySQL 5.5 to scale very well because of key buffer contention, even if you would heave read only workload. The segmented key cache in MariaDB should help with this.
Hi,
I have a query on 4 InnoDB tables (read only) which executes on a multicore server in about 11 seconds, however it will take about 15 seconds each when executed by 2 different clients at the same time (the other client use copies of the same tables, so there are no contentions for data).
I was able to solve this same issue with MyISAM tables with multiple key caches, is there a workaroud like that for InnoDB tables ?
Thanks,
Hiromichi
All of our “workarounds” are built into Percona Server 🙂
See https://www.percona.com/doc/percona-server/5.5/
Hi Baron,
Thank you for your tip, I downloaded the latest percona server but ran into the following error.
Any idea ?
Thanks,
Hiromichi
p.s. The server does not like –innodb_adaptive_hash_partitions=8 option on command line.
I don’t know why underscores are converted to hyphens either.
—————————————————————————————————————————————————————
120131 16:00:43 mysqld_safe Starting mysqld daemon with databases from /usr/local/paralleluniverse-0.1/data
120131 16:00:43 [Note] Flashcache bypass: disabled
120131 16:00:43 [Note] Flashcache setup error is : open flash device failed
120131 16:00:43 [Note] Plugin ‘FEDERATED’ is disabled.
120131 16:00:43 InnoDB: The InnoDB memory heap is disabled
120131 16:00:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120131 16:00:43 InnoDB: Compressed tables use zlib 1.2.3
120131 16:00:43 InnoDB: Using Linux native AIO
120131 16:00:43 InnoDB: Initializing buffer pool, size = 1.0G
120131 16:00:43 InnoDB: Completed initialization of buffer pool
120131 16:00:43 InnoDB: highest supported file format is Barracuda.
120131 16:00:43 InnoDB: Waiting for the background threads to start
120131 16:00:44 Percona XtraDB (https://www.percona.com) 1.1.8-20.1 started; log sequence number 1604009568
120131 16:00:44 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable ‘innodb-adaptive-hash-partitions=8’
120131 16:00:44 [ERROR] Aborting
120131 16:00:44 InnoDB: Starting shutdown…
120131 16:00:49 InnoDB: Shutdown completed; log sequence number 1604009568
120131 16:00:49 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
120131 16:00:49 mysqld_safe mysqld from pid file /usr/local/paralleluniverse-0.1/data/desktop-intel.localdomain.pid ended
Hiromichi,
No offense, but I don’t give support on blog post comment threads.