Optimiza el uso elevado de CPU en las instancias

El uso elevado de CPU en una instancia puede deberse a varias razones, 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 aprovisionamiento insuficiente analiza el uso de CPU. Si los niveles de uso de la CPU son superiores al 95% durante un período significativo en los últimos 30 días, el recomendador te alerta y proporciona estadísticas adicionales para ayudar 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 aprovisionamiento insuficiente identifica que esa instancia tiene un uso de CPU elevado.

Recomendaciones

El uso de CPU aumenta de forma proporcional con la carga de trabajo. Para reducir el uso de CPU, verifica las consultas en ejecución y optimízalas. Estos son algunos pasos para verificar el consumo de CPU.

Verifica Threads_running y Threads_connected

Usa la siguiente consulta para ver la cantidad de subprocesos activos:
```
> SHOW STATUS like 'Threads_%';
```
Threads_running es un subconjunto de Threads_connected. El resto de los subprocesos están inactivos. Un aumento de Threads_running contribuiría a un aumento en el uso de CPU. Es recomendable comprobar lo que se está ejecutando en esos subprocesos.
Verifica los estados de las consultas

Ejecuta el comando SHOW PROCESSLIST para ver las consultas en ejecución. Muestra todos los subprocesos conectados en orden y su instrucción de SQL en ejecución.
```
mysql> SHOW [FULL] PROCESSLIST;
```
Presta atención a las columnas de estado y duración. Verifica si hay muchas consultas atascadas en el mismo estado.
- Si muchos subprocesos muestran Updating, es posible que haya una contención de bloqueo de registro. Consulta el siguiente paso.
- Si muchos subprocesos muestran Waiting para el bloqueo de metadatos de tabla, verifica la consulta para conocer la tabla y, luego, busca un DDL (como ALTER TABLE) que pueda contener el bloqueo de metadatos. Un DDL también puede esperar el bloqueo de metadatos de la tabla si una consulta temprana, como una SELECT query de larga duración, la retiene.
Verifica la contención de bloqueo de registros

Cuando las transacciones mantienen bloqueos en registros de índice populares, bloquean otras transacciones que solicitan los mismos bloqueos. Esto podría entrar en un efecto en cadena y causar que se detengan varias solicitudes y que aumente el valor de Threads_running. Para diagnosticar la contención de bloqueo, usa la tabla information_schema.innodb_lock_waits.

La siguiente consulta enumera cada transacción de bloqueo y la cantidad 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;
```
Un solo DML grande y muchos DML pequeños simultáneos pueden causar contenciones de bloqueo de filas. Puedes optimizar esto desde la aplicación mediante los siguientes pasos:
- Evita las transacciones largas, ya que los bloqueos de las filas se mantienen hasta que la transacción finaliza.
- Divide un DML grande en varios DML pequeños.
- Agrupa un DML de fila única en fragmentos pequeños.
- Minimiza la contención entre subprocesos. Por ejemplo, si el código de la aplicación usa un grupo de conexiones, asigna un rango de ID al mismo subproceso.
Busca transacciones de larga duración
- Usar SHOW ENGINE INNODB STATUS
  
  En la sección TRANSACCIONES, puedes ver todas las transacciones abiertas ordenadas desde las más antiguas.
```
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
```
  Comienza con las transacciones más antiguas y encuentra respuestas a las siguientes preguntas:
  - ¿Durante cuánto tiempo se ejecutan estas transacciones?
  - ¿Cuántas estructuras de bloqueo y bloqueos de filas están presentes?
  - ¿Cuántas entradas de registro de deshacer hay?
  - ¿Cuáles son los hosts y usuarios que se conectan?
  - ¿Cuál es la instrucción de SQL en curso?
- Usar information_schema.innodb_trx
  
  Si SHOW ENGINE INNODB STATUS se truncó, 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 declaraciones de larga duración actuales, puedes decidir detener estas transacciones para reducir la presión sobre el servidor o esperar a que se completen las transacciones críticas. Si las transacciones anteriores no muestran ninguna actividad, ve al paso siguiente para encontrar el historial de transacciones.
Verifica las instrucciones de SQL de las transacciones de larga duración
- Usar performance_schema
  
  Para usar performance_schema, primero debes activarlo. Es un cambio que requiere que reinicies la instancia. Después de activar performance_schema, verifica 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 habilitados, habilítalos:
```
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 conserva los últimos 10 eventos definidos por performance_schema_events_statements_history_size. Por lo general, son suficientes para ubicar 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;
```
- Usa el registro de consultas lento
  
  Para la depuración, puedes capturar todas las consultas que tardaron más de N cantidad de segundos en el registro lento de consultas. Puedes habilitar los registros de consultas lentos si editas la configuración de la instancia en la página de la instancia de la consola de Google Cloud o gcloud CLI y, luego, ves los registros con el visor de registros. en la consola de Google Cloud o gloud CLI.
Verifica la contención de semáforo

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

Para ver la contención de semáforo, 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
...
```
Con cada espera de semáforo, la primera línea muestra el subproceso que está esperando, el semáforo específico y el tiempo que ha esperado. Si hay tiempos de semáforo frecuentes cuando se ejecuta SHOW ENGINE INNODB STATUS de manera repetida, en especial esperas de más de unos segundos, significa que el sistema se encuentra con cuellos de botella de simultaneidad.

Existen diferentes puntos de contención en las distintas cargas de trabajo y parámetros de configuración.

Cuando los semáforos suelen estar en btr0sea.c, la indexación de hash adaptable puede ser la fuente de contención. Intenta inhabilitarla con la consola de Google Cloud o gcloud CLI.
Optimiza las consultas SELECT largas

Primero, revisa la consulta. Identifica el objetivo de la consulta y la mejor manera de obtener los resultados. El mejor plan de consultas es el que minimiza el acceso a los datos.
- Verifica el plan de ejecución de consultas:
```
mysql> EXPLAIN <the query>;
```
Consulta la documentación de MySQL para aprender a interpretar el resultado y evaluar la eficiencia de la consulta.
- Usa el índice correcto
Verifica la columna de claves para ver si se usa el índice esperado. De lo contrario, actualiza las estadísticas del índice:
```
mysql> analyze table <table_name>
```
Aumenta la cantidad 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.
- Usa el índice por completo
Cuando uses un índice de varias columnas, verifica las columnas key_len a fin de ver si el índice se aprovecha por completo para filtrar los registros. Las columnas ubicadas más a la izquierda deben ser comparaciones de igualdad, y el índice se puede usar hasta la primera condición de rango, incluida esta última.
- Usa las sugerencias del optimizador
Otra forma de garantizar que el índice sea correcto es usar Sugerencia de índice y Sugerencia para el orden de unión de tabla.
Evita una lista larga de historial con READ COMMITTED

La lista del historial es la lista de transacciones no borradas en el espacio de tabla para deshacer. El nivel de aislamiento predeterminado de una transacción es REPEATABLE READ, que requiere que una transacción lea la misma instantánea durante su duración. Por lo tanto, una consulta SELECT bloquea definitivamente la eliminación definitiva de los registros que se realizaron desde que comenzó la consulta (o la transacción). Por lo tanto, una lista larga de historial ralentiza el rendimiento de la consulta. Una forma de evitar que se cree una lista larga de historial es cambiar el nivel de aislamiento de transacciones a READ COMMITTED. Con READ COMMITTED, ya no es necesario mantener la lista del historial para obtener una vista de lecturas coherente. Puedes cambiar el nivel de aislamiento de transacciones de manera 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.
Ajusta la configuración del servidor

Hay mucho que decir sobre la configuración del servidor. Si bien la información completa está fuera del alcance de este documento, vale la pena mencionar que el servidor también informa múltiples variables de estado que brindan sugerencias sobre qué tan bien están las configuraciones relacionadas. Por ejemplo:
- Ajusta thread_cache_size si Threads_created/Connections es grande. Una caché de subproceso adecuado reduciría el tiempo de creación de subprocesos y ayudaría a una carga de trabajo altamente simultánea.
- 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 podría marcar una diferencia en un entorno altamente simultáneo.
Finaliza una conexión no deseada

Puedes detener la consulta si la consulta parece no ser válida o si ya no es necesaria. Para obtener información sobre cómo identificar y finalizar el subproceso de MySQL, consulta Administra conexiones de bases de datos.

Por último, si el uso de CPU sigue siendo alto y las consultas forman el tráfico necesario, considera aumentar los recursos de CPU en tu instancia para evitar fallas o tiempo de inactividad en la base de datos.