All pages
Powered by GitBook
1 of 18

Buffers, Caches and Threads

Optimize MariaDB Server performance by tuning buffers, caches, and threads. This section covers essential configurations to maximize throughput and responsiveness for your database workloads.

Query Cache

The query cache stores results of SELECT queries so that if the identical query is received in future, the results can be quickly returned.

This is extremely useful in high-read, low-write environments (such as most websites). It does not scale well in environments with high throughput on multi-core machines, so it is disabled by default.

Note that the query cache cannot be enabled in certain environments. See Limitations.

Setting Up the Query Cache

Unless MariaDB has been specifically built without the query cache, the query cache will always be available, although inactive. The have_query_cache server variable will show whether the query cache is available.

SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

If this is set to NO, you cannot enable the query cache unless you rebuild or reinstall a version of MariaDB with the cache available.

To see if the cache is enabled, view the query_cache_type server variable. It is enabled by default in MariaDB versions up to 10.1.6, but disabled starting with MariaDB 10.1.7 - if needed enable it by setting query_cache_type to 1.

Although enabled in versions prior to MariaDB 10.1.7, the query_cache_size is by default 0KB there, which effectively disables the query cache. From 10.1.7 on the cache size defaults to 1MB. If needed set the cache to a size large enough amount, for example:

SET GLOBAL query_cache_size = 1000000;

Starting from MariaDB 10.1.7, query_cache_type is automatically set to ON if the server is started with the query_cache_size set to a non-zero (and non-default) value.

See Limiting the size of the Query Cache below for details.

How the Query Cache Works

When the query cache is enabled and a new SELECT query is processed, the query cache is examined to see if the query appears in the cache.

Queries are considered identical if they use the same database, same protocol version and same default character set. Prepared statements are always considered as different to non-prepared statements, see Query cache internal structure for more info.

If the identical query is not found in the cache, the query will be processed normally and then stored, along with its result set, in the query cache. If the query is found in the cache, the results will be pulled from the cache, which is much quicker than processing it normally.

Queries are examined in a case-sensitive manner, so :

SELECT * FROM t

Is different from :

SELECT * FROM t

Comments are also considered and can make the queries differ, so :

/* retry */SELECT * FROM t

Is different from :

/* retry2 */SELECT * FROM t

See the query_cache_strip_comments server variable for an option to strip comments before searching.

Each time changes are made to the data in a table, all affected results in the query cache are cleared. It is not possible to retrieve stale data from the query cache.

When the space allocated to query cache is exhausted, the oldest results will be dropped from the cache.

When using query_cache_type=ON, and the query specifies SQL_NO_CACHE (case-insensitive), the server will not cache the query and will not fetch results from the query cache.

When using query_cache_type=DEMAND and the query specifies SQL_CACHE, the server will cache the query.

Queries Stored in the Query Cache

If the query_cache_type system variable is set to 1, or ON, all queries fitting the size constraints will be stored in the cache unless they contain a SQL_NO_CACHE clause, or are of a nature that caching makes no sense, for example making use of a function that returns the current time. Queries with SQL_NO_CACHE will not attempt to acquire query cache lock.

If any of the following functions are present in a query, it will not be cached. Queries with these functions are sometimes called 'non-deterministic' - don't get confused with the use of this term in other contexts.

BENCHMARK()

CONNECTION_ID()

CONVERT_TZ()

CURDATE()

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

CURTIME()

DATABASE()

ENCRYPT() (one parameter)

FOUND_ROWS()

GET_LOCK()

LAST_INSERT_ID()

LOAD_FILE()

MASTER_POS_WAIT()

NOW()

RAND()

RELEASE_LOCK()

SLEEP()

SYSDATE()

UNIX_TIMESTAMP() (no parameters)

USER()

UUID()

UUID_SHORT()

A query will also not be added to the cache if:

  • It is of the form:

    • SELECT SQL_NO_CACHE ...

    • SELECT ... INTO OUTFILE ...

    • SELECT ... INTO DUMPFILE ...

    • SELECT ... FOR UPDATE

    • SELECT * FROM ... WHERE autoincrement_column IS NULL

    • SELECT ... LOCK IN SHARE MODE

  • It uses TEMPORARY table

  • It uses no tables at all

  • It generates a warning

  • The user has a column-level privilege on any table in the query

  • It accesses a table from INFORMATION_SCHEMA, mysql or the performance_schema database

  • It makes use of user or local variables

  • It makes use of stored functions

  • It makes use of user-defined functions

  • It is inside a transaction with the SERIALIZABLE isolation level

  • It is quering a table inside a transaction after the same table executed a query cache invalidation using INSERT, UPDATE or DELETE

The query itself can also specify that it is not to be stored in the cache by using the SQL_NO_CACHE attribute. Query-level control is an effective way to use the cache more optimally.

It is also possible to specify that no queries must be stored in the cache unless the query requires it. To do this, the query_cache_type server variable must be set to 2, or DEMAND. Then, only queries with the SQL_CACHE attribute are cached.

Limiting the Size of the Query Cache

There are two main ways to limit the size of the query cache. First, the overall size in bytes is determined by the query_cache_size server variable. About 40KB is needed for various query cache structures.

The query cache size is allocated in 1024 byte-blocks, thus it should be set to a multiple of 1024.

The query result is stored using a minimum block size of query_cache_min_res_unit. Check two conditions to use a good value of this variable: Query cache insert result blocks with locks, each new block insert lock query cache, a small value will increase locks and fragmentation and waste less memory for small results, a big value will increase memory use wasting more memory for small results but it reduce locks. Test with your workload for fine tune this variable.

If the strict mode is enabled, setting the query cache size to an invalid value will cause an error. Otherwise, it will be set to the nearest permitted value, and a warning will be triggered.

SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 67108864 |
+------------------+----------+

SET GLOBAL query_cache_size = 8000000;
Query OK, 0 rows affected, 1 warning (0.03 sec)

SHOW VARIABLES LIKE 'query_cache_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 7999488 |
+------------------+---------+

The ideal size of the query cache is very dependent on the specific needs of each system. Setting a value too small will result in query results being dropped from the cache when they could potentially be re-used later. Setting a value too high could result in reduced performance due to lock contention, as the query cache is locked during updates.

The second way to limit the cache is to have a maximum size for each set of query results. This prevents a single query with a huge result set taking up most of the available memory and knocking a large number of smaller queries out of the cache. This is determined by the query_cache_limit server variable.

If you attempt to set a query cache that is too small (the amount depends on the architecture), the resizing will fail and the query cache will be set to zero, for example :

SET GLOBAL query_cache_size=40000;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect query_cache_size value: '40000'             |
| Warning | 1282 | Query cache failed to set size 39936; new query cache size is 0 |
+---------+------+-----------------------------------------------------------------+

Examining the Query Cache

A number of status variables provide information about the query cache.

SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1158     |
| Qcache_free_memory      | 3760784  |
| Qcache_hits             | 31943398 |
| Qcache_inserts          | 42998029 |
| Qcache_lowmem_prunes    | 34695322 |
| Qcache_not_cached       | 652482   |
| Qcache_queries_in_cache | 4628     |
| Qcache_total_blocks     | 11123    |
+-------------------------+----------+

Qcache_inserts contains the number of queries added to the query cache, Qcache_hits contains the number of queries that have made use of the query cache, while Qcache_lowmem_prunes contains the number of queries that were dropped from the cache due to lack of memory.

The above example could indicate a poorly performing cache. More queries have been added, and more queries have been dropped, than have actually been used.

Note that before MariaDB 5.5, queries returned from the query cache did not increment the Com_select status variable, so to find the total number of valid queries run on the server, add Com_select to Qcache_hits. Starting from MariaDB 5.5, results returned by the query cache count towards Com_select (see MDEV-4981).

The QUERY_CACHE_INFO plugin creates the QUERY_CACHE_INFO table in the INFORMATION_SCHEMA, allowing you to examine the contents of the query cache.

Query Cache Fragmentation

The Query Cache uses blocks of variable length, and over time may become fragmented. A high Qcache_free_blocks relative to Qcache_total_blocks may indicate fragmentation. FLUSH QUERY CACHE will defragment the query cache without dropping any queries :

FLUSH QUERY CACHE;

After this, there will only be one free block :

SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 6101576  |
| Qcache_hits             | 31981126 |
| Qcache_inserts          | 43002404 |
| Qcache_lowmem_prunes    | 34696486 |
| Qcache_not_cached       | 655607   |
| Qcache_queries_in_cache | 4197     |
| Qcache_total_blocks     | 8833     |
+-------------------------+----------+

Emptying and disabling the Query Cache

To empty or clear all results from the query cache, use RESET QUERY CACHE. FLUSH TABLES will have the same effect.

Setting either query_cache_type or query_cache_size to 0 will disable the query cache, but to free up the most resources, set both to 0 when you wish to disable caching.

Limitations

  • The query cache needs to be disabled in order to use OQGRAPH.

  • The query cache is not used by the Spider storage engine (amongst others).

  • The query cache also needs to be disabled for MariaDB Galera cluster versions prior to "5.5.40-galera", "10.0.14-galera" and "10.1.2".

LOCK TABLES and the Query Cache

The query cache can be used when tables have a write lock (which may seem confusing since write locks should avoid table reads). This behaviour can be changed by setting the query_cache_wlock_invalidate system variable to ON, in which case each write lock will invalidate the table query cache. Setting to OFF, the default, means that cached queries can be returned even when a table lock is being held. For example:

1> SELECT * FROM T1
+---+
| a |
+---+
| 1 |
+---+
-- Here the query is cached

-- From another connection execute:
2> LOCK TABLES T1 WRITE;

-- Expected result with: query_cache_wlock_invalidate = OFF
1> SELECT * FROM T1
+---+
| a |
+---+
| 1 |
+---+
-- read from query cache


-- Expected result with: query_cache_wlock_invalidate = ON
1> SELECT * FROM T1
-- Waiting Table Write Lock

Transactions and the Query Cache

The query cache handles transactions. Internally a flag (FLAGS_IN_TRANS) is set to 0 when a query was executed outside a transaction, and to 1 when the query was inside a transaction (begin / COMMIT / ROLLBACK). This flag is part of the "query cache hash", in others words one query inside a transaction is different from a query outside a transaction.

Queries that change rows (INSERT / UPDATE / DELETE / TRUNCATE) inside a transaction will invalidate all queries from the table, and turn off the query cache to the changed table. Transactions that don't end with COMMIT / ROLLBACK check that even without COMMIT / ROLLBACK, the query cache is turned off to allow row level locking and consistency level.

Examples:

SELECT * FROM T1 <first INSERT TO query cache, USING FLAGS_IN_TRANS=0>
+---+
| a |
+---+
| 1 |
+---+
BEGIN;
SELECT * FROM T1 <first INSERT TO query cache, USING FLAGS_IN_TRANS=1>
+---+
| a |
+---+
| 1 |
+---+
SELECT * FROM T1 <result FROM query cache, USING FLAGS_IN_TRANS=1>
+---+
| a |
+---+
| 1 |
+---+
INSERT INTO T1 VALUES(2);  <invalidate queries FROM TABLE T1 AND disable query cache TO TABLE T1>
SELECT * FROM T1 <don't USE query cache, a normal query FROM innodb TABLE>
+---+
| a |
+---+
| 1 |
| 2 |
+---+
SELECT * FROM T1 <don't USE query cache, a normal query FROM innodb TABLE>
+---+
| a |
+---+
| 1 |
| 2 |
+---+
COMMIT;  <query cache IS now turned ON TO T1 TABLE>
SELECT * FROM T1 <first INSERT TO query cache, USING FLAGS_IN_TRANS=0>
+---+
| a |
+---+
| 1 |
+---+
SELECT * FROM T1 <result FROM query cache, USING FLAGS_IN_TRANS=0>
+---+
| a |
+---+
| 1 |
+---+

Query Cache Internal Structure

Internally, each flag that can change a result using the same query is a different query. For example, using the latin1 charset and using the utf8 charset with the same query are treated as different queries by the query cache.

Some fields that differentiate queries are (from "Query_cache_query_flags" internal structure) :

  • query (string)

  • current database schema name (string)

  • client long flag (0/1)

  • client protocol 4.1 (0/1)

  • protocol type (internal value)

  • more results exists (protocol flag)

  • in trans (inside transaction or not)

  • autocommit (autocommit session variable)

  • pkt_nr (protocol flag)

  • character set client (character_set_client session variable)

  • character set results (character_set_results session variable)

  • collation connection (collation_connection session variable)

  • limit (sql_select_limit session variable)

  • time zone (time_zone session variable)

  • sql_mode (sql_mode session variable)

  • max_sort_length (max_sort_length session variable)

  • group_concat_max_len (group_concat_max_len session variable)

  • default_week_format (default_week_format session variable)

  • div_precision_increment (div_precision_increment session variable)

  • lc_time_names (lc_time_names session variable)

Timeout and Mutex Contention

When searching for a query inside the query cache, a try_lock function waits with a timeout of 50ms. If the lock fails, the query isn't executed via the query cache. This timeout is hard coded (MDEV-6766 include two variables to tune this timeout).

From the sql_cache.cc, function "try_lock" using TIMEOUT :

struct timespec waittime;
        set_timespec_nsec(waittime,(ulong)(50000000L));  /* Wait for 50 msec */
        int res= mysql_cond_timedwait(&COND_cache_status_changed,
                                      &structure_guard_mutex, &waittime);
        if (res == ETIMEDOUT)
          break;

When inserting a query inside the query cache or aborting a query cache insert (using the KILL command for example), a try_lock function waits until the query cache returns; no timeout is used in this case.

When two processes execute the same query, only the last process stores the query result. All other processes increase the Qcache_not_cached status variable.

SQL_NO_CACHE and SQL_CACHE

There are two aspects to the query cache: placing a query in the cache, and retrieving it from the cache.

  1. Adding a query to the query cache. This is done automatically for cacheable queries (see (Queries Stored in the Query Cache) when the query_cache_type system variable is set to 1, or ON and the query contains no SQL_NO_CACHE clause, or when the query_cache_type system variable is set to 2, or DEMAND, and the query contains the SQL_CACHE clause.

  2. Retrieving a query from the cache. This is done after the server receives the query and before the query parser. In this case one point should be considered:

When using SQL_NO_CACHE, it should be after the first SELECT hint, for example :

SELECT SQL_NO_CACHE .... FROM (SELECT SQL_CACHE ...) AS temp_table

instead of

SELECT SQL_CACHE .... FROM (SELECT SQL_NO_CACHE ...) AS temp_table

The second query will be checked. The query cache only checks if SQL_NO_CACHE/SQL_CACHE exists after the first SELECT. (More info at MDEV-6631)

This page is licensed: CC BY-SA / Gnu FDL

Thread Command Values

A thread can have any of the following COMMAND values (displayed by the COMMAND field listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table, as well as the PROCESSLIST_COMMAND value listed in the Performance Schema threads Table). These indicate the nature of the thread's activity.

Value
Description

Binlog Dump

Master thread for sending binary log contents to a slave.

Change user

Executing a change user operation.

Close stmt

Closing a prepared statement.

Connect

Replication slave is connected to its master.

Connect Out

Replication slave is in the process of connecting to its master.

Create DB

Executing an operation to create a database.

Daemon

Internal server thread rather than for servicing a client connection.

Debug

Generating debug information.

Delayed insert

A delayed-insert handler.

Drop DB

Executing an operation to drop a database.

Error

Error.

Execute

Executing a prepared statement.

Fetch

Fetching the results of an executed prepared statement.

Field List

Retrieving table column information.

Init DB

Selecting default database.

Kill

Killing another thread.

Long Data

Retrieving long data from the result of executing a prepared statement.

Ping

Handling a server ping request.

Prepare

Preparing a prepared statement.

Processlist

Preparing processlist information about server threads.

Query

Executing a statement.

Quit

In the process of terminating the thread.

Refresh

Flushing a table, logs or caches, or refreshing replication server or status variable information.

Register Slave

Registering a slave server.

Reset stmt

Resetting a prepared statement.

Set option

Setting or resetting a client statement execution option.

Sleep

Waiting for the client to send a new statement.

Shutdown

Shutting down the server.

Statistics

Preparing status information about the server.

Table Dump

Sending the contents of a table to a slave.

Time

Not used.

This page is licensed: CC BY-SA / Gnu FDL

Thread Pool

Optimize MariaDB Server with the thread pool. This section explains how to manage connections and improve performance by efficiently handling concurrent client requests, reducing resource overhead.

Thread Pool in MariaDB

Problem That Thread Pools Solve

The task of scalable server software (and a DBMS like MariaDB is an example of such software) is to maintain top performance with an increasing number of clients. MySQL traditionally assigned a thread for every client connection, and as the number of concurrent users grows this model shows performance drops. Many active threads are a performance killer, because increasing the number of threads leads to extensive context switching, bad locality for CPU caches, and increased contention for hot locks. An ideal solution that would help to reduce context switching is to maintain a lower number of threads than the number of clients. But this number should not be too low either, since we also want to utilize CPUs to their fullest, so ideally, there should be a single active thread for each CPU on the machine.

MariaDB Thread Pool Features

The current MariaDB thread pool was implemented in MariaDB 5.5. It replaced the legacy thread pool that was introduced in MariaDB 5.1. The main drawback of the previous solution was that this pool was static–it had a fixed number of threads. Static thread pools can have their merits, for some limited use cases, such as cases where callbacks executed by the threads never block and do not depend on each other. For example, imagine something like an echo server.

However, DBMS clients are more complicated. For example, a thread may depend on another thread's completion, and they may block each other via locks and/or I/O. Thus it is very hard, and sometimes impossible, to predict how many threads would be ideal or even sufficient to prevent deadlocks in every situation. MariaDB 5.5 implements a dynamic/adaptive pool that itself takes care of creating new threads in times of high demand and retiring threads if they have nothing to do. This is a complete reimplementation of the legacy pool-of-threads scheduler, with the following goals:

  • Make the pool dynamic, so that it will grow and shrink whenever required.

  • Minimize the amount of overhead that is required to maintain the thread pool itself.

  • Make the best use of underlying OS capabilities. For example, if a native thread pool implementation is available, then it should be used, and if not, then the best I/O multiplexing method should be used.

  • Limit the resources used by threads.

There are currently two different low-level implementations – depending on OS. One implementation is designed specifically for Windows which utilizes a native CreateThreadpool API. The second implementation is primarily intended to be used in Unix-like systems. Because the implementations are different, some system variables differ between Windows and Unix.

When to Use the Thread Pool

Thread pools are most efficient in situations where queries are relatively short and the load is CPU-bound, such as in OLTP workloads. If the workload is not CPU-bound, then you might still benefit from limiting the number of threads to save memory for the database memory buffers.

When the Thread Pool is Less Efficient

There are special, rare cases where the thread pool is likely to be less efficient.

  • If you have a very bursty workload, then the thread pool may not work well for you. These tend to be workloads in which there are long periods of inactivity followed by short periods of very high activity by many users. These also tend to be workloads in which delays cannot be tolerated, so the throttling of thread creation that the thread pool uses is not ideal. Even in this situation, performance can be improved by tweaking how often threads are retired. For example, with thread_pool_idle_timeout on Unix, or with thread_pool_min_threads on Windows.

  • If you have many concurrent, long, non-yielding queries, then the thread pool may not work well for you. In this context, a "non-yielding" query is one that never waits or which does not indicate waits to the thread pool. These kinds of workloads are mostly used in data warehouse scenarios. Long-running, non-yielding queries will delay execution of other queries. However, the thread pool has stall detection to prevent them from totally monopolizing the thread pool. See Thread Groups in the Unix Implementation of the Thread Pool: Thread Group Stalls for more information. Even when the whole thread pool is blocked by non-yielding queries, you can still connect to the server through the extra-port TCP/IP port.

  • If you rely on the fact that simple queries always finish quickly, no matter how loaded your database server is, then the thread pool may not work well for you. When the thread pool is enabled on a busy server, even simple queries might be queued to be executed later. This means that even if the statement itself doesn't take much time to execute, even a simple SELECT 1, might take a bit longer when the thread pool is enabled than with one-thread-per-connection if it gets queued.

Configuring the Thread Pool

The thread_handling system variable is the primary system variable that is used to configure the thread pool.

There are several other system variables as well, which are described in the sections below. Many of the system variables documented below are dynamic, meaning that they can be changed with SET GLOBAL on a running server.

Generally, there is no need to tweak many of these system variables. The goal of the thread pool was to provide good performance out-of-the box. However, the system variable values can be changed, and we intended to expose as many knobs from the underlying implementation as we could. Feel free to tweak them as you see fit.

If you find any issues with any of the default behavior, then we encourage you to submit a bug report.

See Thread Pool System and Status Variables for the full list of the thread pool's system variables.

Configuring the Thread Pool on Unix

On Unix, if you would like to use the thread pool, then you can use the thread pool by setting the thread_handling system variable to pool-of-threads in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
thread_handling=pool-of-threads

The following system variables can also be configured on Unix:

  • thread_pool_size – The number of thread groups in the thread pool, which determines how many statements can execute simultaneously. The default value is the number of CPUs on the system. When setting this system variable's value at system startup, the max value is 100000. However, it is not a good idea to set it that high. When setting this system variable's value dynamically, the max value is either 128 or the value that was set at system startup--whichever value is higher. See Thread Groups in the Unix Implementation of the Thread Pool for more information.

  • thread_pool_max_threads – The maximum number of threads in the thread pool. Once this limit is reached, no new threads will be created in most cases. In rare cases, the actual number of threads can slightly exceed this, because each thread group needs at least two threads (i.e. at least one worker thread and at least one listener thread) to prevent deadlocks. The default value in MariaDB 5.5 and MariaDB 10.0 is 500. The default value in MariaDB 10.1 is 1000 in MariaDB 10.1. The default value in MariaDB 10.2 and later is 65536.

  • thread_pool_stall_limit – The number of milliseconds between each stall check performed by the timer thread. The default value is 500. Stall detection is used to prevent a single client connection from monopolizing a thread group. When the timer thread detects that a thread group is stalled, it wakes up a sleeping worker thread in the thread group, if one is available. If there isn't one, then it creates a new worker thread in the thread group. This temporarily allows several client connections in the thread group to run in parallel. However, note that the timer thread will not create a new worker thread if the number of threads in the thread pool is already greater than or equal to the maximum defined by the thread_pool_max_threads variable, unless the thread group does not already have a listener thread. See Thread Groups in the Unix Implementation of the Thread Pool: Thread Group Stalls for more information.

  • thread_pool_oversubscribe – Determines how many worker threads in a thread group can remain active at the same time once a thread group is oversubscribed due to stalls. The default value is 3. Usually, a thread group only has one active worker thread at a time. However, the timer thread can add more active worker threads to a thread group if it detects a stall. There are trade-offs to consider when deciding whether to allow only one thread per CPU to run at a time, or whether to allow more than one thread per CPU to run at a time. Allowing only one thread per CPU means that the thread can have unrestricted access to the CPU while its running, but it also means that there is additional overhead from putting threads to sleep or waking them up more frequently. Allowing more than one thread per CPU means that the threads have to share the CPU, but it also means that there is less overhead from putting threads to sleep or waking them up. This is primarily for internal use, and it is not meant to be changed for most users. See Thread Groups in the Unix Implementation of the Thread Pool: Thread Group Oversubscription for more information.

  • thread_pool_idle_timeout – The number of seconds before an idle worker thread exits. The default value is 60. If there is currently no work to do, how long should an idle thread wait before exiting?

Configuring the Thread Pool on Windows

The Windows implementation of the thread pool uses a native thread pool created with the CreateThreadpool API.

On Windows, if you would like to use the thread pool, then you do not need to do anything, because the default for the thread_handling system variable is already preset to pool-of-threads.

However, if you would like to use the old one thread per-connection behavior on Windows, then you can use that by setting the thread_handling system variable to one-thread-per-connection in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
thread_handling=one-thread-per-connection

On older versions of Windows, such as XP and 2003, pool-of-threads is not implemented, and the server will silently switch to using the legacyone-thread-per-connection method.

The native CreateThreadpool API allows applications to set the minimum and maximum number of threads in the pool. The following system variables can be used to configure those values on Windows:

  • thread_pool_min_threads – The minimum number of threads in the pool. Default is 1. This applicable in a special case of very “bursty” workloads. Imagine having longer periods of inactivity after periods of high activity. While the thread pool is idle, Windows would decide to retire pool threads (based on experimentation, this seems to happen after thread had been idle for 1 minute). Next time high load comes, it could take some milliseconds or seconds until the thread pool size stabilizes again to optimal value. To avoid thread retirement, one could set the parameter to a higher value.

  • thread_pool_max_threads – The maximum number of threads in the pool. Threads are not created when this value is reached. The default from MariaDB 5.5 to MariaDB 10.0 is 500 (this has been increased to 1000 in MariaDB 10.1). This parameter can be used to prevent the creation of new threads if the pool can have short periods where many or all clients are blocked (for example, with “FLUSH TABLES WITH READ LOCK”, high contention on row locks, or similar). New threads are created if a blocking situation occurs (such as after a throttling interval), but sometimes you want to cap the number of threads, if you’re familiar with the application and need to, for example, save memory. If your application constantly pegs at 500 threads, it might be a strong indicator for high contention in the application, and the thread pool does not help much.

Configuring Priority Scheduling

Starting with MariaDB 10.2.2, it is possible to configure connection prioritization. The priority behavior is configured by the thread_pool_priority system variable.

By default, if thread_pool_priority is set to auto, then queries would be given a higher priority, in case the current connection is inside a transaction. This allows the running transaction to finish faster, and has the effect of lowering the number of transactions running in parallel. The default setting will generally improve throughput for transactional workloads. But it is also possible to explicitly set the priority for the current connection to either 'high' or 'low'.

There is also a mechanism in place to ensure that higher priority connections are not monopolizing the worker threads in the pool (which would cause indefinite delays for low priority connections). On Unix, low priority connections are put into the high priority queue after the timeout specified by the thread_pool_prio_kickup_timer system variable.

Configuring the Extra Port

MariaDB allows you to configure an extra port for administrative connections. This is primarily intended to be used in situations where all threads in the thread pool are blocked, and you still need a way to access the server. However, it can also be used to ensure that monitoring systems (including MaxScale's monitors) always have access to the system, even when all connections on the main port are used. This extra port uses the old one-thread-per-connection thread handling.

You can enable this and configure a specific port by setting the extra_port system variable.

You can configure a specific number of connections for this port by setting the extra_max_connections system variable.

These system variables can be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
extra_port = 8385
extra_max_connections = 10

Once you have the extra port configured, you can use the mariadb client with the -P option to connect to the port.

$ mariadb -u root -P 8385 -p

Monitoring Thread Pool Activity

Currently there are two status variables exposed to monitor pool activity.

Variable
Description

Threadpool_threads

Number of threads in the thread pool. In rare cases, this can be slightly higher than thread_pool_max_threads, because each thread group needs at least two threads (i.e. at least one worker thread and at least one listener thread) to prevent deadlocks.

Threadpool_idle_threads

Number of inactive threads in the thread pool. Threads become inactive for various reasons, such as by waiting for new work. However, an inactive thread is not necessarily one that has not been assigned work. Threads are also considered inactive if they are being blocked while waiting on disk I/O, or while waiting on a lock, etc. This status variable is only meaningful on Unix.

Thread Groups in the Unix Implementation of the Thread Pool

On Unix, the thread pool implementation uses objects called thread groups to divide up client connections into many independent sets of threads. See Thread Groups in the Unix Implementation of the Thread Pool for more information.

Fixing a Blocked Thread Pool

When using global locks, even with a high value on the thread_pool_max_threads system variable, it is still possible to block the entire pool.

Imagine the case where a client performs FLUSH TABLES WITH READ LOCK then pauses. If then the number of other clients connecting to the server to start write operations exceeds the maximum number of threads allowed in the pool, it can block the Server. This makes it impossible to issue the UNLOCK TABLES statement. It can also block MaxScale from monitoring the Server.

To mitigate the issue, MariaDB allows you to configure an extra port for administrative connections. See Configuring the Extra Port for information on how to configure this.

Once you have the extra port configured, you can use the mariadb client with the -P option to connect to the port.

$ mariadb -u root -P 8385 -p

This ensures that your administrators can access the server in cases where the number of threads is already equal to the configured value of the thread_pool_max_threads system variable, and all threads are blocked. It also ensures that MaxScale can still access the server in such situations for monitoring information.

Once you are connected to the extra port, you can solve the issue by increasing the value on the thread_pool_max_threads system variable, or by killing the offending connection, (that is, the connection that holds the global lock, which would be in the sleep state).

Information Schema

The following Information Schema tables relate to the thread pool:

  • Information Schema THREAD_POOL_GROUPS Table

  • Information Schema THREAD_POOL_QUEUES Table

  • Information Schema THREAD_POOL_STATS Table

  • Information Schema THREAD_POOL_WAITS Table

MariaDB Thread Pool vs Oracle MySQL Enterprise Thread Pool

Commercial editions of MySQL since 5.5 include an Oracle MySQL Enterprise thread pool implemented as a plugin, which delivers similar functionality. A detailed discussion about the design of the feature is atMikael Ronstrom's blog. Here is the summary of similarities and differences, based on the above materials.

Similarities

  • On Unix, both MariaDB and Oracle MySQL Enterprise Threadpool will partition client connections into groups. The thread_pool_size parameter thus has the same meaning for both MySQL and MariaDB.

  • Both implementations use similar schema checking for thread stalls, and both have the same parameter name for thread_pool_stall_limit (though in MariaDB it is measured using millisecond units, not 10ms units like in Oracle MySQL).

Differences

  • The Windows implementation is completely different – MariaDB's uses native Windows threadpooling, while Oracle's implementation includes a convenience function WSAPoll() (provided for convenience to port Unix applications). As a consequence of relying on WSAPoll(), Oracle's implementation will not work with named pipes and shared memory connections.

  • MariaDB uses the most efficient I/O multiplexing facilities for each operating system: Windows (the I/O completion port is used internally by the native threadpool), Linux (epoll), Solaris (event ports), FreeBSD and OSX (kevent). Oracle uses optimized I/O multiplexing only on Linux, with epoll, and uses poll() otherwise.

  • Unlike Oracle MySQL Enterprise Threadpool, MariaDB's one is builtin, not a plugin.

MariaDB Thread Pool vs Percona Thread Pool

Percona's implementation is a port of the MariaDB's threadpool with some added features. In particular, Percona added priority scheduling to its 5.5-5.7 releases. MariaDB 10.2 and Percona priority scheduling work in a similar fashion, but there are some differences in details.

  • MariaDB's 10.2 thread_pool_priority=auto,high, low correspond to Percona's thread_pool_high_prio_mode=transactions,statements,none

  • Percona has a thread_pool_high_prio_tickets connection variable to allow every nth low priority query to be put into the high priority queue. MariaDB does not have corresponding settings.

  • MariaDB has a thread_pool_prio_kickup_timer setting, which Percona does not have.

Thread Pool Internals

Low-level implementation details are documented in theWL#246

Running Benchmarks

When running sysbench and maybe other benchmarks, that create many threads on the same machine as the server, it is advisable to run benchmark driver and server on different CPUs to get the realistic results. Running lots of driver threads and only several server threads on the same CPUs will have the effect that OS scheduler will schedule benchmark driver threads to run with much higher probability than the server threads, that is driver will pre-empt the server. Use "taskset –c" on Linuxes, and "set /affinity" on Windows to separate benchmark driver and server CPUs, as the preferred method to fix this situation.

A possible alternative on Unix (if taskset or a separate machine running the benchmark is not desired for some reason) would be to increase thread_pool_size to make the server threads more "competitive" against the client threads.

When running sysbench, a good rule of thumb could be to give 1/4 of all CPUs to the sysbench, and 3/4 of CPUs to mariadbd. It is also good idea to run sysbench and mariadbd on different NUMA nodes, if possible.

Notes

The thread_cache_size system variable is not used when the thread pool is used and the Threads_cached status variable will have a value of 0.

See Also

  • Thread Pool System and Status Variables

  • Threadpool Benchmarks

  • Thread Pool in MariaDB 5.1 - 5.3

This page is licensed: CC BY-SA / Gnu FDL

Thread Groups in the Unix Implementation of the Thread Pool

This article does not apply to the thread pool implementation on Windows. On Windows, MariaDB uses a native thread pool created with the CreateThreadpool APl, which has its own methods to distribute threads between CPUs.

On Unix, the thread pool implementation uses objects called thread groups to divide up client connections into many independent sets of threads. The thread_pool_size system variable defines the number of thread groups on a system. Generally speaking, the goal of the thread group implementation is to have one running thread on each CPU on the system at a time. Therefore, the default value of the thread_pool_size system variable is auto-sized to the number of CPUs on the system.

When setting the thread_pool_size system variable's value at system startup, the max value is 100000. However, it is not a good idea to set it that high. When setting its value dynamically, the max value is either 128 or the value that was set at system startup--whichever value is higher. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL thread_pool_size=32;

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
..
thread_handling=pool-of-threads
thread_pool_size=32

If you do not want MariaDB to use all CPUs on the system for some reason, then you can set it to a lower value than the number of CPUs. For example, this would make sense if the MariaDB Server process is limited to certain CPUs with the taskset utility on Linux.

If you set the value to the number of CPUs and if you find that the CPUs are still underutilized, then try increasing the value.

The thread_pool_size system variable tends to have the most visible performance effect. It is roughly equivalent to the number of threads that can run at the same time. In this case, run means use CPU, rather than sleep or wait. If a client connection needs to sleep or wait for some reason, then it wakes up another client connection in the thread group before it does so.

One reason that CPU underutilization may occur in rare cases is that the thread pool is not always informed when a thread is going to wait. For example, some waits, such as a page fault or a miss in the OS buffer cache, cannot be detected by MariaDB.

Distributing Client Connections Between Thread Groups

When a new client connection is created, its thread group is determined using the following calculation:

thread_group_id = connection_id %  thread_pool_size

The connection_id value in the above calculation is the same monotonically increasing number that you can use to identify connections in SHOW PROCESSLIST output or the information_schema.PROCESSLIST table.

This calculation should assign client connections to each thread group in a round-robin manner. In general, this should result in an even distribution of client connections among thread groups.

Types of Threads

Thread Group Threads

Thread groups have two different kinds of threads: a listener thread and worker threads.

  • A thread group's worker threads actually perform work on behalf of client connections. A thread group can have many worker threads, but usually, only one will be actively running at a time. This is not always the case. For example, the thread group can become oversubscribed if the thread pool's timer thread detects that the thread group is stalled. This is explained more in the sections below.

  • A thread group's listener thread listens for I/O events and distributes work to the worker threads. If it detects that there is a request that needs to be worked on, then it can wake up a sleeping worker thread in the thread group, if any exist. If the listener thread is the only thread in the thread group, then it can also create a new worker thread. If there is only one request to handle, and if the thread_pool_dedicated_listener system variable is not enabled, then the listener thread can also become a worker thread and handle the request itself. This helps decrease the overhead that may be introduced by excessively waking up sleeping worker threads and excessively creating new worker threads.

Global Threads

The thread pool has one global thread: a timer thread. The timer thread performs tasks, such as:

  • Checks each thread group for stalls.

  • Ensures that each thread group has a listener thread.

Thread Creation

A new thread is created in a thread group in the scenarios listed below.

In all of the scenarios below, the thread pool implementation prefers to wake up a sleeping worker thread that already exists in the thread group, rather than to create a new thread.

Worker Thread Creation by Listener Thread

A thread group's listener thread can create a new worker thread when it has more client connection requests to distribute, but no pre-existing worker threads are available to work on the requests. This can help to ensure that the thread group always has enough threads to keep one worker thread active at a time.

A thread group's listener thread creates a new worker thread if all of the following conditions are met:

  • The listener thread receives a client connection request that needs to be worked on.

  • There are more client connection requests in the thread group's work queue that the listener thread still needs to distribute to worker threads, so the listener thread should not become a worker thread.

  • There are no active worker threads in the thread group.

  • There are no sleeping worker threads in the thread group that the listener thread can wake up.

  • And one of the following conditions is also met:

    • The entire thread pool has fewer than thread_pool_max_threads.

    • There are fewer than two threads in the thread group. This is to guarantee that each thread group can have at least two threads, even if thread_pool_max_threads has already been reached or exceeded.

Thread Creation by Worker Threads during Waits

A thread group's worker thread can create a new worker thread when the thread has to wait on something, and the thread group has more client connection requests queued, but no pre-existing worker threads are available to work on them. This can help to ensure that the thread group always has enough threads to keep one worker thread active at a time. For most workloads, this tends to be the primary mechanism that creates new worker threads.

A thread group's worker thread creates a new thread if all of the following conditions are met:

  • The worker thread has to wait on some request. For example, it might be waiting on disk I/O, or it might be waiting on a lock, or it might just be waiting for a query that called the SLEEP() function to finish.

  • There are no active worker threads in the thread group.

  • There are no sleeping worker threads in the thread group that the worker thread can wake up.

  • And one of the following conditions is also met:

    • The entire thread pool has fewer than thread_pool_max_threads.

    • There are fewer than two threads in the thread group. This is to guarantee that each thread group can have at least two threads, even if thread_pool_max_threads has already been reached or exceeded.

  • And one of the following conditions is also met:

    • There are more client connection requests in the thread group's work queue that the listener thread still needs to distribute to worker threads. In this case, the new thread is intended to be a worker thread.

    • There is currently no listener thread in the thread group. For example, if the thread_pool_dedicated_listener system variable is not enabled, then the thread group's listener thread can became a worker thread, so that it could handle some client connection request. In this case, the new thread can become the thread group's listener thread.

Listener Thread Creation by Timer Thread

The thread pool's timer thread can create a new listener thread for a thread group when the thread group has more client connection requests that need to be distributed, but the thread group does not currently have a listener thread to distribute them. This can help to ensure that the thread group does not miss client connection requests because it has no listener thread.

The thread pool's timer thread creates a new listener thread for a thread group if all of the following conditions are met:

  • The thread group has not handled any I/O events since the last check by the timer thread.

  • There is currently no listener thread in the thread group. For example, if the thread_pool_dedicated_listener system variable is not enabled, then the thread group's listener thread can became a worker thread, so that it could handle some client connection request. In this case, the new thread can become the thread group's listener thread.

  • There are no sleeping worker threads in the thread group that the timer thread can wake up.

  • And one of the following conditions is also met:

    • The entire thread pool has fewer than thread_pool_max_threads.

    • There are fewer than two threads in the thread group. This is to guarantee that each thread group can have at least two threads, even if thread_pool_max_threads has already been reached or exceeded.

  • If the thread group already has active worker threads, then the following condition also needs to be met:

    • A worker thread has not been created for the thread group within the throttling interval.

Worker Thread Creation by Timer Thread during Stalls

The thread pool's timer thread can create a new worker thread for a thread group when the thread group is stalled. This can help to ensure that a long query can't monopole its thread group.

The thread pool's timer thread creates a new worker thread for a thread group if all of the following conditions are met:

  • The timer thread thinks that the thread group is stalled. This means that the following conditions have been met:

    • There are more client connection requests in the thread group's work queue that the listener thread still needs to distribute to worker threads.

    • No client connection requests have been allowed to be dequeued to run since the last stall check by the timer thread.

  • There are no sleeping worker threads in the thread group that the timer thread can wake up.

  • And one of the following conditions is also met:

    • The entire thread pool has fewer than thread_pool_max_threads.

    • There are fewer than two threads in the thread group. This is to guarantee that each thread group can have at least two threads, even if thread_pool_max_threads has already been reached or exceeded.

  • A worker thread has not been created for the thread group within the throttling interval.

Thread Creation Throttling

In some of the scenarios listed above, a thread is only created within a thread group if no new threads have been created for the thread group within the throttling interval. The throttling interval depends on the number of threads that are already in the thread group.

In MariaDB 10.5 and later, thread creation is not throttled until a thread group has more than 1 + thread_pool_oversubscribe threads:

Number of Threads in Thread Group
Throttling Interval (milliseconds)

0-(1 + thread_pool_oversubscribe)

0

4-7

50 * THROTTLING_FACTOR

8-15

100 * THROTTLING_FACTOR

16-65536

20 * THROTTLING_FACTOR

THROTTLING_FACTOR = ([thread_pool_stall_limit](thread-pool-system-status-variables.md#thread_pool_stall_limit) / MAX (500,[thread_pool_stall_limit](thread-pool-system-status-variables.md#thread_pool_stall_limit))) <>

Thread Group Stalls

The thread pool has a feature that allows it to detect if a client connection is executing a long-running query that may be monopolizing its thread group. If a client connection were to monopolize its thread group, then that could prevent other client connections in the thread group from running their queries. In other words, the thread group would appear to be stalled.

This stall detection feature is implemented by creating a timer thread that periodically checks if any of the thread groups are stalled. There is only a single timer thread for the entire thread pool. The thread_pool_stall_limit system variable defines the number of milliseconds between each stall check performed by the timer thread. The default value is 500. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL thread_pool_stall_limit=300;

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
..
thread_handling=pool-of-threads
thread_pool_size=32
thread_pool_stall_limit=300

The timer thread considers a thread group to be stalled if the following is true:

  • There are more client connection requests in the thread group's work queue that the listener thread still needs to distribute to worker threads.

  • No client connection requests have been allowed to be dequeued to run since the last stall check by the timer thread.

This indicates that the one or more client connections currently using the active worker threads may be monopolizing the thread group, and preventing the queued client connections from performing work. When the timer thread detects that a thread group is stalled, it wakes up a sleeping worker thread in the thread group, if one is available. If there isn't one, then it creates a new worker thread in the thread group. This temporarily allows several client connections in the thread group to run in parallel.

The thread_pool_stall_limit system variable essentially defines the limit for what a "fast query" is. If a query takes longer than thread_pool_stall_limit, then the thread pool is likely to think that it is too slow, and it will either wake up a sleeping worker thread or create a new worker thread to let another client connection in the thread group run a query in parallel.

In general, changing the value of the thread_pool_stall_limit system variable has the following effect:

  • Setting it to higher values can help avoid starting too many parallel threads if you expect a lot of client connections to execute long-running queries.

  • Setting it to lower values can help prevent deadlocks.

Thread Group Oversubscription

If the timer thread were to detect a stall in a thread group, then it would either wake up a sleeping worker thread or create a new worker thread in that thread group. At that point, the thread group would have multiple active worker threads. In other words, the thread group would be oversubscribed.

You might expect that the thread pool would shutdown one of the worker threads when the stalled client connection finished what it was doing, so that the thread group would only have one active worker thread again. However, this does not always happen. Once a thread group is oversubscribed, the thread_pool_oversubscribe system variable defines the upper limit for when worker threads start shutting down after they finish work for client connections. The default value is 3. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL thread_pool_oversubscribe=10;

It can also be set in a server option group in an option file prior to starting up the server. For example:

[mariadb]
..
thread_handling=pool-of-threads
thread_pool_size=32
thread_pool_stall_limit=300
thread_pool_oversubscribe=10

To clarify, the thread_pool_oversubscribe system variable does not play any part in the creation of new worker threads. The thread_pool_oversubscribe system variable is only used to determine how many worker threads should remain active in a thread group, once a thread group is already oversubscribed due to stalls.

In general, the default value of 3 should be adequate for most users. Most users should not need to change the value of the thread_pool_oversubscribe system variable.

This page is licensed: CC BY-SA / Gnu FDL

Thread Pool System and Status Variables

This article describes the system and status variables used by the MariaDB thread pool. For a full description, see Thread Pool in MariaDB.

System variables

extra_max_connections

  • Description: The number of connections on the extra_port.

    • See Thread Pool in MariaDB: Configuring the Extra Port for more information.

  • Commandline: --extra-max-connections=#

  • Scope: Global

  • Dynamic: Yes

  • Data Type: numeric

  • Default Value: 1

  • Range: 1 to 100000

extra_port

  • Description: Extra port number to use for TCP connections in a one-thread-per-connection manner. If set to 0, then no extra port is used.

    • See Thread Pool in MariaDB: Configuring the Extra Port for more information.

  • Commandline: --extra-port=#

  • Scope: Global

  • Dynamic: No

  • Data Type: numeric

  • Default Value: 0

thread_handling

  • Description: Determines how the server handles threads for client connections. In addition to threads for client connections, this also applies to certain internal server threads, such as Galera slave threads. On Windows, if you would like to use the thread pool, then you do not need to do anything, because the default for the thread_handling system variable is already preset to pool-of-threads.

    • When the default one-thread-per-connection mode is enabled, the server uses one thread to handle each client connection.

    • When the pool-of-threads mode is enabled, the server uses the thread pool for client connections.

    • When the no-threads mode is enabled, the server uses a single thread for all client connections, which is really only usable for debugging.

  • Commandline: --thread-handling=name

  • Scope: Global

  • Dynamic: No

  • Data Type: enumeration

  • Default Value: one-thread-per-connection (non-Windows), pool-of-threads (Windows)

  • Valid Values: no-threads, one-thread-per-connection, pool-of-threads.

  • Documentation: Using the thread pool.

  • Notes: In MySQL, the thread pool is only available in MySQL Enterprise. In MariaDB it's available in all versions.

thread_pool_dedicated_listener

  • Description: If set to 1, then each group will have its own dedicated listener, and the listener thread will not pick up work items. As a result, the queueing time in the Information Schema Threadpool_Queues and the actual queue size in the Information Schema Threadpool_Groups table will be more exact, since IO requests are immediately dequeued from poll, without delay.

    • This system variable is only meaningful on Unix.

  • Commandline: thread-pool-dedicated-listener={0|1}

  • Scope:

  • Dynamic:

  • Data Type: boolean

  • Default Value: 0

  • Introduced: MariaDB 10.5.0

thread_pool_exact_stats

  • Description: If set to 1, provides better queueing time statistics by using a high precision timestamp, at a small performance cost, for the time when the connection was added to the queue. This timestamp helps calculate the queuing time shown in the Information Schema Threadpool_Queues table.

    • This system variable is only meaningful on Unix.

  • Commandline: thread-pool-exact-stats={0|1}

  • Scope:

  • Dynamic:

  • Data Type: boolean

  • Default Value: 0

  • Introduced: MariaDB 10.5.0

thread_pool_idle_timeout

  • Description: The number of seconds before an idle worker thread exits. The default value is 60. If there is currently no work to do, how long should an idle thread wait before exiting?

    • This system variable is only meaningful on Unix.

    • The thread_pool_min_threads system variable is comparable for Windows.

  • Commandline: thread-pool-idle-timeout=#

  • Scope: Global

  • Dynamic: Yes

  • Data Type: numeric

  • Default Value: 60

  • Documentation: Using the thread pool.

thread_pool_max_threads

  • Description: The maximum number of threads in the thread pool. Once this limit is reached, no new threads will be created in most cases.

    • On Unix, in rare cases, the actual number of threads can slightly exceed this, because each thread group needs at least two threads (i.e. at least one worker thread and at least one listener thread) to prevent deadlocks.

  • Scope:

  • Commandline: thread-pool-max-threads=#

  • Scope: Global

  • Dynamic: Yes

  • Data Type: numeric

  • Default Value:

    • 65536 (>= MariaDB 10.2.4)

    • 1000 (<= MariaDB 10.2.3, >= MariaDB 10.1)

    • 500 (<= MariaDB 10.0)

  • Range: 1 to 65536

  • Documentation: Using the thread pool.

thread_pool_min_threads

  • Description: Minimum number of threads in the thread pool. In bursty environments, after a period of inactivity, threads would normally be retired. When the next burst arrives, it would take time to reach the optimal level. Setting this value higher than the default would prevent thread retirement even if inactive.

    • This system variable is only meaningful on Windows.

    • The thread_pool_idle_timeout system variable is comparable for Unix.

  • Commandline: thread-pool-min-threads=#

  • Data Type: numeric

  • Default Value: 1

  • Documentation: Using the thread pool.

thread_pool_oversubscribe

  • Description: Determines how many worker threads in a thread group can remain active at the same time once a thread group is oversubscribed due to stalls. The default value is 3. Usually, a thread group only has one active worker thread at a time. However, the timer thread can add more active worker threads to a thread group if it detects a stall. There are trade-offs to consider when deciding whether to allow only one thread per CPU to run at a time, or whether to allow more than one thread per CPU to run at a time. Allowing only one thread per CPU means that the thread can have unrestricted access to the CPU while its running, but it also means that there is additional overhead from putting threads to sleep or waking them up more frequently. Allowing more than one thread per CPU means that the threads have to share the CPU, but it also means that there is less overhead from putting threads to sleep or waking them up.

    • See Thread Groups in the Unix Implementation of the Thread Pool: Thread Group Oversubscription for more information.

    • This is primarily for internal use, and it is not meant to be changed for most users.

    • This system variable is only meaningful on Unix.

  • Scope: Global

  • Dynamic: Yes

  • Data Type: numeric

  • Default Value: 3

  • Range: 1 to 65536

  • Documentation: Using the thread pool.

thread_pool_prio_kickup_timer

  • Description: Time in milliseconds before a dequeued low-priority statement is moved to the high-priority queue.

    • This system variable is only meaningful on Unix.

  • Commandline: thread-pool-kickup-timer=#

  • Scope: Global

  • Dynamic: Yes

  • Data Type: numeric

  • Default Value: 1000

  • Range: 0 to 4294967295

  • Introduced: MariaDB 10.2.2

  • Documentation: Using the thread pool.

thread_pool_priority

  • Description: Thread pool priority. High-priority connections usually start executing earlier than low-priority. If set to 'auto' (the default), the actual priority (low or high) is determined by whether or not the connection is inside a transaction.

  • Commandline: --thread-pool-priority=#

  • Scope: Global,Connection

  • Data Type: enum

  • Default Value: auto

  • Valid Values: high, low, auto.

  • Introduced: MariaDB 10.2.2

  • Documentation: Using the thread pool.

thread_pool_size

  • Description: The number of thread groups in the thread pool, which determines how many statements can execute simultaneously. The default value is the number of CPUs on the system. When setting this system variable's value at system startup, the max value is 100000. However, it is not a good idea to set it that high. When setting this system variable's value dynamically, the max value is either 128 or the value that was set at system startup--whichever value is higher.

    • See Thread Groups in the Unix Implementation of the Thread Pool for more information.

    • This system variable is only meaningful on Unix.

  • Commandline: --thread-pool-size=#

  • Scope: Global

  • Dynamic: Yes

  • Data Type: numeric

  • Default Value: Based on the number of processors (but see MDEV-7806).

  • Range: 1 to 128 (< MariaDB 5.5.37, MariaDB 10.0.11), 1 to 100000 (>= MariaDB 5.5.37, MariaDB 10.0.11)

  • Documentation: Using the thread pool.

thread_pool_stall_limit

  • Description: The number of milliseconds between each stall check performed by the timer thread. The default value is 500. Stall detection is used to prevent a single client connection from monopolizing a thread group. When the timer thread detects that a thread group is stalled, it wakes up a sleeping worker thread in the thread group, if one is available. If there isn't one, then it creates a new worker thread in the thread group. This temporarily allows several client connections in the thread group to run in parallel. However, note that the timer thread will not create a new worker thread if the number of threads in the thread pool is already greater than or equal to the maximum defined by the thread_pool_max_threads variable, unless the thread group does not already have a listener thread.

    • See Thread Groups in the Unix Implementation of the Thread Pool: Thread Group Stalls for more information.

    • This system variable is only meaningful on Unix.

    • Note that if you are migrating from the MySQL Enterprise thread pool plugin, then the unit used in their implementation is 10ms, not 1ms.

  • Commandline: --thread-pool-stall-limit=#

  • Scope: Global

  • Dynamic: Yes

  • Data Type: numeric

  • Default Value: 500

  • Range: 10 to 4294967295 (< MariaDB 10.5), 1 to 4294967295 (>= MariaDB 10.5)

  • Documentation: Using the thread pool.

Status variables

Threadpool_idle_threads

  • Description: Number of inactive threads in the thread pool. Threads become inactive for various reasons, such as by waiting for new work. However, an inactive thread is not necessarily one that has not been assigned work. Threads are also considered inactive if they are being blocked while waiting on disk I/O, or while waiting on a lock, etc.

    • This status variable is only meaningful on Unix.

  • Scope: Global, Session

  • Data Type: numeric

Threadpool_threads

  • Description: Number of threads in the thread pool. In rare cases, this can be slightly higher than thread_pool_max_threads, because each thread group needs at least two threads (i.e. at least one worker thread and at least one listener thread) to prevent deadlocks.

  • Scope: Global, Session

  • Data Type: numeric

See Also

  • Thread Pool in MariaDB

This page is licensed: CC BY-SA / Gnu FDL

Thread Pool in MariaDB 5.1 - 5.3

This article describes the old thread pool in MariaDB 5.1 - 5.3. MariaDB 5.5 and later use an improved thread pool - see Thread pool in MariaDB.

About pool of threads

This is an extended version of the pool-of-threads code from MySQL 6.0. This allows you to use a limited set of threads to handle all queries, instead of the old 'one-thread-per-connection' style. In recent times, its also been referred to as "thread pool" or "thread pooling" as this feature (in a different implementation) is available in Enterprise editions of MySQL (not in the Community edition).

This can be a very big win if most of your queries are short running queries and there are few table/row locks in your system.

Instructions

To enable pool-of-threads you must first run configure with the--with-libevent option. (This is automatically done if you use any 'max' scripts in the BUILD directory):

./configure --with-libevent

When starting mysqld with the pool of threads code you should use

mysqld --thread-handling=pool-of-threads --thread-pool-size=20

Default values are:

thread-handling=  one-thread-per-connection
thread-pool-size= 20

One issue with pool-of-threads is that if all worker threads are doing work (like running long queries) or are locked by a row/table lock no new connections can be established and you can't login and find out what's wrong or login and kill queries.

To help this, we have introduced two new options for mysqld; extra_port and extra_max_connections:

--extra-port=#             (Default 0)
--extra-max-connections=#  (Default 1)

If extra-port is <> 0 then you can connect max_connections number of normal threads + 1 extra SUPER user through the 'extra-port' TCP/IP port. These connections use the old one-thread-per-connection method.

To connect with through the extra port, use:

mysql --port='number-of-extra-port' --protocol=tcp

This allows you to freely use, on connection bases, the optimal connection/thread model.

See also

  • Thread-handling and thread-pool-size variables

  • How MySQL Uses Threads for Client Connections

This page is licensed: CC BY-SA / Gnu FDL

Thread States

Understand MariaDB Server thread states. This section explains the different states a thread can be in, helping you monitor and troubleshoot query execution and server performance.

Delayed Insert Connection Thread States

This article documents thread states that are related to the connection thread that processes INSERT DELAYED statements.

These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

Value
Description

allocating local table

Preparing to allocate rows to the delayed-insert handler thread. Follows from the got handler lock state.

Creating delayed handler

Creating a handler for the delayed-inserts.

got handler lock

Lock to access the delayed-insert handler thread has been received. Follows from the waiting for handler lock state and before the allocating local table state.

got old table

The initialization phase is over. Follows from the waiting for handler open state.

storing row into queue

Adding new row to the list of rows to be inserted by the delayed-insert handler thread.

waiting for delay_list

Initializing (trying to find the delayed-insert handler thread).

waiting for handler insert

Waiting for new inserts, as all inserts have been processed.

waiting for handler lock

Waiting for delayed insert-handler lock to access the delayed-insert handler thread.

waiting for handler open

Waiting for the delayed-insert handler thread to initialize. Follows from the Creating delayed handler state and before the got old table state.

This page is licensed: CC BY-SA / Gnu FDL

Delayed Insert Handler Thread States

This article documents thread states that are related to the handler thread that inserts the results of INSERT DELAYED statements.

These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

Value
Description

insert

About to insert rows into the table.

reschedule

Sleeping in order to let other threads function, after inserting a number of rows into the table.

upgrading lock

Attempting to get lock on the table in order to insert rows.

Waiting for INSERT

Waiting for the delayed-insert connection thread to add rows to the queue.

This page is licensed: CC BY-SA / Gnu FDL

Event Scheduler Thread States

This article documents thread states that are related to event scheduling and execution. These include the Event Scheduler thread, threads that terminate the Event Scheduler, and threads for executing events.

These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table

Value
Description

Clearing

Thread is terminating.

Initialized

Thread has be initialized.

Waiting for next activation

The event queue contains items, but the next activation is at some time in the future.

Waiting for scheduler to stop

Waiting for the event scheduler to stop after issuing SET GLOBAL event_scheduler=OFF.

Waiting on empty queue

Sleeping, as the event scheduler's queue is empty.

This page is licensed: CC BY-SA / Gnu FDL

General Thread States

This article documents the major general thread states. More specific lists related to delayed inserts, replication, the query cache and the event scheduler are listed in:

  • Event Scheduler Thread States

  • Query Cache Thread States

  • Master Thread States

  • Slave Connection Thread States

  • Slave I/O Thread States

  • Slave SQL Thread States

These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table

Value
Description

After create

The function that created (or tried to create) a table (temporary or non-temporary) has just ended.

Analyzing

Calculating table key distributions, such as when running an ANALYZE TABLE statement.

checking permissions

Checking to see whether the permissions are adequate to perform the statement.

Checking table

Checking the table.

cleaning up

Preparing to reset state variables and free memory after executing a command.

closing tables

Flushing the changes to disk and closing the table. This state will only persist if the disk is full or under extremely high load.

converting HEAP to Aria

Converting an internal MEMORY temporary table into an on-disk Aria temporary table.

converting HEAP to MyISAM

Converting an internal MEMORY temporary table into an on-disk MyISAM temporary table.

copy to tmp table

A new table has been created as part of an ALTER TABLE statement, and rows are about to be copied into it.

Copying to group table

Sorting the rows by group and copying to a temporary table, which occurs when a statement has different GROUP BY and ORDER BY criteria.

Copying to tmp table

Copying to a temporary table in memory.

Copying to tmp table on disk

Copying to a temporary table on disk, as the resultset is too large to fit into memory.

Creating index

Processing an ALTER TABLE ... ENABLE KEYS for an Aria or MyISAM table.

Creating sort index

Processing a SELECT statement resolved using an internal temporary table.

creating table

Creating a table (temporary or non-temporary).

Creating tmp table

Creating a temporary table (in memory or on-disk).

deleting from main table

Deleting from the first table in a multi-table delete, saving columns and offsets for use in deleting from the other tables.

deleting from reference tables

Deleting matched rows from secondary reference tables as part of a multi-table delete.

discard_or_import_tablespace

Processing an ALTER TABLE ... IMPORT TABLESPACE or ALTER TABLE ... DISCARD TABLESPACE statement.

end

State before the final cleanup of an ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statement.

executing

Executing a statement.

Execution of init_command

Executing statements specified by the --init_command mariadb client option.

filling schema table

A table in the information_schema database is being built.

freeing items

Freeing items from the query cache after executing a command. Usually followed by the cleaning up state.

Flushing tables

Executing a FLUSH TABLES statement and waiting for other threads to close their tables.

FULLTEXT initialization

Preparing to run a full-text search. This includes running the fulltext search (MATCH ... AGAINST) and creating a list of the result in memory

init

About to initialize an ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statement. Could be performaing query cache cleanup, or flushing the binary log or InnoDB log.

Killed

Thread will abort next time it checks the kill flag. Requires waiting for any locks to be released.

Locked

Query has been locked by another query.

logging slow query

Writing statement to the slow query log.

NULL

State used for SHOW PROCESSLIST.

login

Connection thread has not yet been authenticated.

manage keys

Enabling or disabling a table index.

Opening table[s]

Trying to open a table. Usually very quick unless the limit set by table_open_cache has been reached, or an ALTER TABLE or LOCK TABLE is in progress.

optimizing

Server is performing initial optimizations in for a query.

preparing

State occurring during query optimization.

Purging old relay logs

Relay logs that are no longer needed are being removed.

query end

Query has finished being processed, but items have not yet been freed (the freeing items state.

Reading file

Server is reading the file (for example during LOAD DATA INFILE).

Reading from net

Server is reading a network packet.

Removing duplicates

Duplicated rows being removed before sending to the client. This happens when SELECT DISTINCT is used in a way that the distinct operation could not be optimized at an earlier point.

removing tmp table

Removing an internal temporary table after processing a SELECT statement.

rename

Renaming a table.

rename result table

Renaming a table that results from an ALTER TABLE statement having created a new table.

Reopen tables

Table is being re-opened after thread obtained a lock but the underlying table structure had changed, so the lock was released.

Repair by sorting

Indexes are being created with the use of a sort. Much faster than the related Repair with keycache.

Repair done

Multi-threaded repair has been completed.

Repair with keycache

Indexes are being created through the key cache, one-by-one. Much slower than the related Repair by sorting.

Rolling back

A transaction is being rolled back.

Saving state

New table state is being saved. For example, after, analyzing a MyISAM table, the key distributions, rowcount etc. are saved to the .MYI file.

Searching rows for update

Finding matching rows before performing an UPDATE, which is needed when the UPDATE would change the index used for the UPDATE

Sending data

Sending data to the client as part of processing a SELECT statement or other statements that returns data like INSERT ... RETURNING . Often the longest-occurring state as it also include all reading from tables and disk read activities. Where an aggregation or un-indexed filtering occurs there is significantly more rows read than what is sent to the client.

setup

Setting up an ALTER TABLE operation.

Sorting for group

Sorting as part of a GROUP BY

Sorting for order

Sorting as part of an ORDER BY

Sorting index

Sorting index pages as part of a table optimization operation.

Sorting result

Processing a SELECT statement using a non-temporary table.

statistics

Calculating statistics as part of deciding on a query execution plan. Usually a brief state unless the server is disk-bound.

System lock

Requesting or waiting for an external lock for a specific table. The storage engine determines what kind of external lock to use. For example, the MyISAM storage engine uses file-based locks. However, MyISAM's external locks are disabled by default, due to the default value of the skip_external_locking system variable. Transactional storage engines such as InnoDB also register the transaction or statement with MariaDB's transaction coordinator while in this thread state. See MDEV-19391 for more information about that.

Table lock

About to request a table's internal lock after acquiring the table's external lock. This thread state occurs after the System lock thread state.

update

About to start updating table.

Updating

Searching for and updating rows in a table.

updating main table

Updating the first table in a multi-table update, and saving columns and offsets for use in the other tables.

updating reference tables

Updating the secondary (reference) tables in a multi-table update

updating status

This state occurs after a query's execution is complete. If the query's execution time exceeds long_query_time, then Slow_queries is incremented, and if the slow query log is enabled, then the query is logged. If the SERVER_AUDIT plugin is enabled, then the query is also logged into the audit log at this stage. If the userstats plugin is enabled, then CPU statistics are also updated at this stage.

User lock

About to request or waiting for an advisory lock from a GET LOCK() call. For SHOW PROFILE, means requesting a lock only.

User sleep

A SLEEP() call has been invoked.

Waiting for commit lock

FLUSH TABLES WITH READ LOCK is waiting for a commit lock, or a statement resulting in an explicit or implicit commit is waiting for a read lock to be released. This state was called Waiting for all running commits to finish in earlier versions.

Waiting for global read lock

Waiting for a global read lock.

Waiting for table level lock

External lock acquired,and internal lock about to be requested. Occurs after the System lock state. In earlier versions, this was called Table lock.

Waiting for xx lock

Waiting to obtain a lock of type xx.

Waiting on cond

Waiting for an unspecified condition to occur.

Writing to net

Writing a packet to the network.

This page is licensed: CC BY-SA / Gnu FDL

Master Thread States

This article documents thread states that are related to replication master threads. These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

Value
Description

Finished reading one binlog; switching to next binlog

After completing one binary log, the next is being opened for sending to the slave.

Master has sent all binlog to slave; waiting for binlog to be updated

All events have been read from the binary logs and sent to the slave. Now waiting for the binary log to be updated with new events.

Sending binlog event to slave

An event has been read from the binary log, and is now being sent to the slave.

Waiting to finalize termination

State that only occurs very briefly while the thread is terminating.

This page is licensed: CC BY-SA / Gnu FDL

Query Cache Thread States

This article documents thread states that are related to the Query Cache. These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

Value
Description

checking privileges on cached query

Checking whether the user has permission to access a result in the query cache.

checking query cache for query

Checking whether the current query exists in the query cache.

invalidating query cache entries

Marking query cache entries as invalid as the underlying tables have changed.

sending cached result to client

A result found in the query cache is being sent to the client.

storing result in query cache

Saving the result of a query into the query cache.

Waiting for query cache lock

Waiting to take a query cache lock.

This page is licensed: CC BY-SA / Gnu FDL

Replica I/O Thread States

This article documents thread states that are related to replica I/O threads. These correspond to the Slave_IO_State shown by SHOW REPLICA STATUS and the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

Value
Description

Checking master version

Checking the primary's version, which only occurs very briefly after establishing a connection with the primary.

Connecting to master

Attempting to connect to primary.

Queueing master event to the relay log

Event is being copied to the relay log after being read, where it can be processed by the SQL thread.

Reconnecting after a failed binlog dump request

Attempting to reconnect to the primary after a previously failed binary log dump request.

Reconnecting after a failed master event read

Attempting to reconnect to the primary after a previously failed request. After successfully connecting, the state will change to Waiting for master to send event.

Registering slave on master

Registering the replica on the primary, which only occurs very briefly after establishing a connection with the primary.

Requesting binlog dump

Requesting the contents of the binary logs from the given log file name and position. Only occurs very briefly after establishing a connection with the primary.

Waiting for master to send event

Waiting for binary log events to arrive after successfully connecting. If there are no new events on the primary, this state can persist for as many seconds as specified by the slave_net_timeout system variable, after which the thread will reconnect. Prior to MariaDB 10.6.18, MariaDB 10.11.8, MariaDB 11.0.6, MariaDB 11.1.5, MariaDB 11.2.4 and MariaDB 11.4.2, the time was from SLAVE START. From these versions, the time is since reading the last event.

Waiting for slave mutex on exit

Waiting for replica mutex while the thread is stopping. Only occurs very briefly.

Waiting for the slave SQL thread to free enough relay log space.

Relay log has reached its maximum size, determined by relay_log_space_limit (no limit by default), so waiting for the SQL thread to free up space by processing enough relay log events.

Waiting for master update

State before connecting to primary.

Waiting to reconnect after a failed binlog dump request

Waiting to reconnect after a binary log dump request has failed due to disconnection. The length of time in this state is determined by the MASTER_CONNECT_RETRY clause of the CHANGE MASTER TO statement.

Waiting to reconnect after a failed master event read

Sleeping while waiting to reconnect after a disconnection error. The time in seconds is determined by the MASTER_CONNECT_RETRY clause of the CHANGE MASTER TO statement.

This page is licensed: CC BY-SA / Gnu FDL

Replica Connection Thread States

This article documents thread states that are related to connection threads that occur on a replicatioin replica. These correspond to the STATE values listed by the SHOW PROCESSLIST statement or in the Information Schema PROCESSLIST Table as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

Value
Description

Changing master

Processing a CHANGE MASTER TO statement.

Killing slave

Processing a STOP SLAVE statement.

Opening master dump table

A table has been created from a master dump and is now being opened.

Reading master dump table data

After the table created by a master dump (the Opening master dump table state) the table is now being read.

Rebuilding the index on master dump table

After the table created by a master dump has been opened and read (the Reading master dump table data state), the index is built.

This page is licensed: CC BY-SA / Gnu FDL

Replica SQL Thread States

This article documents thread states that are related to replication slave SQL threads. These correspond to the Slave_SQL_State shown by SHOW SLAVE STATUS as well as the STATE values listed by the SHOW PROCESSLIST statement and the Information Schema PROCESSLIST as well as the PROCESSLIST_STATE value listed in the Performance Schema threads Table.

Value
Description

Apply log event

Log event is being applied.

Making temp file

Creating a temporary file containing the row data as part of a LOAD DATA INFILE statement.

Reading event from the relay log

Reading an event from the relay log in order to process the event.

Slave has read all relay log; waiting for the slave I/O thread to update it

All relay log events have been processed, now waiting for the I/O thread to write new events to the relay log.

Waiting for work from SQL thread

In parallel replication the worker thread is waiting for more things from the SQL thread.

Waiting for prior transaction to start commit before starting next transaction

In parallel replication the worker thread is waiting for conflicting things to end before starting executing.

Waiting for worker threads to be idle

Happens in parallel replication when moving to a new binary log after a master restart. All slave temporary files are deleted and worker threads are restarted.

Waiting due to global read lock

In parallel replication when worker threads are waiting for a global read lock to be released.

Waiting for worker threads to pause for global read lock

FLUSH TABLES WITH READ LOCK is waiting for worker threads to finish what they are doing.

Waiting while replication worker thread pool is busy

Happens in parallel replication during a FLUSH TABLES WITH READ LOCK or when changing number of parallel workers.

Waiting for other master connection to process GTID received on multiple master connections

A worker thread noticed that there is already another thread executing the same GTID from another connection and it's waiting for the other to complete.

Waiting for slave mutex on exit

Thread is stopping. Only occurs very briefly.

Waiting for the next event in relay log

State before reading next event from the relay log.

This page is licensed: CC BY-SA / Gnu FDL