Tuesday, October 4, 2011

Testing MySQL 5.6.3 network performance improvements

I'm excited to see the new features in MySQL 5.6.3. Replication enhancements such as parallel SQL threads, crash safe slave and binlog checksum have been desired for years. I really appreciate that MySQL development team has released 5.6.3 in timely manner.

In this blog entry, I'd like to pick up one of my most favorite performance enhancements in MySQL 5.6.3: "network performance improvements". This was reported by Mark Callaghan as bug#54790 over one year ago, and finally it's fixed (I assume it's mainly developed by Davi Arnaut at MySQL dev team) in 5.6.3. The root cause of this problem was that prior to 5.6.3, under high loads MySQL internally had to call fcntl() lots of times to change non-blocking sockets to blocking (with timeout and alarm) and vice versa. fcntl() is serialized on Linux, so it doesn't scale with many CPU cores. You may suffer from this performance issue when clients are not locally based (= in most cases) and query per second is quite high (hundreds of thousands or even more). In many real world environments qps is not so much high, but in the near future these numbers will not be unrealistic, with faster H/W components.

I have tested some simple read-only benchmarks on 16-core and 24-core box, and results in 5.6.3 are great.





These benchmarks are in-memory PK lookup from a remote client. Connections are persistent. The client server never became bottleneck. Apparently the maximum throughput of 5.6.3 is much higher than 5.5 (75% higher on 24 cores, 34% higher on 16 cores). It's interesting that 16 core vs 24 core performance difference is not so much in 5.5 (only 10%), but it's huge in 5.6 (45%). 45% improvement is close to theoretical maximum (16core to 24core: 50%), so for these kinds of benchmarks, 5.6 is really scalable at least up to 24 cores.

Though many MySQL scalability issues could be mitigated by running multiple instances on a single server, you couldn't avoid this performance problem because calling fcntl() is serialized on Linux kernel side. So this performance fix is really important. With MySQL 5.6, I think we can take more scaling-up strategies rather than scaling-out.

How about disk i/o bound benchmarks? On disk bound workloads, network performance improvements do not cause so much impacts of course (since qps is much lower), but as below, 5.6 showed good results compared to 5.5. These performance improvements mainly come from reducing InnoDB internal kernel mutex contentions in 5.6. I'd like to investigate more time to understand internal differences.





These benchmarks were SATA SSD bound (8 drives, RAID1+0) benchmarks. InnoDB buffer pool size was 1GB and active data size was 2.5GB. 5.6 shows better results at higher concurrency.

45 comments:

the brinkman said...
This comment has been removed by the author.
the brinkman said...

excellent work, excellent post, Yoshinori... Fantastic!! Just wondering how 5.6.3 scales on a 32 core or even 48-core machine?! can we expecte an approximately linear performance improvement on the 16-core and 24-core on which u've done the benchmarking?

many thanks.

Cédric said...

Woua, this 5.6.3 seems to be really great !
Thx for these benchs

Cédric said...

Woua, this 5.6.3 seems to be really great !
Thx for these benchmarks

Andy said...

1) Is handlersocket running on 5.5 affected by the fcntl() bug?

2) Which SSD did you use for the test?

Thanks.

Vivek Mehta said...

How the issue of calling fcntl() many times is solved in 5.6.3? Thanks.

the brinkman said...

Hi Yoshinori,
me again.... could you please kindly share the essential hardware specs for the 24-core server you used for this test. this post does not mention them in detail. thanks...

RS Gold said...

superb operate, exceptional submit, Yoshinori... Fantastic!! Just simply thinking exactly how Your five.Six.A few guitar scales for a Thirty-two central or maybe 48-core device?! will we expecte a great around straight line performance step up from your 16-core plus 24-core on which u've carried out the particular benchmarking?

Runescape Gold for Sale
Eden Gold

riftplat said...

buy wow gold
cheap wow gold

replica hermes said...

A number of the watches worn by u-boat replica watches NASA astronauts are displayed in

various places IdolReplicas around the world. For instance, the watches worn by Neil Armstrong

and Mike Collins are currently at the National replica hermes Air and Space Museum in

Washington D.C. Other burberry outlets places that various watches are located at include the Museum of Science and Industry in Chicago,

The Omega Museum in Switzerland and the U.S. Naval Academy in Annapolis.

Buy Tera Gold said...

Cleanup enemies along with completing quests is exactly what WOW Gold first-timers connected with Bargain Tera Amber that do not have any especial(a) destroys choose to begin with. Ever since the World of Warcraft is especially immense, people will need to obtain great places at which they will get more Riot gilded. The easiest ways instructor can may be to husbandry RS GoldScream gilded.You can choose the unlike location to take a crap Sidesplitter gilt as reported by your current characters's attributes. With plenty of aureate you may buy the guns,gadgets as well as as well belongings you cherished.If you're a Hunting watch you'll probably World Of Warcraft Gold try a Punk Urs Aureate land owner's manual for invite practicing your canine friend to advance them faster.

your eyes said...

That is our very first time i go here. I came across a lot of enjoyable stuff with your weblog, especially their dialogue. In the plenty of responses on the articles, I assume I'm not really the only person obtaining all of the amusement below! Continue the excellent work.
onlinegame2u.net

RS Gold said...

It all looks so great!http://www.facebook.com/pages/FZF/296158577089118?ref=tn_tnmn
http://www.4rsgold.com/christmas/active.html
http://www.fzf.com/christmas/christmas.html

gamefan said...

Well written ! i have to admit that it's very informative article. This really is another really very amazing and interesting publish. You've provided the publish that's proportional to the interest. Thank you for this publish.
http://www.4rsgold.com/christmas/active.html
http://www.fzf.com/christmas/christmas.html

gamefan said...

I recently found your web site doing research on the internet. It reduced the problem considerably. Thanks.
http://www.4rsgold.com/christmas/active.html
http://www.fzf.com/christmas/christmas.html

lace wedding dress said...

A holy church, rings, a bouquet, a 3-floor wedding cake, lace wedding dress champagne, and the moving promise “ I do”, make up the happiest moment in one’s life. Then it is the wedding.
lace wedding gowns
A gorgeous wedding dress has been inside a girl’s dream since she was five years old. It is widely said that the bride is the most beautiful woman all over the world.
Bridal gowns vary in different parts of the world. For example, cheap wedding dresses plus sizein traditional Chinese culture, the color red is regarded for centuries as the symbol of good luck; while in the Occident, wedding dresses 2012 a full-length white wedding dress is always associated with romance and sanctity. But, with the globalization, wedding dresses tend to be alike in the whole world.

Buy Runescape Gold said...

Well written ! i have to admit that it's very informative article. This really is another really very amazing and interesting publish. You've provided the publish that's proportional to the interest. Thank you for this publish.
http://rsgoldifzf.fiftiz.fr/blog/55304,the-northeast-is-marked-by-fashion-forward-trends.html
http://www.bloggen.be/unasheng522/
http://www.bloggen.be/wowgolduna/
http://www.bloggen.be/wowuna/
http://rsgoldifzf.blogg.se/?tmp=12034812
http://www.mojatu.com/unasheng522/blog/organizing-a-fashion-show-can-be-exciting/

Anonymous said...

You can choose the unlike location to take a crap Sidesplitter gilt as Subing
reported by your current characters's attributes.

Zegaara said...

I was actually looking for this resource a few weeks back. Thanks for sharing with us your wisdom.This will absolutely going to help me in my projects .

Classic Dresses
Classic Bridesmaid Dresses
Wedding Dresses with Sleeves
Flower Girl Dresses

Empire Wedding Dresses

Coupon Code said...

you will like the blogs below!
http://coupondeals.eklablog.com
http://bloguitos.com/discountcoupon
http://discountcoupon588.webs.com
http://discountcoupon.quebecblogue.com/

Anonymous said...

From there, fake breitling watches hundreds plan to make the march to hermes bolide Montgomery over the next five replica watches saledays, ending with a rally Friday at replica watches
the Rev. Martin Luther King Jr.'s Dexter Avenue King Baptist Church.

yan said...

http://www.chanelreplicasbags.me.uk
http://www.replicawatchestore.co.uk
http://www.breitlingreplicas.us
http://www.watcheshop.org.uk
http://www.hermesreplicas.net
http://www.omegawatchesuk.co.uk

巨人倍増 said...

威哥王 通販: http://www.kanpoucom.com/product/96.html
三便宝カプセル: http://kanpoucom.com/product/150.html
中華牛鞭: http://kanpoucom.com/product/167.html
巨人倍増: http://www.kanpoucom.com/product/138.html
福源春: http://www.kanpoucom.com/product/313.html
消渇丸: http://www.kanpoucom.com/product/211.html

三便宝 said...

Cialis: http://www.kanpocom.com/Pro/Cialis.html
レビトラ 販売: http://www.kanpocom.com/Pro/levitra.html
媚薬販売: http://www.kanpocom.com/meiyao/
三便宝: http://www.kanpocom.com/pro/satibo-capsules.html
さんべんぼう: http://www.kanpocom.com/pro/satibo-capsules.html
五便宝: http://www.kanpocom.com/Pro/wodibo-capsules.html

ティファニー said...

ティファニー 1837: http://www.beautiful-tiffany.com/catalog/14.html
ティファニー イヤリング: http://www.beautiful-tiffany.com/catalog/4.html
ティファニー ピアス: http://www.beautiful-tiffany.com/catalog/4.html
ティファニー: http://www.beautiful-tiffany.com
ティファニー リング: http://www.beautiful-tiffany.com/catalog/5.html
ティファニー 指輪: http://www.beautiful-tiffany.com/catalog/5.html

cambridge satchel us said...

If you are this kind type of lady then you should try to change it. A Cambridge Satchel Bag will help you a lot.



Maybe you work is very satisfying, but in the appropriate time shows charming is spruce. Cambridge Satchel Fluro is the best presenter means, it often can bring you to the affinity is unexpected fashion. If you don't want to have "outfit is tender" suspected, the choice of fluro can be low-key, such as powder purple.

hugginss jetterees said...

You giggle and choose to kill him in addition to you happen to be actively playing Runescape. Even though you happen to be experiencing the iron the newbie dropped in Runescape, two level 80 mages look incheap gold.

soft said...

That is our very first time i go here. I came across a lot of enjoyable stuff with your weblog, especially their dialogue. In the plenty of responses on the articles, I assume I'm not really the only person obtaining all of the amusement below! Continue the excellent work.


gw2 gold|cheap GW2 Gold

Belkowski Gaylord said...

Good job, good post, Yoshinori... That's great! !Just want to know 5 6 3 dimensions in 32 even 48 nuclear core machine? !Can we expected is an approximate linear performance improvements and 24 core will launch this, you have already completed the benchmarking?
2013 new nike nfl jerseys

GuildWars2Items said...

Diablo 3 Gold someone who makes you believe that there really is good in the world, someone who makes you laugh until you can't stop Buy Diablo 3 Gold, someone who changes your life just by being part of it Cheap Diablo 3 Gold, this is Forever Friendship

GuildWars2Items said...

Life is too short to wake up in the morning with regrets. So, love the people who treat you right and forget about the ones who do not buy c9 gold, Life is like a hot bath. It feels good while you're in it, but the longer you stay in, the more wrinkled you get cheap c9 gold, what gets us into trouble is not what we don't know.It's what we know for sure that just ain't so c9 money.

Maria Arredondo said...

It takes strength to do what must be done when the work is unpleasant and uncomfortable [url=http://scarletbladegold.com]scarlet blade gold[/url], It takes strength to persist in the face of obstacles, when it would be much easier to simply give up [url=www.gameim.com/product/Scarlet_Blade_Gold.html]scarlet blade gold[/url], It takes strength to be polite to someone when that person has been rude to you[url=http://scarletbladegold.com]scarlet blade gold[/url].

Maria Arredondo said...

The past is gone and static. Nothing we can do will change it.scarlet blade gold, the future is before us and dynamic. Everything we do will affect it rs gold, You laugh at mescarlet blade gold for being different , but I laugh at you for being the same.

Maria Arredondo said...

The consequences of today are determined by the actions of the past rs gold. To change your future, alter your decisions today Runescape gp, Experience is a hard teacher because she gives the test first, the lesson afterwards rs gp, but it takes character to keep you there.

Maria Arredondo said...

Pray for powers equal to your tasks.Then the doing of work shall be no miracle, but you shall be the miracleRunescape Gold, Fear not that the life shall come to an end, but rather fear that it shall never have a beginning rs gold, At twenty years of age, the will reigns; Runescape Gold at thirty, the wit; and at forty, the judgment. What gets us into trouble is not what we don't know.It's what we know for sure that just ain't so.

Maria Arredondo said...

Life is like a hot bath. It feels good while you're in it, but the longer you stay in, the more wrinkled you get rs gold, Life is too short to wake up in the morning with regrets. So, love the people who treat you right and forget about the ones who do not Runescape Gold, In the Orient young bulls are tested for the fight arena in a certain manner rs gold. Each is brought to the ring and allowed to attack a picador who pricks them with a lance..

Cahaya Mandiri said...

It is amazing posting and incredible work, It has suitable information, I presently wanted to say that you have really so motivating and very informative post. Thanks for wonderful posting
tips cara agar cepat hamil l CARA BELAJAR BAHASA INGGRIS l the best acne treatment l how to lose weight fast easy
margahayuland l BELAJAR BISNIS ONLINE l tips cepat hamil l how to get rid of acne home remedies l
home remedies for acne l how to cure acne fast l
baju batik modern l toko sepatu online l grosir jam tangan online l
jual jam tangan l toko jam tangan murah peluang usaha online l is acne no more for you l how to get rid of acne naturally
how to clear acne l cure acne naturally
best natural remedies for acne l acne no more l tempat belajar bisnis online
peluang usaha rumahan l cara mendapatkan uang dari internet
makanan sehat agar cepat hamil l penyebab tidak bisa hamil lcara agar cepat hamil


dveeeeeee said...

network performance improvements". This was reported by Mark Callaghan as bug#54790 over one year ago, and finally it's fixed (I assume it's mainly developed by Davi Arnaut at MySQL dev team) in 5.6.3. The root cause of this problem was that prior to 5.6.3, under high loads MySQL internally had to call fcntl() lots of times to change non-blocking sockets to blocking (with timeout and alarm) and vice versa. fcntl() eve isk is serialized on Linux, so it doesn't scale with many CPU cores. You may suffer from this performance issue when clients are not locally based (= in most cases) and query per second is quite high (hundreds of thousands or even more). In many real world environments qps is not so much high, but in the http://www.safeeveisk.com/ near future these numbers will not be unrealistic, with faster H/W components

hitesh kumar said...

There is a good advantage to register Free Online Art Classes. Instead of using the online classes, you do not have to go to a class that you can participate to relax the class in the country.

Roovi Kusnandar said...

The past is gone and static. Nothing we can do will change it.
Jual Sepatu Pria Online Murah
Sepatu Pria Online
Jual Sandal Murah
How to Get Rid of Forehead Acne Naturaly Quickly
Jual Sepatu Online
Jual Jaket Kulit Pria Murah Online
Modif Motor Terbaru
Jual Sandal Wanita
Toko Sepatu Futsal Murah
Jual Beli Motor Bekas Murah
Jual Sandal Murah
Kesehatan-ku.info
www.fullhdtvs.net

wow9gamer said...

come from reducing InnoDB internal kernel mutex contentions in 5.6. I'd like to investigate more time to understand internal differences.
WOW Gold Kaufen
World of Warcraft Gold

Jerukukuk said...

great article , nice information very usefull, thankyou
jam tangan alexandre christie
jam tangan couple
jam tangan original

Jerukukuk said...

great article , nice information very usefull, thankyou
jam tangan alexandre christie
jam tangan couple
jam tangan original

blognya fariz said...

The past is gone and static. Nothing we can do will change it.

menghilangkan kutil di penis | menghilangkan kutil di vagina | obat keluar nanah dari kemaluan | Kutil kelamin pada pria | obat penis mengeluarkan nanah | pengobatan kutil kelamin pada wanita hamil | menghilangkan kutil kelamin | obat untuk menghilangkan kutil di kelamin pria | pengobatan kutil kelamin pada wanita hamil | menghilangkan kutil kelamin | obat untuk menghilangkan kutil di kelamin pria | pengobatan kutil kelamin pada wanita hamil | menghilangkan kutil kelamin

obat penis said...

Father mother or sister I love what I see with this, is very interesting content that led me curious to go to your website is. our greetings alat bantu pria.

Post a Comment