In his post benchmarking MySQL Replication with Multi-threaded Slaves Mat Keep sets out to measure the performance improvement achieved when enabling the Multi-Threaded Slave enhancement delivered as a part of MySQL 5.6. His testing apparatus included two Linux servers, one running the replication master, the other running the replication slave. Only the slave was involved in the actual measurements, Keep writes, and was based on the following configuration:

• Hardware: Oracle Sun Fire X4170 M2 Server
• CPU: 2 sockets, 6 cores with hyper-threading, 2930 MHz.
• OS: 64-bit Oracle Enterprise Linux 6.1
• Memory: 48 GB

Two MySQL servers were started on two different hosts, configured as replication master and slave. Ten sysbench schemas were created, each with a single table. 10,000 rows were inserted in each of the 10 tables, for a total of 100,000 rows. When the inserts had replicated to the slave, the slave threads were stopped. The slave data directory was copied to a backup location and the slave threads' position in the master binlog noted. Ten sysbench clients, each configured with 10 threads, were spawned at the same time to generate a random schema load against each of the 10 schemas on the master. Each sysbench client executed 10,000 "update key" statements.

The number of slave workers to test with was configured using SET GLOBAL slave_parallel_workers=<workers>. Then the slave IO thread was started and the test waited for all the update queries to be copied over to the relay log on the slave. The benchmark clock was started and then the slave SQL thread was started. The test waited for the slave SQL thread to finish executing the 100k update queries, doing "select master_pos_wait()". When master_pos_wait() returned, the benchmark clock was stopped and the duration calculated. The calculated duration from the benchmark clock should be close to the time it took for the SQL thread to execute the 100,000 update queries, Keep says he assumed. The 100k queries divided by this duration gave the benchmark metric, reported as Queries Per Second (QPS).

He found that multi-threaded slaves deliver ~5x higher replication performance when configured with 10 worker threads, with the load evenly distributed across the 10 x schemas. This result is compared to the current replication implementation which is based on a single SQL thread only (i.e. zero worker threads). Further results show that the configuration does not scale noticeably from 5 to 9 worker threads. When configured with 10 worker threads, however, scalability increases significantly. The conclusion therefore is that it is desirable to configure the same number of worker threads as schemas.

-More Information -

MySQL 5.6 Replication

Read More ... [...read more...]

		fullsource
Print Email Current Profile Archive Blog Generate newsletter