El sitio va lento: qué mirar en MySQL y PostgreSQL

«El sitio va lento» acaba casi siempre en la base de datos. La revisión lleva unos minutos si se hace en el orden correcto: primero lo que se ve durante la propia ralentización, después lo que se ha ido acumulando durante semanas.

Mire mientras ocurre el problema

La regla principal: la información más valiosa está disponible precisamente cuando todo va lento. El primer comando para MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Para PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Verá qué se está ejecutando ahora mismo y desde hace cuánto. La respuesta suele ser inmediata: una consulta pesada retiene a las demás, o cien consultas iguales esperan a que se libere un bloqueo.

Las conexiones

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Si Max_used_connections se acerca a max_connections, la aplicación recibe de vez en cuando el rechazo «demasiadas conexiones». Subir el límite no es lo primero que hay que hacer: cada conexión ocupa memoria, y aumentarlo en un servidor al que ya le falta solo acelera el camino al swap. Averigüe antes por qué no se liberan las conexiones: normalmente una consulta larga o la ausencia de un pool en la aplicación.

Conviene mirar lo mismo desde el otro lado: un aumento brusco de conexiones puede venir no de una popularidad creciente sino de bots golpeando una página pesada: una búsqueda, un filtro de catálogo, un generador de informes.

Las consultas lentas

Sin registro de consultas lentas no se puede avanzar. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Un día después:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, en postgresql.conf:

log_min_duration_statement = 1000

Después, la extensión pg_stat_statements, que muestra directamente el tiempo acumulado por consulta:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Fíjese bien: lo que importa no es la consulta más larga sino aquella con mayor tiempo acumulado. Una consulta de 50 milisegundos ejecutada mil veces por minuto hace más daño que un informe de diez segundos una vez al día.

El ajuste que más se olvida

En MySQL y MariaDB es el tamaño del pool de búferes de InnoDB. El valor por omisión son 128 megabytes y no ha cambiado en décadas. En un servidor cuya base ocupa varios gigabytes, eso significa lectura constante desde el disco en lugar de desde la memoria.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Una referencia razonable para un servidor dedicado a bases es la mitad de la memoria; para un servidor donde además corren el servidor web y PHP, la cuarta parte, con cuidado de no provocar swap. Ese único ajuste suele aportar más que una semana de optimización de consultas.

Dos cosas que devoran el disco en silencio

Los registros binarios de MySQL. Sirven para la replicación y la recuperación a un punto en el tiempo, crecen sin parar y por omisión a veces no se eliminan nunca. Para comprobarlo:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Si no tiene replicación y no usa recuperación a un punto en el tiempo, fije la retención en unos pocos días razonables.

Los archivos WAL de PostgreSQL con una ranura de replicación olvidada. Un caso menos conocido y más peligroso: si existe una ranura de replicación cuyo consumidor ya no se conecta, PostgreSQL está obligado a conservar todos los registros desde la desconexión, y los conservará hasta llenar el disco.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Una ranura inactiva que nadie necesita hay que eliminarla. Es una de las pocas situaciones en que una base de datos lleva el disco a cero con seguridad.

Tamaño y crecimiento

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Lo útil no es tanto la cifra actual como la velocidad a la que cambia. Una base que se ha duplicado en un mes sin el correspondiente aumento de tráfico suele significar que alguna tabla de registro se escribe y nunca se limpia: sesiones, historial de tareas, registro de eventos de una extensión.

Seguridad en dos líneas

Ya que tiene abierta la consola de la base, merecen la pena dos comprobaciones. Primero, si la base es accesible desde fuera (ss -tulpn | grep 3306): casi nunca debe estarlo. Segundo, las cuentas que pueden conectarse desde cualquier dirección:

mysql -e "SELECT user, host FROM mysql.user"

Las entradas con host = '%' significan «conexiones desde cualquier parte». Si además el puerto está abierto, entre la base e internet solo hay una contraseña.

Las copias de seguridad

Un último punto. La existencia de un archivo de copia no garantiza nada; solo lo garantiza una restauración probada. Un volcado que nunca se ha restaurado tiene la misma probabilidad de resultar truncado, tomado con un error de bloqueo o de contener la base equivocada. Eso se comprueba de antemano y en una máquina aparte, no el día en que la copia hace falta.

Para la vigilancia diaria bastan tres cifras: el número de conexiones, la cantidad de consultas lentas al día y el tamaño de la base. Las tres cambian despacio y de forma previsible, y cualquier movimiento brusco de una de ellas merece un vistazo. Cómo se ven en una página lo muestra la demostración de abajo.