Author: Tony Bourke
With the introduction of the 2.6 Linux kernel, FreeBSD-5-STABLE, Solaris 10, and now NetBSD 2.0, you might be wondering which of them offers superior database performance. In my previous article, I discussed the tools I chose to test these venerable operating systems and the methodology by which they were tested. The result is this MySQL performance comparison between OpenBSD 3.6; NetBSD 2.0; FreeBSD 5.3 and 4.10; Solaris Express (build 69); and Linux 2.4 and 2.6 (Gentoo-based). Read on for the results.
Before you skip ahead to the results section, bear one thing in mind: the question this article asks is “how would MySQL perform on these operating systems?” It is not, “Which is the best operating system?” In any benchmarking test, there will winners and losers, but the highest performer in one category for a limited set of tests does not a “best” operating system make. Each operating system has its strengths and weaknesses, and a single benchmark can only show a single strength or weakness with a very limited set of parameters.
This article has no agenda other than to show what performance one might expect from MySQL on a number of different operating systems. Such information can be helpful to determine what kind of performance benefit or detriment to expect when running a favored operating system, and allow one to adjust accordingly.
Super Smack 1.2
The Super Smack tests are both CPU/OS-bound, since the data set is relatively small and thus easily cached. Still, as you can see, there was quite a difference in performance between the operating systems. The table type is MyISAM. I invoked Super Smack with the following options:
super-smack /usr/share/smacks/select-key.smack 10 10000
super-smack /usr/share/smacks/update-select.smack 10 10000
For the select-key test, NetBSD 2.0 bested all the other operating systems. Linux 2.4 and 2.6 also did very well, and we see OpenBSD 3.6 beating the other FreeBSD results.
When moving to 2-CPUs, the results change dramatically. Linux is the only operating system to effectively scale for these results, holding a commanding lead. OpenBSD and NetBSD’s dominance fell off, and we can see a weakness in FreeBSD 4.11’s libc_r.
For the update-select tests, Linux 2.4 and 2.6, along with NetBSD 2.0 again took the lead. FreeBSD 5.3 had conspicuously lower showings, and OpenBSD 3.6 and FreeBSD 4.11 with linuxthreads were about average.
As with the select-key tests, only Linux 2.4 and 2.6 kept their leading positions when going to two CPUs. NetBSD 2.0 dropped off as it had before — but not quite as dramatically — and barely holds onto second place behind the Linux results.
SysBench 0.3.1 1M Rows
The SysBench 1M rows provided a good CPU/OS-bound test case. Since I performed one test run to prime the system, almost all of the data was cached by MySQL, so there was little or no disk access.
I used the InnoDB tables for the SysBench tests. SysBench was invoked with the following options:
1M Rows:
To setup:
sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7
--mysql-user=root --mysql-password=mysql --oltp-table-size=1000000 prepare
To run:
sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7
--mysql-user=root --mysql-password=mysql --oltp-table-size=1000000 run
To cleanup:
sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7
--mysql-user=root --mysql-password=mysql --oltp-table-size=1000000 cleanup
10M Rows:
To setup:
sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7
--mysql-user=root --mysql-password=mysql --oltp-table-size=10000000 prepare
To run:
sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7
--mysql-user=root --mysql-password=mysql --oltp-table-size=10000000 run
To cleanup:
sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7
--mysql-user=root --mysql-password=mysql --oltp-table-size=10000000 cleanup
Here we can see both the 2.4 and 2.6 Linux kernels on top, with Solaris 10 and FreeBSD 4.11 with linuxthreads nipping at its heels. NetBSD 2.0 and FreeBSD 5.3 with KSE and linuxthreads performed average, and FreeBSD 4.11 with libc_r and OpenBSD 3.6 performed dramatically worse.
With the addition of a second processor for the 1M rows test, the landscape changes somewhat dramatically. Solaris 10 gets closer to the performance of Linux 2.4 and 2.6, and NetBSD 2.0 drops off significantly. FreeBSD 4.11 with linuxthreads scales relatively well.
SysBench 0.3.1 10M Rows
The SysBench OLTP test with 10M rows creates a 2.6 GB InnoDB file, which can’t be cached (by MySQL or the OS) in the 512 MB of physical memory. As a result it’s a very I/O bound test, and the transaction rate drops across the board.
For the single-CPU tests, Linux again takes the lead with both kernels. Solaris, FreeBSD 5.3 (linuxthreads and KSE), and FreeBSD 4.11 (linuxthreads) score about the same. FreeBSD 4.11 with libc_r performs badly, as does OpenBSD 3.6. The real surprise is how far behind NetBSD 2.0 fell. It had done very well in the other tests, but in this I/O bound test, the results were the worst in the group. I tried several combinations of file system tuning (softdeps, no softdeps, noatime, etc.) but I wasn’t able to get the transaction rate any higher.
Since the 10M row test was I/O bound, adding an extra CPU wouldn’t theoretically help, since there’s only one hard drive spindle to service the MySQL workload — and that’s pretty much how it worked out.
Scalability: overview
Since the results are shown for both single and dual-CPU modes, we can see how well these operating systems scale with MySQL by taking a look at the percentage of delta between the runs. Different workloads lend themselves to CPU scaling better than others. Read operations of course benefit since there’s no table-locking for a read, but that’s only if the data is cached. Write operations are limited by table locks or log writes as well as disk drive spindles, so scalability is limited. By graphing the percentage difference between single and dual-processor results, we can get an indication of how well these operating systems scale with MySQL.
Scalability: Super Smack
For the select-key tests, Linux 2.4 and 2.6 did very well, getting almost double performance with the addition of a second processor. FreeBSD 5.3 with KSE did fairly well too, with FreeBSD 5.3 with linuxthreads and FreeBSD 4.11 with linuxthreads doing so-so. FreeBSD 4.11 with libc_r had no benefit from the second CPU. OpenBSD 3.6 and NetBSD 2.0 suffered as a result of adding a CPU, with NetBSD 2.0 seeing a dramatic drop in performance.
The update-select tests were more subdued due to the write-nature of the tests, but Linux again showed the most gains. FreeBSD 5.3 with both the linuxthreads and KSE threading libraries did fairly well, and surprisingly FreeBSD 4.11 with linuxthreads showed almost no improvement. NetBSD 2.0 again showed a performance decrease, as did OpenBSD 3.6, although to a lesser extent.
Scalability: SysBench 1M Rows
For the 1M Rows tests, you can see that Linux 2.6 and Solaris scaled particularly well, seeing a bit over 50% increase in performance with the addition of a new processor. Linux 2.4 and FreeBSD 5.3 (both linuxthreads and KSE) scaled fairly well, as did FreeBSD 4.11 with linuxthreads. NetBSD 2.0 and FreeBSD 4.11 with libc_r scaled poorly, and OpenBSD 3.6 ended up with a performance loss.
For the 10M Rows, the operations were I/O bound (and thus bound to the single disk), and didn’t see any significant performance difference for any of the operating systems between 1 and 2 CPU runs, topping out at +/-2%. The exception was NetBSD 2.0, which suffered a 9% decrease in performance with the addition of another processor, a drop-off which is consistent with the other test results.
Scalability: overall
Overall, Linux 2.4, Linux 2.6, and Solaris scale the best with the tested MySQL operations. FreeBSD 5.3 (KSE and linuxthreads), and FreeBSD 4.11 (linuxthreads) also scale fairly well. FreeBSD 4.11 with the default libc_r threading, NetBSD 2.0, and OpenBSD 3.6 don’t seem to benefit at all from the addition of multiple processors, and in some cases the results were even worse than the single-CPU configuration.
The Solaris issue
I ran into a strange issue with Solaris 10 for the 10M row SysBench tests. While Solaris had done very well for the 1M tests, it did extremely poorly in the 10M tests, getting the lowest score by far — roughly 3.6 transactions per second, which is lower than even NetBSD 2.0’s results. This was roughly one-seventh the Linux scores, and didn’t seem to make any sense. I checked with Peter Zaitsev, and he put me into contact with Jenny Chen of Sun, and we proceeded to try to figure out what the cause of the bad performance was.
Chen recommended mounting the file system without logging, and to set the sticky bit (chmod +t) to the InnoDB data files and logs. None of those steps seemed to help, so I explored some more.
The final answer I found in the legendary book Sun Performance Tuning: Java and the Internet by Adrian Cockcroft and Richard Pettit. The solution was mounting the data partition with the forcedirectio option. This prevents the file system from being cached at all by Solaris. When I ran the test, the swap drive was completely idle, and the results were dramatic: 21 transactions per second versus 3.6 transactions per second. Oddly enough, setting the sticky bit actually hurts performance by about 1 transaction per second. I get about 20 with +t, and about 21 without +t. It doesn’t make any difference in these tests if the file system is mounted with logging enabled or disabled
either. The noatime option also had no effect.
Although forcedirectio isn’t mentioned in the MySQL documentation, I see that the directio option is specifically recommended on page 161 of Sun Performance Tuning for situations with a large data file (the InnoDB data file was 2.6 GB for the 10M row test). While the book is from 1998 and only covers up to Solaris 2.6, it covers expertly the principles of performance tuning which would apply to all operating systems.
Among the operating systems tested, this caching scenario seems to be unique to Solaris 10. None of the other operating systems, including NetBSD 2.0 (which also had a bad showing), saw any swap activity during the 10M row tests.
Conclusion/final thoughts
Both Linux 2.4 and 2.6 had the strongest showing overall for these tests, dominating just about every benchmark no matter the workload. Scalability for both kernels was also excellent with addition of an extra processor. In fact, I was surprised how well 2.4 had done, as I had somewhat expected 2.6 to show at least a noticeable, if slight, increase over 2.4. Instead, they took turns besting each other from test to test — and in scalability — for a fairly even overall showing.
Solaris 10 had a very strong showing as well, having great speed as well as great scalability. I think the results show that Solaris 10 is a great platform for MySQL. Of course, I didn’t have Super Smack results as I couldn’t get Super Smack to port to Solaris (as detailed in the previous article), so bear that in mind.
NetBSD 2.0 also had a very strong showing, although it was tarnished by two issues. One, MySQL on NetBSD 2.0 doesn’t scale with the addition of CPUs. The results would seem to indicate that it might be wise to run a uniprocessor kernel even if two processors are available. The other issue was the poor I/O performance for the 10M row SysBench test. The SMP scalability issue is easy to understand since, to be fair, this is the first NetBSD release to support multiple processors. The I/O issue is more of a mystery, however.
FreeBSD 5.3 did relatively well in both KSE and linuxthreads mode, although with all the work that’s been done in the SMP and threading realms, I was a little disappointed with the results. Still, it seems that the native threading model for the production release of FreeBSD-5 is ready for prime time, and can replace the long-standing FreeBSD convention of using linuxthreads with MySQL.
For FreeBSD 4.11, however, linuxthreads definitely helped with performance (and in many cases outperformed FreeBSD 5.3). With libc_r, performance lagged far behind linuxthreads for many tests, and there was little scalability. I would say it’s highly advisable to build your FreeBSD 4.11 MySQL binary with linuxthreads.
For all the time it took, I think the tests were worth it. I learned quite a bit about MySQL performance in general, and I’d like to again thank Peter Zaitsev for his methodology recommendations and input, as well as Jenny Chen from Sun for her input.
