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

MySQL tunner and MySQL Performance Tuning and MySQL fragmentation finder
14/04/2012, 11:13
Filed under: GNU/Linux,MySQL | Tags: , , , ,

Here I collected some useful scripts for helping you tuning MySQL:

  • MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. MySQLTuner
  • MySQL Performance Tuning Primer Script. This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
    to produce sane recomendations for tuning server variables. MySQL Performance Tuning Primer Script
  • A Defragmentation Script to Optimize Fragmentated Tables in MySQL, finds your fragmented tables…and defragments them. A Defragmentation Script to Optimize Fragmentated Tables in MySQL

The first two will help you checking the MySQL most important performance values and giving you advice on how to modify them. The last one is really useful for finding every fragmented table and defragment it automatically.

Thanks to the authors!

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


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 :


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:


And I reduce it to:


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