Titles in this page

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.