Aloha – MySQL Dives into the Thread Pool

By now you have probably heard about the MySQL thread pool plugin and API, but you may not have fully processed the details. Here’s the quick summary:  With the new thread pool plugin, there is now an alternative way to handle connection threads in MySQL Enterprise Edition.  With the plugin, MySQL connection threads are shared like an extraordinarily well managed timeshare in Hawaii.  When one connection is “idle”, asking nothing of and expecting nothing from the database, another connection can use that same thread for its database requests.  Threads are released by each connection as soon as the request is completed and  go back into the pool for re-use – just like the theoretical timeshare is up for grabs on the weeks you are not there.

In the older, and still default connection thread model, threads are dedicated to a single client  for the life of the connection and there are as many threads as there are clients currently connected to the database.  This has some disadvantages when the server workload must scale to handle large numbers of connections, and the overhead can be signficant. This occurs for several reasons:

  • Lots of threads use lots of memory and can make the CPU cache ineffective
  • Too many active threads trying to execute in parallel may cause a high level of resource contention and be inappropriate for the amount of parallelism available

The new thread pool plugin offers an alternative thread pool implementation, and focuses on limiting the number of concurrent, short running statements to mazimize performance and reduce overhead.  By limiting the number of concurrent, short running statements and sharing threads, we can control the number of active threads at any one time.  Thread management has been revamped and by managing these threads in a highly efficient manner, we end up reducing overhead and maintaining performance levels as the number of users increase.

Here are the mechanics:  In the new plugin, threads are organized into groups (16 by default but configurable up to 64 on server startup).  Each group starts with one thread and can increase to a maximum of 4096 threads.  Additional threads are created only when necessary.  Each incoming connection request is assigned to a group by round robin. Each group has one listener thread that listens for incoming statement requests.

When a statement request comes in, it is executed immediately by the group’s listener thread if it is not busy and there are no other statement requests waiting.  If the statement request finishes quickly, the listener thread then efficiently returns to listening and is available to execute the next incoming request, preventing the need for a new thread to be created.   If the request does not finish quickly, it runs to completion but another thread is  created as the new listener.

If the listener thread is busy, the request is queued.  There will be a very brief time (configurable with the thread_pool_stall_limit system variable which defaults to 60 ms) while we wait to see if the currently executing statement will finish quickly or not. If it finishes quickly (under thread_pool_stall_limit), we can re-use this thread for the next request in the queue, eliminating the overhead of creating a new thread or having too many short statement trying to execute in parallel .

You can see how this thread pool design strives to have one thread executing per group at any time . The number of groups (thread_pool_size_variable) is very important, because it approximates the number of short running statements that will be executing concurrently at any one time.  Long running statements are prevented from causing other statements to wait, since if they go beyond the thread_pool_stall_limit, another thread will be started and the next request in the queue will execute on it.

Your predominant storage engine will help determine the number of groups you should have.  For InnoDB, between 16 and 36 groups seems to work well in many cases, but for MyISAM set it much lower (4-8).

There are two queues for waiting statements, low and high priority.  The low priority queue contains:

  • all statements for non-transactional storage engines
  • all statements if autocommit is enabled
  • the first statement in  an InnoDB transaction

These statements do not languish in the low priority queue forever since they will get kicked over to the high priority queue when the thread_pool_kickup_timer times them out. However, there is a maximum number of statements that can be moved per time period to keep things under control.

The high priority queue contains

  • any subsequent statements in InnoDB transactions, and
  • any statements kicked up from the low priority queue.

You can find the thread pool plugin and other commercial extensions in MySQL 5.5.16 and above, available on http://support.oracle.com and the Oracle Software Delivery Cloud https://edelivery.oracle.com .  This release contains a plugin library object file which must be placed in the appropriate directory.  The server must then be started with the –plugin-load option. Documentation  and complete install directions for the plugin can be found at http://dev.mysql.com/doc/refman/5.5/en/thread-pool-plugin.html.   There is also a thread pool API available in the Community Edition.

Happy swimming!

Lynn Ferrante has worked with databases in the enterprise for her whole career at MySQL, Oracle, Sybase, and Ingres.  She also worked on an open source project called GenMapp (University of California, San Francisco), and contributed to the development of many database applications in the fields of energy and environment

About these ads

7 thoughts on “Aloha – MySQL Dives into the Thread Pool

    • Client connection pools aim at reducing the number of connections by reusing them. This means that it can have a limited positive impact on CPU caching by reusing some cached data from one query to another. However the client connection pools will not at all affect whether the threadpool will be useful or not. The number of concurrent queries in the MySQL Server will be the same with or without a connection pool. The job of the threadpool is to limit the number of concurrent queries executing in parallel. It will do so independently of the connection pool.

      The threadpool and the client connection pool really solves two orthogonal problems. The client connection pool solves the problem of having to connect to the MySQL Server often by caching closed connection on the client side. The threadpool solves the scheduling problem when too many connections are executing in parallel.

      The threadpool problem can only be solved on the server side. The client side can only limit execution of queries from one client and has no access to the global state of all connections.

    • Hi Zhang. The thread pool plugin is a commercial feature, but the API is available in both the Community and Enterprise editions of MySQL 5.5.16 and above. The plugin implementation is of course based on the API and is quite different from any previous implementations . It is a complete redesign with fantastic scalability.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s