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


MySQL mysqldump tunning
31/12/2012, 12:32
Filed under: GNU/Linux,MySQL,Performance

Hello since April!
I am a little bit busy in the last months with some personal projects, bla bla bla…
Anyway, I had to improve the mysqldump command because the admin was getting some mails saying:
MySQL Error : MySQL server has gone away

The old command was:
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
The new command is:
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS --single-transaction --quick --lock-tables=false $db | $GZIP -9 > $FILE

The source of the tip is http://stackoverflow.com of course…



Google’s nap lets our server breath for a while
18/04/2012, 10:12
Filed under: Apache,General,GNU/Linux,MySQL,Performance | Tags: , , , ,

After 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 yourself:

Graph where you can see the difference between google indexing our site and google having a nap

Grap where you can see the difference on the CPU usage between google indexing yourself and google having a nap

Does anybody need me to draw a circle around the difference? 🙂



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: , , , , ,

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