MySQL 5.x performance with logging

There has been much talking about MySQL performance related to logging. Since MySQL 5.1.21, when Bug #30414 was reported (Slowdown (related to logging) in 5.1.21 vs. 5.1.20) I have been monitoring the performance of the server, both on 5.0 and 5.1.
Recently, I got a very powerful server, which makes these measurements meaningful.
Thus, I measured the performance of the server, using all publicly available sources, because I want this benchmark to be repeatable by everyone.
I will first describe the method used for the benchmarks, and then I report the results.

The server

The server is a Linux Red Hat Enterprise 5.2, running on a 8core processor, with 32 GB RAM and 1.5 TB storage.

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)

$ cat /proc/cpuinfo |grep "processor\|model name" | sort |uniq
model name : Intel(R) Xeon(R) CPU           E5450  @ 3.00GHz
processor : 0
processor : 1
processor : 2
processor : 3
processor : 4
processor : 5
processor : 6
processor : 7

cat /proc/meminfo
MemTotal:     32967056 kB
MemFree:      22790272 kB

Method

I downloaded the source code tarball, as released for every version of MySQL, from 5.0.45 to 5.0.77 and from 5.1.20 to 5.1.33. I also took the code for 5.0.79 from the Bazaar tree on Launchpad.
For each version, I did the following:
* expanded the tarball to a directory;
* compiled the code using ./BUILD/compile-pentium64-max;
* built a binary using ./source/make_binary_distribution;
* installed the binary using MySQL Sandbox, assigning 5GB of RAM to innodb_buffer_pool_size;
* Tun the sysbench OLTP transactional test, 8 threads, 1,000,000 records, 1 minute run;

After installing, all database instances where shut down, and only one database server was active at any given time during the tests, to have clean results.
The servers were cleaned up, with all databases and log files removed between tests.
Additionally, all operating system memory cache was erased between tests.

The sysbench commands used for the tests were the following:

sysbench --test=oltp --oltp-table-size=1000000 \
  --mysql-db=test --mysql-user=msandbox \
  --mysql-password=msandbox --mysql-host=127.0.0.1 \
  --mysql-port=$PORT --num-threads=8 prepare

sysbench --test=oltp --oltp-table-size=1000000 \
  --mysql-db=test --mysql-user=msandbox \
  --mysql-password=msandbox --mysql-host=127.0.0.1 \
  --mysql-port=$PORT --max-time=60 --oltp-read-only=on \
  --max-requests=0 --num-threads=8 run

sysbench --test=oltp --oltp-table-size=1000000 \
  --mysql-db=test --mysql-user=msandbox  \
  --mysql-password=msandbox --mysql-host=127.0.0.1  \
  --mysql-port=$PORT --max-time=60 --oltp-read-only=off \
  --max-requests=0 --num-threads=8 run

The not so exciting results first

Using Sysbench with --oltp-read-only=on, MySQL 5.0 outperforms MySQL 5.1 constantly. In a read-only situation, it looks like 5.0 is much better than 5.1.




Additional bad news is that Bug #30414 is still unfixed. Table logging takes away 50% of performance and thus is not recommended. General logs on file, instead, with the ability of logging on demand, is an affordable diagnostic tool.
Partial good news can be deducted by the ability of MySQL 5.1 to perform better with file logging, compared to MySQL 5.0.

The good news

Normal operations in a MySQL server are not read only, and include a binary log. For my second batch of testing, then, I compared performance with --oltp-read-only=off with and without binary logs. In this situation, MySQL 5.1 outperforms MySQL 5.0 in all cases. It's also nice to observe that the performance is improving gradually from earlier versions to recent ones. Kudos to MySQL engineers for their constant dedication to the server improvement.




The even better news is that, in MySQL 5.1, performance gain on 5.0.x has been increasing steadily.

What does this tell you? That MySQL 5.1 is much, much better than 5.0 for online transaction processing, while MySQL 5.0 seems to be better at concurrent read-only operations. My immediate reaction is that I should use MySQL 5.1 for a master and 5.0 for slaves. However, there are two considerations that stop me. First, using a master of a higher version than the slaves is not recommended, although some tests that I made after this discovery show that you can actually do it, provided that you don't use any specific MySQL 5.1 features. And second, the improvement path is such, that I believe MySQL 5.1 is going to catch up on 5.0 for read-only operations. As soon as this happens, I will let you know immediately! Probably, we will know more at the Users Conference 2009.

Summing up

I think that MySQL 5.1 has much to offer in comparison to 5.0.
The tests that I have performed are not definite. Sysbench is just one of the possible tests, and it doesn't mean that your production server will follow the same pattern or have the same performance. It's just an independent way of measuring the server performance, and especially is a repeatable way, which everyone can reproduce.
I warmly invite you to repeat these tests on different machines and let me know your results.