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
yThreads_connected
Usa la siguiente consulta para ver la cantidad de subprocesos activos:
> SHOW STATUS like 'Threads_%';
Threads_running
es un subconjunto deThreads_connected
. El resto de los subprocesos están inactivos. Un aumento deThreads_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 (comoALTER 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 unaSELECT query
de larga duración, la retiene.
- Si muchos subprocesos muestran
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 tablainformation_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
Utilizar
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?
Utilizar
information_schema.innodb_trx
Si
SHOW ENGINE INNODB STATUS
se truncó, una forma alternativa de examinar todas las transacciones abiertas es usar la tablainformation_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
Utilizar
performance_schema
Para usar
performance_schema
, primero debes activarlo. Es un cambio que requiere que reinicies la instancia. Después de activarperformance_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 esprocesslist_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 ogcloud CLI
y, luego, ves los registros con el visor de registros. en la consola de Google Cloud ogloud 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
largasPrimero, 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
siThreads_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
siTable_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.
- Ajusta
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.