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
5.1.6520712
5.5.2824000
5.5.25Maria28073
5.5.25Maria,ThreadPool27653
5.6.937800

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);
  thread_count--;
  delete thd;
  ...

5.6.9: one_thread_per_connection_end()
  ...
  mysql_mutex_unlock(&LOCK_thread_count);
  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.

42 comments:

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: http://blog.mariadb.org/mariadb-5-5-thread-pool-performance/

So it's strange to see your results

Yoshinori Matsunobu said...

@Andy

I believe their benchmark is using persistent connections.

Andy said...

Yoshinori,

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

Yoshinori,

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 https://mariadb.atlassian.net/browse/MDEV-3945.

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: http://communication-business.blogspot.com/

tahir sumar said...

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

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
www.jobzcorner.com

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

Yoshinori,

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

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
hotcurrentaffairs.com

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.
www.jobzcorner.com

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.
LolsGag.Com

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
ForexAffairs.Com

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
ForexAffairs.Com

Momo Wu said...

good blog, this topic is really interesting, thanks for sharing, my contribution here. http://www.cdlfz.com/

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
SuperCars.Com

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 |

Post a Comment