Cómo arreglar un excesivo uso de CPU de MySQL

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:

• Activa el slow query log de MySQL: 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.

---