Solución de problemas de consumo elevado de CPU en Azure Database for MySQL con servidor flexible

  • Artículo

SE APLICA A: Azure Database for MySQL: servidor único Azure Database for MySQL: servidor flexible

Importante

El servidor único de Azure Database for MySQL está en la ruta de retirada. Se recomienda encarecidamente actualizar al servidor flexible de Azure Database for MySQL. Para más información sobre la migración al servidor flexible de Azure Database for MySQL, consulte ¿Qué ocurre con Azure Database for MySQL con servidor único?

El servidor flexible de Azure Database for MySQL proporciona una serie de métricas que puede usar para identificar cuellos de botella de recursos y problemas de rendimiento en el servidor. Para determinar si el servidor está experimentando un uso elevado de la CPU, supervise métricas como "Porcentaje de CPU del host", "Total de conexiones", "Porcentaje de memoria del host" y "Porcentaje de E/S". En ocasiones, ver una combinación de estas métricas proporcionará información sobre lo que podría estar causando el aumento del uso de la CPU en la instancia de servidor flexible de Azure Database for MySQL.

Por ejemplo, considere un aumento repentino en las conexiones que inicia el aumento de las consultas de base de datos que hacen que el uso de la CPU se dispare.

Además de capturar métricas, es importante realizar un seguimiento de la carga de trabajo para comprender si una o varias consultas están causando el pico en el uso de la CPU.

Causas elevadas de CPU

Los picos de CPU pueden producirse por varias razones, principalmente debido a picos en conexiones y consultas SQL mal escritas, o una combinación de ambas:

Pico de conexiones

Un aumento de las conexiones puede provocar un aumento en los subprocesos, lo que a su vez puede provocar un aumento en el uso de la CPU, ya que tiene que administrar estas conexiones junto con sus consultas y recursos. Para solucionar problemas de un pico de conexiones, debe comprobar la métrica Total de Conectar ions y consultar la sección siguiente para obtener más información sobre estas conexiones. Puede usar el performance_schema para identificar los hosts y los usuarios conectados actualmente al servidor con los siguientes comandos:

Hosts conectados actuales

   select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
   where CURRENT_CONNECTIONS > 0
   and host not in ('NULL','localhost');

Usuarios conectados actuales

   select USER,CURRENT_CONNECTIONS from performance_schema.users
   where CURRENT_CONNECTIONS >0
   and USER not in ('NULL','azure_superuser');

Consultas SQL mal escritas

Las consultas que son costosas de ejecutar y que analizan una gran cantidad de filas sin un índice, o aquellas que realizan operaciones temporales junto con otros planes ineficaces, pueden provocar picos de CPU. Aunque algunas consultas se pueden ejecutar rápidamente en una sola sesión, pueden provocar picos de CPU cuando se ejecutan en varias sesiones. Por lo tanto, es fundamental explicar siempre las consultas que capture de la lista de procesos de presentación y asegurarse de que sus planes de ejecución sean eficaces. Esto se puede lograr asegurándose de que examinan un número mínimo de filas mediante filtros o cláusulas where, usan índices y evitan el uso de una ordenación temporal grande junto con otros planes de ejecución incorrectos. Para obtener más información sobre los planes de ejecución, vea EXPLAIN Output Format.

Captura de detalles de la carga de trabajo actual

El comando SHOW (FULL) PROCESSLIST muestra una lista de todas las sesiones de usuario conectadas actualmente a la instancia de servidor flexible de Azure Database for MySQL. También proporciona detalles sobre el estado actual y la actividad de cada sesión.

Este comando solo genera una instantánea del estado de sesión actual y no proporciona información sobre la actividad histórica de la sesión.

Echemos un vistazo a la salida de ejemplo de la ejecución de este comando.

mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id    | User             | Host               | db            | Command     | Time   | State                       | Info                                     |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
|     1 | event_scheduler  | localhost          | NULL          | Daemon      |     13 | Waiting for next activation | NULL                                     |
|     6 | azure_superuser  | 127.0.0.1:33571    | NULL          | Sleep       |    115 |                             | NULL                                     
|
| 24835 | adminuser        | 10.1.1.4:39296     | classicmodels | Query       |      7 | Sending data                | select * from classicmodels.orderdetails;|
| 24837 | adminuser        | 10.1.1.4:38208     | NULL          | Query       |      0 | starting                    | SHOW FULL PROCESSLIST                    |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)

Tenga en cuenta que hay dos sesiones propiedad del usuario "adminuser", ambas desde la misma dirección IP:

  • La sesión 24835 ha estado ejecutando una instrucción SELECT durante los últimos siete segundos.
  • La sesión 24837 está ejecutando la instrucción "show full processlist".

A veces, puede ser necesario finalizar una consulta, como una consulta de informes o HTAP que ha provocado el aumento del uso de CPU de la carga de trabajo de producción. Sin embargo, tenga en cuenta siempre las posibles consecuencias de terminar una consulta antes de realizar la acción en un intento de reducir el uso de la CPU. Otras veces, si hay consultas de larga duración identificadas que conducen a picos de CPU, ajuste estas consultas para que los recursos se usen de forma óptima.

Análisis detallado de la carga de trabajo actual

Debe usar al menos dos orígenes para obtener información precisa sobre el estado de una sesión, transacción y consulta:

  • La lista de procesos del servidor de la tabla INFORMATION_SCHEMA.PROCESSLIST, a la que también puede acceder mediante la ejecución del comando SHOW [FULL] PROCESSLIST.
  • Metadatos de transacción de InnoDB de la tabla INFORMATION_SCHEMA.INNODB_TRX.

Con información de solo uno de estos orígenes, es imposible describir el estado de conexión y transacción. Por ejemplo, la lista de procesos no le informa de si hay una transacción abierta asociada a cualquiera de las sesiones. Por otro lado, los metadatos de transacción no muestran el estado de sesión y el tiempo invertido en ese estado.

A continuación, se muestra una consulta de ejemplo que combina la información de la lista de procesos con algunas de los fragmentos importantes de los metadatos de transacción de InnoDB:

mysql> select    p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state,    substring(p.info, 1, 50) as info,    t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level   from information_schema.processlist p    left join information_schema.innodb_trx t    on p.id = t.trx_mysql_thread_id \G

En el ejemplo siguiente se muestra la salida de esta consulta:

*************************** 1. row *************************** 
        session_id: 11 
               user: adminuser 
               host: 172.31.19.159:53624 
                 db: NULL 
            command: Sleep 
               time: 636 
              state: cleaned up 
               info: NULL 
        trx_started: 2019-08-01 15:25:07 
    trx_age_seconds: 2908 
  trx_rows_modified: 17825792 
trx_isolation_level: REPEATABLE READ 
*************************** 2. row *************************** 
         session_id: 12 
               user: adminuser 
               host: 172.31.19.159:53622 
                 db: NULL 
            command: Query 
               time: 15 
              state: executing 
               info: select * from classicmodels.orders 
        trx_started: NULL 
    trx_age_seconds: NULL 
  trx_rows_modified: NULL 
trx_isolation_level: NULL

En la tabla siguiente se muestra un análisis de esta información, por sesión.

Ámbito Análisis
Sesión 11 Esta sesión está inactiva (en suspensión) actualmente sin que se ejecuten consultas, y ha estado así durante 636 segundos. Dentro de la sesión, una transacción abierta durante 2908 segundos ha modificado 17 825 792 filas y usa el aislamiento REPEATABLE READ.
Sesión 12 La sesión está ejecutando actualmente una instrucción SELECT, que se ha estado ejecutando durante 15 segundos. No hay ninguna consulta que se ejecute dentro de la sesión, como se indica por los valores NULL para trx_started y trx_age_seconds. La sesión seguirá manteniendo el límite de recolección de elementos no utilizados durante su ejecución, a menos que use el aislamiento READ COMMITTED más relajado.

Tenga en cuenta que, si una sesión se notifica como inactiva, ya no ejecuta ninguna instrucción. En ese momento, la sesión ha completado cualquier trabajo anterior y está esperando nuevas instrucciones del cliente. Sin embargo, las sesiones inactivas siguen siendo responsables de cierto consumo de CPU y uso de memoria.

Enumeración de las transacciones abiertas

La salida de la consulta siguiente proporciona una lista de todas las transacciones que se ejecutan actualmente en el servidor de bases de datos en orden de la hora de inicio de la transacción, de forma que pueda identificar fácilmente si hay transacciones de larga duración y de bloqueo que superen su tiempo de ejecución esperado.

SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;

Descripción de los estados de subprocesos

Las transacciones que contribuyen a un mayor uso de CPU durante la ejecución pueden tener subprocesos en varios estados, como se describe en las secciones siguientes. Use esta información para comprender mejor el ciclo de vida de las consultas y varios estados de los subprocesos.

Comprobación de permisos o apertura de tablas

Este estado normalmente significa que la operación de apertura de tabla consume mucho tiempo. Normalmente, puede aumentar el tamaño de la caché de tablas para mejorar el problema. Sin embargo, la apertura lenta de las tablas también puede indicar otros problemas, como tener demasiadas tablas en la misma base de datos.

Envío de datos

Aunque este estado puede significar que el subproceso envía datos a través de la red, también puede indicar que la consulta lee datos del disco o la memoria. Este estado puede deberse a un examen de tabla secuencial. Debe comprobar los valores de innodb_buffer_pool_reads e innodb_buffer_pool_read_requests para determinar si se sirve un gran número de páginas desde el disco a la memoria. Para más información, consulte Solución de problemas de memoria baja en el servidor flexible de Azure Database for MySQL.

Actualizando

Este estado suele significar que el subproceso está realizando una operación de escritura. Compruebe la métrica relacionada con E/S en el Monitor de rendimiento para comprender mejor lo que hacen las sesiones actuales.

Esperando el bloqueo <lock_type>

Este estado indica que el subproceso está esperando un segundo bloqueo. En la mayoría de los casos, puede ser un bloqueo de metadatos. Debe revisar todos los demás subprocesos y ver quién está realizando el bloqueo.

Descripción y análisis de eventos de espera

Es importante comprender los eventos de espera subyacentes en el motor de MySQL, ya que las esperas largas o un gran número de esperas en una base de datos pueden provocar un mayor uso de la CPU. En el ejemplo siguiente se muestra el comando adecuado y la salida de ejemplo.

SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
 Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event                           | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog              |            7090 | 255.54 s        | 36.04 ms      |
| wait/io/file/innodb/innodb_log_file  |           17798 | 55.43 s         | 3.11 ms       |
| wait/io/file/innodb/innodb_data_file |          260227 | 39.67 s         | 0.15 ms       |
| wait/io/table/sql/handler            |         5548985 | 11.73 s         | 0.00 ms       |
| wait/io/file/sql/FRM                 |            1237 | 7.61 s          | 6.15 ms       |
| wait/io/file/sql/dbopt               |              28 | 1.89 s          | 67.38 ms      |
| wait/io/file/myisam/kfile            |              92 | 0.76 s          | 8.30 ms       |
| wait/io/file/myisam/dfile            |             271 | 0.53 s          | 1.95 ms       |
| wait/io/file/sql/file_parser         |              18 | 0.32 s          | 17.75 ms      |
| wait/io/file/sql/slow_log            |               2 | 0.05 s          | 25.79 ms      |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)

Restricción del tiempo de ejecución de instrucciones SELECT

Si no conoce el costo y el tiempo de ejecución de las operaciones de base de datos que implican consultas SELECT, cualquier instrucción SELECT de larga duración puede provocar imprevisibilidad o volatilidad en el servidor de bases de datos. El tamaño de las instrucciones y transacciones, así como el uso de recursos asociado, continúa creciendo en función del crecimiento del conjunto de datos subyacente. Debido a este crecimiento sin límites, las instrucciones de usuario final y las transacciones tardan más y más, consumen cada vez más recursos hasta que sobrecargan el servidor de bases de datos. Al usar consultas SELECT sin límites, se recomienda configurar el parámetro max_execution_time para que se anulen las consultas que superen esta duración.

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 así acomodar la carga de trabajo.
  • Evite transacciones grandes o de larga duración dividiéndolas en transacciones más pequeñas.
  • Ejecute instrucciones SELECT en servidores de réplica de lectura siempre que sea posible.
  • Use las alertas sobre el "Porcentaje de CPU del host" para recibir 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.

Pasos siguientes

Para encontrar respuestas de los compañeros a sus preguntas o publicar una nueva pregunta o respuesta, visite Stack Overflow.