MySQL y MariaDB: dónde está la configuración, el buffer pool de InnoDB, el registro de consultas lentas, un índice, la réplica y el error Too many connections

Qué vas a conseguir

Llevar el día a día de un servidor MySQL o MariaDB en Linux más allá de las copias: saber qué fichero de configuración se está aplicando de verdad, dar al buffer pool de InnoDB la memoria que necesita sin que el kernel mate el proceso, encontrar las consultas que hacen ir lenta la aplicación y arreglar la peor con un índice, leer el estado de la réplica y resolver el ticket clásico: la aplicación da "Too many connections".

Antes de empezar

• MySQL 8.4 LTS, o MariaDB 10.11 u 11.4 LTS, en Ubuntu, Debian o RHEL, con sudo en el servidor.
• Acceso como root del motor por el socket local (sudo mysql o sudo mariadb): en las instalaciones de los paquetes de la distribución, root@localhost entra por auth_socket / unix_socket sin contraseña.
• Las cuentas, la copia programada y la restauración probada están en la guía de MySQL, MariaDB y PostgreSQL: permisos mínimos y copias; el bloqueo por una transacción abierta y el espacio en disco, en la de PostgreSQL, MySQL y SQL Server. Esta guía no los repite.
• Una ventana de cambios para lo que exige reiniciar el servicio y para crear índices en tablas grandes.

1. Dónde está la configuración (y cuál manda)

El servidor lee varios ficheros en orden y el último valor leído gana. Dónde están depende del motor y de la distribución:

›mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"Default options are read from the following files in the given order:/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf›my_print_defaults mysqld--user=mysql--bind-address=10.20.40.11--innodb_buffer_pool_size=8G--max_connections=300--innodb_buffer_pool_size=12G

• my_print_defaults mysqld junta todas las líneas de la sección [mysqld] de todos los ficheros. Si un parámetro sale dos veces, se aplica el último: aquí 12G, no 8G. Es el origen del "lo cambié y no hace nada".
• Pon tus cambios en un fichero propio, /etc/mysql/mysql.conf.d/zz-contoso.cnf o /etc/my.cnf.d/zz-contoso.cnf: se lee al final y una actualización del paquete no lo pisa. En MariaDB, el binario es mariadbd y las secciones [mariadbd] y [mariadb] también valen.
• En MySQL 8, performance_schema.variables_info dice de dónde sale cada valor: SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATH FROM performance_schema.variables_info WHERE VARIABLE_NAME='max_connections'; devuelve GLOBAL + ruta del fichero, PERSISTED (un SET PERSIST, guardado en mysqld-auto.cnf dentro del directorio de datos) o DYNAMIC (un SET GLOBAL que se perderá al reiniciar). MariaDB no tiene esta tabla ni SET PERSIST.

Para lo que es dinámico, SET GLOBAL aplica el cambio en caliente y el fichero lo mantiene tras el reinicio: haz siempre las dos cosas.

2. El buffer pool de InnoDB y la memoria

El buffer pool es la caché de datos e índices de InnoDB, y el parámetro que más influye en el rendimiento. El valor por defecto (128 MB) es para un portátil, no para un servidor.

• En un servidor dedicado a la base de datos, entre el 50 % y el 75 % de la RAM. Con 16 GB, unos 10-12 GB. El resto es para el sistema, las conexiones y la caché de ficheros.
• Si comparte servidor con la aplicación, bastante menos, y se mide.

›SELECT ROUND(@@innodb_buffer_pool_size/1024/1024/1024,1) AS pool_gb, @@max_connections AS max_conn;+---------+----------+| pool_gb | max_conn |+---------+----------+|    12.0 |      300 |+---------+----------+›SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%s';+----------------------------------+-------------+| Variable_name                    | Value       |+----------------------------------+-------------+| Innodb_buffer_pool_read_requests | 98411250117 || Innodb_buffer_pool_reads         | 1822304     |+----------------------------------+-------------+›SET GLOBAL innodb_buffer_pool_size = 12*1024*1024*1024;

• Innodb_buffer_pool_reads son las lecturas que no estaban en memoria y fueron a disco. Frente a read_requests, aquí menos del 0,01 %: el pool es suficiente. Si ese porcentaje sube y el disco va al 100 % de uso en iostat -x, falta memoria.
• El tamaño se cambia en caliente (MySQL 5.7+ y MariaDB 10.2+) y se ajusta a un múltiplo de innodb_buffer_pool_chunk_size. SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status' dice cuándo ha terminado. Pon el mismo valor en el fichero.

Lo que ve el sistema:

›ps -o pid,rss,cmd -C mysqld    PID   RSS CMD   1184 13921408 /usr/sbin/mysqld›free -g               total        used        free      shared  buff/cache   availableMem:              15          13           0           0           1           1›journalctl -k --since "-7d" | grep -i "out of memory"nov 01 03:14:22 srv-db-01 kernel: Out of memory: Killed process 1184 (mysqld) total-vm:16123400kB, anon-rss:14810112kB

Un mysqld muerto por el OOM killer del kernel aparece como un reinicio sin motivo en el log de errores (/var/log/mysql/error.log en Debian y Ubuntu, /var/log/mysqld.log en RHEL; MariaDB suele escribir en el journal: journalctl -u mariadb). La causa casi nunca es solo el buffer pool: es el pool más cada conexión con sus búferes (sort_buffer_size, join_buffer_size, read_buffer_size, tablas temporales hasta tmp_table_size). Con 300 conexiones activas a la vez, unos pocos MB por conexión son varios GB. Por eso max_connections y la memoria se miran juntos.

3. max_connections

• Por defecto, 151 en los dos motores. Es dinámico: SET GLOBAL max_connections = 300; y el valor en el fichero.
• MySQL y MariaDB guardan una conexión más para una cuenta con CONNECTION_ADMIN o SUPER (en MariaDB 10.5+, CONNECTION ADMIN): es la que te deja entrar como root cuando la aplicación ya ha llenado todas. Si la aplicación usa una cuenta con esos privilegios, también se come esa.
• En MySQL 8.4 puedes definir además una interfaz de administración (admin_address y admin_port, por defecto 33062) que no cuenta para el límite; requiere reinicio para activarla.
• El límite real lo pone también el sistema: cada conexión usa descriptores de fichero. Si el log de arranque dice Changed limits: max_open_files o max_connections, el servicio de systemd necesita LimitNOFILE más alto (un override con sudo systemctl edit mysql).

›SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections','Connection_errors_max_connections');+-----------------------------------+-------+| Variable_name                     | Value |+-----------------------------------+-------+| Connection_errors_max_connections | 0     || Max_used_connections              | 142   || Threads_connected                 | 61    || Threads_running                   | 3     |+-----------------------------------+-------+

Max_used_connections es el máximo desde el último arranque y Connection_errors_max_connections, cuántas conexiones se rechazaron por el límite. Llévalos a la monitorización: si el máximo se acerca al límite, el ticket del paso 7 está de camino.

4. El registro de consultas lentas

Se activa en caliente, sin reinicio:

›SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';›SET GLOBAL long_query_time = 1;›SET GLOBAL slow_query_log = ON;

[mysqld]innodb_buffer_pool_size = 12Gmax_connections         = 300slow_query_log          = ONslow_query_log_file     = /var/log/mysql/mysql-slow.loglong_query_time         = 1

• long_query_time está en segundos (admite decimales, 0.5) y por defecto es 10: casi nada llega a 10 segundos, por eso el registro suele estar vacío aunque esté activo. Ojo: el SET GLOBAL vale para las conexiones nuevas; las del pool de la aplicación siguen con el valor anterior hasta que se reconectan.
• En MariaDB 10.11 y posteriores los nombres nuevos son log_slow_query, log_slow_query_file y log_slow_query_time; los antiguos siguen funcionando como alias.
• El fichero crece rápido con un umbral bajo: entra en logrotate (Debian y Ubuntu ya traen /etc/logrotate.d/mysql-server o mariadb). No actives log_queries_not_using_indexes en producción sin un límite: registra todo lo que hace un recorrido completo, aunque tarde 1 ms.

Leerlo a mano no sirve: hay miles de entradas casi iguales. Dos herramientas las agrupan por consulta, con los valores sustituidos por N o ?:

›sudo mysqldumpslow -s t -t 3 /var/log/mysql/mysql-slow.logReading mysql slow query log from /var/log/mysql/mysql-slow.logCount: 1840  Time=2.41s (4434s)  Lock=0.00s (0s)  Rows=12.0 (22080), crm_app[crm_app]@[10.20.30.21]  SELECT * FROM pedidos WHERE cliente_id = N ORDER BY fecha DESC LIMIT NCount: 96  Time=8.12s (779s)  Lock=0.00s (0s)  Rows=1.0 (96), informes[informes]@[10.20.30.40]  SELECT COUNT(*) FROM pedidos WHERE estado = 'S' AND fecha >= 'S'›sudo pt-query-digest /var/log/mysql/mysql-slow.log --limit 3 | sed -n '/Profile/,/^$/p'# Profile# Rank Query ID                     Response time   Calls R/Call V/M# ==== ============================ =============== ===== ====== =====#    1 0x6A1E3F0C2D9B47A1E0F43B2C... 4434.4 71.2%   1840 2.4100  0.31 SELECT pedidos#    2 0x91C47D2B0E3A6F1C55D2A0B8... 779.5 12.5%     96 8.1200  1.02 SELECT pedidos

• -s t ordena por tiempo total: la consulta de 2,4 segundos que se ejecuta 1.840 veces pesa más que la de 8 segundos que corre 96. Arreglar la primera es lo que nota el usuario.
• En MariaDB, la herramienta es mariadb-dumpslow (con mysqldumpslow como enlace en muchas distribuciones). pt-query-digest viene en el paquete percona-toolkit y sirve para los dos motores.

5. EXPLAIN y un índice

›EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 4187 ORDER BY fecha DESC LIMIT 20\G           id: 1  select_type: SIMPLE        table: pedidos         type: ALLpossible_keys: NULL          key: NULL         rows: 1843221     filtered: 10.00        Extra: Using where; Using filesort

Se lee de arriba abajo:

• type: ALL y key: NULL: recorre la tabla entera (1,8 millones de filas) para devolver 20. Es lo que hay que quitar.
• Using filesort: ordena en memoria o en disco después de leer. Un índice que empiece por la columna del WHERE y siga por la del ORDER BY resuelve las dos cosas.

›SHOW INDEX FROM pedidos;›ALTER TABLE pedidos ADD INDEX idx_pedidos_cliente_fecha (cliente_id, fecha), ALGORITHM=INPLACE, LOCK=NONE;Query OK, 0 rows affected (41.88 sec)›EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 4187 ORDER BY fecha DESC LIMIT 20\G         type: refpossible_keys: idx_pedidos_cliente_fecha          key: idx_pedidos_cliente_fecha         rows: 37        Extra: Backward index scan

• ALGORITHM=INPLACE, LOCK=NONE crea el índice sin bloquear las escrituras (DDL en línea de InnoDB). Si el motor no puede hacerlo así, da un error en lugar de bloquear la tabla en silencio: mejor que lo diga.
• Aun así consume disco y E/S y, si hay réplica, la réplica lo aplica después y acumula retraso mientras tanto. En tablas grandes, en la ventana de cambios.
• EXPLAIN ANALYZE (MySQL 8) ejecuta la consulta y da tiempos reales; en MariaDB el equivalente es ANALYZE SELECT .... Los dos ejecutan la consulta: nunca con un UPDATE o DELETE en producción.
• Comprueba el efecto el día siguiente con mysqldumpslow: la consulta tiene que haber desaparecido del primer puesto. Y no crees índices "por si acaso": cada uno ralentiza las escrituras.

6. El estado de la réplica

El retraso y los dos hilos en Yes están en la guía de permisos mínimos y copias. Lo que conviene tener claro aquí es qué comando y qué nombres usa cada motor:

En MariaDB los campos siguen con los nombres antiguos aunque el comando sea SHOW REPLICA STATUS: un script de monitorización que busque Seconds_Behind_Source en MariaDB no encuentra nada y parece que todo va bien.

›SHOW REPLICA STATUS\G             Source_Host: 10.20.40.11      Replica_IO_Running: Yes     Replica_SQL_Running: No   Seconds_Behind_Source: NULL          Last_SQL_Errno: 1062          Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). ... Duplicate entry '88123' for key 'pedidos.PRIMARY'       Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-4421907

• Un hilo SQL parado por un duplicado casi siempre significa que alguien escribió en la réplica. La réplica debe estar con read_only = ON y super_read_only = ON (MySQL); en MariaDB, read_only = ON, teniendo en cuenta que quien tenga el privilegio READ_ONLY ADMIN (incluido en SUPER hasta MariaDB 11.0) sigue pudiendo escribir.
• No saltes el error a ciegas (sql_replica_skip_counter o una transacción vacía con GTID): puedes dejar la réplica con datos distintos sin que nada avise. Anota la fila, compara origen y réplica y, si hay dudas, se reconstruye la réplica desde una copia nueva. Esa decisión, con el responsable de la aplicación.
• Antes de conmutar o de sacar copias de la réplica, los dos hilos en Yes y el retraso en 0.

7. El ticket: "la aplicación da 'Too many connections'"

Lunes, 9:10. El CRM no carga para nadie. En el log de la aplicación (Java) se repite: Data source rejected establishment of connection, message from server: "Too many connections". Desde un cliente:

›mysql -h srv-db-01.contoso.local -u crm_app -p crmERROR 1040 (08004): Too many connections

1. Entra igualmente, como root por el socket local: usa la conexión reservada del paso 3.

›sudo mysql›SHOW GLOBAL STATUS LIKE 'Threads_connected';+-------------------+-------+| Threads_connected | 301   |+-------------------+-------+›SELECT user, SUBSTRING_INDEX(host,':',1) AS cliente, command, COUNT(*) AS n, MAX(time) AS max_s FROM performance_schema.processlist GROUP BY 1,2,3 ORDER BY n DESC LIMIT 5;+-----------+-------------+---------+-----+-------+| user      | cliente     | command | n   | max_s |+-----------+-------------+---------+-----+-------+| crm_app   | 10.20.30.22 | Sleep   | 212 | 26840 || crm_app   | 10.20.30.21 | Sleep   |  61 |   410 || crm_app   | 10.20.30.21 | Query   |   6 |     2 || informes  | 10.20.30.40 | Sleep   |  18 |  3311 || root      | localhost   | Query   |   1 |     0 |+-----------+-------------+---------+-----+-------+

En MariaDB, la misma consulta con information_schema.processlist (en MySQL 8.4 también existe, pero está obsoleta). SHOW FULL PROCESSLIST da lo mismo sin agrupar, útil cuando son pocas.

1. Qué dice la tabla: no hay consultas pesadas (6 en Query, de 2 segundos); hay 212 conexiones dormidas desde un solo servidor de aplicación, 10.20.30.22, la más antigua de hace 7 horas y media. Ese servidor no devuelve las conexiones o tiene un pool mal dimensionado. Las causas habituales:

• Un nodo nuevo de la aplicación (o un segundo contenedor) con el mismo pool: 2 nodos × pool de 150 = 300, justo el límite.
• Un fallo en el código que abre conexiones fuera del pool y no las cierra; se nota porque el número crece cada hora sin bajar nunca.
• wait_timeout en su valor por defecto, 28.800 segundos (8 horas): el servidor tarda 8 horas en cerrar una conexión abandonada.

1. Devuelve el servicio, cerrando solo las dormidas de ese origen y con más de 10 minutos:

›sudo mysql -N -e "SELECT CONCAT('KILL ', id, ';') FROM performance_schema.processlist WHERE user='crm_app' AND host LIKE '10.20.30.22:%' AND command='Sleep' AND time > 600" > /tmp/kill-crm.sql›wc -l /tmp/kill-crm.sql208 /tmp/kill-crm.sql›sudo mysql < /tmp/kill-crm.sql›sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"+-------------------+-------+| Threads_connected | 93    |+-------------------+-------+

Reiniciar la aplicación de srv-app-01/srv-app-02 también las libera, pero corta a los usuarios conectados; reiniciar MySQL las corta a todos y no arregla nada: en unas horas vuelven.

1. Que el servidor cierre lo abandonado: wait_timeout a un valor razonable, por ejemplo 600 segundos, en caliente y en el fichero:

›SET GLOBAL wait_timeout = 600;# y en zz-contoso.cnf: wait_timeout = 600

Afecta a las conexiones nuevas. Y tiene una condición: el pool de la aplicación tiene que renovar sus conexiones antes de ese tiempo (en HikariCP, maxLifetime e idleTimeout por debajo de 600 segundos). Si no, la aplicación intenta usar una conexión que el servidor ya cerró y el error cambia a Communications link failure o MySQL server has gone away. Pásalo al equipo de la aplicación antes de bajar el valor, no después.

1. El pool, dimensionado: la suma de los pools de todos los nodos y herramientas tiene que caber en max_connections con margen. Con 2 nodos de CRM, la herramienta de informes y las copias: 2 × 40 + 20 + 5 = 105, holgado con 300. Un pool de 150 por nodo casi nunca hace falta: con Threads_running en 3, la aplicación usa a la vez muy pocas conexiones.

1. Subir el límite, solo si hace falta y con números: si después de ajustar los pools la suma real sigue rozando el límite, se sube, pero comprobando la memoria del paso 2 (buffer pool + conexiones × búferes) y el LimitNOFILE del servicio. SET GLOBAL max_connections = 400; y el fichero; en MySQL, SET PERSIST hace las dos cosas a la vez. Subirlo a 2.000 "para que no vuelva a pasar" solo cambia el error por un OOM a las 3 de la mañana.

En el ticket: hora, Threads_connected y de dónde venían las conexiones, cuántas se cerraron, el cambio en wait_timeout y en el pool, y la alerta nueva: Threads_connected > 80 % de max_connections durante 5 minutos.

Siguientes pasos

• Monitoriza Threads_connected, Max_used_connections, Connection_errors_max_connections, el retraso de la réplica y el tamaño del registro lento con mysqld_exporter y la guía de Grafana.
• Si la aplicación tiene muchos nodos o funciones que abren y cierran conexiones sin parar, un ProxySQL o MaxScale delante de la base de datos hace de pool común.
• La guía de PostgreSQL cubre las mismas tareas en el otro motor: autenticación, conexiones y consultas pesadas.

¿Falta algo o en tu entorno es distinto? Escríbeme y lo corrijo.