MySQL Thread Cache Size

From Knowledge Center
Jump to: navigation, search

It's advisable to set thread_cache_size so that most new connections use threads from the cache rather than newly created threads. This saves some thread-creation overhead, though normally does not create a significant performance improvement.

Good Values to look at:

mysql> SHOW GLOBAL STATUS LIKE 'Connections';
mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
mysql> SHOW GLOBAL STATUS LIKE 'Threads_cached';
mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';

Set the Thread Cache Size:

On the fly:

mysql> set global thread_cache_size = 16;
Query OK, 0 rows affected (0.00 sec)

or for a persistent change, edit my.cnf

thread_cache_size       = 16

Have a look at the variables:

mysql> show variables;
+--------------------------------------------------------+--------
| Variable_name                                          | Value
| thread_cache_size                                      | 16

Also good to look at if connections are sleeping:

MariaDB [(none)]> show processlist;
+--------+------+-----------------+---------------------+---------+------+-------+------------------+----------+
| Id     | User | Host            | db                  | Command | Time | State | Info             | Progress |
+--------+------+-----------------+---------------------+---------+------+-------+------------------+----------+
| 250067 | root | localhost:56640 | NULL                | Sleep   |    9 |       | NULL             |    0.000 |
| 267438 | u1t  | localhost:41638 | db_1234567890234567 | Sleep   |   84 |       | NULL             |    0.000 |
| 267444 | u1t  | localhost:41654 | db_1234567890234567 | Sleep   |   83 |       | NULL             |    0.000 |
| 267466 | u1t  | localhost:41700 | db_1234567890234567 | Sleep   |   75 |       | NULL             |    0.000 |
| 267467 | u1t  | localhost:41706 | db_1234567890234567 | Sleep   |   73 |       | NULL             |    0.000 |
| 267527 | root | localhost       | NULL                | Query   |    0 | init  | show processlist |    0.000 |
+--------+------+-----------------+---------------------+---------+------+-------+------------------+----------+

If so, configure wait_timeout = 60 in the my.cnf which essentially is the number of seconds the server waits for activity on a noninteractive connection before closing it.

References: