Este post es la parte 1 de una serie de 3 partes sobre el monitoreo de MySQL. La Parte 2 trata sobre la recopilación de métricas de MySQL, y la Parte 3 explica cómo monitorear MySQL usando Datadog.
¿Qué es MySQL?
MySQL es el servidor de base de datos relacional de código abierto más popular del mundo. Propiedad de Oracle, MySQL está disponible en la Edición de la Comunidad de descarga gratuita, así como en ediciones comerciales con características y soporte adicionales. Lanzado inicialmente en 1995, MySQL ha generado bifurcaciones de alto perfil para tecnologías competidoras como MariaDB y Percona.
Key MySQL statistics
Si su base de datos se ejecuta lentamente o no sirve consultas por cualquier motivo, cada parte de su pila que depende de esa base de datos también sufrirá problemas de rendimiento. Para mantener su base de datos funcionando sin problemas, puede supervisar activamente las métricas que cubren cuatro áreas de rendimiento y utilización de recursos:
- Rendimiento de consultas
- Rendimiento de ejecución de consultas
- Conexiones
- Uso del grupo de búfer
Los usuarios de MySQL pueden acceder a cientos de métricas de la base de datos, por lo que en este artículo nos centraremos en un puñado de métricas clave que le permitirán obtener información en tiempo real sobre el estado y el rendimiento de su base de datos. En la segunda parte de esta serie, le mostraremos cómo acceder y recopilar todas estas métricas.
Este artículo hace referencia a la terminología métrica introducida en nuestra serie de monitorización 101, que proporciona un marco para la recopilación y las alertas de métricas.
Compatibilidad entre versiones y tecnologías
Algunas de las estrategias de monitoreo discutidas en esta serie son específicas para las versiones 5.6 y 5.7 de MySQL. Las diferencias entre esas versiones se señalarán a lo largo del camino.
La mayoría de las métricas y estrategias de monitoreo descritas aquí también se aplican a tecnologías compatibles con MySQL, como MariaDB y Percona Server, con algunas diferencias notables. Por ejemplo, algunas de las características de MySQL Workbench, que se detallan en la Parte 2 de esta serie, no son compatibles con las versiones actualmente disponibles de MariaDB.
Los usuarios de Amazon RDS deben consultar nuestras guías de supervisión especializadas para MySQL en RDS y para Amazon Aurora compatible con MySQL.
Rendimiento de consultas
| Nombre | Descripción | Tipo métrico | Disponibilidad |
|---|---|---|---|
| Preguntas | Recuento de sentencias ejecutadas (enviadas por el cliente) | Trabajo: Rendimiento | Variable de estado del servidor |
| Com_select | Instrucciones SELECT | Trabajo: Rendimiento | Variable de estado del servidor |
| Escribe | Inserta, actualiza o elimina | Trabajo: Rendimiento | Calculado a partir de variables de estado del servidor |
Su principal preocupación en el monitoreo de cualquier sistema es asegurarse de que su trabajo se realice de manera efectiva. El trabajo de una base de datos está ejecutando consultas, por lo que su primera prioridad de monitoreo debe ser asegurarse de que MySQL esté ejecutando consultas como se espera.
MySQL tiene un contador interno (una «variable de estado del servidor», en lenguaje MySQL) llamado Questions, que se incrementa para todas las sentencias enviadas por aplicaciones cliente. La vista centrada en el cliente proporcionada por la métrica Questions a menudo hace que sea más fácil de interpretar que el contador Queries relacionado, que también cuenta sentencias ejecutadas como parte de programas almacenados, así como comandos como PREPARE y DEALLOCATE PREPARE ejecutados como parte de sentencias preparadas del lado del servidor.
Para consultar una variable de estado del servidor como Questions o Com_select:
SHOW GLOBAL STATUS LIKE "Questions";+---------------+--------+| Variable_name | Value |+---------------+--------+| Questions | 254408 |+---------------+--------+También puede supervisar el desglose de los comandos de lectura y escritura para comprender mejor la carga de trabajo de su base de datos e identificar posibles cuellos de botella. Las consultas de lectura generalmente se capturan con la métrica Com_select. Escribe incrementa una de tres variables de estado, dependiendo del comando:
Escribe = Com_insert + Com_update + Com_delete
Métrica sobre la que alertar: Preguntas
La tasa actual de consultas aumentará y disminuirá de forma natural y, como tal, no siempre es una métrica procesable basada en umbrales fijos. Pero vale la pena alertar sobre cambios repentinos en el volumen de consultas: las caídas drásticas en el rendimiento, especialmente, pueden indicar un problema grave.
Rendimiento de la consulta
| Nombre | Descripción | Tipo métrico | Disponibilidad |
|---|---|---|---|
| Tiempo de ejecución de la consulta | Tiempo de ejecución medio, por esquema | Trabajo: Rendimiento | Consulta de esquema de rendimiento |
| Errores de consulta | Número de sentencias SQL que generaron errores | Trabajo: Error | Consulta de esquema de rendimiento |
| Slow_queries | Número de consultas que exceden el límite configurable long_query_time |
Trabajo: Rendimiento | Variable de estado del servidor |
Los usuarios de MySQL tienen una serie de opciones para monitorear la latencia de las consultas, tanto mediante el uso de las métricas integradas de MySQL como consultando el esquema de rendimiento. Habilitadas de forma predeterminada desde MySQL 5.6.6, las tablas de la base de datos performance_schema dentro de MySQL almacenan estadísticas de bajo nivel sobre eventos del servidor y ejecución de consultas.
Resumen de instrucciones de esquema de rendimiento
Muchas métricas clave se encuentran en la tabla events_statements_summary_by_digest del esquema de rendimiento, que captura información sobre latencia, errores y volumen de consultas para cada instrucción normalizada. Una fila de muestra de la tabla muestra una instrucción que se ha ejecutado dos veces y que se ha ejecutado en promedio en 325 milisegundos (todas las mediciones del temporizador se realizan en picosegundos):
*************************** 1. row *************************** SCHEMA_NAME: employees DIGEST: 0c6318da9de53353a3a1bacea70b4fce DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? COUNT_STAR: 2 SUM_TIMER_WAIT: 650358383000 MIN_TIMER_WAIT: 292045159000 AVG_TIMER_WAIT: 325179191000 MAX_TIMER_WAIT: 358313224000 SUM_LOCK_TIME: 520000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 520048 SUM_ROWS_EXAMINED: 520048... SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-03-24 14:25:32 LAST_SEEN: 2016-03-24 14:25:55La tabla digest normaliza todas las instrucciones (como se ve en el campo DIGEST_TEXT anterior), ignorando los valores de datos y estandarizando los espacios en blanco y las mayúsculas, de modo que las dos consultas siguientes se considerarían las mismas:
select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;Para extraer un tiempo de ejecución promedio por esquema en microsegundos, puede consultar el esquema de rendimiento:
SELECT schema_name , SUM(count_star) count , ROUND( (SUM(sum_timer_wait) / SUM(count_star)) / 1000000) AS avg_microsec FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-------+--------------+| schema_name | count | avg_microsec |+--------------------+-------+--------------+| employees | 223 | 171940 || performance_schema | 37 | 20761 || sys | 4 | 748 |+--------------------+-------+--------------+Del mismo modo, para contar el número total de instrucciones por esquema que generaron errores:
SELECT schema_name , SUM(sum_errors) err_count FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-----------+| schema_name | err_count |+--------------------+-----------+| employees | 8 || performance_schema | 1 || sys | 3 |+--------------------+-----------+El esquema sys
Consultar el esquema de rendimiento como se muestra arriba funciona muy bien para recuperar métricas de la base de datos mediante programación. Sin embargo, para consultas e investigaciones ad hoc, generalmente es más fácil usar el esquema sys de MySQL. El esquema sys proporciona un conjunto organizado de métricas en un formato más legible para el ser humano, lo que simplifica mucho las consultas correspondientes. Por ejemplo, para encontrar las sentencias más lentas (aquellas en el percentil 95 por tiempo de ejecución):
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;O para ver qué sentencias normalizadas han generado errores:
SELECT * FROM sys.statements_with_errors_or_warnings;Muchos otros ejemplos útiles se detallan en la documentación del esquema sys. El esquema sys está incluido en MySQL a partir de la versión 5.7.7, pero los usuarios de MySQL 5.6 pueden instalarlo con solo unos pocos comandos. Consulte la Parte 2 de esta serie para obtener instrucciones.
Consultas lentas
Además de la gran cantidad de datos de rendimiento disponibles en el esquema de rendimiento y el esquema sys, MySQL cuenta con un contador Slow_queries, que aumenta cada vez que el tiempo de ejecución de una consulta supera el número de segundos especificado por el parámetro long_query_time. El umbral se establece en 10 segundos de forma predeterminada:
SHOW VARIABLES LIKE 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+ El parámetro long_query_time se puede ajustar con un solo comando. Por ejemplo, para establecer el umbral de consulta lenta en cinco segundos:
SET GLOBAL long_query_time = 5;(Tenga en cuenta que es posible que tenga que cerrar la sesión y volver a conectarse a la base de datos para que el cambio se aplique a nivel de sesión.)
Investigar problemas de rendimiento de consultas
Si sus consultas se ejecutan más lentamente de lo esperado, a menudo es el caso de que una consulta recientemente cambiada sea la culpable. Si no se determina que ninguna consulta sea excesivamente lenta, lo siguiente que se debe evaluar son las métricas a nivel de sistema para buscar restricciones en los recursos principales (CPU, E/S de disco, memoria y red). La saturación de CPU y los cuellos de botella de E/S son los culpables comunes. También es posible que desee comprobar la métrica Innodb_row_lock_waits, que cuenta la frecuencia con la que el motor de almacenamiento InnoDB tuvo que esperar para adquirir un bloqueo en una fila en particular. InnoDB ha sido el motor de almacenamiento predeterminado desde la versión 5.5 de MySQL, y MySQL utiliza bloqueo a nivel de fila para tablas InnoDB.
Para aumentar la velocidad de las operaciones de lectura y escritura, muchos usuarios querrán ajustar el tamaño del grupo de búfer utilizado por InnoDB para almacenar en caché los datos de tablas e índices. Más información sobre la supervisión y el cambio de tamaño del grupo de búfer a continuación.
Métricas para alertar sobre
- Tiempo de ejecución de la consulta: Administrar la latencia de las bases de datos clave es fundamental. Si el tiempo de ejecución promedio de las consultas en una base de datos de producción comienza a aumentar, busque restricciones de recursos en las instancias de la base de datos, posibles bloqueos de filas o tablas y cambios en los patrones de consulta en el lado del cliente.
- Errores de consulta: Un aumento repentino de errores de consulta puede indicar un problema con su aplicación cliente o con su propia base de datos. Puede usar el esquema sys para explorar rápidamente qué consultas pueden estar causando problemas. Por ejemplo, para enumerar las 10 sentencias normalizadas que han devuelto la mayoría de los errores:
SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10; -
Slow_queries: La forma de definir una consulta lenta (y, por lo tanto, de configurar el parámetrolong_query_time) depende de su caso de uso. Cualquiera que sea su definición de» lento», es probable que desee investigar si el número de consultas lentas supera los niveles de referencia. Para identificar las consultas reales que se ejecutan lentamente, puede consultar el esquema sys o sumergirse en el registro opcional de consultas lentas de MySQL, que está deshabilitado de forma predeterminada. Más información sobre cómo habilitar y acceder al registro de consultas lentas está disponible en la documentación de MySQL.Conexiones
| Nombre | Descripción | Tipo métrico | Disponibilidad |
|---|---|---|---|
| Threads_connected | Conexiones actualmente abiertas | Recurso: Utilización | Variable de estado del servidor |
| Threads_running | Conexiones actualmente en ejecución | Recurso: Utilización | Variable de estado del servidor |
| Connection_errors_ internal | Recuento de conexiones rechazadas debido a un error del servidor | Recurso: Error | Variable de estado del servidor |
| Aborted_connects | Recuento de intentos fallidos de conexión al servidor | Recurso: Error | Variable de estado del servidor |
| Connection_errors_ max_connections | Recuento de conexiones rechazadas debido a max_connections límite |
Recurso: Error | Variable de estado del servidor |
Comprobar y establecer el límite de conexión
Supervisar las conexiones de cliente es fundamental, porque una vez que haya agotado las conexiones disponibles, se rechazarán las nuevas conexiones de cliente. El límite de conexión de MySQL es por defecto 151, pero se puede verificar con una consulta:
SHOW VARIABLES LIKE 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+La documentación de MySQL sugiere que los servidores robustos deben ser capaces de manejar conexiones de cientos o miles de personas:
«Linux o Solaris deben ser capaces de soportar de 500 a 1000 conexiones simultáneas de forma rutinaria y hasta 10.000 conexiones si tiene muchos gigabytes de RAM disponibles y la carga de trabajo de cada uno es baja o el objetivo de tiempo de respuesta es poco exigente. Windows está limitado a (tablas abiertas × 2 + conexiones abiertas) < 2048 debido a la capa de compatibilidad Posix utilizada en esa plataforma.»
El límite de conexión se puede ajustar sobre la marcha:Sin embargo,
SET GLOBAL max_connections = 200;Esa configuración volverá al valor predeterminado cuando se reinicie el servidor. Para establecer permanentemente el límite de conexión, agregue una línea como esta a su archivo de configuración my.cnf (consulte esta publicación para obtener ayuda para localizar el archivo de configuración):
max_connections = 200Supervisión de la utilización de la conexión
MySQL expone hilos de conexión de conteo de métricas Threads_connected, un hilo por conexión. Al monitorear esta métrica junto con el límite de conexión configurado, puede asegurarse de tener suficiente capacidad para manejar nuevas conexiones. MySQL también expone la métrica Threads_running para aislar cuáles de esos subprocesos están procesando consultas de forma activa en un momento dado, a diferencia de las conexiones que están abiertas pero que actualmente están inactivas.
Si su servidor alcanza el límite max_connections, comenzará a rechazar conexiones. En ese caso, se incrementará la métrica Connection_errors_max_connections, al igual que la métrica Aborted_connects que realiza un seguimiento de todos los intentos de conexión fallidos.
MySQL expone una variedad de otras métricas sobre errores de conexión, que pueden ayudarlo a investigar problemas de conexión. La métrica Connection_errors_internal es una buena para ver, porque se incrementa solo cuando el error proviene del propio servidor. Los errores internos pueden reflejar una condición de falta de memoria o la incapacidad del servidor para iniciar un nuevo subproceso.
Métricas sobre las que alertar
-
Threads_connected: Si un cliente intenta conectarse a MySQL cuando todas las conexiones disponibles están en uso, MySQL devolverá un error de «Demasiadas conexiones»e incrementaráConnection_errors_max_connections. Para evitar este escenario, debe supervisar el número de conexiones abiertas y asegurarse de que se mantenga de forma segura por debajo del límitemax_connectionsconfigurado. -
Aborted_connects: Si este contador está aumentando, sus clientes están intentando conectarse a la base de datos y no lo logran. Investigue el origen del problema con métricas de conexión detalladas, comoConnection_errors_max_connectionsyConnection_errors_internal.
Uso del grupo de búfer
| Nombre | Descripción | Tipo métrico | Disponibilidad |
|---|---|---|---|
| Innodb_buffer_pool_pages_total | Número total de páginas en el grupo de búferes | Recurso: Utilización | Variable de estado del servidor |
| Utilización del grupo de búfer | Relación de páginas usadas con el total del grupo de búfer | Recurso: Utilización | Calculado a partir de variables de estado del servidor |
| Innodb_buffer_pool_read_requests | Solicitudes realizadas al grupo de búferes | Recurso: Utilización | Variable de estado del servidor |
| Innodb_buffer_pool_reads | Solicitudes que el grupo de búfer no pudo cumplir | Recurso: Saturación | Variable de estado del servidor |
El motor de almacenamiento predeterminado de MySQL, InnoDB, utiliza un área de memoria llamada grupo de búfer para almacenar datos en caché para tablas e índices. Las métricas del grupo de búferes son métricas de recursos en lugar de métricas de trabajo, y como tales son principalmente útiles para investigar (en lugar de detectar) problemas de rendimiento. Si el rendimiento de la base de datos comienza a deslizarse mientras la E/S del disco está aumentando, la ampliación del grupo de búferes a menudo puede proporcionar beneficios.
Dimensionamiento del grupo de búfer
El grupo de búfer tiene por defecto un tamaño relativamente pequeño de 128 mebibytes, pero MySQL aconseja que puede aumentarlo hasta el 80 por ciento de la memoria física en un servidor de base de datos dedicado. MySQL también agrega algunas notas de precaución, sin embargo, ya que la sobrecarga de memoria de InnoDB puede aumentar la huella de memoria en aproximadamente un 10 por ciento más allá del tamaño del grupo de búfer asignado. Y si se queda sin memoria física, su sistema recurrirá a la paginación y el rendimiento sufrirá significativamente.
El grupo de búfer también se puede dividir en regiones separadas, conocidas como instancias. El uso de varias instancias puede mejorar la concurrencia de los grupos de búferes en el rango de varios GiB.
Las operaciones de redimensionamiento del grupo de búfer se realizan en trozos, y el tamaño del grupo de búfer se debe establecer en un múltiplo del tamaño del fragmento multiplicado por el número de instancias:
innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instancesEl tamaño de fragmento predeterminado es de 128 MiB, pero se puede configurar a partir de MySQL 5.7.5. El valor de ambos parámetros se puede comprobar de la siguiente manera:
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";Si la consulta innodb_buffer_pool_chunk_size no devuelve resultados, el parámetro no se puede ajustar en su versión de MySQL y se puede suponer que es de 128 MiB.
Para establecer el tamaño del grupo de búfer y el número de instancias al iniciar el servidor:
$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16A partir de MySQL 5.7.5, también puede cambiar el tamaño del grupo de búfer sobre la marcha a través de un comando SET que especifique el tamaño deseado en bytes. Por ejemplo, con dos instancias de grupo de búfer, puede establecer cada una en un tamaño de 4 GiB estableciendo el tamaño total en 8 GiB:
SET GLOBAL innodb_buffer_pool_size=8589934592;Métricas de grupo de búfer InnoDB clave
MySQL expone un puñado de métricas en el grupo de búfer y su utilización. Algunas de las más útiles son las métricas que rastrean el tamaño total del grupo de búferes, la cantidad que se usa y la eficacia con la que el grupo de búferes sirve lecturas.
Las métricas Innodb_buffer_pool_read_requests y Innodb_buffer_pool_reads son clave para comprender la utilización del grupo de búferes. Innodb_buffer_pool_read_requests rastrea el número de solicitudes de lectura lógica, mientras que Innodb_buffer_pool_reads rastrea el número de solicitudes que el grupo de búfer no pudo satisfacer y, por lo tanto, tuvo que leerse desde el disco. Dado que la lectura desde la memoria es generalmente órdenes de magnitud más rápida que la lectura desde el disco, el rendimiento se verá afectado si Innodb_buffer_pool_reads comienza a subir.
La utilización del grupo de búferes es una métrica útil para comprobar antes de considerar cambiar el tamaño del grupo de búferes. La métrica de utilización no está disponible de inmediato, pero se puede calcular fácilmente de la siguiente manera:
(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_totalSi su base de datos está sirviendo un gran número de lecturas desde el disco, pero el grupo de búfer está lejos de estar lleno, puede ser que su caché se haya borrado recientemente y todavía se esté calentando. Si su grupo de búfer no se llena, pero está sirviendo lecturas de manera efectiva, es probable que su conjunto de datos de trabajo encaje cómodamente en la memoria.
La alta utilización del grupo de búfer, por otro lado, no es necesariamente algo malo de forma aislada, ya que los datos antiguos o no utilizados se eliminan automáticamente de la caché mediante una política de LRU. Pero si el grupo de búferes no sirve de manera efectiva su carga de trabajo de lectura, puede ser el momento de escalar su caché.
Convertir métricas de grupos de búferes en bytes
La mayoría de las métricas de grupos de búfer se informan como un recuento de páginas de memoria, pero estas métricas se pueden convertir en bytes, lo que facilita la conexión de estas métricas con el tamaño real de su grupo de búferes. Por ejemplo, para encontrar el tamaño total del grupo de búfer en bytes utilizando la variable de estado del servidor, seguimiento de páginas totales en el grupo de búfer:
Innodb_buffer_pool_pages_total * innodb_page_sizeEl tamaño de página InnoDB es ajustable, pero por defecto es de 16 Kb, o 16.384 bytes. Su valor actual se puede comprobar con una consulta SHOW VARIABLES :
SHOW VARIABLES LIKE "innodb_page_size";Conclusión
En esta publicación hemos explorado algunas de las métricas más importantes que debe monitorear para controlar la actividad y el rendimiento de MySQL. Si está desarrollando su monitoreo MySQL, capturar las métricas que se describen a continuación lo pondrá en el camino hacia la comprensión de los patrones de uso de su base de datos y las restricciones potenciales. También le ayudarán a identificar cuándo es necesario escalar o mover las instancias de la base de datos a hosts más potentes para mantener un buen rendimiento de la aplicación.
- Rendimiento de consultas
- Latencia y errores de consultas
- Conexiones y errores de cliente
- Utilización del grupo de búfer
La parte 2 de esta serie proporciona instrucciones para recopilar y supervisar todas las métricas que necesita de MySQL.
Agradecimientos
Muchas gracias a Dave Stokes de Oracle y Ewen Fortune de VividCortex por proporcionar valiosos comentarios sobre este artículo antes de su publicación.