Solución de problemas de rendimiento de consultas en el servidor flexible de Azure Database for MySQL
El rendimiento de las consultas puede verse afectado por varios factores, por lo que es importante examinar el ámbito de los síntomas que experimenta en la instancia de servidor flexible de Azure Database for MySQL. Por ejemplo, el rendimiento de las consultas es lento para:
- ¿Todas las consultas que se ejecutan en la instancia de servidor flexible de Azure Database for MySQL?
- ¿Un conjunto específico de consultas?
- ¿Una consulta específica?
Tenga en cuenta también que los cambios recientes en la estructura o los datos subyacentes de las tablas que consulta pueden afectar al rendimiento.
Habilitación de la funcionalidad de registro
Antes de analizar consultas individuales, debe definir puntos de referencia de consultas. Con esta información, puede implementar la funcionalidad de registro en el servidor de bases de datos para realizar un seguimiento de las consultas que superen un umbral que especifique en función de las necesidades de la aplicación.
Servidor flexible de Azure Database for MySQL, se recomienda usar la característica de registro de consultas lentas para identificar las consultas que tardan más de N segundos en ejecutarse. Después de identificar las consultas del registro de consultas lentas, puede usar los diagnósticos de MySQL para solucionar estos problemas.
Antes de empezar a realizar un seguimiento de las consultas de larga duración, debe habilitar el parámetro slow_query_log mediante Azure Portal o la CLI de Azure. Con este parámetro habilitado, también debe configurar el valor del parámetro long_query_time para especificar el número de segundos que las consultas se pueden ejecutar antes de identificarse como consultas de "ejecución lenta". El valor predeterminado del parámetro es de 10 segundos, pero puede ajustar el valor para satisfacer las necesidades del Acuerdo de Nivel de Servicio de la aplicación.
Aunque el registro de consultas lentas es una excelente herramienta para realizar el seguimiento de las consultas de larga duración, hay ciertos escenarios en los que es posible que no sea eficaz. Por ejemplo, el registro de consultas lentas:
- Afecta negativamente al rendimiento si el número de consultas es muy alto o si la instrucción de la consulta es muy grande. Ajuste el valor del parámetro
long_query_timeen consecuencia. - Puede que no sea útil si también ha habilitado el parámetro
log_queries_not_using_index, que especifica para registrar las consultas esperadas para recuperar todas las filas. Las consultas que realizan un examen de índice completo aprovechan un índice, pero se registrarían porque el índice no limita el número de filas devueltas.
Recuperación de información de los registros
Los registros están disponibles hasta siete días después de su creación. Puede enumerar y descargar los registros de consultas lentas mediante Azure Portal o la CLI de Azure. En Azure Portal, vaya al servidor, en Supervisión, seleccione Registros del servidor y, a continuación, seleccione la flecha hacia abajo situada junto a una entrada para descargar los registros asociados a la fecha y hora en que está investigando.
Además, si los registros de consultas lentas se integran con los registros de Azure Monitor mediante los registros de diagnóstico, puede ejecutar consultas en un editor para analizarlas aún más:
AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10
Nota
Para obtener más ejemplos para empezar a diagnosticar los registros de consultas lentas mediante los registros de diagnóstico, consulte Análisis de registros en los registros de Azure Monitor.
En la siguiente instantánea, se muestra una consulta lenta de ejemplo.
# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @ [172.30.209.6] Id: 735026
# Query_time: 25.314811 Lock_time: 0.000000 Rows_sent: 126 Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;
La consulta se ejecutó en 26 segundos, examinó más de 443 000 filas y devolvió 126 filas de resultados.
Normalmente, debe centrarse en las consultas con valores altos para Query_time y Rows_examined. Sin embargo, si observa consultas con un valor alto de Query_time pero solo de algunas filas para Rows_examined, esto suele indicar la presencia de un cuello de botella de recursos. En estos casos, debe comprobar si hay alguna limitación de E/S o uso de CPU.
Generación de perfiles de una consulta
Después de identificar una consulta de ejecución lenta específica, puede usar el comando EXPLAIN y la generación de perfiles para recopilar más detalles.
Para revisar el plan de consulta, ejecute el siguiente comando:
EXPLAIN <QUERY>
Nota:
Para más información sobre el uso de instrucciones EXPLAIN, consulte Rendimiento de consultas de perfil en Azure Database for MySQL: Servidor flexible mediante EXPLAIN.
Además de crear un plan EXPLAIN para una consulta, puede usar el comando SHOW PROFILE, que permite diagnosticar la ejecución de las instrucciones que se han ejecutado en la sesión actual.
Para habilitar la generación de perfiles y generar perfiles de una consulta específica de una sesión, ejecute el siguiente conjunto de comandos:
SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;
Nota
La generación de perfiles de consultas individuales solo está disponible en una sesión y no se pueden generar perfiles de las instrucciones históricas.
Echemos un vistazo más detenidamente al uso de estos comandos para generar perfiles de una consulta. En primer lugar, habilite la generación de perfiles para la sesión actual, ejecute el comando SET PROFILING = 1:
SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
A continuación, ejecute una consulta poco óptima que realice un recorrido de tabla completo:
mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 1 row in set (27.60 sec) |
Después, muestre una lista de todos los perfiles de consulta disponibles mediante la ejecución del comando SHOW PROFILES:
SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration | Query |
| +----------+-------------+----------------------------------------------------+ |
| 1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
| +----------+-------------+----------------------------------------------------+ |
| 1 row in set, 1 warning (0.00 sec) |
Por último, para mostrar el perfil de la consulta 1, ejecute el comando SHOW PROFILE FOR QUERY 1.
SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status | Duration |
| +----------------------+-----------+ |
| starting | 0.000102 |
| checking permissions | 0.000028 |
| Opening tables | 0.000033 |
| init | 0.000035 |
| System lock | 0.000018 |
| optimizing | 0.000017 |
| statistics | 0.000025 |
| preparing | 0.000019 |
| executing | 0.000011 |
| Sending data | 27.594038 |
| end | 0.000041 |
| query end | 0.000014 |
| closing tables | 0.000013 |
| freeing items | 0.000088 |
| cleaning up | 0.000020 |
| +----------------------+-----------+ |
| 15 rows in set, 1 warning (0.00 sec) |
Enumeración de las consultas más usadas en el servidor de bases de datos
Siempre que esté solucionando problemas de rendimiento de las consultas, resulta útil comprender qué consultas se ejecutan con más frecuencia en la instancia de servidor flexible de Azure Database for MySQL. Puede usar esta información para medir si alguna de las consultas principales tarda más de lo habitual en ejecutarse. Además, un desarrollador o un DBA podrían usar esta información para identificar si alguna consulta tiene un aumento repentino en el recuento y la duración de la ejecución de las consultas.
Para enumerar las 10 consultas más ejecutadas principales en la instancia de servidor flexible de Azure Database for MySQL, ejecute la consulta siguiente:
SELECT digest_text AS normalized_query,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;
Nota:
Use esta consulta para crear un punto de referencia de las consultas principales ejecutadas en el servidor de bases de datos y determinar si se ha producido un cambio en las consultas principales o si alguna de las consultas existentes en el punto de referencia inicial ha aumentado en cuanto a la duración de la ejecución.
Enumeración de las 10 consultas más costosas por tiempo de ejecución total
La salida de la consulta siguiente proporciona información sobre las 10 primeras consultas que se ejecutan en el servidor de bases de datos y su número de ejecuciones en él. También proporciona otra información útil, como las latencias de consulta, sus tiempos de bloqueo, el número de tablas temporales creadas como parte del tiempo de ejecución de consultas, etc. Use esta salida de consulta para realizar un seguimiento de las consultas principales de la base de datos y los cambios en factores, como latencias, lo que podría indicar la posibilidad de ajustar aún más la consulta para ayudar a evitar riesgos futuros.
SELECT REPLACE(event_name, 'statement/sql/', '') AS statement,
count_star AS all_occurrences ,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency,
Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency,
Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time ,
sum_rows_affected AS sum_rows_changed,
sum_rows_sent AS sum_rows_selected,
sum_rows_examined AS sum_rows_scanned,
sum_created_tmp_tables, sum_created_tmp_disk_tables,
IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables /
sum_created_tmp_tables * 100, 0))) AS
tmp_disk_tables_percent,
sum_select_scan,
sum_no_index_used,
sum_no_good_index_used
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name LIKE 'statement/sql/%'
AND count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;
Supervisión de la recolección de elementos no utilizados de InnoDB
Cuando la recolección de elementos no utilizados de InnoDB se bloquea o retrasa, la base de datos puede desarrollar un retraso de purga sustancial que puede afectar negativamente al uso del almacenamiento y al rendimiento de las consultas.
La longitud de la lista del historial (HLL) de segmentos de reversión de InnoDB mide el número de registros de cambios almacenados en el registro de la fase de reversión. Un valor HLL creciente indica que los subprocesos de recolección de elementos no utilizados de InnoDB (subprocesos de purga) no están al día con la carga de trabajo de escritura o que la purga está bloqueada por una consulta o transacción de larga duración.
Los retrasos excesivos en la recolección de elementos no utilizados pueden tener consecuencias graves y negativas:
- El espacio de tablas del sistema InnoDB se expandirá, lo que acelera el crecimiento del volumen de almacenamiento subyacente. En ocasiones, el espacio de tablas del sistema puede crecer en varios terabytes como resultado de una purga bloqueada.
- Los registros marcados con eliminación no se quitarán de forma oportuna. Esto puede hacer que los espacios de tablas de InnoDB crezcan e impidan que el motor reutilice el almacenamiento ocupado por estos registros.
- El rendimiento de todas las consultas podría degradarse y el uso de la CPU podría aumentar debido al crecimiento de las estructuras de almacenamiento de InnoDB.
Como resultado, es importante supervisar los valores, patrones y tendencias de HLL.
Búsqueda de valores de HLL
Para encontrar el valor de HLL, ejecute el comando show engine innodb status. El valor se mostrará en la salida, bajo el encabezado TRANSACTIONS:
show engine innodb status\G
****************** 1. row ******************
(...)
------------
TRANSACTIONS
------------
Trx id counter 52685768
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle
History list length 2964300
(...)
También puede determinar el valor de HLL consultando la tabla information_schema.innodb_metrics:
mysql> select count from information_schema.innodb_metrics
-> where name = 'trx_rseg_history_len';
+---------+
| count |
| +---------+ |
| 2964300 |
| +---------+ |
| 1 row in set (0.00 sec) |
Interpretación de los valores de HLL
Al interpretar los valores de HLL, tenga en cuenta las directrices enumeradas en la tabla siguiente:
| Valor | Notas |
|---|---|
| Menor que ~10 000 | Valores normales, lo que indica que la recolección de elementos no utilizados no se está retrasando. |
| Entre ~10 000 y ~1 000 000 | Estos valores indican un retraso menor en la recolección de elementos no utilizados. Estos valores pueden ser aceptables si permanecen estables y no aumentan. |
| Mayor que ~1 000 000 | Estos valores se deben investigar y pueden requerir acciones correctivas. |
Solución para valores de HLL excesivos
Si el valor de HLL muestra picos grandes o muestra un patrón de crecimiento periódico, investigue las consultas y transacciones que se ejecutan en la instancia del servidor flexible de Azure Database for MySQL inmediatamente. Después, puede resolver cualquier problema de la carga de trabajo que pueda impedir el progreso del proceso de recolección de elementos no utilizados. Aunque no se espera que la base de datos esté libre de retraso de purga, no debe permitir que el retraso crezca de forma incontrolada.
Para obtener la información de las transacciones de la tabla information_schema.innodb_trx, por ejemplo, ejecute los siguientes comandos:
select * from information_schema.innodb_trx
order by trx_started asc\G
Los detalles de la columna trx_started le ayudarán a calcular la antigüedad de la transacción.
mysql> select * from information_schema.innodb_trx
-> order by trx_started asc\G
****************** 1. row ******************
trx_id: 8150550
trx_state: RUNNING
trx_started: 2021-11-13 20:50:11
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 19
trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)
Para obtener información sobre las sesiones de base de datos actuales, incluido el tiempo invertido en el estado actual de la sesión, compruebe la tabla information_schema.processlist. La salida siguiente, por ejemplo, muestra una sesión que ha estado ejecutando activamente una consulta durante los últimos 1462 segundos:
mysql> select user, host, db, command, time, info
-> from information_schema.processlist
-> order by time desc\G
****************** 1. row ******************
user: test
host: 172.31.19.159:38004
db: employees
command: Query
time: 1462
info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)
Recomendaciones
Asegúrese de que la base de datos tenga suficientes recursos asignados para ejecutar las consultas. En ocasiones, es posible que tenga que escalar verticalmente el tamaño de la instancia para obtener más núcleos de CPU y memoria adicional para acomodar la carga de trabajo.
Evite transacciones grandes o de larga duración dividiéndolas en transacciones más pequeñas.
Configure innodb_purge_threads según la carga de trabajo para mejorar la eficacia de las operaciones de purga en segundo plano.
Nota
Pruebe los cambios realizados en esta variable de servidor para cada entorno para medir el cambio en el comportamiento del motor.
Use alertas en "Porcentaje de CPU del host", "Porcentaje de memoria de host" y "Total de conexiones" para que reciba notificaciones si el sistema supera cualquiera de los umbrales especificados.
Use Información de rendimiento de consultas o libros de Azure para identificar las consultas problemáticas o que se ejecutan lentamente y, a continuación, optimizarlas.
En el caso de los servidores de bases de datos de producción, recopile diagnósticos a intervalos regulares para asegurarse de que todo se ejecuta sin problemas. Si no es así, solucione y resuelva los problemas que identifique.