Wednesday, March 25, 2009

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


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= \
--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= \
--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= \
--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.

read only 5.0

read-only 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.

Read-write 5.0

read-write TPS 5.1
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.


rpbouman said...


interesting stuff!

Would it be great to have a constant source of these for each new build.

Anonymous said...

Very interesting article. It would be even better with absolute (0-2500) graphs. Relative graphs (1500-2500) don't really show much.

Mark Callaghan said...

Excellent presentation. I need to start using sandbox. Can you mention the my.cnf settings used for file and table logging? 5.1 has the potential to provide much better performance than 5.0.

Giuseppe Maxia said...

The my.cnf setting had only innodb_buffer_pool_size=5G.
And it has the binary log activated in the tests where I measure binlog vs no binlog.
Nothing else.


Massimo Brignoli said...

Ciao Giuseppe,

very good job!! Have you tried to change the I/O scheduler (from CFQ to deadline) to see how this impact the loss of performance with general log and binary log activated?

Dimitriy A said...

Great article. It is nice that you were able to invest so much time to get this detailed results. Thanks.

One suggestion: Change the charts so that they start at 0 TPS.

Anonymous said...

" MySQL 5.0 outperforms MySQL 5.1 constantly. In a read-only situation, it looks like 5.0 is much better than 5.1"

I wonder if the same would be true for when using > 8 threads.

Great article!

Baron said...

The answer is not at 8 threads, but at higher thread counts, yes. See