MySQL Enterprise Scalability

by Lucas Vogel

The MySQL Thread Pool is a server-side component in MySQL Enterprise that helps sustain server performance over an increasing number of simultaneous user connections to the database server. See how MySQL Enterprise maintains up to 60x better scalability than MySQL Community.

The MySQL 8.0 Thread Pool is a proprietary server-side plugin component that uses its own algorithm to help build sustainable scalability within MySQL 8.0 Server. Despite the internal complexities of the plugin, Installing and operating the thread pool plugin is fairly simple. It is highly recommended that the Query Analyzer component of MySQL 8.0 Enterprise Monitor to make sure you don't have any inefficient queries creating performance and scalability issues within your application.

To help provide a thorough understanding of it, we're going to paraphrase some key highlights from the blog of Mikael Ronstrom, Senior MySQL Architect at Oracle, who explains this plugin in great detail on his blog.

The Problem Domain

Within MySQL Server, there is a one-to-one mapping between threads and connections. This creates some serious bottlenecks where the number of connections and SQL statements executing exceeded the number of CPUs available to handle them. As the CPU work increases, the CPU cache rate limit decreases, resulting in more work for the CPU and slowing down the system as a whole. This also created 'hot spots' in the database engine where multiple chained statements would take longer to execute, creating even more scalability issues.

The Problem Solution

To tackle this problem, the Thread Pool plugin creates thread groups, each managing connections, threads, queues and other data structures required to implement thread group operations. The main task of the plugin is to try to operate a thread group to ensure there are only one or zero concurrently executed queries per thread group. There are two scenarios where this is ignored, however. In the first scenario, a long-running query is considered stalled and therefore not running as an active connection, allowing other threads to execute. This also happens whenever an execution statement is blocked by locks or I/O operations. Callbacks are made to the thread pool when a block begins and ends, allowing the thread pool to decide when to start a new query or when to allow a new incoming query to start.

Another performance impact is on concurrent transactions, emphasizing the need to keep the number of transactional statements to a minimum. This is handled by prioritizing queued queries based on whether they've already started a transaction or not. Transactions will also create a higher priority for the connection that initiated the transaction. To minimize any side effects of prioritized transactions that take too long, the query will be moved to the high priority queue after a time period has elapsed.

It's also possible to prioritize database connections to make sure queries from that connection always move faster through the database server.

When to use the Thread Pool plugin

The threads_running variable keeps track of the number of threads running concurrently in the database server. If this number spikes into a region where performance suffers (the general guideline is above 40 for InnoDB workloads), then the Thread Pool plugin will benefit your workload. The innodb_thread_concurrency setting should also be configured; instructions on tuning this setting can be found here.

MySQL Thread Pool vs Connection Pool

Threads and connections in MySQL appear to be very intertwined, and for good reason; however, this does not completely apply to the connection pool. The thread pool consists of a number of thread groups, each of which manages a set of connections. When connections are established, they're assigned to the thread pool assigns them to thread groups in round-robin fashion.

The thread pool separates connections and threads, so there is no fixed relationship between connections and the threads that execute statements received from those connections. This differs from the default thread-handling model that associates one thread with one connection such that the thread executes all statements from the connection.

A connection pool maintains a cache of existing connections; these connection pools exist at both the client and server side.

Enterprise Scalability

As with most engines, you get the most out of them by fine-tuning your environment to the workload you are handling. This is no exception to MySQL; however, every update seems to bring in new performance improvements, a great deal of which can be found in the release notes. But MySQL 8 has proven itself to be a drastic performance improvement to the previous version, not only in terms of scalability but in overall performance as well.

If you would like to evaluate the thread pool plugin and see if it would benefit your workload, feel free to reach out and we'd be happy to set you up with an environment you can evaluate for yourself.