Monday, December 16, 2013

Single thread performance regression in 5.6 - Replication

 At Facebook, we have upgraded most of MySQL database tiers to 5.6, except very few tiers that have a special requirement -- very fast single threaded replication speed.

 As Oli mentioned, single threaded performance is worse in 5.6. The regression is actually not visible in most cases. For remote clients, the performance regression is almost negligible because network latency is longer than 5.1->5.6 overhead. If clients are running locally but MySQL server is disk i/o bound, the overhead is negligible too because disk i/o latency is much longer than 5.1->5.6 overhead.

 But the regression is obvious when clients run locally and queries are CPU bound. The most well known local client program for MySQL is SQL Thread (Replication Thread). Yes, 5.6 has a slower replication performance problem, if SQL thread is CPU bound.

 If all of the following conditions are met, SQL thread in 5.6 is significantly slower than 5.1/5.5.
  • Almost all working sets fit in memory = Almost all INSERT/UPDATE/DELETE statements are done without reading disks
  • Very heavily inserted/updated and committed (7000~15000 commits/s)
  • 5.6 Multi-threaded slave can not be used (due to application side restrictions etc)
  • You want to use crash safe slave feature and optionally GTID

 Here is a micro-benchmark result. Please refer a bug report for details (how to repeat etc).

Fig 1. Single thread replication speed (commits per second)

 On this graph, 5.1, 5.5, and 5.6 FILE are not crash safe. fb5.1 and 5.6 TABLE are crash safe. 5.6p has some experimental patches. Crash safe means even if slave mysqld/OS are crashed, you can recover the slave and continue replication without restoring MySQL databases. To make crash safe slave work, you have to use InnoDB storage engine only, and in 5.6 you need to set relay_log_info_repository=TABLE and relay_log_recovery=1. Durability (sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1) is NOT required.

 There are a few observations from the benchmark.
  • 17% drop from 5.1.69 to 5.5.33. I did not profile 5.5 much, but I suspect the regression was mostly caused by Metadata Locking feature introduced from 5.5.
  • From 5.5.33 to 5.6.15(FILE), 20% drop if log-slave-updates was enabled, 18% drop if log-slave-updates was disabled. In 5.6, FILE based replication has an inefficiency bug that it writes to OS filesystem cache too often (bug#70342). 5.6.15p(FILE) applied the fix for the bug, but was still 15% lower than 5.5.33 if log-slave-updates was enabled, 2.5% lower if log-slave-updates was disabled. This shows writing to binlog (from single threaded SQL thread) in 5.6 takes more time than 5.1/5.5.
  • Performance drop for 5.6.15 TABLE was serious. Compared to fb5.1 (crash safe), performance drop was 51.2% (14520/s->6103/s). slave_relay_log_info table is opened/fetched/updated/closed per every transaction commit. By keeping the table opened and skipping fetching row (updating only), performance improved to 7528/s (with log-slave-updates) and 9993/s (without log-slave-updates), but these were still much lower than fb5.1 (12505/s and 17056/s).

 How can we fix and/or avoid the problem?

 From MySQL development point of view, there are some room for performance optimizations.
  • Writing to slave_relay_log_info table should be optimized more. Currently the table is opened/fetched/updated/closed via Storage Engine API for each commit. It will be more efficient by updating the table via Embedded InnoDB API, which is currently used from InnoDB memcached plugin. slave_relay_log_info is a system table, and is updated by SQL thread (and MTS worker threads) only, so some tricky optimizations can be done here.
  • Writing to binlog should be faster in 5.6. 5.6 added many features such as group commit, so it is not surprising to get lower single threaded performance without any optimization.
  • 5.1 to 5.5 performance drop was likely caused by Metadata Locking feature. I have not profiled in depth yet, but I think some functions (i.e. MDL_context::acquire_lock()) can be refactored to get better performance.

 From DBA point of view, there are couple of workarounds. First of all, the regression won't happen in most cases. Most application data should be much larger than RAM, and SQL thread is doing many I/O, so CPU overhead is negligible. Even if working sets fit in memory, many applications don't need over 7000 commits per second on single instance. If you don't need high commit speed, you won't hit the problem either.
 But some applications may really hit the problem -- a kind of queuing application is a typical example. At Facebook we are continuing to use 5.1 for such applications.
 If you want to use 5.6 for such applications, you may disable crash safe slave, because usually database size is very small. If database size is small enough (i.e. < 200GB), you can easily restore from other running master/slaves without taking hours. If you don't need crash safe slave feature, you can use FILE based (default) replication, which is still slower than 5.1/5.5 (see "5.6.15 FILE" on the above graph) but is much better than TABLE based.
 Also, Consider not using log-slave-updates. log-slave-udpates is needed when you use GTID. If you want master failover solution without GTID, MHA is a good enough solution and it does not require log-slave-updates.

19 comments:

Stephane Varoqui said...
This comment has been removed by the author.
Manoj Kusshwaha said...

Nice article amazing article on DBA love it seriously

Hong Zhang said...

One of the hermes outlet aboriginal things humans do in such a austere bearings is to cut aback their expenses. Many of us would accept lived out the acceptable times benumbed the acclaim agenda wave, spending added than we can allow and architecture up debts. The best way to cut spending and accord debts is to astute up on louis vuitton replica big-ticket purchases that are absolutely avoidable.Louis Vuitton handbags accept consistently had a around-the-clock appeal. They accept active a position of pride and accept an enviable afterward of adherent buyers. Every new appearance appear by the aggregation is lapped up by agog fans. There is a acumen for chanel outlet that. You see, Louis Vuitton has bigger on its quality, chic, beauty, appearance and ability over the years. For example, something like the Monogram Denim Mahina XS can accomplish any woman angle out from the crowd; or, if you wish 18-carat leather, try the Balenciaga Giant City Bag. Nothing can exhausted the gucci replica appearance caliber of these bags.A Louis Vuitton accent can transform your wardrobe. It is an apparent assurance of style, amusing chic and sophistication. That said, difficult times anticipate us.Nevertheless, besides top superior affirmation and aswell agreement and quick delivery, they accomplish every accomplishment to absorb the chump feel affable with a big difference.

alatsex terbaru said...

saya suka bisa datang berkunjungi ke sini, dan mohon ijin sberbagi info tentang toko alat bantu, yg jual alat sex, alat bantu sex pria, alat bantu sex wanita.

Tome mark said...

The greater part of the schools and master's level college have diverse necessities and distribute these in their rules. In any case, My Admission Essay Writing assistance there is a fundamental methodology to confirmation paper composing that you ought to utilize.

josez davis said...

However, there usually are many custom made watches that is certainly each stylish in conjunction with traditional, it really is a victorious just one. Your budget can be chosen in to thought, certainly. If you've got money obtain, then at all cost, there usually are timepieces that can look at quite a few 500 dollars. replica MontBlanc watches Simply Cartier Replica watches steer clear of fakes (generally known as unlicensed IWC replica watches options). These include sure cheap, nevertheless this displeasure they're going to give is for certain certainly not worthwhile your financial savings. The different purpose may be a wildcard. Really does this individual like discount knockoff TAG Heuer watches a fashionable monitor? Can they a lot like digital custom made watches? Can the individual take health care of reliability? Really does they demand a unique model? These types of are high quality Replica Breitling Watches significant.

Shahrukh Khan said...

But the regression is obvious when clients run locally and queries are CPU bound. The most well known local client program for MySQL is SQL Thread (Replication Thread). Yes, 5.6 has a slower replication performance problem, if SQL thread is CPU bound.
facebook

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

Br Hills

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

Sobha golden quadrilateral

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

Pre launch Salarpuria East Crest

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

salarpuria east crest

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…


Pre launch salarpuria east crest

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

Pre launch prestige Woodland Park

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

Pre launch prestige pinewood

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

Pre launch sobha rain forest

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…

Pre launch sobha rain forest

Digital Seo said...

I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…


Pre launch sobha green acres

Digital Seo said...


I want to get across my admiration for your kind-heartedness giving support to those individuals that need help on this particular area. Your special dedication to passing the solution along ended up being quite helpful and has consistently permitted i…


Sobha golden quadrilateral

marina peter said...



visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here

visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here
visit here

Post a Comment