设为首页收藏本站

厦门微思网络

 找回密码
 立即注册

QQ登录

只需一步,快速开始

微思网络5月周年庆超值课程8.7折
查看: 2177|回复: 0

Optimize MySQL: The Thread Cache

[复制链接]

该用户从未签到

发表于 2011-10-20 11:06:33 | 显示全部楼层 |阅读模式
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
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

快速回复 返回顶部 返回列表