Receta rápida para optimizar MySQL

Siguiendo con el post anterior (recordemos, tengo un servidor muy humilde en recursos que quiero aprovechar al máximo), le toca el turno al servidor MySQL.

Tengo un libro (High Performance MySQL, de la editorial O’Reilly) que -entre otras cosas- se dedica a explicar cómo obtener el mejor rendimiento de un servidor MySQL, así que en este post no voy a hacer más que abrir el melón 🙂

La cuestión es que, antes de entrar a configurar los cientos de parámetros del fichero de configuración my.cnf, quería ir a tocar los interruptores «evidentes». ¿Hay alguna forma de encontrar fallos de configuración obvios en MySQL para hacer que este sistema gestor rinda mejor? («rendir mejor» puede entenderse de varias formas… yo me refiero al sentido de que siga ofreciendo servicio pero que consuma menos RAM).

Pues sí… hay una pequeña aplicación, llamada mysqltuner.pl, que permite «tunear» el fichero my.cnf de forma muy sencilla. Realmente lo que hace es darte pistas y consejos sobre qué «interruptores» tocar para que el servidor rinda mejor.

Aprovechando un domain hack, el autor de la aplicación ha generado un dominio propio con el mismo nombre que la aplicación en Perl: http://mysqltuner.pl. Así que, para descargar y hacer ejecutable esta joyita, basta con hacer:

wget -O mysqltuner.pl http://mysqltuner.pl
chmod a+x mysqltuner.pl
sudo ./mysqltuner.pl

Si ejecutamos con permisos de sudoer, ni siquiera tenemos que configurar las credenciales de mysql, porque como la aplicación indica en el log:

[OK] Logged in using credentials from debian maintenance account.

Qué cosas… no sabía que hubiera una cuenta de mantenimiento debian con la que hacer… eso, mantenimiento de mysql 🙂 Entre otras cosas, lo primero que me dice mysqltuner es:

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 9M (Tables: 14)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 5
...
-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries

Vamos, que el módulo InnoDB lo tengo activado (consumiendo memoria) y no lo uso. Sabiendo que mi server si algo tiene que cuidar es la RAM… vamos a hacerle caso… También me dice que tengo tablas fragmentadas que convendría arreglar. Apuntado. Lo primero, antes de pifiarla (desactivando InnoDB cuando por defecto MySQL crea bases de datos InnoDB, por ejemplo), vamos a comprobar…

mysql> use information_schema ;
Database changed
mysql> select * from ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Je… Murphy nunca falla. Vamos a arreglar eso (quiero dejar MyISAM por defecto antes de desactivar el soporte InnoDB en MySQL). En el fichero /etc/mysql/my.cnf añado lo siguiente:

default-storage-engine=myisam 
skip_innodb

Y listo. Vamos ahora a optimizar tablas…

mysqlcheck -u root -p -o --all-databases

Pasamos de nuevo mysqltuner.pl:

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED -InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 9M (Tables: 14)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[OK] Total fragmented tables: 0

y ¡premio!

Dejo un ejercicio para el lector 🙂

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4h 9m 7s (43K q [2.891 qps], 1K conn, TX: 152M, RX: 5M)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 597.8M (121% of installed RAM)
-------- Recommendations -----------------------------------------------------
General recommendations:
    ...
    Reduce your overall MySQL memory footprint for system stability
    ...

Eso de que el máximo de RAM que puede consumir MySQL es del 121% de la RAM instalada no me ha gustado (y a mysqltuner.pl tampoco). ¿Qué hacer para corregirlo? Lo veremos en otro post…