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
+----------------------+-------+
| 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,
+----------------------+-------+
| 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