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
