Monday, January 26, 2015

Performance issues and fixes -- MySQL 5.6 Semi-Synchrnous Replication

 Long time ago I wrote a blog post that we started using Semi-Synchronous replication in production at Facebook. We are running our own MySQL facebook-5.6 branch, which added many replication (including Semisync) patches. Some of them were also merged with WebScaleSQL.

 Recently I heard from people from community that they were suffering from Semisync performance issues in official MySQL 5.6. I took some time to review our previous MySQL bug reports, then realized that some important bugs were either still "verified" or inefficiently fixed. Two most affecting bug reports were https://bugs.mysql.com/bug.php?id=70342 and http://bugs.mysql.com/bug.php?id=70669. We fixed both at our branch so I haven't paid much attention after that, but people outside Facebook are certainly affected.

 In this post, I'm going to describe some effective configurations to get better Semisync throughput on master and slaves, by showing simple benchmark numbers. I used three machines -- client and master and semisync slave --, all running on pure flash. They are located within very close distance. I created 100 databases and enabled Multi-Threaded-Slave, and ran 100 mysqlslap processes for 100 databases, with 30 concurrent connections each (3000 concurrent connections in total). All queries were auto-committed inserts and I used InnoDB storage engine on both master and slaves.


1. Set master_info_repository=TABLE
 MySQL 5.6 and 5.7 have a performance bug that writing FILE based master info (and relay log info) files are very expensive. This is especially serious for Semisync replication, since this bug slows down IO thread. On Semisync, slow IO threads takes longer time to send ACK back to the master, so it slows down master throughput as well. Default master_info_repository is FILE, so without changing this parameter to TABLE, you are affected by this bug.
 Here are benchmark results between FILE and TABLE.

5.6.22 master_info_repository=FILE :  5870/s
5.6.22 master_info_repository=TABLE:  7365/s

 These numbers were the number of commits per second on both master and slave instances. Slave didn't lag for most experiments, thanks to multi-threaded slave feature.
 Please don't confuse between master_info_repository and relay_log_info_repository parameters. relay_log_info_repository has to be TABLE, otherwise crash safe slave doesn't work. master_info_repository works on both FILE and TABLE, but I suggest to use TABLE for performance reasons.


2. Reduce durability on master
 Older 5.6 had a bug that slaves couldn't continue replication after crashed master's recovery, even if setting fully durable configurations. Here is a closed bug report.
 This bug report was closed, but it caused some performance regression. Master extended LOCK_log mutex holding duration -- releasing LOCK_log mutex after calling fsync(). This certainly fixed the bug, but caused performance regression because LOCK_log was very hot mutex in 5.6 -- both Binlog Dump thread and application threads need to hold the lock. Hopefully reducing durability (I mean setting sync_binlog=0 and innodb_flush_log_at_trx_commit=0|2) mitigates the regression a lot. When using Semisync replication, you are most certainly to promote a slave on master failure, so durability on master does not matter much == You can reduce durability on master.

5.6.22 master_info_repository=TABLE, full durability:  7365/s
5.6.22 master_info_repository=TABLE, less durability:  9800/s


3. Loss Less Semisync
 MySQL 5.7 improved replication performance a lot. Probably the most effective improvement was that Binlog Dump thread no longer held LOCK_log mutex. In addition to that, 5.7 introduced "Loss-Less Semisync" feature.
 If you read my previous Semisync blog post carefully, you may have noticed that 1. we backported "Loss-Less Semisync" from 5.7, and 2. we got better throughput with Loss-Less Semisync than Normal Semisync. This was because Loss-Less Semisync actually reduced mutex contentions -- LOCK_commit and LOCK_binlog_. My 5.7 benchmark result was as follows.

5.7 Normal Semisync:    12302/s
5.7 Loss Less Semisync: 14500/s
(master_info_repository=TABLE, less durable)

 Reducing LOCK_log contention on Binlog Dump thread and introducing Loss Less Semisync were major contributors in MySQL 5.7 performance improvements. At Facebook, we ported both in our 5.6 branch. It would be interesting for community if these can be available on other distributions, since using 5.7 in production will not happen anytime soon.

In addition to the above three configurations, there are still some considerations to make Semisync throughput not bad / better.


4. Semisync mysqlbinlog
 At Facebook, we implemented mysqlbinlog to speak Semisync protocol, and used it as a Semisync replication reader. On replication slaves, IO thread and SQL thread conflict with internal mutexes. As I mentioned above, slow IO thread slows down Semisync master throughput. Semisync mysqlbinlog doesn't have such slowdown, because it doesn't have SQL thread. So using Semisync mysqlbinlog instead of Semisync slave can improve master throughput.

5.7 Loss Less Semisync + Semisync slave: 14500/s (on both master and slave)
5.7 Loss Less Semisync + Semisync mysqlbinlog + async slave: 48814/s on master, 15363/s on slave

 This shows Semisync mysqlbinlog improved master throughput. But this is actually not so good news -- because slave lags a lot. Fundamentally we need to fix mutex contentions between IO thread and SQL threads.


5. GTID
 There are some open performance bugs in GTID. Especially 5.7 one is serious. If you really need high throughput Semisync, you need to carefully benchmark with GTID (and ask Oracle to fix!).


Here are whole table definitions and mysqlslap commands I used for benchmark.
for i in `seq 1 100`
do
mysql -e "drop database test$i"
mysql -e "create database test$i"
mysql test$i -e "create table t1 (id int auto_increment primary key, \
value int, value2 varchar(100)) engine=innodb"
done

for i in `seq 1 100` do mysqlslap --host=master --query="insert into test$i.t1 \ values (null, 1, 'abcdefghijklmn')" --number-of-queries=100000 --concurrency=30 & done


247 comments:

«Oldest   ‹Older   201 – 247 of 247
exam said...

happy raksha bandhan wishes

exam said...

Gold Cup Final

Philips Huges said...


Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

Installment loans in alabama
Payday loans in alabama
Title loans in alabama
Cash Advances in alabama

Neeraj kumar said...

Thank you because you have been willing to share information with us. This is such a

great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free
MalwareBytes Anti Malware Free
Winrar 64 Bit Download

Neeraj kumar said...

It is amazing to visit your site.Thanks for sharing this information,this is useful to me...
MalwareBytes Anti Malware Free
Winrar 64 Bit Download

Wish You said...

Great information. Thanks a lot.

Rakhi Images

Rakhi 2017

Happy Rakhi 2017

Rakhi Quotes For Sister

Rakhi Images For Sister

Rakhi Images For Brother

Rakhi 2017 Quotes

Rakhi Images 2017

Rakhi Quotes

Rakhi Messages

Rakhi Shayari

Rakhi Status

Karthi Keyan said...

Its fantatic explaintion lot of information gather it...nice article....
seo company in Chennai

Karthi Keyan said...

https://yoshinorimatsunobu.blogspot.in/2015/01/performance-issues-and-fixes-mysql-56.html?showComment=1500360669005#c2386481699467181355

Love Me said...

Very nice and useful information. I like what your blog stands for. I have bookmarked your site and will be back to what new blog posts you create moving forward. Thanks for your great post.
Friv
Friv
Friv
Friv

novin said...

best Nice article thanks very much Thank you ...
طراحی سایت
طراحی وب سایت
سئو
طراحی سایت شرکتی
طراحی سایت ارزان
طراحی سایت فروشگاهی
طراحی سایت
طراحی وب سایت
سئو
طراحی سایت

Tom said...

thanks very much..
Nice article, thanks for sharing.
طراحی سایت
طراحی وب سایت
سئو
طراحی سایت شرکتی
طراحی سایت ارزان
طراحی سایت فروشگاهی
طراحی سایت
طراحی وب سایت
سئو
طراحی سایت

kongponleu88 said...

Thanks for this informative article, I hope you will get most positive response specially for this post. . . .
โกเด้นสล็อต

frivtn100 said...

We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work.
yepi3
friv baby game
friv racing game
friv shooting game
game for girl 2
girl games 3
girl game school

Oiet Facts said...

Friendship Day 2017 

Happy Friendship Day

Friendship Day Images

Friendship Day Wishes

Friendship Day Quotes

Smswishes Quotes said...

independence day 2017
independence day images
independence day speeches
independence day quotes
independence day wishes
tamilrockers

Mai Cyrril said...


Danke, dafur dass Ihr jeden Tag mit uns die Hl. Messe gefeiert habt, fur all die Reparaturen im ganzen Haus Jeux De Friv Juegos Friv Jogos Friv fur Euere Bereitschaft Juegos Friv Jogos Friv Juegos Friv immer zur Verfugung zu stehen Juegos Geometry Dash Juegos Twizl Twizy Juegos Twizl Danke, dass Sie Ihr Muhen um den Aufbau des Leibes Christi mit uns teilten.

Oiet Facts said...

Friendship Day Messages

Friendship Day Messages 2017

Happy Friendship Day Messages 2017

Happy Friendship Day Messages

قمة الدقة said...

قمة الدقة للخدمات
شركة نقل اثاث بالدمام
شركة نقل اثاث بالاحساء
شركة تنظيف موكيت بالدمام
شركة تنظيف موكيت بالخبر
شركة رش مبيدات بالدمام
شركة رش مبيدات بالخبر

putri lubis said...

Thanks for the information
Share and Klick Info Blokwalking. Hammer Of Thor
=> Hammer Of Thor Bogor
=> Hammer Of Thor Solo
=> Hammer Of Thor Jakarta
=> Hammer Of Thor Semarang
=> Hammer Of Thor Surabaya
=> Hammer Of Thor Bandung
=> Hammer Of Thor Jogja

frivtn100 said...

Great website! I really love how it is simple on my eyes and the data are well written.
http://www.friv5game.org/

Ngọc Mai Phạm said...

Herr Cashman, ich danke Ihnen für diese Information. Juegos Yepi Juegos Kizi Juegos Twizl Wir danken aber auch Ihnen Juegos Friv Friv 2 Friv 2.0 liebe Leserinnen und Leser, fur Ihr Interesse und Ihre Mitwirkung Juegos Friv 2.0 Juegos Friv 20 Friv 3 die Informationsgesellschaft in der Schweiz voranzubringen.

Amandeep Singh said...

Gmail is the Google's email service. It has many advantages over Hotmail and Yahoo.
Gmail sign up new account
Summerslam 2017 Live Stream
Mcgregor Vs Mayweather Live Stream

juegos friv said...



Thank you so much for the nice words
wir danken Ihnen fu das Interesse an unseren Produkten und hoffe Friv4school 2018, Friv 2018, Play Friv4school Games Gry Friv 2, Gry Online, FRIV 2 Gry Friv 5, Gry Online, Darmowe Friv Online Wir danken Ihnen Gry Friv, Gry Online, Darmowe Friv Online Juegos Friv 100, Juegos Gratis, FRIV 100 Juegos Friv 1000, Juegos Gratis, FRIV 1000 dass Sie diese Hoffnung mit uns teilen und diesen Schritt auf dem Pilgerweg des Vertrauens Juegos Friv 5, Juegos Gratis, FRIV 5 Juegos De Friv 2, Juegos Gratis, FRIV 2 Juegos Friv 250, Juegos Gratis, Friv 250 Juegos Yepi, Juegos Gratis, Yepi Online mit uns gegangen sind.

Srinu said...

Nice blog and thanks for sharing the post. Check out some latest projects.

Lodha Meridian
SMR Vinay Iconia
Prestige Ivy League
Western Exotica
NCC Urban Gardenia
Quiescent Heights
NCC Urban One
NorthStar Homes District1

sunitha vishnu said...

It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
Android Training in Chennai
Ios Training in Chennai

Golden Duel said...

Earn To Die 2012
From the not Doppler studios, our personal favorite game on this list is Earn To Die 2012.
The setting is a zombie apocalypse in the year 2012. Kill mobs of zombies to earn cash, or perish among the living dead.
Are you up for the task? Click here to play
Friv 5
Friv 8
Io Games
Kizi 5

Super Castle Sprint said...

Normally individuals employ one-part of the brain but mental performance
of an individual doesn't just one part of the brain but almost all places are useful and effective by enjoying these particular kinds.
Gryry
Juegos Friv 10
Juegos Friv 50
Juegos Friv 50
Fnaf 2

hermoine said...

national boyfriend appreciation day 2017

is today national boyfriend day

international boyfriend day

boyfriend day

hermoine said...

http://www.nationalboyfriendday2017.com

National Boyfriend Day

when is national boyfriend day

National Boyfriend Day 2017

national boyfriend appreciation day 2017

national boyfriend appreciation day

when is boyfriend day

national boyfriend day quotes

national boyfriend appreciation day 2017

is today national boyfriend day

international boyfriend day

boyfriend day

Ameen Younas said...

Great News
Hawayein
http://indvspaklivehighlights.com/arsenal-live-stream/

Oiet Facts said...

cool halloween costumes
costume ideas
halloween ideas
happy halloween

unique halloween costumes
easy halloween costumes
halloween costume stores
halloween costume ideas for women

Games Acool said...

In the gaming world, a slew of ways to play games exist: consoles like the Xbox,
handheld devices such as the DS, mobile platforms like smartphones,
and one of our favorite, the browser such as Firefox or Chrome.
http://www.friv2.city/
http://www.friv9game.org/
http://www.friv10.org/
http://www.friv100new.org/
http://www.juegosfriv100.net/
http://www.fripgames.org/
http://www.y88games.net/
http://www.kizi3game.net/
The advantage of in-browser games is that all is required is a computer and an internet connection.
No high end graphics card, dedicated console, monthly subscription, or even a one-time fee.

FUNNY Dinosaurs said...

Friv is our top choice for in-browser gaming and provides a wide range of games to play.
Whether you are partial to zombies, man eating caterpillars,
or simply want to play a game of dress up,
http://www.friv90.org/
http://www.friv800games.org/
http://www.friv80.org/
http://www.friv700game.org/
http://www.friv70.org/
http://www.friv600game.org/
http://www.friv50games.org/
http://www.friv500games.org/
http://www.friv40game.org/
http://www.friv400.org/
http://www.friv30game.org/
http://www.friv300games.org/
http://www.friv20online.org/
http://www.frivigame.org/
http://www.friv900games.org/ has you covered. D
id you not find the type of game you were looking for? Head over to Friv and take a look at their list of top rated games,
and you will absolutely find something for you.

frivtn100 said...

I enjoyed over read your blog post. Your blog have nice information, I got good ideas from this amazing blog. I am always searching like this type blog post. I hope I will see again
http://www.friv1com.org
http://www.friv2game.top
http://www.friv7game.top
http://www.friv8game.top
http://www.friv9game.top
http://www.friv20online.com
http://www.friv100com.org
http://www.friv100game.org
http://www.friv10000com.org
http://www.friv5game.org/

FUNNY Dinosaurs said...

Barbie of Swan Lake
Similar to Charming Bride, Barbie of Swan Lake is a dress up game.
The name says it all: you are a barbie of a girl on a lakeside, and you are tasked with getting dolled up before you meet your prince.
http://www.kizi3game.net/
http://www.friv9game.org/
http://www.y8com.net/
http://www.friv68.org/

Motocross Challenge said...

Charming Bride
From the didiGames studios,
Charming Bride is quite the opposite of the number 1 slot.
There is no fighting, or eating; this one is all about make-believe and dress up.
You are given a female character to dress up for the day she has been planning since she was a little girl: her wedding day.
Then, show off your creation! Click here to play
http://www.kizi18.org/
http://www.friv700games.com/
http://www.friv20game.org/
http://www.friv10com.org/
http://www.acoolgame.org/

Penguin Diner 2 said...

Barbie of Swan Lake
Similar to Charming Bride, Barbie of Swan Lake is a dress up game.
The name says it all: you are a barbie of a girl on a lakeside, and you are tasked with getting dolled up before you meet your prince.
http://www.y8y9y10.com/
http://gamegames.org/
http://www.friv50.org/
http://www.friv40.org/

Motocross Challenge said...

Charming Bride
From the didiGames studios,
Charming Bride is quite the opposite of the number 1 slot.
There is no fighting, or eating; this one is all about make-believe and dress up.
You are given a female character to dress up for the day she has been planning since she was a little girl: her wedding day.
Then, show off your creation! Click here to play
http://www.friv34.org/
http://www.Friv 24018online.net/
http://www.friv100.games/
http://www.friv24.org/
http://www.frin2.org/

Youda Survivor 2 said...

In the gaming world, a slew of ways to play games exist: consoles like the Xbox,
handheld devices such as the DS, mobile platforms like smartphones,
and one of our favorite, the browser such as Firefox or Chrome.
http://www.friv8x.org
http://www.friv6edge.com
http://www.y87.org
http://www.juegosfriv100.net
http://www.fripgames.org
http://www.kizi10game.net
http://www.yepi5.org
The advantage of in-browser games is that all is required is a computer and an internet connection.
No high end graphics card, dedicated console, monthly subscription, or even a one-time fee.
Friv9Game.org, or Friv for short, offers a very wide range of these in-browser games at no cost.
Friv is our number one choice for browser gaming.

Soundarya S said...

software engineer keep follow us the MYSQl response tags thank you for your creating information.



Java training in Chennai

Soundarya S said...

this is a Server side data maintenance very nice information and then MySQL has better performance than any other web server database

Java Training in Chennai

Djakarta Truss said...

Terimaksih atas informasinya....
by : Baja Ringan Jakarta dan Baja Ringan Jakarta serta Baja Ringan Jakarta | Baja Ringan Jakarta Thanks for all

Nikshit Narula said...

Awesome Post
Check Out Diwali images free download 2017
Check Out Diwali images free download
Check Out Diwali images 2017
Check Out Diwali images
Check out Latest Government Job of BSNL Recruitment 2017

Thu Lê said...

I was very impressed by this post, this site has always been pleasant news. Thank you very much for such an interesting post. Keep working, great job! In my free time, I like play game: vex3game.com. What about you?

Games 2 Girls said...

Thanks for sharing the information. It is very useful for my future. keep sharing
run3unblockedgame.com

Games 2 Girls said...

Thanks for sharing the information. It is very useful for my future. keep sharing
sushi-cat2.com

IndraSinh Solanki said...

Happy Halloween Images, Pictures, Meme, Clipart, Quotes, Banner, Gif, Sign, Wallpaper, Games.

«Oldest ‹Older   201 – 247 of 247   Newer› Newest»

Post a Comment