thread_cache_size

Overview

Controls how many threads are cached for use by new client connections. Threads are freed after 5 minutes of idle time.

USAGE

DETAILS

The thread_cache_size system variable controls how many threads are cached for new client connections. Caching threads for new client connections can help improve performance in environments with high volumes of new connections.

Query Thread Cache Size

The number of threads in the cache can be queried by using the SHOW STATUS statement to query the value of the Threads_cached status variable:

SHOW GLOBAL STATUS
   LIKE 'Threads_cached';

Compute Thread Cache Miss Rate

The thread cache miss rate can be computed using the Threads_created and Connections status variables:

If you divide Threads_created by Connections, that shows the thread cache miss rate:

 SELECT threads_created, connections,
    (threads_created / connections) AS thread_cache_miss_rate
 FROM
    (SELECT gs1.VARIABLE_VALUE AS threads_created
    FROM information_schema.GLOBAL_STATUS gs1
    WHERE gs1.VARIABLE_NAME LIKE 'Threads_created') tc
JOIN
    (SELECT gs2.VARIABLE_VALUE AS connections
    FROM information_schema.GLOBAL_STATUS gs2
    WHERE gs2.VARIABLE_NAME LIKE 'Connections') c;

If the above query is executed at different times, the results can be interpreted in the following way:

  • If thread_cache_miss_rate stays constant or grows and approaches 1, the current thread cache size is not large enough to keep up with new connection requests.

  • If thread_cache_miss_rate shrinks and approaches 0, the current thread cache size is either equal to or larger than needed to keep up with new connection requests. To determine whether it is larger than needed, the Threads_cached status variable can also be monitored to determine whether the thread cache consistently contains too many cached threads.

Interactions with Thread Pool

When the thread pool is enabled by setting thread_handling=pool-of-threads, the thread cache is unused, so the thread_cache_size system variable has no effect.

Interactions with max_connections

If the thread_cache_size system variable is set to a value greater than the value of the max_connections system variable, the thread_cache_size value will be set to the max_connections value.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--thread_cache_size=#

Configuration file

Supported

Dynamic

Yes

Scope

Global

Data Type

BIGINT UNSIGNED

Minimum Value

0

Maximum Value

16384

Product Default Value

256

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

151 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Single Node Analytics

10.6

151 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Replicated Transactions

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Single Node Transactions

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

GCP

Multi-Node Analytics

10.6

151 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Single Node Analytics

10.6

151 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Replicated Transactions

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Single Node Transactions

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

151 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Enterprise Server With Replica(s)

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Enterprise Server Single Node

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

GCP

ColumnStore Data Warehouse

10.6

151 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Enterprise Server With Replica(s)

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

Enterprise Server Single Node

10.4

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.5

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

10.6

256 (choices: 0, 128, 256, 512, 1024, 2048, 4096)

PRIVILEGES

EXAMPLES

Set in a Configuration File

A configuration file can persistently set the global value:

[mariadb]
thread_cache_size=512

After changing a configuration file, the server must be restarted. To change the global value without a server restart, set it with SET GLOBAL too.

Set Dynamically with SET GLOBAL

SET GLOBAL can dynamically set the global value:

SET GLOBAL thread_cache_size = 512;

This operation requires the SUPER privilege.

When the server is restarted, the change does not persist. To make the change persist on restart, set it in a configuration file too.

Set on the Command-line

When MariaDB Server (mariadbd) is started via the command-line, the --thread-cache-size command-line option can set the global value:

$ mariadbd .. --thread-cache-size=512 ..

Show Value with SHOW VARIABLES

SHOW GLOBAL VARIABLES can show the global value:

SHOW GLOBAL VARIABLES
   LIKE 'thread_cache_size';

Since this system variable is only global scope, SHOW VARIABLES and SHOW SESSION VARIABLES also show the global value.

Query Value using @@global and @@session

In SELECT and other SQL statements, @@global can be queried for the global value:

SELECT @@global.thread_cache_size;

Since this system variable is only global scope, @@session can't be queried for this variable.

Information Schema

The information_schema.SYSTEM_VARIABLES table can be queried to obtain details about the system variable:

SELECT *
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME LIKE 'thread_cache_size';

The information_schema.GLOBAL_VARIABLES table can be queried to obtain the global value:

SELECT *
FROM information_schema.GLOBAL_VARIABLES
WHERE VARIABLE_NAME LIKE 'thread_cache_size';

This system variable is only global scope, so the information_schema.SESSION_VARIABLES table can also be queried to obtain the global value:

SELECT *
FROM information_schema.SESSION_VARIABLES
WHERE VARIABLE_NAME LIKE 'thread_cache_size';

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES