Pedro's blog: db2, mysql, php, linux and performance


MySQL tuning query cache and thread cache
06/04/2012, 15:43
Filed under: General,GNU/Linux,MySQL | Tags: , , , , ,

Hello,

since we did the migration of a website from the php-nuke+phpbb to drupal+vbulletin, the cpu load of the server was getting around 20-30% more busy. I had to take a look to the mysql performance because it was using around the 20% of the CPU. First of all I activated the query cache on the /etc/my.cnf file:

query-cache-type = 1

And set the cache size to 20MB:

query-cache-size = 20M

After rebooting the MySQL service, the busy percentage decreased a bit. You can learn more about the query-cache on the MySQL Query Cache official website.

mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| have_query_cache | YES   |
+——————+——-+
1 row in set (0.00 sec)

The next step I took was to activate the thread cache:

mysql> SET GLOBAL thread_cache_size=40;
mysql> SELECT @@global.thread_cache_size;
+—————————-+
| @@global.thread_cache_size |
+—————————-+
| 40 |
+—————————-+
1 row in set (0.00 sec)

And include it on the /etc/my.cnf :

thread_cache_size=40

Since then, the MySQL server was caching every thread and every query and the cpu usage performance got reduced around 15%. While I was setting the new values I saw this one:

wait_timeout=600

And I reduce it to:

wait_timeout=10

Because there is no need for holding the connections 10 minutes, 10 seconds are more than enough. By now everything is working fine and you can see the load saving on the graph:

mysql cache optimization

mysql cache optimization




2 Comments so far
Leave a comment

[…] the CMS migration I commented about on the article MySQL Tuning Query Cache And Thread Cache, google had a couple of naps, the last one looked CRAZY on the stats! take a look […]

   Pedro's blog 04.18.12 @ 10:29

Great info buddy.
Thank you.

   Andre 04.15.16 @ 0:26



Leave a comment
Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>



¡IMPORTANTE! Responde a la pregunta: ¿Cuál es el valor de 8 7 ?