Activities

April 2015
M T W T F S S
« Mar   May »
 12345
6789101112
13141516171819
20212223242526
27282930  

MySQL : Improving performance through thread_cache_size

We can improve the MySQL server performance at the best level if it is being used for read operations most of the time. Based of the Database server analysis, it is observed that more than 70% of traffic is being used “select” statements ie read operations. So I increased the “query_cache_size” and found faster throughput in page loading.

Now I’m planing to reduce the database over loading during the peak time.

First we need to identify the values associate with the following variables.

1. max_used_connections
2. Threads_created
3. connections

mysql> SHOW GLOBAL STATUS LIKE 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 164   |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| Threads_created | 2923721 |
+-----------------+---------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'connections';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Connections   | 2924077 |
+---------------+---------+
1 row in set (0.00 sec)

How do I find the best value for thread_cache_size ?

The best value of thread_cache_size will be the higher values of what max_used_connections showing. Also you may need to ensure one thing is the result of
(Threads_created / Connections) should not be more than 1. If it is so, you may need to increase the “thread_cache_size”

The above value showing

Threads_created/Connections = 2923721/2924077 = .9998 which is very close to 1. So we need to increase the thread_cache_size value. So I choose the value 200 as the max_used_connections is currently showing 164 (thread_cache_size > max_used_connections).

A day after I’d change this value,

mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 164   |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| Threads_created | 2926204 |
+-----------------+---------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Connections   | 3087166 |
+---------------+---------+
1 row in set (0.00 sec)

Now the value (Threads_created/Connections) showing some what better digit .947

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>