Optimizar el uso elevado de CPU en las instancias

La alta utilización de la CPU en una instancia puede deberse a varios motivos, como el aumento de las cargas de trabajo, las transacciones pesadas, las consultas lentas y las transacciones de larga duración.

El recomendador de instancias con pocos recursos analiza el uso de la CPU. Si los niveles de utilización de la CPU son iguales o superiores al 95% durante un periodo significativo en los últimos 30 días, el recomendador te enviará una alerta y te proporcionará información adicional para ayudarte a resolver el problema.

En este documento se explica cómo revisar y optimizar una instancia de Cloud SQL para MySQL si el recomendador de instancias con recursos insuficientes la identifica como una instancia con un uso elevado de la CPU.

Usar Información útil sobre las consultas para identificar las consultas que tienen un consumo de CPU elevado

Información valiosa sobre las consultas te ayuda a detectar, diagnosticar y evitar problemas de rendimiento de las consultas que pueden provocar un consumo elevado de CPU en las bases de datos de Cloud SQL.

Usar la auditoría de bases de datos MySQL

Usa la auditoría de bases de datos MySQL para ver el consumo de memoria de tu instancia.

Recomendaciones

El uso de la CPU aumenta proporcionalmente a la carga de trabajo. Para reducir la utilización de la CPU, comprueba las consultas en ejecución y optimízalas. A continuación, te indicamos algunos pasos para comprobar el consumo de CPU.

  1. Consulta Threads_running y Threads_connected.

    Usa la siguiente consulta para ver el número de hilos activos:

    > SHOW STATUS like 'Threads_%';

    Threads_running es un subconjunto de Threads_connected. El resto de los hilos están inactivos. Un aumento de Threads_running contribuiría a un aumento del uso de la CPU. Te recomendamos que compruebes qué se está ejecutando en esos hilos.

  2. Comprobar estados de consultas

    Ejecuta el comando SHOW PROCESSLIST para ver las consultas en curso. Devuelve todos los subprocesos conectados en orden y su instrucción SQL en ejecución.

    mysql> SHOW [FULL] PROCESSLIST;

    Presta atención a las columnas de estado y duración. Comprueba si hay muchas consultas atascadas en el mismo estado.

    • Si muchos hilos muestran Updating, puede que haya una contención de bloqueo de registros. Consulta el siguiente paso.
    • Si muchos subprocesos muestran Waiting para el bloqueo de metadatos de la tabla, comprueba la consulta para conocer la tabla y, a continuación, busca un DDL (como ALTER TABLE) que pueda mantener el bloqueo de metadatos. También puede que una DDL esté esperando el bloqueo de metadatos de una tabla si una consulta anterior, como una SELECT query de larga duración, lo está reteniendo.

  3. Comprobar la contención de bloqueos de registros

    Cuando las transacciones mantienen bloqueos en registros de índice populares, bloquean otras transacciones que solicitan los mismos bloqueos. Esto puede provocar un efecto encadenado que haga que se bloqueen varias solicitudes y que aumente el valor de Threads_running. Para diagnosticar la contención de bloqueos, usa la tabla information_schema.innodb_lock_waits.

    La siguiente consulta muestra cada transacción de bloqueo y el número de transacciones bloqueadas asociadas.

    SELECT
  t.trx_id,
  t.trx_state,
  t.trx_started,
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM
  information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

    Tanto una DML grande como muchas DMLs pequeñas simultáneas pueden provocar conflictos de bloqueo de filas. Para optimizar este proceso desde la aplicación, siga estos pasos:

    • Evita las transacciones largas, ya que los bloqueos de filas se mantienen hasta que finaliza la transacción.
    • Divide una instrucción DML grande en varias más pequeñas.
    • Agrupa en lotes las instrucciones DML de una sola fila en fragmentos pequeños.
    • Minimiza la contención entre los subprocesos. Por ejemplo, si el código de la aplicación usa un grupo de conexiones, asigna un intervalo de IDs al mismo subproceso.

  4. Buscar transacciones de larga duración

    • Usar SHOW ENGINE INNODB STATUS

      En la sección TRANSACCIONES, puede ver todas las transacciones abiertas ordenadas de más antigua a más reciente.

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      Empieza por las transacciones más antiguas y responde a las siguientes preguntas:

      • ¿Cuánto tiempo llevan en marcha estas transacciones?
      • ¿Cuántas estructuras de bloqueo y bloqueos de fila hay?
      • ¿Cuántas entradas de registro de deshacer hay?
      • ¿Quiénes son los anfitriones y los usuarios que se conectan?
      • ¿Cuál es la instrucción SQL en curso?

    • Usar information_schema.innodb_trx

      Si SHOW ENGINE INNODB STATUS se ha truncado, una forma alternativa de examinar todas las transacciones abiertas es usar la tabla information_schema.innodb_trx:

      SELECT
 trx_id, trx_state,
 timestampdiff(second, trx_started, now()) AS active_secs,
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked,
 trx_lock_structs,
 trx_rows_locked,
 trx_rows_modified,
 trx_query
FROM information_schema.innodb_trx

    Si las transacciones muestran las instrucciones de larga duración actuales, puedes decidir si quieres detenerlas para reducir la presión sobre el servidor o esperar a que se completen las transacciones críticas. Si las transacciones antiguas no muestran ninguna actividad, ve al siguiente paso para encontrar el historial de transacciones.

  5. Comprobar las instrucciones SQL de las transacciones de larga duración

    • Usar performance_schema

      Para usar performance_schema, primero debes activarlo. Es un cambio que requiere reiniciar la instancia. Una vez que performance_schema esté activado, comprueba que los instrumentos y los consumidores estén habilitados:

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      Si no están habilitadas, habilítalas:

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      De forma predeterminada, cada subproceso conservaría los últimos 10 eventos definidos por performance_schema_events_statements_history_size. Por lo general, son suficientes para localizar la transacción en el código de la aplicación. Este parámetro no es dinámico.

      Con mysql thread id, que es processlist_id, consulta los eventos del historial:

      SELECT
 t.thread_id,
 event_name,
 sql_text,
 rows_affected,
 rows_examined,
 processlist_id,
 processlist_time,
 processlist_state
FROM events_statements_history h
INNER JOIN threads t
ON h.thread_id = t.thread_id
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • Usar el registro de consultas lentas

      Para depurar, puede registrar en el registro de consultas lentas todas las consultas que hayan tardado más de N un número de segundos. Para habilitar los registros de consultas lentas, edita la configuración de la instancia en la página de la instancia de laGoogle Cloud consola o de gcloud CLI. A continuación, consulta los registros con el visor de registros en laGoogle Cloud consola o en gloud CLI.

  6. Comprobar la contención de semáforos

    En un entorno simultáneo, la exclusión mutua y el bloqueo de lectura/escritura en recursos compartidos pueden ser el punto de contención, lo que ralentiza el rendimiento del servidor. Además, si el tiempo de espera del semáforo es superior a 600 segundos, el sistema puede fallar para salir del bloqueo.

    Para ver la contención de semáforos, usa el siguiente comando:

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

    En cada espera de semáforo, la primera línea muestra el hilo que está esperando, el semáforo específico y el tiempo que ha esperado. Si hay esperas de semáforo frecuentes al ejecutar SHOW ENGINE INNODB STATUS repetidamente, sobre todo si duran más de unos segundos, significa que el sistema está experimentando cuellos de botella de simultaneidad.

    Hay diferentes puntos de contención en diferentes cargas de trabajo y configuraciones.

    Si los semáforos se encuentran a menudo en btr0sea.c, la indexación hash adaptativa podría ser la causa de la contención. Prueba a inhabilitarlo con la Google Cloud consola o con gcloud CLI.

  7. Optimizar las consultas largas de SELECT

    Primero, revisa la consulta. Identifica el objetivo de la consulta y la mejor forma de obtener los resultados. El mejor plan de consulta es el que minimiza el acceso a los datos.

    • Comprueba el plan de ejecución de la consulta:
    mysql> EXPLAIN <the query>;

    Consulta la documentación de MySQL para saber cómo interpretar el resultado y evaluar la eficiencia de la consulta.

    • Usar el índice correcto

    Consulta la columna de clave para ver si se usa el índice esperado. Si no es así, actualiza las estadísticas del índice:

    mysql> analyze table <table_name>

    Aumenta el número de páginas de muestra que se usan para calcular las estadísticas del índice. Para obtener más información, consulta la documentación de MySQL.

    • Aprovechar al máximo el índice

    Cuando se usa un índice de varias columnas, comprueba las columnas key_len para ver si el índice se aprovecha al máximo para filtrar los registros. Las columnas de la izquierda deben ser comparaciones de igualdad y el índice se puede usar hasta la primera condición de intervalo inclusive.

    • Usar sugerencias de Optimizador

    Otra forma de asegurarse de que se usa el índice correcto es aplicar sugerencias de índice y sugerencias para el orden de las combinaciones de tablas.

  8. Evitar una lista de historial larga con READ COMMITTED

    La lista de historial es la lista de transacciones no purgadas en el espacio de tabla de deshacer. El nivel de aislamiento predeterminado de una transacción es REPEATABLE READ, lo que requiere que una transacción lea la misma captura durante toda su duración. Por lo tanto, una consulta SELECT bloquea la purga de los registros de cancelación de modificaciones que se hayan realizado desde que se inició la consulta (o la transacción). Por lo tanto, una lista de historial larga ralentiza el rendimiento de las consultas. Una forma de evitar que se cree una lista de historial larga es cambiar el nivel de aislamiento de las transacciones a READ COMMITTED. Con READ COMMITTED, ya no es necesario mantener la lista del historial para que la vista de lectura sea coherente. Puede cambiar el nivel de aislamiento de las transacciones de forma global para todas las sesiones, para una sola sesión o para la siguiente transacción. Para obtener más información, consulta la documentación de MySQL.

  9. Ajustar la configuración del servidor

    Hay mucho que decir sobre la configuración del servidor. Aunque la historia completa va más allá del alcance de este documento, merece la pena mencionar que el servidor también registra varias variables de estado que dan pistas sobre el rendimiento de las configuraciones relacionadas. Por ejemplo:

    • Ajusta thread_cache_size si Threads_created/Connections es grande. Una caché de subprocesos adecuada reduciría el tiempo de creación de subprocesos y ayudaría a las cargas de trabajo con mucha simultaneidad.
    • Ajusta table_open_cache si Table_open_cache_misses/Table_open_cache_hits no es trivial. Tener tablas en la caché de tablas ahorra tiempo de ejecución de las consultas y puede marcar la diferencia en un entorno de alta simultaneidad.

  10. Finalizar una conexión no deseada

    Puedes detener la consulta si parece no ser válida o si ya no es necesaria. Para saber cómo identificar y finalizar el hilo de MySQL, consulta Gestionar conexiones de bases de datos.

Por último, si el uso de la CPU sigue siendo elevado y las consultas forman parte del tráfico necesario, considera la posibilidad de aumentar los recursos de CPU de tu instancia para evitar que la base de datos falle o se produzca un tiempo de inactividad.

Siguientes pasos