Digging into MariaDB's Source 4: Connections and Threads — One Thread per Connection, and the Thread Pool

What this post covers

This is the fourth post in the "Digging into MariaDB's Source" series. In part 3 we watched mysqld_main() initialize every plugin and engine and finally enter the poll() loop in handle_connections_sockets(). This post starts in that loop. It zooms into the ① Connection zone of the part 2 map, plus the first half of the ② THD · commands zone — the part where the THD is created.

Blue boxes are covered here. Grey boxes come in later posts.

We want to answer four questions.

• From the moment a client connects to port 3306 until the server is ready for its first SQL statement, which threads run which functions?
• Where in the code are Threads_connected, Threads_created and Threads_cached (from SHOW GLOBAL STATUS) counted, and where do thread_cache_size and max_connections take effect?
• What changes with thread_handling=pool-of-threads, and where does an idle connection wait in each mode?
• Can one long query in the thread pool hold up other connections — and what exactly does thread_pool_stall_limit measure?

All line numbers in this post refer to that commit. How to check out the same commit and build the Docker image used for the examples is in part 1.

threadpool_generic.cc alone is 1,770 lines, and besides Linux's epoll it also contains versions for BSD's kqueue, Solaris event ports and Windows IOCP. Authentication (sql_acl.cc) is over 17,000 lines. This post picks only the main path that someone opening a database server's source for the first time can follow; what was left out is listed by name and location in "What this post leaves out" at the end.

Terms to know first

CONNECT: a small object the main thread creates as soon as accept() returns a socket (sql/sql_connect.h line 32). It holds only the socket, the socket type (TCP or Unix socket), the scheduler that will handle the connection, and the connection number (thread_id) it is about to receive. Think of it as "a connection that has no THD yet". Once the thread handling the connection has built a THD, the CONNECT is deleted.

THD (thread descriptor): the biggest object in the server, holding all state for one connection (class THD in sql/sql_class.h). Session variables, the current statement's LEX, open tables, the transaction and the network buffer (NET) all live here. Despite "thread" in its name, it belongs to a connection, not a thread — a key point of this post. In the thread pool, one THD moves between many threads. We saw the overall shape of THD in part 2.

Scheduler (scheduler_functions): a bundle of function pointers that decides "which thread gets a new connection" and "who is told when a connection starts waiting for something". The code is C++, but this is not a class with virtual functions — it is a C-style struct of function pointers. This one struct does the job that an MPM does in httpd (httpd part 8).

thread_handling: the system variable that picks the scheduler. Its values are one-thread-per-connection, no-threads (the main thread does everything) and pool-of-threads (the thread pool). It can only be set at startup (it is read-only).

Thread cache: instead of destroying a thread when its connection ends, the server parks it for a while and reuses it for the next connection (class Thread_cache in sql/thread_cache.h). Its size is thread_cache_size.

Groups, listener and workers in the thread pool: the thread pool splits connections into a few groups (thread_pool_size of them). Each group has its own epoll instance and work queues, and one of the group's threads (workers) acts as the listener, watching every connection socket of the group with epoll. Section 6 covers this in detail.

epoll: a Linux kernel facility where you register many sockets and ask "tell me which ones have data to read". One thread can watch thousands of sockets. It is the same OS facility that httpd's event MPM and Tomcat's Poller use.

1. The main thread — poll(), accept(), and build a CONNECT

The main thread only accepts the socket and passes it on wrapped in a CONNECT; it does not talk to the client (the one exception is when there are too many connections and it sends a single error packet before closing — example 2). From the handshake on, everything is the connection thread's job.

The poll() loop

As we saw in part 3, mysqld_main() ends in run_main_loop(), which is just handle_connections_sockets(). It puts every listening socket (TCP 3306, the Unix socket, and extra_port if configured) into a pollfd array, adds one pipe used to signal shutdown, and loops like this.

  while (!abort_loop)
  {
    retval= poll(fds.get_pos(0), fds.size(), -1);
    // ...
    for (uint retry=0; retry < MAX_ACCEPT_RETRY && !abort_loop; retry++)
    {
      size_socket length= sizeof(struct sockaddr_storage);
      MYSQL_SOCKET new_sock;

      new_sock= mysql_socket_accept(key_socket_client_connection, sock,
                                    (struct sockaddr *)(&cAddr),
                                    &length);
      if (mysql_socket_getfd(new_sock) != INVALID_SOCKET)
        handle_accepted_socket(new_sock, sock);
      else if (socket_errno == SOCKET_EAGAIN || socket_errno == SOCKET_EWOULDBLOCK)
        break;

sql/mysqld.cc lines 6888–6932

The last argument of poll(), -1, means "wait forever". On wake-up it finds which listening socket became readable (the omitted lines 6911–6919) and calls accept() repeatedly until it gets EAGAIN. The listening sockets were made non-blocking before the loop (set_non_blocking_if_supported(), line 6863), so once the queued connections are drained, accept() returns EAGAIN instead of blocking. One wake-up accepts everything that piled up.

handle_accepted_socket() — building a CONNECT

The accepted socket goes to handle_accepted_socket(). Apart from the TCP wrapper check (HAVE_LIBWRAP), it is a single statement.

  if (auto connect= new CONNECT(new_sock,
                                sock.is_unix_domain_socket ?
                                VIO_TYPE_SOCKET : VIO_TYPE_TCPIP,
                                sock.is_extra_port ?
                                extra_thread_scheduler : thread_scheduler))
    create_new_thread(connect);

sql/mysqld.cc lines 6813–6818

Note that the scheduler is chosen per connection. A connection on the normal port gets thread_scheduler; one on extra_port gets extra_thread_scheduler. As section 2 shows, the two can run in different modes.

create_new_thread() — checking the count and assigning a number

  if ((*connect->scheduler->connection_count)++ >=
      *connect->scheduler->max_connections + 1)
  {
    DBUG_PRINT("error",("Too many connections"));
    connect->close_with_error(0, NullS, ER_CON_COUNT_ERROR);
    DBUG_VOID_RETURN;
  }

  uint sum= connection_count + extra_connection_count;
  if (sum > max_used_connections)
  {
    max_used_connections= sum;
    max_used_connections_time= time(nullptr);
  }

  // ... (comment about the embedded library omitted)
  connect->thread_id= next_thread_id();
  connect->scheduler->add_connection(connect);

sql/mysqld.cc lines 6735–6756

Three things happen.

1. It increments the connection count and checks it in one go. If the value before incrementing is max_connections + 1 or more, the connection is refused. So at this point one more than max_connections can get in. Who that extra slot is for is decided during authentication in section 4.
2. It updates the Max_used_connections status variable.
3. It assigns the connection number. The value from next_thread_id() is the Id in SHOW PROCESSLIST, the value of CONNECTION_ID(), and the number you pass to KILL <id>. It is called thread_id, but it is really a connection number.

The last line passes the connection to the scheduler's function pointer add_connection. That is the end of the main thread's work; it goes straight back to poll(). The count is decremented by unlink_thd() when the connection ends (--*thd->scheduler->connection_count;, line 3096). Threads_connected in SHOW STATUS shows this connection_count directly (line 8218) — despite the name, it counts connections, not threads.

Example 1: the accept path and the login path in gdb

To check that order, we attached gdb to a running server. This example was run with MariaDB 13.2.0 (13.2.0-MariaDB-debug), a Debug build of the reference commit (c894d97781) as-is, inside a Docker container (Ubuntu 24.04). The example folder is ex/u04-gdb, and the relevant part looks like this.

# Example written for this post (not MariaDB source)
# ...
lab-start > /dev/null
gdb -p "$(cat /tmp/mariadbd.pid)" -batch \
    -ex 'break create_thread_to_handle_connection' -ex 'break acl_authenticate' \
    -ex 'continue' -ex 'bt' -ex 'continue' -ex 'bt' -ex 'detach' > /tmp/bp.txt 2>/dev/null & GDB=$!
sleep 5
mariadb -e "SELECT 'hello' AS greeting" > /dev/null
# ...

[shortened from ex/u04-gdb/run.sh]

Backtraces at the two breakpoints (argument values and addresses removed):

=== 0. breakpoints: who accepts, who logs in
#0  create_thread_to_handle_connection at /usr/src/mariadb/sql/mysqld.cc:6682
#1  create_new_thread at /usr/src/mariadb/sql/mysqld.cc:6756
#2  handle_accepted_socket at /usr/src/mariadb/sql/mysqld.cc:6818
#3  handle_connections_sockets at /usr/src/mariadb/sql/mysqld.cc:6930
#4  run_main_loop at /usr/src/mariadb/sql/mysqld.cc:6171
#5  mysqld_main at /usr/src/mariadb/sql/mysqld.cc:6590
#6  main at /usr/src/mariadb/sql/main.cc:34

#0  acl_authenticate at /usr/src/mariadb/sql/sql_acl.cc:17039
#1  check_connection at /usr/src/mariadb/sql/sql_connect.cc:1184
#2  login_connection at /usr/src/mariadb/sql/sql_connect.cc:1252
#3  thd_prepare_connection at /usr/src/mariadb/sql/sql_connect.cc:1440
#4  do_handle_one_connection at /usr/src/mariadb/sql/sql_connect.cc:1504
#5  handle_one_connection at /usr/src/mariadb/sql/sql_connect.cc:1426
#6  pfs_spawn_thread at /usr/src/mariadb/storage/perfschema/pfs.cc:2198
#7  ?? () from /lib/x86_64-linux-gnu/libc.so.6
#8  ?? () from /lib/x86_64-linux-gnu/libc.so.6

The two backtraces have different bottoms. The first is the main thread, coming up from main(); the second is a new thread starting in handle_one_connection(). You can see directly that accepting and logging in happen on different threads. pfs_spawn_thread is a wrapper that performance_schema inserts when threads are created (this build includes performance_schema, so the frame appears even when it is disabled at runtime). We checked that every line number matches the reference commit.

Example 2: "Too many connections" comes from two places

To see the max_connections + 1 slot, we started the server with --max-connections=10, kept 10 connections open as an unprivileged user app, and then tried the following (ex/u04-max-connections).

# Example written for this post (not MariaDB source)
# ...
lab-start --max-connections=10 > /dev/null
mariadb -e "CREATE USER app@localhost; SELECT @@max_connections"

echo "--- 1. ten idle 'app' clients"
for i in $(seq 1 10); do (sleep 8 | mariadb -u app) & done
sleep 3

echo "--- 2. 11th client as 'app'"
mariadb -u app -e "SELECT 'app got in' AS result" 2>&1 || true

echo "--- 3. 11th client as root (admin)"
# keep one admin connection open with SLEEP(4) while we try the next step (-n: print after each statement)
mariadb -n -N -e "SELECT 'root got in'; SELECT SLEEP(4)" > /tmp/root.out 2>&1 &
sleep 1.5
cat /tmp/root.out

echo "--- 4. 12th client as root"
mariadb -e "SELECT 'second root got in' AS result" 2>&1 || true
# ...

[shortened from ex/u04-max-connections/run.sh]

@@max_connections
10
--- 1. ten idle 'app' clients
--- 2. 11th client as 'app'
ERROR 1040 (08004): Too many connections
--- 3. 11th client as root (admin)
root got in
--- 4. 12th client as root
ERROR 2002 (HY000): Received error packet before completion of TLS handshake. The authenticity of the following error cannot be verified: 1040 - Too many connections
--- 5. status after everyone left
+-----------------------------------+-------+
 Variable_name                     | Value |
+-----------------------------------+-------+
 Aborted_connects                  | 2     |
 Connection_errors_max_connections | 1     |
 Max_used_connections              | 11    |
+-----------------------------------+-------+

Same error 1040, two different shapes.

• The 11th app client passed create_new_thread() (the value before incrementing, 10, is less than 10 + 1). A connection thread was created and authentication finished, and then it was stopped by the check in acl_authenticate() that we'll see in section 4. The handshake had completed, so the client gets a plain ERROR 1040.
• The 11th root client skipped that same check thanks to its admin privilege. That is the "one more" slot.
• The 12th root client was stopped in create_new_thread() (value before incrementing 11 ≥ 11). There is no connection thread and no handshake: on the main thread, close_with_error() creates a throwaway THD just to send the error, close_connection() sends one error packet, and the socket is closed (sql_connect.cc lines 1600–1613, mysqld.cc lines 3037–3041). The client got an error packet instead of the server greeting (handshake), so it wraps it in error 2002, "received error packet before completion of TLS handshake". The wording comes from the reference commit's mariadb client trying TLS by default.

The status variables count this difference too. Connection_errors_max_connections is only incremented by CONNECT::close_and_delete() when the error is ER_CON_COUNT_ERROR (lines 1585–1586), so it counted only the 12th root; Aborted_connects counted both.

Easy to get wrong: the admin slot is about CONNECTION ADMIN, not SUPER

Many people have read in older MySQL docs that "one slot is reserved for users with the SUPER privilege". In the reference commit, the privilege this check looks at is PRIV_IGNORE_MAX_CONNECTIONS, which equals CONNECTION_ADMIN_ACL (sql/privilege.h line 428) — the name after privileges were split up. And the slot is "one, no matter how many admins": like the 12th root in the example, a second admin is refused too.

2. The scheduler — a bundle of function pointers chosen by thread_handling

What add_connection points to is decided at startup. First, the struct.

struct scheduler_functions
{
  uint max_threads;
  Atomic_counter<uint> *connection_count;
  ulong *max_connections;
  bool (*init)(void);
  void (*add_connection)(CONNECT *connect);
  void (*thd_wait_begin)(THD *thd, int wait_type);
  void (*thd_wait_end)(THD *thd);
  void (*post_kill_notification)(THD *thd);
  void (*end)(void);
  /** resume previous unfinished command (threadpool only)*/
  void (*thd_resume)(THD* thd);
};

sql/scheduler.h lines 28–41

There are seven function pointers; leaving out thd_resume, which only the thread pool uses, these are the scheduler's contract.

A pointer may be empty. Callers use the MYSQL_CALLBACK(obj, func, params) macro, which means "call it if the object and function exist" (sql/sql_callback.h lines 33–37). The one-thread-per-connection mode does not fill thd_wait_begin, so wait notifications do nothing in that mode.

Three values and their defaults

static const char *thread_handling_names[]=
{
  "one-thread-per-connection", "no-threads",
#ifdef HAVE_POOL_OF_THREADS
  "pool-of-threads",
#endif
  0
};

#if defined (_WIN32) && defined (HAVE_POOL_OF_THREADS)
/* Windows is using OS threadpool, so we're pretty sure it works well */
#define DEFAULT_THREAD_HANDLING 2
#else
#define DEFAULT_THREAD_HANDLING 0
#endif

sql/sys_vars.cc lines 3478–3492

The default depends on the OS. On Linux it is value 0, one-thread-per-connection; on Windows it is value 2, pool-of-threads (the comment: Windows uses the OS thread pool, so we're pretty sure it works well). HAVE_POOL_OF_THREADS is turned on by sql/CMakeLists.txt on Linux, Solaris, Windows and systems with kqueue (lines 229–234). The variable is declared READ_ONLY (line 3497), so SET GLOBAL cannot change it; only my.cnf or a startup option can.

The struct is filled according to the value in get_options().

  if (thread_handling <= SCHEDULER_ONE_THREAD_PER_CONNECTION)
    one_thread_per_connection_scheduler(thread_scheduler, &max_connections,
                                        &connection_count);
  else if (thread_handling == SCHEDULER_NO_THREADS)
    one_thread_scheduler(thread_scheduler, &connection_count);
  else
    pool_of_threads_scheduler(thread_scheduler,  &max_connections,
                                        &connection_count); 

  one_thread_per_connection_scheduler(extra_thread_scheduler,
                                      &extra_max_connections,
                                      &extra_connection_count);

sql/mysqld.cc lines 9393–9404

The extra_port scheduler is always "one thread per connection", whatever thread_handling says.

The last three lines matter. extra_port is always one thread per connection. It is an emergency exit, left so an administrator can get in and look around when the thread pool is stuck. When the pool cannot create more workers, the message it writes to the error log even says you can still connect with a superuser account via extra_port to troubleshoot (threadpool_generic.cc lines 1724–1732). extra_max_connections defaults to 1 (sys_vars.cc line 7145).

With no-threads, add_connection becomes handle_connection_in_main_thread(), which is the single line do_handle_one_connection(connect, false) (lines 6671–6674). The main thread handles that connection itself until it ends, so it cannot accept anything else meanwhile.

Easy to get wrong: the enum has no pool-of-threads

enum scheduler_types in scheduler.h has only three entries: SCHEDULER_ONE_THREAD_PER_CONNECTION=0, SCHEDULER_NO_THREADS and SCHEDULER_TYPES_COUNT (lines 57–69). There is no name for value 2 (pool-of-threads); as the code above shows, "neither of the two" (else) means thread pool. So other code asks "is this the thread pool?" as thread_handling == SCHEDULER_TYPES_COUNT (e.g. sql/wsrep_thd.cc line 509). Comparing against "the count" looks like a bug at first sight, but it is intentional; the comment above the enum says the last entry is also used to mark the thread handling as dynamic (lines 53–55).

3. One thread per connection — handle_one_connection() and the thread cache

In the default mode, add_connection is create_thread_to_handle_connection() (scheduler.cc line 117).

void create_thread_to_handle_connection(CONNECT *connect)
{
  DBUG_ENTER("create_thread_to_handle_connection");

  if (thread_cache.enqueue(connect))
    DBUG_VOID_RETURN;

  /* Create new thread to handle connection */
  inc_thread_created();
  DBUG_PRINT("info",(("creating thread %lu"), (ulong) connect->thread_id));
  connect->prior_thr_create_utime= microsecond_interval_timer();

  pthread_t tmp;
  if (auto error= mysql_thread_create(key_thread_one_connection,
                                      &tmp, &connection_attrib,
                                      handle_one_connection, (void*) connect))
  {
    // ... (send ER_CANT_CREATE_THREAD and close the connection)
  }
  DBUG_PRINT("info",("Thread created"));
  DBUG_VOID_RETURN;
}

sql/mysqld.cc lines 6681–6711

If a thread is parked in the cache, it gets the connection (enqueue()); only otherwise is a new thread created. Only a new thread bumps the Threads_created status variable via inc_thread_created(). So if Threads_created keeps growing, the cache is too small.

The life of a connection thread

A new thread starts in handle_one_connection(). It names itself one_connection (line 1421; on Linux this becomes the OS thread name via pthread_setname_np()) and calls do_handle_one_connection(connect, true). The heart of that function is its outer for loop.

  for (;;)
  {
    bool create_user= TRUE;

    mysql_socket_set_thread_owner(thd->net.vio->mysql_socket);
    if (thd_prepare_connection(thd))
    {
      create_user= FALSE;
      goto end_thread;
    }      

    while (thd_is_connection_alive(thd))
    {
      if (mysql_audit_release_required(thd))
        mysql_audit_release(thd);
      if (do_command(thd))
	break;
    }
    end_connection(thd);

end_thread:
    close_connection(thd);

    if (thd->userstat_running)
      update_global_user_stats(thd, create_user, time(NULL));

    unlink_thd(thd);
    if (IF_WSREP(thd->wsrep_applier, false) || !put_in_cache ||
        !(connect= thread_cache.park()))
      break;
    // ... (re-attaching performance_schema instrumentation for the new connection omitted)
    if (!(connect->create_thd(thd)))
    {
      // ... (out of resources: close the connection and end the thread)
    }
    delete connect;
    // ... (store_globals() switches thread globals to the new connection)
    server_threads.insert(thd);
  }
  delete thd;

sql/sql_connect.cc lines 1499–1555

One thread serves several connections in turn. A thread woken from the cache even reuses the THD object.

Three things to notice.

1. The inner while is the life of the connection. do_command() reads and runs one packet and returns, over and over, while the connection is alive. The inside of do_command() (reading packets and dispatch_command()) is in part 5. In this mode, even while the connection is idle, this thread waits inside do_command() for the next packet (we'll see it in gdb in section 5).
2. The outer for is the life of the thread. When the connection ends, it enters the cache with thread_cache.park(), and when it receives a new connection it goes back to the top. If park() returns NULL (the cache is full or time is up), the loop exits, delete thd runs and the thread ends.
3. The THD is reused. The first time, connect->create_thd(NULL) (line 1470) creates a new THD; after waking from the cache, connect->create_thd(thd) passes in the old one. create_thd() then clears it with thd->reset_for_reuse() and just changes the connection number (lines 1626–1636), avoiding re-allocating a big object.

Thread_cache::park() — the cache is one queue and one condition variable

    mysql_mutex_lock(&LOCK_thread_cache);
    if ((connect= list.get()))
      cached_thread_count++;
    else if (cached_thread_count < thread_cache_size && !kill_cached_threads)
    {
      /* Don't kill the thread, just put it in cache for reuse */
      DBUG_PRINT("info", ("Adding thread to cache"));
      cached_thread_count++;
      for (;;)
      {
        int error= mysql_cond_timedwait(&COND_thread_cache, &LOCK_thread_cache,
                                         &abstime);
        flushed= kill_cached_threads;
        if ((connect= list.get()))
          break;
        else if (flushed || error == ETIMEDOUT || error == ETIME)
        {
          /*
            If timeout, end thread.
            If a new thread is requested, we will handle the call, even if we
            got a timeout (as we are already awake and free)
          */
          cached_thread_count--;
          break;
        }
      }
    }
    mysql_mutex_unlock(&LOCK_thread_cache);

sql/thread_cache.h lines 166–193

The cache is a waiting line of CONNECTs (list) plus one condition variable, COND_thread_cache. Parked threads wait on the condition variable; when the main thread's enqueue() puts a CONNECT in the line and calls mysql_cond_signal() (lines 119–132), one wakes up and takes it. cached_thread_count is the Threads_cached status variable (show_cached_thread_count(), mysqld.cc lines 7973–7980). The wait limit abstime is THREAD_CACHE_TIMEOUT, which is 5*60 seconds (sql/sql_const.h line 262): if no new connection arrives within 5 minutes, the thread ends by itself. The description of thread_cache_size says the same — threads are freed after 5 minutes of idle time (sys_vars.cc line 4493). FLUSH THREADS wakes and ends every parked thread via thread_cache.flush() (sql_reload.cc lines 398–399).

Example 3: connections and threads move together

On a server in the default mode, we attached 5 idle clients, detached them, then attached 3 more, printing the status variables and the number of one_connection threads counted with ps -L (ex/u04-one-thread). Idle clients were made with (sleep 6 | mariadb) & — a mariadb whose standard input is empty, so it connects and sends nothing.

--- 0. settings
@@thread_handling	@@thread_cache_size	@@max_connections
one-thread-per-connection	151	151
--- 1. before
connected=1 created=1 cached=0 
one_connection threads (ps -L): 1
--- 2. five idle clients connected
connected=6 created=6 cached=0 
one_connection threads (ps -L): 6
+---------------------------+------+-------+
 NAME                      | ID   | CMD   |
+---------------------------+------+-------+
 thread/sql/one_connection |    6 | Sleep |
 thread/sql/one_connection |    7 | Sleep |
 thread/sql/one_connection |    8 | Sleep |
 thread/sql/one_connection |    9 | Sleep |
 thread/sql/one_connection |   10 | Sleep |
 thread/sql/one_connection |   12 | Query |
+---------------------------+------+-------+
--- 3. the five clients disconnected
connected=1 created=6 cached=5 
one_connection threads (ps -L): 6
--- 4. three new idle clients
connected=4 created=6 cached=2 
one_connection threads (ps -L): 6

The mariadb -e that asks for the status is itself a connection, so connected always includes 1 for itself. The table lists threads named thread/sql/one_connection in performance_schema.threads (for this example the server was started with --performance-schema=ON). Connection numbers differ between runs.

• Step 2: 6 connections, 6 threads. The five idle (Sleep) connections each hold a thread.
• Step 3: the clients have left, but the 6 threads are still there and cached=5. Five are sleeping inside park(); one is serving the connection asking for the status right now.
• Step 4: 3 new clients plus the status query came in, yet created did not rise above 6. All four woke cached threads, leaving 2 in the cache.

Easy to get wrong: thread_cache_size's default of 256 shows up as 151

The DEFAULT of Sys_thread_cache_size is 256 (sys_vars.cc line 4495), yet step 0 printed 151. At the end of get_options() the server shrinks it, reasoning that the cache never needs to be bigger than max_connections.

  /* Ensure that some variables are not set higher than needed */
  if (thread_cache_size > max_connections)
    SYSVAR_AUTOSIZE(thread_cache_size, max_connections);

sql/mysqld.cc lines 9439–9441

max_connections defaults to 151 (MAX_CONNECTIONS_DEFAULT, sql_const.h line 168), so with default settings the cache is 151 too. If the documented default and SELECT @@thread_cache_size disagree, this is why.

4. Login — creating the THD and authenticating

The first thing a connection thread does is create_thd() and thd_prepare_connection().

create_thd() — THD, Vio and NET

  if (thd)
  {
    /* reuse old thd */
    thd->reset_for_reuse();
    /*
      reset tread_id's, but not thread_dbug_id's as the later isn't allowed
      to change as there is already structures in thd marked with the old
      value.
    */
    thd->thread_id= thd->variables.pseudo_thread_id= thread_id;
  }
  else if (!(thd= new THD(thread_id)))
    DBUG_RETURN(0);

sql/sql_connect.cc lines 1626–1638

A new THD comes from new THD(thread_id). The constructor (sql_class.cc line 721 onward) calls init() at the end (line 960), and THD::init() copies the global system variables into the session variables through plugin_thdvar_init() (lines 1388–1399). That is why a value changed with SET GLOBAL applies only to new connections: existing connections keep the copy their THD took.

Next, create_thd() wraps the socket in a Vio (MariaDB's socket abstraction) and attaches it to the THD's NET (the packet read/write buffer) with my_net_init() (lines 1645–1655). For a Unix socket connection, the host is set to localhost right here (lines 1668–1670). The inside of Vio and NET is the topic of part 5. Once the THD is ready, do_handle_one_connection() adds it to the server's list of connections with server_threads.insert(thd) (line 1490). This is the moment the connection starts showing up in SHOW PROCESSLIST (comment: "Make THD visible in show processlist"). Authentication has not happened yet, so the User column shows unauthenticated user at this point.

thd_prepare_connection() → acl_authenticate()

bool thd_prepare_connection(THD *thd)
{
  bool rc;
  lex_start(thd);
  rc= login_connection(thd);
  if (rc)
    return rc;

  MYSQL_CONNECTION_START(thd->thread_id, &thd->security_ctx->priv_user[0],
                         (char *) thd->security_ctx->host_or_ip);

  prepare_new_connection_state(thd);
#ifdef WITH_WSREP
  thd->wsrep_client_thread= true;
  wsrep_open(thd);
#endif /* WITH_WSREP */
  return FALSE;
}

sql/sql_connect.cc lines 1436–1453

This is the path in the second backtrace of example 1. Step by step:

The table above and the excerpts around it joined into one line. From the yellow box on, the connection shows up in PROCESSLIST.

Plugins do the authentication. acl_authenticate() first tries the default plugin (default_auth_plugin_name, which is mysql_native_password in the reference commit, line 93) to send the greeting and receive the user name; if the account uses a different plugin, it tries again with that one (line 17085, lines 17101–17108). One plugin call happens in do_auth_once().

  if (plugin)
  {
    st_mysql_auth *info= (st_mysql_auth *) plugin_decl(plugin)->info;
    switch (info->interface_version >> 8) {
    case 0x02:
      res= info->authenticate_user(mpvio, &mpvio->auth_info);
      break;

sql/sql_acl.cc lines 16951–16957

authenticate_user is a function pointer too. The mysql_native_password plugin is declared at the end of the same file with maria_declare_plugin(mysql_password) and puts native_password_authenticate() into that slot (lines 17579–17612). Just as an engine fills a handlerton, an authentication method is a plugin filling a struct (part 3). This series does not go deeper into authentication plugins or the grant tables; privilege checks come in part 7.

The second max_connections check we put off in section 1 is inside acl_authenticate().

  if (command == COM_CONNECT &&
      !(thd->main_security_ctx.master_access & PRIV_IGNORE_MAX_CONNECTIONS))
  {
    if (*thd->scheduler->connection_count > *thd->scheduler->max_connections)
    {                                         // too many connections
      my_error(ER_CON_COUNT_ERROR, MYF(0));
      DBUG_RETURN(1);
    }
  }

sql/sql_acl.cc lines 17288–17296

The check is split in two because the server can only grant the extra slot after authentication tells it who the user is. create_new_thread(), running on the main thread that knows nothing about privileges, only does a rough "never exceed max_connections + 1" check (comment: "We roughly check here"); the exact check happens on the connection thread after authentication.

Easy to get wrong: a failed login still sends the thread to the cache

Look at the loop in section 3 again: when thd_prepare_connection() fails, it jumps to end_thread, runs close_connection() and unlink_thd(), and enters park() just the same. A connection with a wrong password still creates a thread (or wakes one from the cache), and returns it to the cache when done. A flood of bad logins keeps threads busy waking up.

5. Where does an idle connection wait?

Connections spend most of their time idle, because application connection pools open connections ahead of time and only send queries when needed (the connection pool behind the DataSource in Spring part 18). What is the server thread doing meanwhile?

Example 4: an idle connection in gdb

We attached one idle client, took backtraces of all threads with gdb -batch -ex 'thread apply all bt', and kept only the threads we need (part 1 of ex/u04-gdb).

=== 1. one-thread-per-connection: an idle client
      1 checkpoint_bg
      6 ib_tpool_worker
      1 io_uring_wait
      1 manager
      3 mariadbd
      1 one_connection
      1 page_cleaner
      1 signal_handler
      1 statement_timer
Thread ... "one_connection"
#0  poll () from /lib/x86_64-linux-gnu/libc.so.6
#1  vio_io_wait at /usr/src/mariadb/vio/viosocket.c:960
#2  vio_socket_io_wait at /usr/src/mariadb/vio/viosocket.c:116
#3  handle_ssl_io_error at /usr/src/mariadb/vio/viossl.c:163
#4  vio_ssl_read at /usr/src/mariadb/vio/viossl.c:179
#5  my_real_read at /usr/src/mariadb/sql/net_serv.cc:847
#6  my_net_read_packet_reallen at /usr/src/mariadb/sql/net_serv.cc:1084
#7  my_net_read_packet at /usr/src/mariadb/sql/net_serv.cc:1067
#8  do_command at /usr/src/mariadb/sql/sql_parse.cc:1293
#9  do_handle_one_connection at /usr/src/mariadb/sql/sql_connect.cc:1514
#10 handle_one_connection at /usr/src/mariadb/sql/sql_connect.cc:1426
#11 pfs_spawn_thread at /usr/src/mariadb/storage/perfschema/pfs.cc:2198
#12 ?? () from /lib/x86_64-linux-gnu/libc.so.6
#13 ?? () from /lib/x86_64-linux-gnu/libc.so.6

Thread ... "mariadbd"
#0  poll () from /lib/x86_64-linux-gnu/libc.so.6
#1  handle_connections_sockets at /usr/src/mariadb/sql/mysqld.cc:6890
#2  run_main_loop at /usr/src/mariadb/sql/mysqld.cc:6171
#3  mysqld_main at /usr/src/mariadb/sql/mysqld.cc:6590
#4  main at /usr/src/mariadb/sql/main.cc:34

The list at the top counts threads by name (ib_tpool_worker threads belong to tpool, InnoDB's internal thread pool. We got 6 on both runs, but tpool grows and shrinks its threads with the workload, so 6 is not guaranteed). One idle connection, one one_connection thread, and that thread is stuck in poll() at line 1293 of do_command(), in my_net_read_packet(net, 1), waiting for the next packet — a poll() on a single socket. The main thread (one of the three named mariadbd) is waiting for new connections in the poll() from section 1.

Every idle connection has one thread like this, stopped in poll(). Functions and line numbers come from Example 4's actual output.

viossl appears in #3·#4 because this connection is TLS-encrypted: even over the Unix socket, the reference commit's client and server used TLS (the same reason as the wording of error 2002 in example 2).

How long it waits is set by do_command() before reading.

  if (!thd->skip_wait_timeout)
    my_net_set_read_timeout(net, thd->get_net_wait_timeout());

sql/sql_parse.cc lines 1266–1267

In this mode, wait_timeout is simply "the timeout of a socket read": when it expires the read fails and the connection is closed. If skip_wait_timeout is true this is skipped — and the thread pool is what sets it (section 6).

The trade-offs of this model show up here. The code is simple (read, run, read again), and a slow query on one connection doesn't affect any other. But there are as many threads as idle connections. Thousands of connections mean thousands of threads, each with its own stack memory and OS scheduling overhead. If they all send queries at once, far more threads than CPUs run at the same time and push each other off the CPU. It is the same problem httpd's worker MPM has with keep-alive connections (section 4 of httpd part 8).

6. The thread pool — groups, listeners and workers

Starting with --thread-handling=pool-of-threads, pool_of_threads_scheduler() swaps in a whole new struct.

static scheduler_functions tp_scheduler_functions=
{
  0,                                  // max_threads
  NULL,
  NULL,
  tp_init,                            // init
  tp_add_connection,                  // add_connection
  tp_wait_begin,                      // thd_wait_begin
  tp_wait_end,                        // thd_wait_end
  tp_post_kill_notification,          // post kill notification
  tp_end,                              // end
  tp_resume
};

sql/threadpool_common.cc lines 616–628

Unlike one-thread-per-connection, every function pointer is filled in. thd_wait_begin·thd_wait_end in particular are the heart of section 7. The code is split in two files: threadpool_common.cc is the platform-independent part (login, running one request), and threadpool_generic.cc builds groups and workers on top of OS facilities such as epoll and kqueue. Windows also has threadpool_win.cc, which uses the OS thread pool; Linux builds use only TP_pool_generic (tp_init(), lines 471–494).

A connection is tied to one group

Yellow boxes are places where things wait (the queues and the waiting list); blue boxes are code doing work. Each group has its own epoll.

A new connection becomes a TP_connection_generic object in tp_add_connection() (lines 1320–1323), and its constructor decides the group.

static size_t get_group_id(my_thread_id tid)
{
  return size_t(tid % group_count);
}

sql/threadpool_generic.cc lines 1396–1399

The connection number modulo the number of groups (lines 1422–1424). group_count is kept equal to thread_pool_size (set_pool_size(), line 1683). A connection stays in that group until it ends (only in the rare case of changing thread_pool_size at runtime does change_group() move it). From the group struct thread_group_t (sql/threadpool_generic.h lines 131–151), these are the fields this post uses.

The worker loop: get_event()

A worker thread (worker_main()) repeatedly gets one piece of work with get_event() and calls tp_callback() (lines 1558–1568). The order in which get_event() looks for work defines the character of the pool.

    /* Check if queue is not empty */
    if (!oversubscribed)
    {
      connection = queue_get(thread_group, operation_origin::WORKER);
      if(connection)
      {
        break;
      }
    }

    /* If there is  currently no listener in the group, become one. */
    if(!thread_group->listener)
    {
      thread_group->listener= current_thread;
      thread_group->active_thread_count--;
      mysql_mutex_unlock(&thread_group->mutex);

      connection = listener(current_thread, thread_group);

      mysql_mutex_lock(&thread_group->mutex);
      thread_group->active_thread_count++;
      /* There is no listener anymore, it just returned. */
      thread_group->listener= NULL;
      break;
    }

sql/threadpool_generic.cc lines 1186–1210

The listener is not a separate thread but a role the workers take in turn.

1. The queue comes first. If work is waiting, take it. But if too many workers are already running in the group (too_many_threads(): active workers exceed 1 + thread_pool_oversubscribe and the group is not stalled, lines 1141–1145), the queue is skipped. With the default thread_pool_oversubscribe=3 (sys_vars.cc line 4595), that roughly caps the workers using the CPU at the same time at about 4 per group.
2. If there is no listener, the worker becomes it. Inside listener() it watches every connection in the group with epoll_wait(-1). While it is the listener it is not counted in active_thread_count — the rule is that a waiting thread does not count as "active".
3. If there is already a listener, it looks once more with a non-blocking epoll_wait(0) (lines 1218–1229), and if there is still nothing, it goes to sleep at the front of the waiting list (line 1240). The comment explains why the front: waking the most recently active thread first (LIFO) keeps CPU caches warm, while threads that have been idle for long keep idling until thread_pool_idle_timeout (default 60 seconds, line 4587) ends them.

When the listener receives events, listener() decides whether to handle one itself or queue them.

    bool listener_picks_event=is_queue_empty(thread_group) && !threadpool_dedicated_listener;
    queue_put(thread_group, ev, cnt);
    if (listener_picks_event)
    {
      /* Handle the first event. */
      retval= queue_get(thread_group, operation_origin::LISTENER);
      mysql_mutex_unlock(&thread_group->mutex);
      break;
    }

sql/threadpool_generic.cc lines 798–806

If the queue was empty, the listener takes the first event itself and leaves the listener role. The long comment just above (lines 755–796) explains this as saving the cost of a freshly woken thread waking yet another thread. The price is that "the group has no listener for a while", and that gap is covered by the timer_thread in section 7. With thread_pool_dedicated_listener=1, the listener only watches and never takes queries.

Run one request, then re-arm the socket: tp_callback()

When a worker gets a connection it calls tp_callback(). If the connection has no THD yet, it logs in first through threadpool_add_connection() (lines 237–248), which calls create_thd() and thd_prepare_connection() — the login from section 4, unchanged, on a worker. For a logged-in connection, threadpool_process_request() runs one command with do_command(thd, false) (line 438). Afterwards it finishes like this.

  /* Set priority */
  c->priority= get_priority(c);

  /* Read next command from client. */
  c->set_io_timeout(thd->get_net_wait_timeout());
  c->state= TP_STATE_IDLE;
  if (c->start_io())
    goto error;
  return;

sql/threadpool_common.cc lines 274–282

• Priority: with the default thread_pool_priority=auto, connections with an open transaction go to the high-priority queue (get_priority(), lines 220–221) so that transactions holding locks finish sooner. A connection that waits in the low-priority queue longer than thread_pool_prio_kickup_timer (default 1000 ms) is moved up (check_stall(), lines 621–631).
• wait_timeout: no thread waits on a socket read anymore, so the deadline is written on the connection object (set_io_timeout()) and the timer_thread checks it. Setting thd->skip_wait_timeout= true at login (line 344) is what makes do_command() in section 5 skip the read timeout.
• start_io(): re-arms the socket in epoll. Then tp_callback() simply returns. The worker lets go of this connection and goes back to get_event() to look for more work.

How the socket is registered with epoll is what makes this safe.

int io_poll_associate_fd(TP_file_handle pollfd, TP_file_handle fd, void *data, void*)
{
  struct epoll_event ev;
  ev.data.u64= 0; /* Keep valgrind happy */
  ev.data.ptr= data;
  ev.events=  EPOLLIN|EPOLLET|EPOLLERR|EPOLLRDHUP|EPOLLONESHOT;
  return epoll_ctl(pollfd, EPOLL_CTL_ADD,  fd, &ev);
}

sql/threadpool_generic.cc lines 184–191

EPOLLONESHOT means "after reporting once, take the socket out of the watch list for now". While a request is running, more data arriving on the same connection won't let another worker grab it. When processing is done, start_io() re-arms it with EPOLL_CTL_MOD (io_poll_start_read(), lines 195–202). The comment at the top of the file (lines 127–171) says every supported platform uses this "one-shot" style.

Example 5: 20 connections, how many workers?

We started with --thread-handling=pool-of-threads --thread-pool-size=2 and attached 20 idle clients (ex/u04-thread-pool). To watch idle workers disappear quickly, we lowered --thread-pool-idle-timeout to 3 (default 60 seconds).

--- 0. settings
+-------------------+--------------------+----------------------------+---------------------------+-----------------------------+
 @@thread_handling | @@thread_pool_size | @@thread_pool_idle_timeout | @@thread_pool_stall_limit | @@thread_pool_oversubscribe |
+-------------------+--------------------+----------------------------+---------------------------+-----------------------------+
 pool-of-threads   |                  2 |                          3 |                       500 |                           3 |
+-------------------+--------------------+----------------------------+---------------------------+-----------------------------+
--- 1. twenty idle clients, right after they logged in
Threads_connected=21
      1 timer_thread
     17 worker_thread
--- 2. the same twenty clients, 6 seconds later
Threads_connected=21
      1 timer_thread
      3 worker_thread
+----------+-------------+---------+----------------+-----------------+--------------+--------------+
 GROUP_ID | CONNECTIONS | THREADS | ACTIVE_THREADS | STANDBY_THREADS | QUEUE_LENGTH | HAS_LISTENER |
+----------+-------------+---------+----------------+-----------------+--------------+--------------+
        0 |          10 |       2 |              0 |               1 |            0 |            1 |
        1 |          11 |       2 |              1 |               1 |            0 |            0 |
+----------+-------------+---------+----------------+-----------------+--------------+--------------+

Thread counts are OS thread names counted with ps -L (there is not a single one_connection). The table is information_schema.THREAD_POOL_GROUPS, which sql/thread_pool_info.cc fills straight from the thread_group_t fields (STANDBY_THREADS is the length of waiting_threads, lines 44–76). This information-schema plugin is compiled statically into the server along with the thread pool (sql/CMakeLists.txt line 240).

• Step 1: right after 20 clients logged in at once there are 17 workers (15 and 16 on later runs). Login waits on the network while exchanging handshake packets, and that wait is also reported to the pool through thd_wait_begin() (section 7), so other workers were woken or created meanwhile.
• Step 2: 6 seconds later there are still 21 connections, but only 3 workers (the connection printing the table woke one more while logging in, so the table shows 2+2). The rest had nothing to do for 3 seconds and exited. The 20 idle connections are simply watched with epoll by one listener per group. The exact numbers vary a little between runs.

In part 2 of the same gdb example we took backtraces again with an idle connection (ex/u04-gdb, --thread-pool-size=1).

=== 2. pool-of-threads: login on a worker, then an idle client
#0  thd_prepare_connection at /usr/src/mariadb/sql/sql_connect.cc:1439
#1  threadpool_add_connection at /usr/src/mariadb/sql/threadpool_common.cc:332
#2  tp_callback at /usr/src/mariadb/sql/threadpool_common.cc:241
#3  worker_main at /usr/src/mariadb/sql/threadpool_generic.cc:1567
#4  pfs_spawn_thread at /usr/src/mariadb/storage/perfschema/pfs.cc:2198
#5  ?? () from /lib/x86_64-linux-gnu/libc.so.6
#6  ?? () from /lib/x86_64-linux-gnu/libc.so.6

Thread ... "worker_thread"
#0  epoll_wait () from /lib/x86_64-linux-gnu/libc.so.6
#1  io_poll_wait at /usr/src/mariadb/sql/threadpool_generic.cc:222
#2  listener at /usr/src/mariadb/sql/threadpool_generic.cc:737
#3  get_event at /usr/src/mariadb/sql/threadpool_generic.cc:1203
#4  worker_main at /usr/src/mariadb/sql/threadpool_generic.cc:1563
#5  pfs_spawn_thread at /usr/src/mariadb/storage/perfschema/pfs.cc:2198
#6  ?? () from /lib/x86_64-linux-gnu/libc.so.6
#7  ?? () from /lib/x86_64-linux-gnu/libc.so.6

Thread ... "worker_thread"
#0  ?? () from /lib/x86_64-linux-gnu/libc.so.6
#1  pthread_cond_timedwait () from /lib/x86_64-linux-gnu/libc.so.6
#2  safe_cond_timedwait at /usr/src/mariadb/mysys/thr_mutex.c:544
#3  inline_mysql_cond_timedwait at /usr/src/mariadb/include/mysql/psi/mysql_thread.h:1088
#4  get_event at /usr/src/mariadb/sql/threadpool_generic.cc:1245
#5  worker_main at /usr/src/mariadb/sql/threadpool_generic.cc:1563
#6  pfs_spawn_thread at /usr/src/mariadb/storage/perfschema/pfs.cc:2198
#7  ?? () from /lib/x86_64-linux-gnu/libc.so.6
#8  ?? () from /lib/x86_64-linux-gnu/libc.so.6

We left out the list of thread names and the timer_thread backtrace (a cond_timedwait at line 575 of timer_thread()).

• The top one is a breakpoint on thd_prepare_connection. It is the same login as in example 1, but the bottom is worker_main → tp_callback: login, too, is done by a pool worker.
• No thread is holding the idle connection. One worker, as the listener, sits in epoll_wait() at line 737 of listener(); the other is on the waiting list in cond_timedwait at line 1245 of get_event(). Compare with the default mode in section 5, where a thread waited inside do_command(), and the difference is clear.

7. Announcing waits and detecting stalls — thd_wait_begin() and the timer_thread

The thread pool tries to keep few "workers actually using the CPU" per group. That raises a question: if a running worker stops to wait for a row lock or a disk read, who handles the group's other connections? The answer has two layers.

Layer one: whoever waits says so

The server and engines call thd_wait_begin()·thd_wait_end() around places that may wait a long time. The function forwards to the scheduler in one line, MYSQL_CALLBACK(thd->scheduler, thd_wait_begin, (thd, wait_type)) (sql_class.cc lines 6221–6230). SLEEP(), for example:

  thd_wait_begin(thd, THD_WAIT_SLEEP);
  while (!thd->killed)
  {
    error= timed_cond->wait(cond, &LOCK_item_func_sleep);
    if (error == ETIMEDOUT || error == ETIME)
      break;
    error= 0;
  }
  thd_wait_end(thd);

sql/item_func.cc lines 7527–7535

InnoDB calls the same function before row-lock waits (lock/lock0lock.cc line 2324), page reads (buf/buf0rea.cc lines 211 and 325) and the place where a thread waits for another thread to finish writing the redo log (group_commit_lock::acquire(), used by log_write_up_to(), log/log0sync.cc line 273). Network waits are hooked up by scheduler_init() with vio_set_wait_callback() (scheduler.cc lines 78–79). There are 11 kinds of wait, such as THD_WAIT_SLEEP, THD_WAIT_DISKIO and THD_WAIT_ROW_LOCK (include/mysql/service_thd_wait.h lines 67–77).

In the thread pool the notification ends up in wait_begin().

void wait_begin(thread_group_t *thread_group)
{
  DBUG_ENTER("wait_begin");
  mysql_mutex_lock(&thread_group->mutex);
  thread_group->active_thread_count--;

  DBUG_ASSERT(thread_group->active_thread_count >=0);
  DBUG_ASSERT(thread_group->connection_count > 0);

  if ((thread_group->active_thread_count == 0) &&
     (!is_queue_empty(thread_group) || !thread_group->listener))
  {
    /*
      Group might stall while this thread waits, thus wake
      or create a worker to prevent stall.
    */
    wake_or_create_thread(thread_group);
  }

  mysql_mutex_unlock(&thread_group->mutex);
  DBUG_VOID_RETURN;
}

sql/threadpool_generic.cc lines 1283–1304

The waiting worker drops out of "active", and if that leaves the group with no active workers while the queue has work or there is no listener, another worker is woken or created immediately (wake_or_create_thread(), lines 946–986 — it wakes one from the waiting list if it can, otherwise creates one, spacing creations further apart the more threads the group already has). That is also how 20 logins created 17 workers in example 5. In step 3 of that example we sent 6 SELECT SLEEP(4) at once; THREAD_POOL_WAITS recorded 6 SLEEP waits and the workers grew back to 8.

--- 3. six clients running SELECT SLEEP(4) at the same time
      1 timer_thread
      8 worker_thread
...
+--------+-------+
 REASON | COUNT |
+--------+-------+
 SLEEP  |     6 |
 NET    |    92 |
+--------+-------+

(The group table and THREAD_POOL_STATS in between are partly omitted. NET is the number of network waits since the server started and varies between runs.)

Layer two: the timer_thread catches those who don't say so

A long, CPU-only query waits for nothing, so it sends no notification. For that case the pool has one timer_thread, which wakes every thread_pool_stall_limit (default 500 ms, DEFAULT_THREADPOOL_STALL_LIMIT) and calls check_stall() on every group that has connections (lines 573–590). It checks two things.

  if (!thread_group->listener && !thread_group->io_event_count)
  {
    wake_or_create_thread(thread_group, true);
    mysql_mutex_unlock(&thread_group->mutex);
    return;
  }
  // ...
  if (!is_queue_empty(thread_group) && !thread_group->queue_event_count)
  {
    thread_group->stalled= true;
    TP_INCREMENT_GROUP_COUNTER(thread_group,stalls);
    wake_or_create_thread(thread_group,true);
  }

sql/threadpool_generic.cc lines 639–680

1. No listener, and no network events since the last tick: wake a worker. This is the "listener took an event itself and left its post" case from section 6.
2. The queue has work but nobody dequeued anything since the last tick: mark the group as stalled and wake a worker. While stalled, the too_many_threads() limit is lifted too.

Both counters (io_event_count, queue_event_count) are reset to 0 every tick to see whether anything moved during that tick. So thread_pool_stall_limit is the interval after which "no progress" counts as a stall. The same timer_thread also finds idle connections past their wait_timeout and ends them with KILL_WAIT_TIMEOUT (timeout_check() → tp_timeout_handler(), lines 508–536, threadpool_common.cc lines 543–567).

Example 6: BENCHMARK and SLEEP get different treatment

With a single group (--thread-pool-size=1) and a 3-second tick (--thread-pool-stall-limit=3000), we measured how long another client's SELECT 1 takes (including connecting) while one connection runs a statement of about 8 seconds (ex/u04-pool-stall). The long statement is either the CPU-only BENCHMARK(6000000, MD5('namalzi')) or SLEEP(8), which announces its wait.

# Example written for this post (not MariaDB source)
# ...
lab-start --thread-handling=pool-of-threads --thread-pool-size=1 --thread-pool-stall-limit=3000 > /dev/null
# ...
mariadb -e "FLUSH THREAD_POOL_STATS"
mariadb -N -e "SELECT BENCHMARK(6000000, MD5('namalzi'))" > /dev/null &
sleep 1
timed "SELECT 1"
# ...

[shortened from ex/u04-pool-stall/run.sh — timed is a shell function that takes date +%s.%N before and after]

--- 1. while another client runs BENCHMARK (CPU only, about 8 s)
SELECT 1   took 2.8 s
+----------+------------------+-------------------------------+-------+--------------------+--------+
 GROUP_ID | THREAD_CREATIONS | THREAD_CREATIONS_DUE_TO_STALL | WAKES | WAKES_DUE_TO_STALL | STALLS |
+----------+------------------+-------------------------------+-------+--------------------+--------+
        0 |                0 |                             0 |     2 |                  1 |      0 |
+----------+------------------+-------------------------------+-------+--------------------+--------+
--- 2. while another client runs SLEEP(8)
SELECT 1   took 0.2 s
+----------+------------------+-------------------------------+-------+--------------------+--------+
 GROUP_ID | THREAD_CREATIONS | THREAD_CREATIONS_DUE_TO_STALL | WAKES | WAKES_DUE_TO_STALL | STALLS |
+----------+------------------+-------------------------------+-------+--------------------+--------+
        0 |                1 |                             0 |     3 |                  0 |      0 |
+----------+------------------+-------------------------------+-------+--------------------+--------+

Next to BENCHMARK, SELECT 1 took 2.8 seconds; next to SLEEP, 0.2 seconds (times vary between runs — a second run gave 3.5 s and 0.1 s — and the Debug build is slow). FLUSH THREAD_POOL_STATS resets the counters through stats_reset_table() in thread_pool_info.cc (lines 212–225).

WAKES_DUE_TO_STALL = 1 with STALLS = 0 on the BENCHMARK side shows that this path (the first check in check_stall) was taken.

Easy to get wrong: STALLS is 0, yet it did stall

In the first table of example 6, STALLS is 0, so it is tempting to read "it never stalled". But WAKES_DUE_TO_STALL is 1. The first check in check_stall() (no listener) calls wake_or_create_thread(thread_group, true) and returns before incrementing the stalls counter; stalls only goes up in the second check (queue not moving). The login job of the new connection Y was sitting in the queue, but the first check fired first, and the worker it woke took that job. When monitoring for stalls, look at WAKES_DUE_TO_STALL·THREAD_CREATIONS_DUE_TO_STALL as well as STALLS.

In short, in the thread pool a query that burns CPU for a long time without announcing anything can delay other connections in the same group by up to one or two ticks. Make thread_pool_stall_limit too large and that delay grows; make it too small and workers multiply easily. The comment above check_stall() also notes that if long queries keep flooding in, the thread pool "slowly approaches thread-per-connection behavior" (lines 662–668).

8. How KILL wakes a waiting connection

KILL <id> calls awake_no_mutex() on the target THD (sql_parse.cc line 9229). It sets the killed flag and, for kills that end the connection, notifies the scheduler.

    /* Send an event to the scheduler that a thread should be killed. */
    if (!slave_thread)
      MYSQL_CALLBACK(scheduler, post_kill_notification, (this));

sql/sql_class.cc lines 2160–2162

Both modes end up in the same function.

void post_kill_notification(THD *thd)
{
  DBUG_ENTER("post_kill_notification");
  if (current_thd == thd || thd->system_thread)
    DBUG_VOID_RETURN;

  if (thd->net.vio)
    vio_shutdown(thd->net.vio, SHUT_RD);
  DBUG_VOID_RETURN;
}

sql/scheduler.cc lines 92–101

It shuts down the read side of the socket (SHUT_RD). On a Linux build the socket has in fact already been shut down once: SIGNAL_WITH_VIO_CLOSE is on (configure.cmake line 844), so just before the quoted lines awake_no_mutex() first calls vio_shutdown(active_vio, SHUT_RDWR) on the active_vio remembered at login (sql_class.cc lines 2152–2158; check_connection() sets active_vio at line 1063). Whichever comes first, the effect is the same. In the default mode, the poll() we saw in section 5 wakes right away, the read fails, do_command() returns and the connection is cleaned up. In the thread pool, the socket becomes readable (end of stream) in epoll, the listener queues the connection, and a worker sees thd->killed >= KILL_CONNECTION in threadpool_process_request() and closes it (lines 408–418). Before that, the pool's tp_post_kill_notification() raises the connection's priority to high (lines 599–605) so the doomed connection doesn't wait long in the queue. If the target is waiting inside an engine (for example on a row lock), awake_no_mutex() goes on to notify the engine separately with ha_kill_query() (lines 2166–2169).

KILL does not stop the other thread directly; it sets a flag and closes the socket that thread is waiting on so it wakes up by itself. Line numbers were checked against the paragraph above and sql_class.cc lines 2148–2169.

Comparing with the earlier series

Put side by side, here are three servers' answers to the same question — "which thread takes a connection, and who holds an idle connection?"

MariaDB's thread pool takes the same idea as the event MPM and Tomcat's Poller — detach connections waiting between requests from threads and let a few threads watch them with epoll — and adapts it to a database. The differences are clear too. HTTP requests are usually short, but SQL statements can run for minutes and wait on locks. So MariaDB planted the "I'm about to wait" notification (thd_wait_begin()) deep into the engines, and added the timer_thread to catch long queries that don't announce anything — two layers of protection. Also unlike Tomcat, the listener is a role the workers take in turn rather than a dedicated thread, and a connection stays in one group from start to finish.

What this post leaves out

• Windows code: sql/handle_connections_win.cc (accepting via named pipes and IOCP), sql/threadpool_win.cc (the OS thread pool), threadpool_winsockets.cc. The thread_pool_mode variable exists only on Windows (sys_vars.cc lines 4556–4565).
• The kqueue, event-port and IOCP versions of io_poll_*: threadpool_generic.cc lines 234–443.
• Resuming asynchronous commits: DISPATCH_COMMAND_WOULDBLOCK, thd_async_state and tp_resume(), which release the worker while waiting for group commit (threadpool_common.cc lines 252–264 and 607–614, sql_parse.cc lines 1242–1252).
• PROXY protocol and the host cache: thd_set_peer_addr() (sql_connect.cc line 942 onward), sql/hostname.cc, max_connect_errors.
• Per-user resource limits: max_user_connections, max_connections_per_hour (check_for_max_user_connections(), sql_connect.cc line 131 onward).
• The handshake packet format and the authentication plugin dialogue: send_server_handshake_packet() (sql_acl.cc line 15761), server_mpvio_read_packet() (line 16703). The packet framing is in part 5.
• COM_CHANGE_USER: the other path through acl_authenticate(thd, pkt_len) (lines 17060–17072).
• Closing connections at shutdown: close_connections() (mysqld.cc line 1918 onward) and thread_cache.final_flush().
• Galera (wsrep): not in this build, so every WITH_WSREP block was skipped.

Summary

What's next

We have reached the point where a connection thread (or a pool worker) enters do_command(). The next post goes inside. The stars are my_net_read_packet(), which appeared only by name in the backtraces of section 5 and cuts packets using a 3-byte length and a 1-byte sequence number; the big switch in dispatch_command() that tells COM_QUERY, COM_PING and COM_QUIT apart by one command byte; and Protocol, which sends results back. It continues in part 5: The Protocol and Command Dispatch — How One Packet Reaches dispatch_command.

Part 8 of the httpd series: MPM and part 5 of the Tomcat series: the connector and NIO endpoint are web servers' answers to the same question. Read them with the comparison table above in hand and you'll see how the same idea — "detach idle connections from threads" — took different shapes in three servers. The application side that opens these connections ahead of time and borrows them is the DataSource in Spring part 18: JdbcTemplate.

• Part 1: How I Read It with AI, and the Map
• Part 2: The Big Picture — the Server Layer, Storage Engines, and the Life of One Query
• Part 3: Startup and Plugins — Until mariadbd Takes Its First Connection
• Part 7: Preparation — Checking Privileges, Opening Tables, Resolving Names
• How web servers authenticate: httpd part 13: authentication and authorization, Tomcat part 14: authentication and Realm