Implementation and Optimizations of Percona 5.7 Thread Pool

The article begins by describing the performance degradation that occurs when MySQL handles each client connection with a dedicated thread, and introduces the thread‑pool plugin introduced by MySQL Enterprise and later adopted by MariaDB and Percona.

To enable the thread pool in Percona 5.7, the server must be started with the option --thread-handling=pool-of-threads , which maps to the static member Connection_handler_manager::thread_handling . Depending on this setting, the server constructs either a Thread_pool_connection_handler or the traditional Per_thread_connection_handler during initialization.

Connection_handler_manager::init()
{
  switch (Connection_handler_manager::thread_handling)
  {
  case SCHEDULER_ONE_THREAD_PER_CONNECTION:
    connection_handler = new (std::nothrow) Per_thread_connection_handler();
    break;
  case SCHEDULER_NO_THREADS:
    connection_handler = new (std::nothrow) One_thread_connection_handler();
    break;
  case SCHEDULER_THREAD_POOL:
    connection_handler = new (std::nothrow) Thread_pool_connection_handler();
    break;
  default:
    DBUG_ASSERT(false);
  }
}

connection_event_loop --> listen_for_connection_event
          |__ process_new_connection --> handler->add_connection

The thread‑pool consists of multiple thread_group objects, whose count is controlled by the thread_pool_size system variable. Each group contains a listener thread, several worker threads, and a shared timer thread. Connections are assigned to a group based on their THD::thread_id() .

struct connection_t // each connection
{
  THD *thd;
  thread_group_t *thread_group; // belonging group
  ulonglong abs_wait_timeout;
  bool logged_in;
  uint tickets; // priority scheduling
  bool dump_thread; // special handling for binlog dump
};

struct thread_group_t
{
  mysql_mutex_t mutex;
  connection_queue_t queue; // work queue
  connection_queue_t high_prio_queue; // high‑priority queue
  worker_list_t waiting_threads; // idle workers
  worker_thread_t *listener; // listener thread
  int pollfd;
  int thread_count;
  int active_thread_count;
  int connection_count;
  int waiting_thread_count;
  int dump_thread_count;
  int io_event_count;
  int queue_event_count;
  bool stalled;
} MY_ALIGNED(512);

Functional threads include:

Listener thread : monitors TCP/Unix sockets for new connections, creates THD and connection_t objects, assigns them to a thread_group , and pushes them onto the group's work queue.

Worker threads : repeatedly call get_event to fetch tasks from the work or high‑priority queue, then invoke threadpool_process_request (which eventually calls do_command ) to handle authentication or SQL queries.

Timer thread : periodically checks each thread_group for stall conditions and idle‑connection timeouts, invoking wake_or_create_thread or timeout_check as needed.

Optimizations introduced by Percona include:

Priority scheduling : a high‑priority queue ( high_prio_queue ) ensures that connections holding explicit transactions, LOCK TABLES , or long‑running binlog dump commands are processed first, reducing lock‑wait deadlocks.

Low‑priority queue throttling : when the sum of active and waiting threads exceeds thread_pool_over_subscribe + 1 , workers skip low‑priority tasks to prevent thread‑pool oversubscription.

Special handling for COM_BINLOG_DUMP and COM_BINLOG_DUMP_GTID : these long‑running commands are excluded from active_thread_count to avoid starving other work.

In summary, the article provides a detailed walkthrough of Percona 5.7's thread‑pool architecture, its configuration, core components, and the performance‑enhancing tweaks that address deadlock risks and thread‑pool scaling.