Mysql Tuning para principiantes

Hoy vamos a ver un script de tuning para Mysql, ideal para administradores principiantes o que no dispongan de mucho tiempo para adentrarse en los parámetros de configuración de Mysql. Lo podemos descargar directamente de github en la siguiente web:

https://github.com/rackerhacker/MySQLTuner-perl

Al ejecutarlo nos pedirá un usuario con permisos globales y un password. Comprobará el estado del servidor indicándonos los parámetros a modificar, para obtener un rendimiento optimo. Tiene cuatro secciones principales:

1. Estadísticas Generales; comprueba si es la última versión del script, si la versión de mysql está soportada, si el sistema operativo es de 64 bits. 
2. Estadísticas del motor de almacenamiento. MySAM, InnoDB, etc. 
3. Recomendaciones de seguridad. Comprueba usuarios locales, usuarios anónimos  password en blanco, usuarios remotos, etc. 
4. Métricas de rendimiento. Aquí es donde usando las tablas de estadísticas y rendimiento del servidor nos va a mostrar el estado del motor. Desde cuando está funcionando, bytes enviados y recibidos. El tanto por ciento de aciertos de las diferentes caches/buffers, etc.

Primera Execución

Haremos una primera ejecución para ver el estado del servidor:

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 20)
[--] Data in InnoDB tables: 5M (Tables: 209)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 3

-------- Security Recommendations -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@server.mydomain.com' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@server.mydomain' has no password set.

-------- Performance Metrics -------------------------------------------------

[--] Up for: 13d 0h 39m 23s (4M q [3.579 qps], 499K conn, TX: 841M, RX: 7B)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (7% of installed RAM)
[OK] Slow queries: 0% (40/4M)
[OK] Highest usage of available connections: 21% (21/100)
[!!] Key buffer size / total MyISAM indexes: 8.0M/4.9G
[!!] Key buffer hit rate: 92.0% (1B cached / 99M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (49 temp sorts / 48K sorts)
[OK] Temporary tables created on disk: 10% (3K on disk / 30K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 10K opened)
[OK] Open file limit used: 6% (70/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 5.9M/8.0M

-------- Recommendations -----------------------------------------------------

General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
key_buffer_size (> 4.9G)
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 64)

Tuning

• Lo primero que haremos es borrar los usuarios anónimos y poner passwords a los usuarios locales.
• Añadiremos skip-bdb en el fichero /etc/my.cnf para des-habilitar el motor de bases de datos de Berkley, ya que no se usa.

# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb

• Recomienda realizar un  OPTIMIZE TABLE para desfragmentar tables para un mejor rendimiento. En este caso la tabla que esta más fragmentada es "data_bin" de la base de datos "centreon_storage". Es una tabla grande y que recoge datos de rendimiento de los servidores. Realizaremos su optimización de forma periódica.
• Activaremos el "slow query log" para poder ver posibles consultas mal realizadas o lentas. Añadiremos en my.cnf:

log-slow-queries=/var/log/mysql/slow.log

Y crearemos los directorios y ficheros con los permisos correspondientes:

mkdir -p /var/log/mysql/
touch /var/log/mysql/slow.log
chmod 660 /var/log/mysql/slow.log
chown mysql:mysql /var/log/mysql/slow.log

• Configuraremos la variable "thread_cache_size" a 4 como valor de inicio.
• Incrementaremos de forma gradual los limites de caches y buffers:

key_buffer_size = 1024M
query_cache_size = 16M
thread_cache_size = 16M
table_cache = 128M
join_buffer_size = 8M

Reiniciamos Mysqld i volvemos a ejecutar el script para ver como se reflejan los cambios.

Estado final

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 20)
[--] Data in InnoDB tables: 5M (Tables: 209)
[!!] Total fragmented tables: 1

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 35m 32s (93K q [7.259 qps], 6K conn, TX: 68M, RX: 15M)
[--] Reads / Writes: 30% / 70%
[--] Total buffers: 1.0G global + 10.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 2.1G (53% of installed RAM)
[OK] Slow queries: 0% (0/93K)
[OK] Highest usage of available connections: 21% (21/100)
[OK] Key buffer size / total MyISAM indexes: 1.0G/4.8G
[OK] Key buffer hit rate: 100.0% (7M cached / 591 reads)
[OK] Query cache efficiency: 86.1% (30K cached / 35K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 515 sorts)
[OK] Temporary tables created on disk: 1% (29 on disk / 2K total)
[OK] Thread cache hit rate: 99% (21 created / 6K connections)
[OK] Table cache hit rate: 81% (255 open / 313 opened)
[OK] Open file limit used: 0% (76/65K)
[OK] Table locks acquired immediately: 100% (18K immediate / 18K locks)
[OK] InnoDB data size / buffer pool: 5.9M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance

Se puede observar de forma global que los resultados han mejorado de forma considerable. Las tablas de rendimiento se vacían cada vez que se reinicia mysql, por eso es bueno ejecutar el script de tuning pasado una semana, de está forma las estadísticas de rendimiento y uso nos darán unos valores más reales.