Mejora de rendimiento de MySQL ajustando algunos parámetros

MySQL, al igual que la mayoría de gestores de bases de datos, permite modificar fácilmente sus parámetros que controlan tamaños de memoria dedicados a determinadas tareas, utilización de recursos, límites de concurrencia, etc.

Ajustando adecuadamente estos parámetros se pueden obtener muchas mejoras de rendimiento de la base de datos, sobretodo si el servidor/es de BD no va sobrado de recursos, y si por la parte de optimización SQL no se puede mejorar mucho más.

Yo hace poco he realizado algunos ajustes básicos para mejorar el rendimiento de MySQL, así que voy aprovechar para explicar un poco el proceso que he seguido para quien busque una manera sencilla de hacer una primera optimización de parámetros en la base de datos. Con esto no quiero decir que esta sea la mejor manera de hacerlo, sólo la que a mi me ha resultado bien ;)

Lo primero comentar que puede ser muy útil echar un vistazo dentro de phpMyAdmin a las secciones 'Mostrar información de tiempo de ejecución de MySQL' y 'Mostrar las variables del sistema MySQL', normalmente accesibles desde la página principal de la aplicación.

La primera muestra información y estadísticas que el sistema recopila y mantiene desde su arranque. Hay que prestar especial atención a los valores de variables que se muestran en color rojo, y a los consejos que se indican a la derecha de estos valores.

Consultar las variables sirve para saber los valores actuales de los parámetros que más adelante podríamos modificar para mejorar el rendimiento de nuestro MySQL.

Una vez hecho esto seguramente ya tendremos alguna pista de por donde hay que atacar, pero lo que interesa es algo más concreto que nos sugiera qué parámetros podríamos tocar exactamente para mejorar el rendimiento de MySQL. Para ello yo he utilizado mysqltunner.pl, que es un script en perl que analiza el rendimiento de la base de datos y los valores de los parámetros y tras el análisis realiza sugerencias de modificación de algunos de estos valores.

Si se cumplen los requerimientos, utilizar el script en un sistema Linux/Unix es tan sencillo como ejecutar desde linea de comandos:

> wget mysqltuner.pl
> chmod 755 mysqltuner.pl
> perl mysqltuner.pl 

Una vez hecho esto, el script te puede devolver unos resultados similares a estos:

MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
Bug reports, feature requests, and downloads at https://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.45
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 156M (Tables: 390)
[--] Data in InnoDB tables: 2M (Tables: 126)
[!!] Total fragmented tables: 46

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18m 8s (296K q [272.847 qps], 689 conn, TX: 1B, RX: 39M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 35.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 303.7M (33% of installed RAM)
[OK] Slow queries: 0% (0/296K)
[OK] Highest usage of available connections: 8% (8/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/52.0M
[OK] Key buffer hit rate: 99.2% (2M cached / 21K reads)
[OK] Query cache efficiency: 81.6% (237K cached / 291K selects)
[!!] Query cache prunes per day: 3650717
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
[!!] Joins performed without indexes: 33
[!!] Temporary tables created on disk: 26% (1K on disk / 7K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 48% (128 open / 265 opened)
[OK] Open file limit used: 11% (229/2K)
[OK] Table locks acquired immediately: 99% (73K immediate / 73K locks)
[!!] InnoDB data size / buffer pool: 2.0M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (> 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 60M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 2M)

En temas de optimización nunca hay que tocar las cosas alegremente, por lo que ahora lo que toca es estudiar la información y las recomendaciones que propone el script e ir aplicando las que nos parezcan más acertadas para mejorar el rendimiento.

• La primera recomendación es que se ejecute el comando OPTIMIZE TABLE sobre varias tablas en las que ha detectado que existe fragmentación. Para ello hay que averiguar qué tablas pueden requerir una defragmentación y aplicar este comando sobre ellas.
   
• Las siguientes recomendaciones ya son sugerencias sobre los parámetros de la BD.
  La aplicación muestra unos valores de inicio en los casos en que la propuesta es aumentar el valor. Lo mejor es ir subiendo poco a poco e ir validando si obtenemos mejoras de velocidad, y también que no llegamos a utilizar más memoria de la que tenemos disponible.

Para modificar los valores de los parámetros se puede editar el fichero my.cnf, que suele encontrarse en el directorio /etc

En este caso, por ejemplo, podríamos haber insertado en nuestro my.cnf las lineas

# Parametros modificados por recomendaciones de sqltuner
query-cache-size = 16M
join-buffer-size = 256K
tmp-table-size = 90M
max-heap-table-size = 90M
thread-cache-size = 4
innodb-buffer-pool-size = 4M
# Fin de modificaciones de optimizacion

Después hay que reiniciar la base de datos para que estos cambios surtan efecto.

Comentar por último que para que la información que nos proporciona el script sea fiable, la base de datos debería llevar funcionando ininterrumpidamente por lo menos 24 horas, por lo que para ir ajustando los valores con ejecuciones de sqltuner no sirve modificar los parámetros y volver a ejecutar el script al momento.

Después de reiniciar la BD para aplicar los cambios hay que esperar un dia o dos antes de volver a pedir ayuda al script para tunear nuestra base de datos.