Cómo arreglar un excesivo uso de CPU de MySQL

LinuxMySQL

Recientemente me di cuenta de que una serie de bases de datos que creía optimizadas mediante el uso de índices comenzaron a consumir tiempo de uso de la CPU de un servidor hasta límites insospechables. Incluso los usuarios dejaron de poder acceder al servidor cuando tenía cierto número de visitas debido a que se superaba el timeout o tiempo de espera para la ejecución de scripts. Tenía varios proyectos hospedados en el servidor y el gran problema era encontrar esa consulta o consultas problemáticas.

El caso es que existen diferentes métodos mediante los cuales podrás encontrar la consulta problemática. En este tutorial intentaré recopilarlos, comenzando por el que más útil me ha resultado.

Encuentra las consultas más lentas

No siempre podrás llegar a la causa del problema de entrada. De hecho, en ocasiones quizás la encuentres tras dar primero con la consulta problemática. Sin embargo, nunca está demás aplicar algo de sentido común a la hora de entender lo que ha ocurrido con tus bases de datos. Pero el sentido común no suele dar mucho de sí mismo cuando te encuentras en una situación problemática y la empresa para la que trabajas está perdiendo clientes.

Vamos a explicar los pasos a seguir para encontrar las consultas MySQL problemáticas que causan un alto uso de la CPU, incluso cercano al 100%, o que pueden ralentizar tu servidor. Vamos a ver diversos métodos que podrán ayudarte.

Consulta todos los procesos MySQL

Lo primero que podrías hacer es consultar la lista de procesos MySQL. Las consultas se ponen  a la cola para ser ejecutas en caso de superarse el número máximo de consultas que pueden ejecutarse en paralelo.  Si ves que una consulta se repite muchas veces, seguramente sea esa la consulta problemática.

Para ver las consultas en ejecución, conéctate a MySQL desde la terminal de comandos como usuario root mediante el siguiente comando:

mysql -u root

Luego usa el siguiente comando, que mostrará un listado con los procesos MySQL en ejecución, junto con la consulta que está siendo ejecutada:

SHOW FULL PROCESSLIST;

Este otro comando también podría ser de utilidad, mostrando el estado del motor InnoDB de MySQL:

SHOW ENGINE INNODB STATUS;

Estos comandos suelen bastar para encontrar las consultas MySQL problemáticas. Eso sí, todavía tendrás que investigar acerca del archivo en el que se ejecuta la consulta.

Consulta el slow query log de MySQL

MySQL incorpora una funcionalidad que permite crear un log con aquellas consultas más lentas. Además de guardar estas consultas en un log, también podrán almacenarse en una tabla MySQL, aunque por lo general, se recomienda usar un simple archivo como log. Podrás configurar el margen de duración de las consultas a partir del cual se almacenarán en el log. De este modo, echando un vistazo rápido al log podrás encontrar aquellas consultas más lentas de tu base de datos.

En la siguiente guía podrás encontrar cómo activar el slow query log de MySQL:

Una vez haya activado el log, podrás encontrar la fecha de ejecución de las consultas, su duración o el número de veces que se han ejecutado. Una vez hayas estudiado el log, no te quedará otra que, en efecto, usar algo de sentido común y, en base a las tablas usadas, pensar en cuál es el script en el que se encuentras el problema

Consulta la lista de usuarios MySQL conectados

Si existen varias cuentas de usuario en tu servidor y no tienes el control sobre algunas de ellas, es posible que algún usuario esté ejecutado algún script indeseado, o también podría darse el caso de que su cuenta haya sido hackeada. Estos dos últimos escenarios suelen darse si vendes cuentas de hosting compartido.

Para encontrar quiénes son los posibles usuarios problemáticos, sigue los pasos que se describen a continuación:

  1. Primero conéctate al MySQL como usuario root desde la terminal de comandos:
    mysql -u root

    Si se te pide una contraseña, prueba a conectarte desde la cuenta root de tu servidor o a usar el siguiente comando, que te pedirá la contraseña:

    mysql -u root -p
  2. Luego, una vez conectado a MySQL, ejecuta la siguiente consulta:
    SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
           GROUP_CONCAT(DISTINCT user) AS users,
           COUNT(*) AS threads
    FROM information_schema.processlist
    GROUP BY host_short
    ORDER BY COUNT(*), host_short;

La consulta anterior mostrará por pantalla los usuarios con conexiones activas, así como el número de hilos que utilizan.

Desactiva las conexiones persistentes de MySQL

La idea de las conexiones persistentes es que una misma conexión entre un cliente y una base de datos MySQL pueda ser reutilizada, en lugar de ser creada y destruida con cada consulta.

El problema de las conexiones persistentes es que podrían hacer que el número máximo de conexiones se alcance muy pronto, provocando caídas en algunos de tus servicios, al no ser capaces de conectarse a MySQL. Por ello, esta opción debe ser usada con cautela.

Si por ejemplo usas PHP, puedes desactivar las conexiones persistentes editando el archivo php.ini, que es el archivo de configuración de PHP. Si no sabes en dónde encontrarlo, consulta la guía en la que explico cómo encontrar el archivo php.ini, ya que su ubicación variará en función de tu sistema.

Una vez estés editando el archivo, debes desactivar la opción mysql.allow_persistent para desactivar las conexiones persistentes:

mysql.allow_persistent = Off

Puedes encontrar más información acerca de las conexiones persistentes aquí.

Arregla las consultas más lentas de MySQL

En muchas ocasiones podrás optimizar tus consultas mediante índices, capaces de acelerar exponencialmente tus consultas. Sin embargo, también podría darse el caso de que ya estén optimizadas, estando el problema en los propios recursos de tu servidor o en problemas de arquitectura. Si el tráfico se ha incrementado recientemente, quizás necesites mejorar la CPU o la memoria del mismo, o en caso de que el tráfico sea considerable, usar un balanceador de carga.

Evita que MySQL haga un uso excesivo de la CPU

Si no quieres que este problema te pille por sorpresa, siempre puedes usar alguna aplicación de monitorización como NewRelic, AppDynamics o EverSQL. Estas herramientas incluyen funcionalidades que permiten cosas como:

  • Detectar las consultas más lentas.
  • Optimizar automáticamente las consultas.
  • Priorizar aquellas consultas que necesitan ser optimizadas.
  • Detección de índices redundantes.
  • Detección de aquellas tablas que deberían tener un índice y no lo tienen.

Dependiendo de la aplicación o framework que uses, siempre podrás usar ciertas herramientas en particular que permitan monitorizar tus aplicaciones. Si por ejemplo usas Laravel, puedes usar herramientas como Laravel Flare para obtener una notificación cuando ocurre algún error en tu sistema.

Dicho de otro modo, más vale prevenir que curar. En mi caso, el problema estaba en una sencilla consulta a la tabla de productos de una web de WordPress que usaba WooCommerce. Los productos de la tienda eran miles y en consecuencia la tabla postmeta de metadatos de WordPress contenía millones de registros.


Avatar de Edu Lazaro

Edu Lázaro: Ingeniero técnico en informática, actualmente trabajo como desarrollador web y programador de videojuegos.

👋 Hola! Soy Edu, me encanta crear cosas y he redactado esta guía. Si te ha resultado útil, el mayor favor que me podrías hacer es el de compatirla en Twitter 😊

Si quieres conocer mis proyectos, sígueme en Twitter.

3 comentarios en “Cómo arreglar un excesivo uso de CPU de MySQL

  1. Hola, espero se encuentre bien, tengo un gran problema con mi servidor se cuelga a cada momento nose si es problema de red, php o de mysql
    la version es un mysql 5.5 en ubuntu 14
    php 5 y apache 2
    la base que estoy trabajando pesa en MB | 4.848.37 | y nose que hacer es un caos

    1. Puedes probar a ejecutar el comando top para comprobar el uso de la memoria y de la CPU. Ejecuta también la consulta SHOW FULL PROCESSLIST. Si ves muchas consultas en ejecución, quizás tengas que optimizar algunas consultas o la configuración de MySQL. Por cierto, usar PHP5 es ya un riesgo; deberías estar usando PHP8 o como mínimo PHP7. Aunque supongo que en ocasiones no queda otra.

  2. Hola, cuando hacemos un mytop en un debian y el ratio now esta en rojo ¿que podemos hacer o ver?

    tengo picos de consumo de mysql pero no hay mucho cuando hago SHOW FULL PROCESSLIST;

    Fijat en el ratio

    Id User Host/IP DB Time % Cmd State Query
    — —- ——- — —- – — —– ———-
    1 system u 0 0.0 Daemon InnoDB purge wo
    2 system u 0 0.0 Daemon InnoDB purge wo
    4 system u 0 0.0 Daemon InnoDB purge wo
    3 system u 0 0.0 Daemon InnoDB purge co
    5 system u 0 0.0 Daemon InnoDB shutdown
    15154 0 0.0 Sleep
    15388 8 0 0.0 Sleep
    15413 root localhost 0 0.0 Query Init show full processlist

    MySQL on localhost (10.3.39) up 0+03:51:06 [17:58:25]
    Queries: 3.1M qps: 234 Slow: 0.0 Se/In/Up/De(%): 162/01/03/01
    Sorts: 198 qps now: 602 Slow qps: 0.0 Threads: 3 ( 6/ 6) 164/03/00/01
    Cache Hits: 2.2M Hits/s: 165.4 Hits now: 415.4 Ratio: 43.6%
    Ratio now: 42.0%
    Handler: (R/W/U/D) 1640/ 19/ 0/ 3 Tmp: R/W/U: 328/ 242/ 0
    ISAM Key Efficiency: 50.0% Bps in/out: 60.6k/742.8k Now in/out: 169.9k/ 2.0M

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

“- Hey, Doc. No tenemos suficiente carretera para ir a 140/h km. - ¿Carretera? A donde vamos, no necesitaremos carreteras.”