Este es un post difícil, y no pretende ni mucho menos, ser una guía definitiva de la optimización de MySQL, si no un mero resumen de lo que podrían ser algunas acciones encaminadas a mejorar el rendimiento de MySQL, basado sobre todo, en las directrices de la documentación oficial. Si tienes pensado aplicar alguna de estas medidas, te recomendaría probarla antes en un entorno de test. 😉
Identificar el cuello de botella
El cuello de botella suele venir por:
- Búsquedas en el disco duro: actualmente los discos suelen tener una velocidad de búsqueda de menos de 10ms, pudiendo tener un máximo teórico de 100 búquedas por segundo. Distribuyendo los datos en diferentes discos se aumentará el rendimiento.
- i/o: una vez el disco esté en posición (haya encontrado el segmento con los datos que buscamos) necesitaremos leer los datos. Actualmente un disco entrega información a razón de 10-20MB/s. Se puede montar un entorno en RAID para la lectura en paralelo de múltiples discos, o usar discos con mayor número de IOPS, como los discos SSD.
- Ciclos de CPU: Se recomienda trabajar con tablas pequeñas para mejorar el rendimiento.
Optimización del sistema
La documentación oficial de MySQL nos recomienda (entiendo que en el caso de disponer un servidor MySQL dedicado):
- Utilizar un SO Solaris o Linux con kernel mayor o igual al 2.4, para aprovechar al máximo las múltiples CPU del server.
- Deshabilitar la partición de swap si se dispone de suficiente RAM (aquí explica cómo).
- Deshabilitar «external-locking» para las tablas MyISAM. Ésto viene deshabilitado por defecto en las versiones actuales de MySQL, pero puede verificarse con el siguiente comando:
mysql> show variables like ‘%locking%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| skip_external_locking | ON |
+———————–+——-+
1 row in set (0.00 sec)
Parámetros de optimización principales
Dos sentencias básicas para ver los parámetros de configuración actuales del servidor MySQL son:
- mysql> Show variables;
- mysql> Show status;
Según la documentación oficial, las dos variables más importantes para optimizar MySQL son:
- key_buffer_size: tamaño del buffer usado para los bloques de índices. Máximo 4GB en plataformas de 32bits. Para entornos MyISAM, un valor aceptable sería un 25% de la RAM. Sin embargo, un valor demasiado elevado (por encima del 50%) relentizará el servidor. Se puede verificar el rendimiento del key buffer con «show status», calculando:
- «show global variables like «%buffer%»;» mostrará entre otros, el valor definido para «key_buffer_size».
- El resultado de dividir Key_reads/Key_read_requests, que debería ser menor que 0.01
- El resultado de dividir Key_writes/Key_write_requests, que debería ser cercano a 1, o mucho menor en función del tipo de queries ejecutadas.
- table_open_cache: («table_cache» para versiones antiguas de MySQL): número máximo de tablas abiertas para todos los threads. Dependerá del valor dado a max_connections (151 por defecto). El valor de «table_cache» debería ser el valor de «max_connextions» x N, donde N es el número máximo de tablas por join que se vaya a usar en cualquier consulta, añadiéndo además algo de espacio para tablas temporales y file descriptors. Se ha de tener en cuenta, que cada conexión concurrente abrirá una tabla MyISAM. Además, si se incrementa el valor de «table_cache», se aumentará el número de file descriptors que mysqld necesita.
- «show global variables like «%table%»;» mostrará entre otros, el valor definido para «table_open_cache».
- «SHOW GLOBAL STATUS LIKE ‘Opened_tables’;» mostrará el número total de tablas abiertas desde la última vez que se inició el servicio de MySQL.
- «SHOW GLOBAL STATUS LIKE ‘Open_tables’;» mostrará el número de tablas actualmente abiertas.
Otros parámetros de optimización
Otros parámetros que pueden influrir en el rendimiento de mysql son:
- max_connections: define el número de conexiones a MySQL simultáneas. Por defecto 151 (150 más la consola local). Si se alcanza este límite aparecerá el error de «Unable to connect to database: Too many connections» y claramente será necesario aumentar éste valor.
- wait_timeout: define el número de segundos de timeout para una conexión no interactiva. De esta forma, se evitará tener procesos en estado «sleep» en mysql que no se cierren y que por tanto pongan en peligro el valor «max_connections»
- tmp_table_size / max_heap_table_size: MySQL usará el menor valor de entre «tmp_table_size» y «max_heap_table_size» para determinar el límite que puede ocupar una tabla temporal en memoria. Si una tabla temporal en memoria excede este límite, MySQL cogerá la tabla y la meterá en disco como una tabla MyISAM.
Optimización de la i/o
Para optimizar la i/o del disco, se recomienda usar un disco dedicado para almacenar las bases de datos de mysql. Ésto se puede especificar en el fichero my.cnf:
datadir=/data/mysql
socket=/data/mysql/mysql.sock
tmpdir = /data/mysql/tmp
La partición de datos, se recomienda montarla con los siguientes parámetros (que se pueden especificar en el /etc/fstab), para mejorar el rendimiento:
- -o noatime
- -o async
Además, se recomienda instalar la utilidad «hdparm» para optimizar el disco para MySQL, con la siguiente instrucción:
hdparm -m 16 -d 1
Actualización: En mi caso concreto, me funciona mejor montar la partición con las opciones «noatime,barrier», tal y como vimos al intentar mejorar la velocidad de replicación de un slave.
/dev/md4 /data ext4 defaults,noatime,barrier=0 0 0
Otra mejora para la i/o es dejar todos los logs en un disco diferente del que se usa para el dbpath. Éstos consejos, realmente cuentan de cara a mejorar la i/o y por tanto el rendimiento de la base de datos.
Motor SQL
El motor que usemos para nuestras tablas, también influirá en el rendimiento, dependiendo de si se genera tablas transaccional-safe (TST) como InnoDB o no-transaccionales como MyISAM (por defecto). En resumen, las no-transaccionales son:
- Mucho más rápidas
- Requieren menos espacio en disco
- Requieres menos menoria para los updates
Las transaccional-safe en cambio, ofrecen un sistema de protección frente a fallos de hardware, updates que fallan, e incluso rollbacks, además de mejor rendimiento para tablas con múltiples updates y lecturas simultáneas. Así pues, por lo general, el rendimiento aumentará con tablas MyISAM, pero ten en cuenta que la configuración del MySQL cambiará mucho si usas MyISAM o InnoDB.
MySQL Query Cache / Memcached
Cuando el rendimiento es más importante que la exactitud, MySQL ofrece un sistema de caché que almacena queries y resultados, ofreciendo así resultados cacheados en lugar de volver a ejecutar la query. Es posible, entonces, devolver algún resultado cacheado que habría cambiado si se hubiera ejecutado la query de nuevo.
Esta caché es ideal en entornos en los cuales las tablas no suelen modificarse muy a menudo, y entornos que suelen recibir siempre las mismas queries. Sin embargo, seguramente se prefieran usar soluciones más potentes como memcached, que realizan también un cacheado de la información y no tienen las limitaciones de MySQL Query Cache.
Más info aquí y también aquí (este último enlace es especialmente interesante).
Benchmark suite
En instalaciones de MySQL desde source, se contará con varios scripts para testear el rendimiento (qué operaciones se realizan bien y qué otras se realizan lentas), dentro del directorio sql-bench, ubicado en el mismo directorio de instalación. Si no se dispone de dichos tests, se podrán instalar desde yum, con el paquete «mysql-bench» (disponible desde el repo de remi).
Si se ha instalado el paquete mediante yum, se podrá ejecutar el primer test con «perl run-all-tests –server=MySQL» desde «/usr/share/sql-bench».
Registro de consultas lentas
Se puede especificar en el fichero de configuración my.cnf los parámetros necesarios para guardar un registro de las consultas lentas, aquellas que tarden más de «long_query_time» segundos en ejecutarse. Sin embargo, no conviene tener siempre este log activado, pues en entornos lentos o con muchas queries pesadas, este log comerá demasiados recursos:
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes
Optimizar queries con EXPLAIN
La cláusula EXPLAIN (o EXPLAIN EXTENDED) puesta delante de una query, mostrará información sobre el plan de ejecución de la query, incluyendo las tablas implicadas, cómo se juntarán las tablas, y en qué orden. De esta manera, se proporciona una herramienta de ayuda para la optimización de queries.
Utilidades
Existen diferentes herramientas que ayudan a identificar posibles mejoras a realizar en una instalación MySQL. Una de ellas es un script en perl llamado «MySQLTurner«. Éste script no realiza ningún tipo de cambio en ningún fichero de configuración, únicamente realiza una serie de comprobaciones y muestra un pequeño informe con recomendaciones de seguridad y rendimiento, indicando además valores a ajustar a las variables de configuración del MySQL según el resultado de los tests obtenidos. No ofrecerá «la solución» pero sí que se obtienen recomendaciones bastante buenas.
Otra opción es usar el asistente de configuración de MySQL de Percona. Muy, muy recomendable.
Otras opciones
Dependiendo de nuestro entorno, mejoraremos mucho el rendimiento usando un disco con RAID para el dbpath, o incluso montando un entorno de Replicación Master-Slave, o un Cluster MySQL.
Fuentes:
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
http://forge.mysql.com/wiki/Top10SQLPerformanceTips
http://foro.elhacker.net/bases_de_datos/optimizacion_mysql_para_programadores_y_dba-t274965.0.html
SHOW VARIABLES;
Foto por magerleagues
5 respuestas a «Optimización de mysql»