Recently I encountered a MySQL database that easily was running with 300 to 600 rows in the processlist. The Max connections was set easily more than twice this amount as well. This was a set up that I just do not agree with. I was called in because it also proved itself not to be working out very well. So here are a few of my thoughts on the process I uncovered.
So if your execute the process above what is your percentage ? You want this to be as close to 100 as possible. So for example the server I recently encountered had a % under 10%. So how do you fix this and raise your %?
The variable thread_cache_size has a default of 0. If you start to notice your process grow and but queries are not blocked by deadlocks and etc, then you should check your "Threads_created % of Connections" as mentioned above. It is likely that your % will be low. You can raise the % and drastically improve performance of your database by finding the sweet spot that fit your server environment. The thread_cache_size can be changed in a live environment. So this allows you to set the variable then monitor the status value of the "Threads_created" (see above to get value). If this continues to increment in value then continue to raise the thread_cache_size. Typically I prefer to raise the value by 25 at a time for a few then move to 500 at a time. I often check the "Threads_created % of Connections" and the 'Threads_created'. Once you get close to the sweet spot you will notice the % to gain and the processlist to begin to drop in rows. Typically one more adjustment of the thread_cache_size will get you into the sweet spot.
Every server and environment is different.
Some servers might be 98% with a thread_cache_size of 50 while others have a 98% with the thread_cache_size set to 15000. The max is 16384.
So if nothing else... find out what your percentage is first then look into making adjustments.
- In my opinion the majority of MySQL databases in use will not need a max connections or 1500 or more. The more connections you allow the more overhead you bring to your server. Use your connections efficiently.
- Second, Understand the % of threads_created versus the connections used. You might consider this the threads created hit rate. BTW.. This is not new information, this is information that has been understood in the community for some time. I do not pretend to present this in any other way than trying to help others. So do the following to understand your current %
- show status like 'Threads_created';
- set @Threads_created=< result from query above>;
- show status like 'Connections';
- set @Connections=< result from query above>;
- select 100 - (( @Threads_created / @Connections ) * 100) as "Threads_created % of Connections"\G
So if your execute the process above what is your percentage ? You want this to be as close to 100 as possible. So for example the server I recently encountered had a % under 10%. So how do you fix this and raise your %?
The variable thread_cache_size has a default of 0. If you start to notice your process grow and but queries are not blocked by deadlocks and etc, then you should check your "Threads_created % of Connections" as mentioned above. It is likely that your % will be low. You can raise the % and drastically improve performance of your database by finding the sweet spot that fit your server environment. The thread_cache_size can be changed in a live environment. So this allows you to set the variable then monitor the status value of the "Threads_created" (see above to get value). If this continues to increment in value then continue to raise the thread_cache_size. Typically I prefer to raise the value by 25 at a time for a few then move to 500 at a time. I often check the "Threads_created % of Connections" and the 'Threads_created'. Once you get close to the sweet spot you will notice the % to gain and the processlist to begin to drop in rows. Typically one more adjustment of the thread_cache_size will get you into the sweet spot.
Every server and environment is different.
Some servers might be 98% with a thread_cache_size of 50 while others have a 98% with the thread_cache_size set to 15000. The max is 16384.
So if nothing else... find out what your percentage is first then look into making adjustments.
