Mysql Worker thread analysis

Every connection to mysql database needs a thread to process the request and send back results. Each connections use some part of cpu resource to execute the thread. If you application makes lots of separate connections to your database over short periods of time or create and close  lot of connections  every seconds, the process of spawning new threads at high rate cause a lot of overhead on cpu which allocate and de allocate associated stacks for each connections or threads. To resolve this problem mysql implements thread cache, which allow it to save threads from connections that are being closed and reuse them for new connections. The parameter thread_cache_size defined how many unused threads can be kept in memory so that application can reuse them without creating a new thread.

To check what is current value of thread_cache_size configured:

 mysql> select @@thread_cache_size;

+---------------------+

| @@thread_cache_size |

+---------------------+

|                 512 | 

+---------------------+

1 row in set (0.00 sec)

 Or

mysql> show variables like '%thread%';

+----------------------------+--------+

| Variable_name              | Value  |

+----------------------------+--------+

| max_delayed_threads        | 20     | 

| max_insert_delayed_threads | 20     | 

| myisam_repair_threads      | 1      | 

| thread_cache_size          | 512    | 

+----------------------------+--------+

To determine how much the thread_cache_size variable should be set, we need to check thread status

mysql>show status like '%thread%';

+------------------------------------------+-------+

| Variable_name             | Value |

+------------------------------------------+-------+

| Slow_launch_thread        | 0     |

| Threads_cached            | 88    |

| Threads_connected         | 27    |

| Threads_created           | 115   |

| Threads_running           | 5     |

+------------------------------------------+-------+

8 rows in set (0.00 sec)

mysql> show status like '%connections%';

+----------------------+--------+

| Variable_name        | Value  |

+----------------------+--------+

| Connections          | 291200 |

| Max_used_connections | 115    |

+----------------------+--------+

2 rows in set (0.00 sec)

Thread_cache_size should be atleast equal to max_connections configured, 150 in above case.

Or we need to monitor the beheaviour of thread creation on mysql for some time and then get following ratios/values:

thread cache hit rate  = 100 - ((Threads_created / Connections) * 100)

current thread per second =  Threads_Created  - Threads_Created_after_one_second

historical thread per second = Threads_Created / Uptime

Given the above value of 115 for Threads_created, and the value of 291200  that Connections showed, My Thread Cache Hit Ratio is 99% which is good.
The ideal situation is to get Threads_created as close as possible to thread_cache_size - no new connections having to wait for new thread allocation - staying as close to around a 99% hit ratio as you can

What is thread_concurrency variable in mysql : after goggling and from mysql manual i found  it does nothing on GNU/Linux so dont waste time in using this variable. There are two problem with this variable, One is it is deprecated in latest mysql version 5.6 and second it only works on old Solaris Versions .

From mysql manual

This variable is specific to Solaris systems, for which mysqld invokes the thr_setconcurrency() with the variable value. This function enables applications to give the threads system a hint about the desired number of threads that should be run at the same time. This variable was added in MySQL 3.23.7.

 What is initial memory taken by a thread when a client connect to mysql? 
mysql allocate memory to each thread efficiently whenever a new connection is made to mysql server. Initially a connection use memory specified by three specific variables thread_stack, a connection buffer and result buffer variable net_buffer_length. The connection buffer and result buffer begin with size equal to net_buffer_length and dynamically expand up to max_allowed_packed when needed and memory released after the results sent to the client. Thread kept initial memory until released by the client, after the thread disconnected its memory released to the system. in case thread moved to thread cache, memory remains allocated to thread.

A thread consume memory additionally to initial buffer whenever query is performing specific function/operation i.e. sorting, joining, caching, temporary tables creation, scanning, updating etc. For these operations a thread allocate memory defined in other buffer read_buffer_size, join_buffer_size, sort_buffer_size etc.

We can calculate how much maximum memory can be allocated to a thread when  we assume thread is doing all operations:

total per-thread buffer usage = (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size + net_buffer_length ) 

Lets Calculate minimum memory assigned to threads at any point of time:

Get the current running/connected thread connected and thread cached.


mysql> show status like '%thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+|     
| Threads_cached                           | 136   |
| Threads_connected                        | 4     |
| Threads_created                          | 140   |
| Threads_running                          | 2     |
+------------------------------------------+-------+

Get the thread value for thread_stack and net_buffer_length

mysql> show variables like '%thread_stack%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| thread_stack  | 262144 |
+---------------+--------+ 

mysql> show variables like '%net_buffer_length%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+

So minimum memory allocated to threads is  : (262144+16384)*140=38993920=37MB