As your website gets more traffic, you’ll find that you run into problems where MySQL slows down. One of the values to work with is the thread_cache_size.
The value for thread_cache_size in MySQL should always be AT LEAST as big as your setting for max_used_connections. You find out what this value is for your server by running the command
SHOW GLOBAL STATUS LIKE ‘Max_used_connections’;
If your MySQL database is reporting that your max_used_connections is 51, and your current value for thread_cache_size is 10, then you have a problem.
By default, the max_connections setting is set to 10.
Here’s how to increase that value.
First, load up MySQL Workbench. That’s the easiest way to adjust values for your database. Log into it.
Once in, go to your Administration main tab. On the left, under the Instance section, use Options File.
You’re now editing the parameters of your option file. Choose the Advanced tab on the right.
Scroll down to look for the thread_cache_size area. It’s probably currently set to 10.
Update that to a new value. Set it to whatever you get back from that max_used_connection value plus 8. Stay below 100.
When you put in a new value, click Apply. You’ll get a confirmation window showing you the old value and new value, to make sure you didn’t mistype something. Confirm your changes.
In order for your MySQL server to use these new values, you have to stop and start the server. On the left-hand menu under Instance is an option to Startup / Shutdown. Choose that. Stop the server. Then start it again.
You should now be running with your new setting.
Leave a Reply