Cómo analizar problemas de rendimiento de MySQL

¿Tiene problemas con la carga excesiva del servidor MySQL o se da cuenta de que algunas consultas tardan demasiado en ejecutarse? A continuación, le mostramos cómo comenzar a analizar los problemas de rendimiento de MySQL.

Antes de comenzar, debe tener en cuenta que cada servidor y conjunto de datos tiene sus propios desafíos de rendimiento. Este artículo está destinado a proporcionar una guía general sobre cómo verificar la configuración del servidor y las consultas individuales para detectar problemas ocultos.

Hacer frente a la carga del servidor

La indicación más obvia de que algo anda mal a menudo proviene de una desaceleración generalizada. Si observa períodos prolongados de uso elevado de recursos, la modificación del archivo de configuración de MySQL podría conducir a configuraciones más óptimas.

Un buen lugar para comenzar es correr MySQLTuner. Este script evalúa automáticamente su servidor MySQL contra 300 posibles indicadores de rendimiento. Producirá una lista de consejos que podrían ayudarlo a sacar más provecho de su entorno.

MySQLTuner se distribuye como un script de Perl, por lo que necesitará Perl instalado en su sistema. Utilice los siguientes comandos para descargar y ejecutar MySQLTuner:

wget http://mysqltuner.pl/ -O mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl --host 127.0.0.1 --username root --pass mysql-password

La sintaxis de conexión es similar a mysql cliente de línea de comandos. Deberías conectarte como root usuario para que el script tenga acceso completo a su servidor.

MySQLTuner es útil porque su calificación se basa en tu alojamiento del servidor tu conjunto de datos. Sin embargo, el resultado es simplemente sugerente: no todas las recomendaciones tendrán un impacto y algunas pueden reducir el rendimiento.

MySQLTuner es un script de solo lectura. No realizará ningún cambio en la configuración de su servidor. Si acepta una sugerencia, debe actualizar manualmente la variable indicada en su archivo de configuración de MySQL. La ubicación de este archivo varía según la distribución del sistema operativo. Las posiciones comunes incluyen /etc/mysql/my.cnf es /etc/mysql/mysql.conf.d/mysqld.cnf.

Después de cambiar una variable, reinicie el servidor MySQL:

sudo /etc/init.d/mysql restart

Ahora debe dejar que el servidor funcione normalmente durante un tiempo. Luego puede ejecutar MySQLTuner nuevamente para reevaluar el rendimiento del servidor. Podría sugerir otro cambio a la misma variable. Siga haciendo cambios, pero asegúrese de equilibrar todas las configuraciones. No necesariamente podrá establecer cada variable en el valor sugerido sin que aparezcan nuevas sugerencias. La salida de MySQLTuner recomienda dejar el servidor funcionando durante 24 horas para obtener datos de evaluación precisos.

Personalización del tamaño del búfer

Cambiar el tamaño del búfer y de la caché puede proporcionar una mejora notable del rendimiento. El valor predeterminado de MySQL es un tamaño de búfer relativamente pequeño, que no funcionará bien para cargas de trabajo más grandes. Los valores se escriben en el archivo de configuración de MySQL usando K, M, o G para indicar el contenedor (p. ej. 512M significa 512 megabytes).

• innodb_buffer_pool_size : Como regla general, generalmente se establece en el 70-80% de la memoria disponible. Define el tamaño del grupo utilizado para almacenar consultas en las tablas InnoDB. Intente hacerlo al menos tan grande como el tamaño total de su conjunto de datos, siempre que tenga suficiente memoria disponible.
• innodb_buffer_pool_instances : Un valor entre 1 y 64, que define el número de grupos de búfer de InnoDB que se utilizarán. Cada página almacenada en el grupo de búferes se asigna aleatoriamente a una de las instancias. Más instancias pueden mejorar la simultaneidad.
• innodb_log_file_size : Tamaño máximo de rehacer los archivos de registro en un grupo de registros. Estos archivos se utilizan durante la recuperación ante desastres para restaurar transacciones incompletas. Los valores más altos mejoran el rendimiento, pero aumentan el tiempo de recuperación en caso de accidente.
• key_buffer_size : Esto es similar a innodb_buffer_pool_size pero se usa para tablas MyISAM. Tenga en cuenta que si está utilizando exclusivamente tablas MyISAM o InnoDB, debe establecer la variable relevante en consecuencia y cambiar la otra a un tamaño relativamente bajo, como 32M. De lo contrario, estaría desperdiciando RAM al proporcionar un amplio espacio de búfer para un tipo de tabla no utilizado.
• join_buffer_size : Establece el tamaño del búfer utilizado para las combinaciones sin índices. Aumentar el tamaño de este búfer acelerará las consultas que utilizan combinaciones no indexadas. Establecer un valor demasiado alto puede causar problemas de memoria, ya que se asigna un búfer de combinación para cada combinación completa entre tablas. Las combinaciones complejas entre varias tablas necesitan varios búferes, cada uno de los cuales join_buffer_size capacidad, que puede consumir rápidamente una gran cantidad de RAM. El valor predeterminado es 256K.
• sort_buffer_size : Gusta join_buffer_size , pero aplicable a clasificación operaciones usando filesort. Los valores más altos pueden acelerar la clasificación de conjuntos de resultados grandes, pero se corre el riesgo de aumentar el uso de memoria en un servidor muy activo.

Siempre se debe considerar cualquier cambio en el tamaño del conjunto de datos y los recursos de hardware del servidor. Establecer estos valores demasiado bajos afectará el rendimiento de la consulta, mientras que establecerlos demasiado altos podría provocar un uso excesivo de la memoria o incluso quedarse sin memoria. MySQLTuner emitirá advertencias si la configuración corre el riesgo de consumir toda la memoria disponible del sistema.

Análisis de consultas lento

Habilitar el registro de consultas lentas proporciona información sobre consultas de bajo rendimiento. Puede hacer esto desde una sesión de shell de MySQL:

sudo mysql
SET GLOBAL slow_query_on = "On";
SET GLOBAL slow_query_log_file = "/slow-query.log";
SET GLOBAL long_query_time = 5;

Esta configuración registrará cualquier consulta que tarde más de cinco segundos en ejecutarse /slow-query.log. Revise periódicamente este archivo para identificar consultas de larga duración.

Una vez que haya encontrado una consulta problemática, puede usar un archivo EXPLAIN declaración para tener una idea de qué está causando la desaceleración. Prefije la consulta con EXPLAIN y ejecute el comando en un shell MySQL. Tú obtener la salida tabulada mostrando cómo MySQL intenta ejecutar la consulta.

los EXPLAIN la salida incluye información sobre los índices disponibles, las claves utilizadas y el número de registros evaluados. Interpretación EXPLAIN los datos son un tema en sí mismos. Una guía detallada sobre el significado de cada campo está disponible en Documentación de MySQL.

Puede ser más fácil usar MySQL Workbench para ejecutar un archivo EXPLAIN gráficamente. Esto puede ayudarlo a visualizar el enfoque del motor de la base de datos para recuperar el conjunto de resultados. En MySQL Workbench, presione Ctrl + T para abrir una nueva pestaña de consulta. Escriba su consulta y presione Ctrl + Alt + X para ejecutarla como un archivo EXPLAIN (No es necesario agregar el EXPLAIN prefijo manualmente.). En el panel de resultados, verá el plan de ejecución visual que destaca las operaciones involucradas.

El papel de los índices

Es importante asegurarse de que el conjunto de datos contenga índices adecuados. El uso adecuado de los índices aumenta considerablemente el rendimiento de las consultas.

SELECT * FROM users WHERE Email = '[email protected]';

Esta consulta debe tener un índice en users.Email campo. Sin un índice, MySQL necesitaría realizar un escaneo completo de la tabla, lo que provocaría un escaneo lento de cada registro.

Con index, el motor de la base de datos puede identificar registros mucho más rápido. Para ello, crea una nueva estructura de datos que contiene el valor del campo y un puntero al registro de origen. Luego, los punteros se pueden ordenar para que MySQL pueda saltar directamente a los datos relevantes.

Para agregar un índice a un campo existente, use el ADD INDEX declaración con ALTER TABLE:

ALTER TABLE my_table ADD INDEX my_index (my_field);

Entonces deberías correr OPTIMIZE TABLE my_table para indexar los datos existentes y recalcular las estadísticas de la consulta.

Cuando trabaja con varios campos, puede crear un índice de cobertura. Este es un índice que incorpora todos los campos.

SELECT * FROM my_table WHERE x = 1 AND y = 2 ORDER BY z;
 
ALTER TABLE my_table ADD INDEX covering_index (x, y, z);

Cuando se utilizan ratios de cobertura, el orden de los campos es importante. Si preguntaste WHERE z = 1 ORDER BY x, no se utilizará el índice creado anteriormente.

Debe asegurarse de que los campos utilizados en WHERE o JOIN ... ON las cláusulas están cubiertas por un índice. La consulta de campos no indexados puede convertirse rápidamente en un cuello de botella de rendimiento. Tenga cuidado con la indexación cada sin embargo, si nunca consulta ese campo, el índice no es necesario en la cabeza que todavía necesita ser mantenido por MySQL.

Puede identificar las consultas que se beneficiarían de un índice habilitando el registro de consultas no indexadas. Siga las instrucciones anteriores para habilitar el registro de consultas lentas. Entonces deberías correr SET GLOBAL log_queries_not_using_indexes = "On" desde un shell de MySQL. Esto comenzará a registrar consultas con índices faltantes en el registro de consultas lentas. Las consultas no indexadas se incluirán incluso si no superan el tiempo de consulta lento configurado.

Conclusión

No existe un enfoque único para mejorar el rendimiento de MySQL. Los pasos a seguir dependerán de los recursos del servidor, el tamaño del conjunto de datos y el nivel de conflicto de recursos causado por otras cargas de trabajo que se ejecutan en la máquina.

Tampoco debe descuidar la capa de su aplicación: es posible que MySQL no sea la raíz de sus problemas de rendimiento. Compruebe la forma en que consulta su código. Si hay un bucle excesivo, como el uso de una consulta en una rutina N + 1, la refactorización de ese código podría tener un impacto mucho mayor que la microgestión del servidor MySQL.