Thread_pool_hybrid – a faster more scalable MySQL connection handler

MySQL thread pool hybrid (poll/epoll) client connection handler.

Faster and more scalable than both the default MySQL Community Edition connection per thread handler (see benchmarks) and the MySQL Enterprise Edition thread pool handler (see benchmarks).

Allows much higher client counts without a drop in throughput vs the closed source "thread pool" handler while keeping the low latency performance of open source "connection per thread" handler, when the client count is low. When the count of clients gets very high and therefore threads, becomes too high the cost of too frequent context switching starts to dominate the CPU and memory bus. And adding more clients makes the time spent context switching greater and greater, and means there is less time available to do actual work.

So before that can happen, thread_pool_hybrid has each thread waiting on a single client socket and a shared eventfd in poll(...). When reaching the maximum number of threads it gets a notification through the eventfd and switches to calling epoll_wait(...). And now instead of getting messages from a single client socket, a thread can get them from any open client socket added to the thread pool epoll instance. When this happens each thread in the pool was either processing a request or waiting on poll(...). And threads waiting on poll(...) will get the message, those processing a request will, when they are done processing the current request, will check and see they are supposed to call epoll_wait(...).

There are, by default, the same number of independent thread pools as there are processors available to the machine. When a new connection comes in, add_connection(...) in the handler is called by MySQL, and it looks to see which is the next thread pool via the global atomic<size_t> next_thread_pool variable. It assigns the client socket to it, and then increments the next_thread_pool unless it is on the last thread pool, in which case it changes next_thread_pool to zero. This helps ensure all thread pools are saturating the CPUs relatively evenly.

First, compile the plugin and install in to plugin dir

Then, you can load the plugin into mysqld via the mysql client:

You uninstall like this:

However it will uninstall the library and then crash the server! This is because uninstalling this way removes the thread_pool_hybrid code from the running server, but that same code is currently serving the client request. So it crashes, having the code it is in the middle of running, disappear.

Or you can install by adding this to a config file (often in /etc/my.cnf):

Or you can add a launch parameter like this:

And uninstall by removing these settings.

If your Linux server is dedicated to just running MySQL, you should probably not worry about changing the defaults, you won't see much, if any, performance gain by changing things. But if the server is shared with a application server and or you just want to play around, here are all the configurable parameters you can add the configuration file or command line parameters.

Total thread pools. Setting to 0 defaults to number cores available.

Maximum number of the threads per pool. Though the total count can grow larger than this if all the threads are waiting on lock(s), as the connection holding the lock(s) might not have a thread and the server would hang due to resource deadlock. So it will add a thread and serve another connection until all the coonections either have a thread or the lock(s) are released.

Until the thread counts reaches maximum, the minimum threads waiting in epoll_wait. This is so if there is a sudden surge in connections the server can keep up by using threads in reserve.

How long extra threads -- above thread_pool_hybrid_min_waiting_threads_per_pool -- should wait in milliseconds inside epoll_waiting before dyings. 0 to instantly die.

Enables or disables connection-per-thread mode until the connection count is greater than thread_pool_hybrid_max_threads_per_pool. Why switch it off? I dunno, to test how slow epoll is with a lower connection count?

When set to file that the MySQL process has write access to, will append a bunch of debugging messages to the file as it works. This is dynamic and can be turned on like this:

Then you can actviely observe the debug messages in near real time with the command line:

If the server can't open the file for writing, it will not set the value.

You can turn off the debug messages with:

The debug messages look like this:

An explanation of the values:

302385=sequence number 256980=thread_id [29=epollfd,3=threads,3=epoll_waiting,0=lock_waiting,0=connections] Waiting in epoll=message

This function gives information about the Nth (starting from zero) thread pool.

Valid range for thread_pool is 0 to thread_pool_hybrid_total_thread_pools - 1.

Valid numbers for info are:

You must first activate the user defined function (UDF) like this:

To display all the information about the first thread pool at once, use this line: