How to optimize Thread Cache variables for MySQL server

If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. Your CPU will thank you.

Ref : http://jeremy.zawodny.com/blog/archives/000173.html

“As soon as I optimized the thread cache, MySQL’s server load dropped over 50%!”

Ref: http://www.epigroove.com/posts/63/optimize_mysql_the_thread_cache

How to set thread cache :

You should keep your thread cache ( variables name ‘thread_cache_size’) large enough that Threads_created doesn’t increase very often.

To check whether the thread cache is large enough, watch the “Threads_created” status variable and ‘thread_cache_size’ system variables.

mysql> show status like ‘threads_created’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| Threads_created | 2 |
+—————–+——-+
1 row in set (0.00 sec)

Bellow commands will show the size of threads cache size :

mysql> show variables like ‘thread_cache_size’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 12 |
+——————-+——-+
1 row in set (0.00 sec)

A good approach is to watch the Threads_connected variable and try to set thread_cache_size large enough to handle the typical fluctuation in your workload.

Bellow commands will show all threads related status:

mysql> show status like ‘threads_%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 1 |
+——————-+——-+
4 rows in set (0.00 sec)

How to monitor Threads by cacti : Working on it…

Leave a Reply

*