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.

29 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

toko online said...

Thanks for sharing!

Jual Obat Tidur Cair - Obat Bius Herbal
Obat tidur Cair ~ Bridal Red Drop ~ Obat Tidur Herbal
Obat Bius Permen Karet ~ Obat Bius Paten
Obat Bius Chlorophyll ~ Obat Bius Semprot
Obat Tidur Kapsul ~ Obat Tidur Mujarab
leeping Beauty ~ Obat Tidur Cair Paten
Serbuk Tian ~ Powder Germany ~ Obat Bius Manjur
Obat Bius Cair ~ Liquid Sex ~ Obat Bius Ampuh

toko online said...

Thanks for sharing!

Jual Obat Kuat, Alat bantu sex, Obat perangsang wanita
Potenzol Cair, Obat Perangsang Wanita
Penis Mutiara Maju Mundur ~ Alat Bantu Seks Wanita Terpopuler
Alat Bantu Sex Wanita ~ Penis Ikat Pinggang ~ Alat Bantu Terpopuler
Vimax Piils, Obat Pembesar Penis
Viagra Usa 100mg, Obat Kuat




Jual Obat Kuat, Alat bantu sex, Obat perangsang wanita
Alat Bantu Sex Wanita ~ Penis Ikat Pinggang ~ Alat Bantu Terpopuler
Penis Mutiara Maju Mundur ~ Alat Bantu Seks Wanita Terpopuler

Lan Linh said...

Great! Thanks for sharing the information. That is very helpful for increasing my knowledge in this field.
hulk games
mickey mouse games
rollercoaster creator
super adventure pals
madness project nexus hacked
my little pony puzzle
my little pony halloween

toko online said...


Thank you for the information by reading this article I know a lot of things and we also develop ya can also visit us at

Jual Obat Kuat, Alat bantu sex
jual obat bius, jual obat tidur
jual obat bius tokcer
jual obat tidur tokcer



wasirdenature said...

obat ambeien luar obat alami ambeien eksternal obat wasir cina ampuh penyembuhan wasir alami obat salep wasir di apotik apa obat kemaluan keluar nanah untuk pria obat herbal wasir ambeclear denature obat menyembuhkan kencing perih dan bernanah obat gonore atau kemaluan keluar cairan nanahobat cairan nanah keluar dari alat kelamin

dela keny said...

I came across the serach engine and find your website, you have great and nice blog, Thanks for sharing, God bless you
JUAL VIMAX HERBAL
vIMAX HERBAL
VIMAX CANADA
PEMBESAR PENIS VIAMX HERBAL
OBAT VIMAX HERBAL
VIMAX ASLI
AGEN VIMAX
PROMO VIMAX
VIMAX ORIGINAL CANADA
VIMAX ORIGINAL HERBAL
VIMAX HERBAL ORIGINAL
VIMAX ASLI ORIGINAL CANADA
VIMAX HERBAL PENIS BESAR
CARA MEMPERBESAR PENIS
TIPS MEMPERBESAR PENIS VIMAX
PEMBESAR VIMAX ORIGINAL
VIMAX KAPSUL
OBAT PEMBESAR PENIS
PEMBESAR PENIS
JUAL VIMAX ASLI
JUAL OBAT VIMAX
VMENPLUS HERBAL
VIMAX PILL
VIMAX EXTENDER
VMENPLUS
OBAT EJAKULASI DINI
TESTIMONI VIMAX
TENTANG VIMAX KAPSUL
CARA PEMESANAN VIMAX
OBAT PEMBESAR VIMAX
VIMAX HERBAL ASLI
Harga Vimax Herbal Asli 2015
HUKUM MEMPERBESAR PENIS
selaput dara buatan
selaput dara keperawanan
selaput keperawanan
Jual Selaput Dara
selaput dara palsu
selaput dara

dela keny said...

thank you sir for the news you give this. really very good indeed. and I want to add a little bit of information specifically for adult men and women.
ARTIKELNYA KEREN-KEREN ,, KUNJUNGI BALIK YA :)
ALAT BANTU SEX
JUAL ALAT SEX IMPORT
ALAT BANTU SEX PRIA
ALAT BANTU SEX WANITA
ANEKA KONDOM
TOKO ALAT BANTU SEX IMPORT ASLI DI INDONESIA
PENGHILANG DAN PENGHAPUS TATTO PERMANEN
PERANGSANG WANITA OPIUM SPRAY

VMENPLUS
VMENPLUS HERBAL
PEMBESAR PENIS ALAMI
OBAT EJAKULASI DINI
OBAT VMENPLUS

** ALAT BANTU SEX UNTUK PRIA **

ALAT SEX UNTUK BDSM
BONEKA SEX KOREA FULL BODI
ALAT ONANI PRIA VAGINA SILIKON
ALAT SEX PRIA - VAGINA GETAR BERJEMBUT
ALAT ONANI PRIA - VAGINA FLESHLIGHT
VAGINA PANTAT NUNGGING GETAR FULL SILIKON

** ALAT BANTU SEX UNTUK WANITA **

DOG SLAVE TAIL BUTT PLUG
JUAL SUPER STAPLESS DILDO VIBRATOR GETAR
FANTASI BLISS VIBE ALAT SEX WANITA
ALAT BANTU SEX WANITA - BUTTERLY DANCE VIBRATOR TERBARU
BONEKA PENIS PRIA SILIKON
VIBRATOR MIC MINI MURAH
KAPSUL PENGGELI VAGINA WIRELESS
JUAL DILDO PENIS MANUAL SILIKON
PENIS DUA KEPALA SILIKON
PENIS TEMPEL GETAR
PENIS MUTIARA - ALAT BANTU SEX WANITA

** ALAT PEMBESAR PENIS **
JUAL PRO EXTENDER PEMBERSAR DAN PANJANG PENIS
JUAL ALAT PEMBESAR DAN PANJANG PENIS VAKUM POMPA USA ASLI

SEMOGA BERMANFAAT YA GAN

dela keny said...


MACAM - MACAM ALAT BANTU SEX PRIA DEWASA

JUAL BONEKA SEKS FULL BODI TERBARU


JUAL VAGINA PANTAT BERTATO GETAR SILIKON


JUAL TENGA ALAT BANTU SEX


JUAL ALAT BANTU ONANI FLESHLIGHT


JUAL VAGINA PINGGUL FULL SILIKON


JUAL VAGINA BULU GETAR

MACAM - MACAM ALAT BANTU SEX WANITA DEWASA.


JUAL VIBRATOR FANTASY BLISS MURAH


JUAL ALAT BANTU WANITA VIBRATOR THORN DRAGON


JUAL SEX TOYS HAPPY SOME


JUAL PENGGELI PUTING PAYUDARA


PENGGELI VAGINA TERBARU MIC


JUAL C-STRING ALAT SEX WANITA



PENGOBATAN DAN OBAT KESEHATAN HERBAL ALAMI


SEMENAX OBAT HERBAL PENYUBUR SPERMA


OBAT PERANGSANG UNUTUK PRIA ATAU WANITA


OPIUM SPRAY

JUAL PERANGSANG RED SPIDER CAIR



OBAT DAN ALAT PEMBESAR PENIS

VMENPLUS HERBAL

VIMAX PEMBESAR PENIS DEWASA

MAXMAN CAPSULE

PRO EXTENDER

Su Su said...

Thank you very much for your post, it makes us have more and more discs in our life, So kind for you, I also hope you will make more and more excellent post and let’s more and more talk, thank you very much, dear.
unblocked games| unblockedgames| unblocked games at school| unblocked games for kids
friv| friv4school| friv 4 school| monster high games| monster high| monster high drees up
happy wheels| happywheels| happy wheels demo| happy wheels unblocked| happy wheels hacked
shooting games| shooting| tetris| tetris unblocked| tetris game| tetris online

Khương Hoàng Gia said...

Hút bể phốt tại hà nội
Hút bể phốt tại cầu giấy
Hút bể phốt tại tây hồ
Hút bể phốt tại hai bà trưng
Hút bể phốt tại đống đa
Hút bể phốt tại thanh xuân
Hút bể phốt tại hoàn kiếm
Hút bể phốt tại long biên
Hút bể phốt tại hà đông
Hút bể phốt tại mỹ đình
Hút bể phốt tại bà đình
Hút bể phốt tại hoàng mai
Hút bể phốt tại KCN
Cách thông tắc bồn cầu
Cách thông tắc toilet
Cách thông tắc bể phốt
Cách thông tắc bệ xí
Cách khử mùi nhà vệ sinh
Cách thông tắc toilet không có pittong

sửa khóa
dọn nhà theo giờ
máy lọc không khí
mở tài khoản Forex
Máy lọc không khí Airocide
mở tai khoản IronFX
Vận chuyển hàng hóa bắc nam
Nước uống đóng bình
Sửa khóa tại nhà Hà Nội
Thuốc hỗ trợ điều trị ung thư, sữa cho bệnh ung thư
Máy lọc không khí gia đình

Post a Comment