The price of safe data - Benchmarking semi synchronous replication

Some time ago I wrote about MySQL 5.5 semi-synchronous replication. Since then, I have wanted to benchmark the overhead of semi-synchronous replication with a decent server. Now the occasion presented itself, thanks to some related business that I had to benchmark, and thus I did a few simple runs with and without semi-synchronous replication enabled, to see the impact of this feature on performance. If you haven't read the article on semi-synchronous replication, the bottom line is that, with this feature enabled, the master waits until at least one slave has acknowledged receipt for the data before returning a positive result to the client. This means that for each commit there are two network calls between master and slave. My gut feeling was that this feature would be costly in terms of query response time, although I was not prepared to such a big impact as I found out in my test. I needed a substantial set of data, and I got it by exporting the employees table from the employees test database, using one INSERT per record. Thus, I had about 300,000 records, which are a fair amount for this kind of test. Had I sent the records in a big multiple insert chunk of 10,000 records each, I would have had only 30 commits, which would not have been easy to measure. So, here goes.

regular replication

$ time mysql < employees.sql 

real 0m27.997s
user 0m1.394s
sys 0m1.046s

semi-synchronous replication

$ time mysql < employees.sql 
real 1m24.277s
user 0m3.842s
sys 0m6.270s

Semi-synchronous replication was three times slower than regular replication. The test was taken using one master in one host and one slave in two more hosts. The measurements were the same if I had only one or both slaves enabled. Using row-based replication instead of statement-based did not make any substantial impact. Now my question is: who would be prepared to accept such a performance impact for the sake of more data safety? Data is important, but response time to customers is also important. Your mileage may vary. I know many customers who would think twice before accepting this onerous trade off. I am curious to know what experience others have had with this feature, and how much performance they are willing to sacrifice for safety.

Quick benchmarking trick

I have been doing quite a lot of benchmarking recently.
I needed to find a safe way of measuring the time spend by the database doing a long task, like catching up on a huge backlog of accumulated replication updates. The problem with measuring this event is that I can record when it starts, but I can't easily detect when it finishes. My initial approach was to monitor the database and count the tables rows to see when the task was done, but I ended up affecting the task performance with my additional queries. So I thought of another method.
Since I had control on what was sent from the master to the slave, I used the following:
The initial time is calculated as the minimum creation time of the databases that I know are created during the exercise. Let's say that I had 5 databases named from db1 to db5:

set @START = (select min(create_time) from information_schema.tables where table_schema like "db%")

Then, to make sure that I catch the exact moment that the task is finished, I added to the master a command for each database:

create table db1.last_table (i int);
create table db2.last_table (i int);
create table db3.last_table (i int);
create table db4.last_table (i int);
create table db5.last_table (i int);

To know if the task is done, I query the database as follows:

select count(*) from information_schema.tables where table_schema like "db%" and table_name="last_table";

If the count is less than 5 (the number of databases that were in my binary logs), I wait more.
Finally, when the count matches the expected one, I get the end time:

set @END = (select max(create_time) from information_schema.tables where table_schema like "db%" and table_name="last_table"');

Now I have two values, @START, and @END

select timediff(@END,@START) as elapsed;
+----------+
| elapsed  |
+----------+
| 00:09:44 |
+----------+

It does not matter if I query the database immediately, or hour after coming back from my errands. Using the table creation times makes sure that I get a clean start and finish time.
I put all the above in a script, and I can check the elapsed time without fear of mistakes.

MySQL 5.4 performance with logging

About a month ago, I published the results of MySQL 5.x performance with logging. The results covered several versions, from 5.0.45 to 5.1.33. Among the conclusions of the post was the consideration that MySQL 5.0.x is faster than MySQL 5.1 in read only operations. I hinted that better results may come for MySQL 5.1. When I wrote that post I had, in fact, an ace up my sleeve, because I had already benchmarked the performance of MySQL 5.4, using the same criteria shown in my previous post. The results, as you can see from the charts below, tell that you don't need to downgrade to 5.0 to achieve the performance you want, but MySQL 5.4 may be the right choice.

MySQL 5.1 + 5.4 read-only logging performance

MySQL 5.1 + 5.4 read-only logging performance

MySQL 5.0 read-only Logging performance

MySQL 5.0 read-only Logging performance

MySQL 5.1 + 5.4 binlog performance

MySQL 5.1 + 5.4 binlog performance

MySQL 5.0 binlog performance

MySQL 5.0 binlog performance

A piece of trivia. On April 9, users of MySQL Sandbox may have noticed that I left a clue of what was about to happen. Among the accepted versions, you could see "5.3, 5.4. 5.5" :-)

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.