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.

58 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.

PatriciaMoncrief said...

I have not been this excited by a blog entry for a long while! You have it, whatever that implies in blogging. Anyway, you’re unquestionably somebody that has something to say that individuals must to listen. Keep up the magnificent work. Continue moving the people! It is very helpful information for me. Because I am new in blogging and I require great instructional exercise like your post. If you like to travel then you can use munnar call taxi for a trust worthy travel.

Eileen Burton said...

I really like the way you discuss this kind of topic.I would like to thank you for your nicely written content,its useful and your writing style helped me to read it without any difficulty. affordable assignments by assignment valley

Croose Hackle said...

Arinetsupport.com serve the various options about remote dba experts, remote dba services, remote dba support, remote database, oracle remote, dba remote, database administration, oracle dba support, Oracle support and Oracle Consultants.

killer king said...

valentine day wishes
Valentine Day Quotes For Girlfriend
Valentine day Poems
Valentine day Messages

killer king said...

valentine day wishes
Valentine Day Quotes For Girlfriend
Valentine day Poems
Valentine day Messages

Naveen Chaudhary said...

valentine wish
valentine quotes for girlfriend
valentine day wishes for girlfriend
valentine day poems for girlfriend

Naila Musdalifah said...

boleh tuh artikelnye
Perangsang phero-x

Obat tidur Cepat

Pelicin perangsang

Potenzol germany

Blue Wizard germany

opium topcer

clawing sex love


Minh Vương said...

dịch vụ lau dọn nhà cửa
vệ sinh văn phòng
vệ sinh nhà cửa,giặt thảm,giặt ghế sofa
lau kính tòa nhà cao tầng
dịch vụ vệ sinh sau xây dựng

lolyta said...

Nice blog and article, thanks for sharing. However want to statement on few common issues, The website taste is great, the articles is in point of fact excellent. Vimax IZON Canada Original | Obat Pembesar Penis
vimax izon

vimax izon canada

vimax izon asli

harga vimax izon

jual vimax izon

vimax

Haritha said...

Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.

Informatica Training in Chennai

soroush afzalzadeh said...

ممنونم از سایتتون به ما سایت هم در زمینه رزرو هتل سری بزنید

Mincecraft Games said...

Great! Thanks for sharing the information. That is very helpful for increasing my knowledge in this field.
- minecraft games
- happy wheels
- happy wheels
- tetris
- brain games
- puzzle games
- agario
- abcya
- fnaf

keerthisuresh said...

I read this content really awesome. You provided another one great article. I hope this information may change my business carrier.

Websphere Training in Chennai

Alex jones said...

Really I appreciate the effort you made to share the knowledge. The topic here I found was really effective to the topic which I was researching for a long time.
Assignment writing service

Rohith said...



Great post....Thank you for posting the great content……I found it quiet interesting, hopefully you will keep posting such blogs…
If you Want more seo course in chennai

Denisa Cretu said...

Very interesting blog. A lot of blogs I see these days don't really provide anything that I'm interested in, but I'm most definately interested in this one. Just thought that I would post and let you know.
friv2planet.com , juegosfrivol.com , friv400game.com

smith john said...
This comment has been removed by the author.
smith john said...

Your Blog post like a hub of knowledge. keep continue and I really enjoy reading your blog, it's always so interesting every article you post!Thanks for sharing it!
dissertation writing

Suraj Kumar said...

New year packages 2017
New year packages near Delhi


Are you planning to take a grand trip with the family around New Year? If yes then enlisted below are the details of some New Year packages that might come in handy while you are selecting the destination. New Year has always been a special time and it’s that time of the year when everyone likes to be around family and friends and make sure that everyone has a good time. Everyone have specific plans for a getaway or a New Year party Near Delhi as it is the one of the most happening places in the country. But how could you know where to look for and which plan to finalize? So if you are looking to do a celebration near Delhi then the following information might help.

New Year packages 2017 Near Delhi

stewart louis said...

Once you alum from college, you important of at the access akin and plan your way up. Dissertation Writing Help Having some demonstrative of business internship colleague afore graduation array of helps quarter some experience, but it’s not plentiful to just bung you up to the commercial superintendent level.

Aaron Abbey said...

I am mostly concerned about single-threaded performance, which has started to stagnate these past few years, and now I worry it could even regress. Dissertation Writing Services

huong duong said...

Whenever sadness, lost love or feel ugly, or no reason, just because today has paid or surfing facebook show this afternoon swatch beautiful son, so a girl can buy right about one lipstick each
slither.io l slitherio l slither io l slither l baixar slither.io

Deba Sheesh said...

This is my first time i visit here. I found so many entertaining stuff in your blog. Keep up the good work.
Plastic Flow Meter

Priya Sharma said...

i can visit frist time and give all the information and tips to help me thanks for share. packers and movers in alwar

Anjali Sharma said...

New Year is the best event for everyone so celebrate this New Year 2017 with us and send some wonderful and awesome New Year Wishes, Greeting, Quotes and many more for your Love ones.
Best Wishes 2017
New Year Wishes 2017
New Year Wishes Messages
Christmas Messages 2016
New Year 2017 HD Wallpapers

Charu Chhabra said...

I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well Assignment help| Need Assignment help

Charu Chhabra said...

I am an antique article collector and this article is one of the best I have ever read. I hope to see the same amazing work from you in future. Thanks Assignment help | Need Assignment help | Marketing Assignment Help | Law Assignment help |

Om Sai Packers and Movers said...

This replication of single thread performance regression is highly readable. This is an amazing one the way things are highlighted. Need some help from trusted professionals take one from packers and movers in alwar

Guru Bijak said...

Are you planning to take a grand trip with the family around New Year? If yes then enlisted below are the details of some New Year packages that might come in handy while you are selecting the destination. New Year has always been a special time and it’s that time of the year when everyone likes to be around family and friends and make sure that everyone has a good time. Everyone have specific plans for a getaway or a New Year party Near Delhi as it is the one of the most happening places in the country. But how could you know where to look for and which plan to finalize? So if you are looking to do a celebration near Delhi then the following information might help.

muslimah
istri

Albert Barkley said...

Good post. So we can hope that we will have best user and navigation experience after up-gradation of your database.
Cheap dissertation writing service

mona lissa said...

happy mothers day greetings for friends

happy easter wishes for friends

best status for mom in hindi

total collection of bahubali 2

Arif Hossen said...

Happy mothers day 2017
Happy mothers day 2017 is

celebrated on the second Sunday of May every year, so the 2017 edition

of it will take place on May 14.

Happy Mothers Day 2017 Quotes

Best Happy Mothers Day 2017

Quotes
in english for the celebration of this year Mothers day.

Just select any of the Quotes from our collection.A mother is a person

who, seeing there are only four pieces of pie for five people, promptly

announces she never did care for pie. – Tenneva Jordan

mother's day gifts 2017
Great mother's day gifts 2017

Ideas For all that Mom does for her family, she deserves to be

celebrated every day of the year but especially on Mother’s Day. Here at

Great Mother’s Day Gift Ideas, we help you find the perfect present that

expresses your love and gratitude to the woman who has rocked you to

sleep, dried your tears and loved you unconditionally.


mothers day quotes 2017
mothers day quotes 2017

mothers day poems 2017

mothers day cards2017

happy mothers day images 2017


happy mothers day images 2017



Mother's day 2017 SMS,status

text messages



http://www.mothersday2017.co/


http://www.mothersday2017.co/
http://www.memorialday2017.co/

mother's day
Happy mothers day 2017
Mother's day 2017
Mothers day gifts
Mothers day gifts 2017
Mothers day quotes 2017
Happy mothers day quotes 2017


Mothers day images
Happy mothers day images 2017


Memorial Day 2017

http://packersmoversbangalore-in.blogspot.in/ said...

I must say you had done a tremendous job,I appreciate all your efforts.Thanks alot for your writings......Waiting for a new 1...Please visit our wonderful and valuable website:
Packers And Movers in Bangalore charges

Joanne Criss said...

Very Informative post you gave to us, really helpful I would like to share something really helpful for education. All Assignment Help

Ankit Meena said...

Mothers Day 2017 Quotes In Hindi
Happy Mothers Day 2017 Wishes In Hindi
Mothers Day Advance Wishes
Mothers Day Bollywood Songs List
Mothers Day FB Status For Mom
Mothers Day Surprise Ideas for Mom
Mothers Day 2017 Images In HD

Mark Jark said...

Hello Have a wonderful day I saw this website is very informative if here any movie lover who want download Cinema box apk its very nice app you can watch latest movie on your phone cinema box iphone also available so now where ever you are just Download Online streaming app and enjoy with love once. If you having some problem like Cinema box not working than go this guide you will find easy way to get off this problems. cinema box App is fast downloading movie app and its free so don’t miss and enjoy…!

Box Office Collection said...

short mother poem

Rahul Lokhande said...


Happy Mothers Day
Happy Mothers Day 2017
Happy Mothers Day Images
Happy Mothers Day Pictures
Happy Mothers Day Quotes

Packers Movers Hyderabad said...

Generally I don't read anything but thanks for writing when i read this i felt how graceful as a writer you are.thank you for posting- Best Packing Moving Organisation in Hyderabad @
Packers And Movers Hyderabad charges

packers and movers lingampally

packers and movers hitech-city

baahubali2 said...

Mothers Day Gifts 2017
Mothers Day Wishes 2017
Mothers Day Images 2017
Mothers Day History 2017
Mothers Day Quotes 2017
Mothers Day Poems 2017
Mothers Day Greetings 2017
Mothers Day Cards 2017
Mothers Day Pictures 2017

baahubali2 said...

Mothers Day Images 2017
Mothers Day Videos 2017
Mothers day clipart 2017
Mothers Day Pictures 2017
Mothers day messages 2017

baahubali2 said...

Mother’s Day Background Images 2017
Mothers Day Cake 2017
Mother’s Day Wallpapers 2017
Mothers day poems 2017
Mother’s day quotes 2017

baahubali2 said...

Mother’s day crafts 2017
Mother’s Day Gifts 2017
Mother’s day Coloring pages 2017
Mother’s Day 2017
Mother’s day Cards 2017

baahubali2 said...

Mothers's Day 2017
Mothers day flowers 2017
Mothers day dressing ideas 2017
Mothers Day Decoration Ideas 2017
Mother’s day Images 2017

baahubali2 said...

Eid Gifts 2017
Eid Wishes 2017
Eid Images 2017
Eid Pictures 2017
Eid Quotes 2017
Eid-Ul-Adha 2017
Eid-Ul-Fitr 2017
Eid Cards 2017

baahubali2 said...

Fathers Day Gifts 2017
Fathers Day Wishes 2017
fathr s day images 2017
fathers day pictures 2017
fathers day qutes 2017
fathers day poems 2017
fathers day greetings 2017
fathers day cards 2017

baahubali2 said...

Flight PNR Status
Indigo Airlines
Jet Airways
Go Air
Air India
Spicejet
IRCTC PNR Status

baahubali2 said...

Bahubali 2 Full Movie
Bahubali 2 Watch Online
Download Bahubali 2
Bahubali 2 Hindi Dubbed
Box Office Collection

Coupon Paid said...

Mothers day sayings and Quotes

Children's Mothers day Quotes

Mothers day ideas Live

Mother's day special dp

Mother pics with Quotes

One line Quotes for mom

Spiritual Quotes for Mother's day

Mothers day Quotes for all moms

Mother's day message from Mother to daughter

Special message for Mother's day

Mothers day poems and Quotes

Emma Mark said...

mother's day wishes to all mothers
Thanks to the publication of one! I enjoyed reading your Content, it could be a great author. I'll be sure to bookmark your blog and now finally back again in the near future.
happy mothers day 2017 messages
happy mothers day 2017 greetings

William Smith said...

KMSPICO 11
KMSPICO Activator
KMSAuto Net

William Smith said...


KMSpico Activator
Download KmsPico
Download KMSpico
Download KMSpico For Windows 10
Download KMSpico For Windows

Amit Chavan said...

led gadgets
apkmirror google play services
facebook icon for desktop<
tech under 100
5.1 speaker test
ps3 controller to windows 10
best netbooks

William Smith said...

Super Mario Maker PC Download

Download Super Mario Maker for PC

Priya Kannan said...

Such a great articles in my carrier, It's wonderful commands like easiest understand words of knowledge in information's.
Websphere Training in Chennai

Post a Comment