MySQL Database performance

There are two popular SQL database benchmarks that are able to perform concurrent queries: super-smack and sysbench.

Investigation of the super-smack workload shows that it performs large amounts of I/O in units of a single byte, which leads to huge numbers of context switches.  It is largely a microbenchmark of context switch performance, and for this reason it is difficult to relate super-smack performance results to real-world database performance.

sysbench appears to provide a more realistic workload.  It is documented here.

Initial progress in improving mysql performance on 8 CPU systems was reported here (Feb 2007).  At that time FreeBSD out-performed Linux by a large margin at high loads.  As of Linux 2.6.22 (Fedora 8) they have fixed the most serious scaling problem but they still have 15% lower performance than FreeBSD on this workload.  Linux 2.6.23 incorporated a new "Completely Fair" Scheduler which performs significantly worse than 2.6.22 on this workload.  2.6.24 has not yet been evaluated.

MySQL 5.0.51 is currently used for performance comparisons.  This is the best performing version of MySQL that has been tested (there have been steady improvements through the 5.0.x series), but there are still serious bottlenecks within the MySQL application.  Above about 8 clients there is high contention on pthread mutexes within the MySQL application itself, which causes a performance drop at higher loads.  MySQL 5.1 appears to perform significantly worse than 5.0.

The MySQL configuration file used may be found here.  The most important setting is the innodb_thread_concurrency=0; this works around a serious scalability problem in how innodb handles concurrency (if non-zero, every operation acquires a mutex to check whether the concurrency limit has been reached, which effectively serializes operations long before the concurrency limit is reached).  The innodb backend was used as this has better performance and scaling than myisam.

sysbench command-line options are

sysbench --test=oltp --num-threads=${i} --mysql-user=root --max-time=120 --max-requests=0 --oltp-read-only=on run

All other options are unchanged from the defaults.  In particular the sysbench client is on the same system as the database server, and they communicate via UNIX domain socket.  This is to isolate performance of the database backend from other layers such as TCP and the ethernet driver.  When starting a test run a 2 minute warmup with 8 threads is performed first and the results discarded.

Performance comparison graphs

Read-write performance

When testing write performance it is important to make sure you are not just benchmarking your disk hardware.  Options include storing the database on a memory disk, if the test machine has sufficient RAM, or using a suitably fast disk array and verifying that it is not I/O bound.

FreeBSD 7.0 has not yet been optimized for concurrent filesystem write performance.  The main limitation is the implementation of the lockmgr(9) primitive, which is mostly used for locking of access to the filesystem layer.  The current (and historical) implementation is designed for a UP system, but is currently being rewritten.  It is expected to bring significant performance improvements once this work is complete.  It may be possible to incorporate these changes into FreeBSD 7.1.

Update (1 June 2008): The lockmgr work is complete and stable in 8.0. However as is often the case, fixing one problem brings another to light. Work on the buffer cache in the 8.0 branch is expected to bring further dramatic performance improvements.