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.
“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…