Optimize MySQL: The Thread Cache | Epigroove.com

Optimize MySQL: The Thread Cache | Epigroove.com: "threads_created"

With the recent release of the new Babble site and the resulting increase in mysql activity (MySQL was averaging around 300 queries per second), mysql and server loads rose to a level that was starting to get uncomfortable. So I decided to flip through the MySQL status variables to see if anything looked abnormal. That's when I discovered that my "thread cache hit rate" was abysmal.

The hit rate should be as close to 100% as possible. You can calculate your hit ratio by dividing the 'threads_created' status variable by the 'connections' status variable:

100 - ((Threads_created / Connections) * 100)

My hit rate was less than 1%. Almost every mysql connection was causing a new thread to be created, and a lot of threads were being created, thus creating a lot of unnecessary overhead.

The cause of the problem was that 'thread_cache_size' was set to 0. Thread_cache_size determines how many threads MySQL will hold open in memory to handle new connections. So in my case, MySQL wasn't holding any in cache so it had to create new threads all the time. Not cool.

To determine what you should set 'thread_cache_size' to, pay close attention to the 'threads_created' status variable. If it keeps going up it means your 'thread_cache_size' is set too low. Just keep bumping up 'thread_cache_size' until 'threads_created' no longer increments. My optimal thread_cache_size turned out to be 50.

As soon as I optimized the thread cache, MySQL's server load dropped over 50%!

This MySQL optimization technique worked wonders for me, so I'm sharing it in hopes that it will help you.

BTW- If you don't have it yet, go download the MySQL Workbench. It makes viewing and changing MySQL variables much easier.