Wednesday, December 12, 2012

Non-persistent connection performance improvements in 5.6

One of my favorite performance improvements in 5.6 is "faster non-persistent connections". I couldn't find this information from release notes, but non-persistent connection is really faster in 5.6.

Here are sysbench micro-benchmark results. I tested from 100 concurrent clients, running remotely. Clients simply connected and disconnected repeatedly.
versionconnections per second

The whole sysbench command I used is as below.
[remote_client]$ sysbench --test=oltp --oltp-table-size=2000000 
 --max-requests=1000000 --mysql-table-engine=innodb --db-ps-mode=disable
 --mysql-engine-trx=yes --oltp-read-only --oltp-skip-trx --oltp-dist-type=special
 --oltp-reconnect-mode=query --oltp-connect-delay=0 --db-driver=mysql
 --mysql-user=root --mysql-host=$server_host --mysql-port=$server_port
 --num-threads=100 run
I tested 1000+ connections and got similar numbers.

As you can see, 5.6 is nearly 2x faster than 5.1.

There are many performance improvements in 5.6, so I'm not sure which fix contributed the most. I assume the biggest one is that THD (quite a large C++ class inside MySQL) destructor is no longer called during holding a global mutex. Prior to 5.6, THD destructor is called under a global LOCK_thread_count mutex. This is not efficient. I experienced some serious connection/disconnection problems caused by the global mutex in MySQL Cluster a few years ago, and there were some fixes in MySQL Cluster source tree. Now in 5.6, THD destructor is called outside of the LOCK_thread_count mutex as below. This is great news.
5.1/5.5: one_thread_per_connection_end() -> unlink_thd()
  (void) pthread_mutex_lock(&LOCK_thread_count);
  delete thd;

5.6.9: one_thread_per_connection_end()
  delete thd;
5.6 also fixed some connection performance issues reported by Domas. Some of major issues are still ongoing. So once all of them are fixed, we can expect even better performance:)

Apparently persistent connection is much more efficient than non-persistent, but this is not always possible. In addition to that, 5.6 improves query performance in high concurrency as well. I believe many production engineers will welcome these performance improvements.


Chetan P. said...

Nice Work... Your work will be very useful.

Andy said...

Any idea why Maria with thread pool is slower than Maria without thread pool? Isn't the whole point of thread pool is to increase connection performance?

According to their own test, thread pool increases performance significantly:

So it's strange to see your results

Yoshinori Matsunobu said...


I believe their benchmark is using persistent connections.

Andy said...


If their benchmark was using persistent connections, why would thread pool increase performance at all?

I thought the whole idea of thread pool is to decrease connection overhead by eliminating the need to create a new thread for each new connection. In the case of persistent connection there is no new connection to be created and hence no new thread creation overhead. Hence thread pool wouldn't be helpful in this case. Did I miss something?

wlad said...

Andy, you"re mixing the a threadpool and a much older feature, thread cache. Thread cache does what you describe. Threadpool is a much more versatile feature, and it also has a kind-of thread cache inside , i.e threads do no go away once connection is finished. With lot of locking, like in degenerate case presented here, there is generally not much benefit of thread pool, and pool can be even whooping 1% slower , like in case presented here:)

Laxman Kumar said...

Cheers for submitting this. This addressed a whole lot of concerns in which I had.
pool tables

Sinisa said...


Great article.

I would just like to provide the reason why is Maria not faster with ThreadPool. I guess that this is a thread pool from MySQL version 6.0, which was never released. That thread pool is not very efficient as it uses an additional library as an abstraction layer for each OS. It does not use native functionality of each OS.

There was also a very fast ThreadPool that runs only on Solaris, developed 6 years ago, by Monty and maintained by him and me. This was a commercial feature for one big company and not available under GPL.

wlad said...

Yoshi, can you tell how to get this benchmark to run? If I do what you describe here, I'm running into socket creation problems after couple of seconds (netstat has thousands TIME_WAIT sockets, so client runs out of "socket address space", which is 64K sockets).

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

Freeing THD under mutex is now fixed in current MariaDB 5.5 tree, lp:maria/5.5. The corresponding bug is

I would appreciate if you could rerun your tests with it (my own tests do not count as much, since I'm an interested party ;))

Ornov Chowdhury Bappi said...

This article so informative. I have gotten many information form this article. Thanks for sharing this article.
You also get anther article about Business communication degree solutions:

tahir sumar said...

Most Popular and Famous Vehicles, Latest Speed Cars, Sports Cars Info and Pictures.

foxinfosoft said...

your site is very informative
Thank you
iPhone application development

Cassadee Pope said...

Very informative post! Thanks because this can be a helpful tips for businesses, such a brilliant idea!

Benchmarking Improvement

muhammad ibraheem said...

Investment Plans to make money online, Online Jobs can make money online from home, Just Visit

geetha said...

wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, MySql online trainingamong the MySql in Hyderabad. Classroom Training in Hyderabad India

caton yesiondre said...

A heads as much as close by gatherings, the score out of your most loved office 2010 key sporting activities team?ˉs latest activity, boarding passes, offer tracking, dinner reservations along with a lot much more.

XL said...


can you show your my.cnf? I've done a similar benchmark and get quite different results. Did you set thread_cache_size?

I also did some changes:

- using --oltp-test-mode=simple to get the most simple workload: connect, point select, disconnect

- using MEMORY tables with just 1000 rows to make the point select fast

- test different privilege types: --skip-grant-tables, GRANT ALL ON *.*, GRANT ALL ON sbtest.*, GRANT ALL ON sbtest.sbtest

- I've run mysqld with the default memory allocator (glibc) and with tcmalloc in LD_PRELOAD. tcmalloc yields much better results at high concurrency

Belkowski Gaylord said...

Wonderful information, I already know your blog from my friend nunn, hyderabad, I read your post at least 7 up to now, let me tell you, your site can provide the best and the most interesting information.
2013 new nike nfl jerseys

GuildWars2Items said...

Be truthful, do what you know is right, put forth effort when it is called for persist Diablo 3 Power Leveling, and the more you do so, the more easily you'll be able to do so Diablo 3 PowerLeveling.Exercise your strength, and you'll surely grow stronger, more capable, and Diablo iii PowerLeveling

GuildWars2Items said...

Life is not measured by the number of breaths we take Runescape Gold, but by the moments that take our breath away rs gold. I have a simple philosophy: Fill what's empty Runescape Gold.

Rebecca Lorenz said...

The connections seemed very interesting. And I'm really amazed. You have shared a very impressive blog! Thanks this could be a big help.

Benchmarking Improvement

Maria Arredondo said...

Life is not measured by the number of breaths we take scarlet blade gold, but by the moments that take our breath away scarlet blade gold. I have a simple philosophy: Fill what's empty scarlet blade gold.

Maria Arredondo said...

Empty what's full. Scratch where it itches. Don't go around saying the world owes you a living rs gold, The world owes you nothing. It was here first. Being happy doesn't mean that everything is perfect Runescape Gold, It means that you've decided to look beyond the imperfections. Do not pray for tasks equal to your powersrs gold.

Robin said...

This really is some agreeable material. It took me some time to finally locate this web page but it was worth the time. I noticed this article was buried in yahoo and not the very first spot. This internet web site has a lot of fine stuff and it doesnt deserve to be burried in the searches like that. By the way I'm going to add this web publication to my list of favorites.

buyGuildwars2gold 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

buyGuildwars2gold said...

c9 online goldlife is not measured by the number of breaths we take, but by the moments that take our breath away buy c9 gold, ability may get you to the top, but it takes character to keep you there cheap c9 gold.

smith rose said...

Adobe Acrobat 9 Pro Download includes Adobe LiveCycle Designer ES software for advanced form creation.
Microsoft Project 2010 Download lets you quickly zoom the timephased part of a view using a zoom slider in the status bar. Simply move the slider to the right to move zoom in (show shorter time intervals, such as days or hours) on your schedule and to the left to zoom out (show longer intervals, such as weeks or months). The zoom slider works in the Gantt Chart, network diagram, and calendar views, as well as in all graph views.

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

Lisa Parkar said...

Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration modern dinette sets

wow9gamer said...

Apparently persistent connection is much more efficient than non-persistent, but this is not always possible. In addition to thatCheap League of Legends Account

Buy LOL Coaching

Sagar Khattak said...

Entertainment for Fun... Entertainment Articles, Entertainment News, Entertainment Pictures, Bollywood, Hollywood and Lollywood Pictures and Videos, Entertainment Latest updates, Hot Entertainment News and Pictures Funny Entertainment Pictures, lol Pictures, Funny Pictures and Much More Fun Only on 1 Current Affairs Network

wow9gamer said...

pparently persistent connection is much more efficient than non-persistent, but this is not always possible. In addition to that
WOW Gold Kaufen
World of Warcraft Gold

shafique said...

Internet Users have very big Good News, Now you can earn with Just Share an add or picture on Facebook, Facebook is the Most popular Website in the World and you can make unlimited income with Just Facebook Posting Program.
Genuines Works of Data Entry, Facebook Posting, Copy Pasting, Add Posting, Clicking, Web Surfing, Website Visiting, Article Sharing, Data Sharing, Google Business Plan and Much More Business Plans.

faisal said...

lol and Funny, Troll Images, Prank Peoples, Funny Peoples, funny planet, funny facts, funny cartoons, funny movies pics, iphone funny, funny jokes, Prank Images, Fail Pictures, Epic Pictures, Lols and Gags, Lol Pictures, Funny Pictures, Lol is the Laugh out of Laugh where you can Fun Unlimited and Laughing Unlimited.

hammad sohail said...

Forex Affaris.. Latest Currency news updates, latest forex trading business updates, trading updates, forex trading latest news, forex brokers directory, forex brokers list, Dollars news affairs, Stock Markets, stock market news, stock market analysis, technology news, international forex markets, international forex business news and all updates about Forex Trading

feroz khan said...

Want to Learn Forex Trading and earn with Forex Business...?? The best Forex Trading Learning Website where you can get any thing about Forex, Trading updates, forex trading latest news, forex brokers directory, forex brokers list, Dollars news affairs, Stock Markets, stock market news, stock market analysis, technology news, international forex markets, international forex business news and all updates about Forex Trading

Momo Wu said...

good blog, this topic is really interesting, thanks for sharing, my contribution here.

hammad sohail said...

Super Cars Latest Mazda Models, Racing Cars, International Sport Cars, Concept Cars, PS-Pod, Strange Vehicles, Nissan, Royce Corniche, Ford Concept Cars, Strange Vehicles, Mercedes and More Sport Cars and Vehicles with Pictures and Info

Manoj Kusshwaha said...

Nice article impressive blog i love it

Jerukukuk said...

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

sadia sulaman said...

Excellent read, Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style.hastings-online |

steppescaribbean |

hawnbyhotel |

partridgeplace |

newelfin |

subito2 |

kingsofbassline |

beckyandneil |

antichew |

oldtimegrates |

sandi said...

panca-samudera said...

The article posted was very informative and useful
thanks for sharing.
jaring futsal , jaring futsal murah , jual jaring futsal , jaring golf , jual jaring golf , jaring golf murah ,
jaring polynet , tangga darurat , jaring truk , jaring cargo , jaring outbound , jual rumput futsal murah ,
jual rumput sintetis murah , tali tambang , cargo net , jaring tanaman , jaring kassa , jaring proyek , jaring bangunan ,
jaring gedung , jaring pengaman proyek , jaring pengaman bangunan , jaring pengaman gedung , jaring peneduh , jaring waring , kasa hijau , tangga darurat , jaring gawang futsal ,
jual jaring gawang futsal murah , jaring peneduh , jaring truk , tali tambang nylon , jaring safety , safety net , jaring , waring , polynet

Mr. Kepo said...

Non-persistent connection performance improvements in 5.6 is my inspiration
Thanks relating to indicating this information, fantastic suggests linked with present which intended theme in order to conversation.
Well, this is my first visit to your blog! Your blog provided us valuable information, You have done a marvelous job.
I have read a few of the articles on your website now, and I really like your style and I added it to my favorites blog site list.
The posts that I’ve been waiting for from fro to seek social bookmarking.
Best regards Vimax and Vimax Asli

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


Mai Doan said...

Tiny Tanks is a popular game that all of you would like. In which you can play Tiny Planes, Run 3 Unblocked Hacked unblocked Games. Or you can go to play Tiny Planes Tiny Planes Beta

Minh Vương said...

chữa thoát vị đĩa đệm
chữa thoát vị đĩa đệm ở Hà Nội
triệu chứng thoát vị đĩa đệm
nguyên nhân thoát vị đĩa đệm

رضا رمضان said...

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

Devi.Angularjs said...

JavaScript Training in Chennai | Ecmascript 6 Training in Chennai | ES6 Training in Chennai | Angular 2 Training in Chennai | HTML5 Training in Chennai

Yeoman Training | D3 Training | ReactJS Training | Gulp Training | CommonJS Training

HTML5 Training in Chennai | Angular 2 Training in Chennai | Node.js Training in chennai

jomana yusef said...

cristiburner said...

You need to see how this kid is able to make
over $2000 a month selling pictures he took
with his camera!

He lays out exactly how you can do it too!
This page might not be up for much longer
so you need to check it out right now.

Post a Comment