Gestión eficiente de Pools de Conexiones en MySQL con Knex.js y Tarn.js

Las conexiones a bases de datos son un recurso crítico que debe gestionarse correctamente para garantizar el rendimiento y estabilidad de las aplicaciones. Una mala gestión de las conexiones puede provocar cuellos de botella, tiempos de respuesta elevados, agotamiento de recursos y en el peor caso, fallos en cascada.

Problemas comunes por mala gestión de conexiones

Algunos problemas típicos que surgen cuando no se gestionan bien las conexiones:

• Agotamiento de las conexiones disponibles, provocando errores al intentar abrir nuevas conexiones.

• Acumulación de conexiones sleeping/idle que consumen recursos innecesarios como memoria y sockets.

• Leaks de conexiones por no liberarlas correctamente. Esto agota el pool rápidamente.

• Alto consumo de CPU y memoria al mantener un pool de conexiones muy grande.

• Degradación del rendimiento al reabrir conexiones constantemente en lugar de reutilizarlas.

• Latencia elevada causada por esperas en la adquisición de conexiones.

Configuración de timeouts de inactividad

MySQL provee parámetros para controlar el tiempo máximo que una conexión puede permanecer inactiva antes de ser cerrada:

wait_timeout: Tiempo de inactividad en segundos antes de que MySQL cierre la conexión. El valor por defecto es 28800 segundos (8 horas).

interactive_timeout: Tiempo de inactividad para conexiones que usen un cliente interactivo como el cliente MySQL command-line. El valor por defecto es 28800 segundos.

Es recomendable reducir estos valores para forzar el cierre más agresivo de conexiones inactivas:

SET GLOBAL wait_timeout = 180; 
SET GLOBAL interactive_timeout = 180;

También se puede configurar a nivel de sesión:

SET SESSION wait_timeout = 180;
SET SESSION interactive_timeout = 180; 

Configuración del pool de conexiones

Las conexiones se deben tomar de un pool y liberar lo antes posible en lugar de abrir y cerrar para cada operación. Esto reduce la sobrecarga de crear nuevas conexiones constantemente.

Knex.js utiliza tarn.js internamente para manejar el pool de conexiones. Tarn implementa un pool de conexiones eficiente y altamente configurable que luego es expuesto por Knex.

En Knex.js el pool se configura defininedo el objeto pool con las siguientes opciones (aunque hay más opciones que explorar):

• min: Número mínimo de conexiones en el pool. Por defecto 2.
• max: Número máximo de conexiones en el pool. Por defecto 10.
• idleTimeoutMillis: Tiempo máximo en milisegundos que una conexión puede permanecer inactiva antes de ser cerrada.

Ejemplo:

const knex = require('knex')({
  // ...
  pool: {
    min: 2,
    max: 10,
    idleTimeoutMillis: 30000,
    afterCreate: (conn: any, done: any) => {
        if (envVars.DB_CLIENT === 'mysql') {
            conn.query('SET SESSION wait_timeout = 180', (err: any) => {
                if (err) {
                   console.error(err);
                }

                done(null, conn);
            });
       }
    }
  }
});

Otras configuraciones importantes en el pool son queueLimit y acquireTimeoutMillis.

queueLimit: Esta opción controla el máximo de solicitudes de conexión que se pueden encolar cuando no hay conexiones disponibles en el pool. Por defecto es 0, lo cual significa ilimitado.

Se recomienda establecer un queueLimit razonable (ej. 50-100) para evitar que la cola crezca indefinidamente en situaciones de sobrecarga. Esto ayuda a fail-fast y evitar latencias extremas o out of memory cuando la cola crece descontroladamente.

acquireTimeoutMillis: Indica el tiempo máximo en milisegundos que se esperará para obtener una conexión antes de lanzar un error de timeout. Por defecto no existe timeout.

Se recomienda establecer un timeout prudente (ej. 5000 ms) para evitar congelamientos de la aplicación cuando no se puede obtener una conexión disponible. También ayuda a identificar más rápidamente cuando el pool está subdimensionado.

Idealmente se quiere evitar timeouts, por lo que hay que ajustar proactivamente el tamaño del pool para que pueda manejar la carga pico sin quedarse sin conexiones disponibles. Los timeouts deben ser una red de seguridad.

Adicionalmente conocer el número de conexiones activas es una métrica importante para para dimensionar adecuadamente el tamaño del pool, aquí algunas formas de obtener esa métrica:

• Muchos clientes de base de datos (como Knex.js) exponen el número de conexiones activas a través de métodos o propiedades. Por ejemplo, en Knex puedes acceder a knex.client.pool.activeCount() para obtener este dato.

• Desde la base de datos MySQL se puede ejecutar SHOW STATUS WHERE variable_name = 'Threads_connected'; para ver las conexiones activas en un momento determinado.

• Si estás usando un sistema de monitoreo como MySQL Enterprise Monitor, Datadog, etc. puedes crear un dashboard que grafique esta métrica en el tiempo.

• Herramientas como 'show processlist' y comandos SQL permiten inspeccionar que queries se están ejecutando y las conexiones asociadas.

• Se puede crear un script o endpoint de health check que periódicamente registre el conteo de conexiones activas.

Lo ideal es monitorizar esta métrica de manera proactiva durante un período representativo de la operación normal y picos esperados. Esto permitirá determinar patrones y valores máximos para dimensionar el pool.

Determinando el tamaño del pool

No existe una fórmula exacta para calcular el tamaño óptimo del pool. Se recomienda:

• Empezar con un tamaño conservador como 5-10 conexiones.

• Ir incrementando progresivamente el límite max observando métricas como waiting time y borrow timeouts.

• El pool debe ser lo suficientemente grande para manejar la carga pico sin timeouts.

• Una buena medida es establecer el max en entre 2-3 veces las conexiones activas durante la carga normal.

• No mantener un pool extremadamente grande ya que desperdicia recursos.

• Analizar incrementos en activas conexiones durante cargas pico para decidir cuanto debe crecer el pool max.

• Si existen varios servicios, cada uno puede tener su propio pool si la carga es muy distinta.

Monitorizando activamente las métricas se puede ir ajustando el tamaño para encontrar un balance ideal para cada aplicación.

Patrones para uso eficiente de conexiones

• Utilizar conexiones solo para realizar una única transacción o unidad de trabajo atómica. Luego liberar inmediatamente.
• No re-adquirir conexiones dentro de un loop. Hacerlo una vez y reutilizar.
• Liberar la conexión en un bloque finally para evitar leaks por excepciones.
• Para operaciones async/await usar transactions para automatizar la adquisición y liberación.

Métricas para monitorizar conexiones

Es clave monitorear métricas del pool para identificar y solucionar problemas:

• active: Número de conexiones en uso
• idle: Número de conexiones inactivas
• waiting: Número de solicitudes esperando por una conexión
• borrow_timeouts: Número de timeouts al obtener una conexión
• total: Número total de conexiones creadas

Herramientas como PMM y MySQL Enterprise Monitor permiten monitorear y alertar basado en umbrales.

Troubleshooting y tunning

Algunas técnicas comunes de troubleshooting:

• Identificar spikes de connections active y waiting. Puede indicar pool pequeño.
• Detectar crecimiento constante de connections total. Puede indicar leak de conexiones.
• Comparar ratio entre connections active e idle. Una alta idle puede indicar tiempos de timeout muy conservadores.
• Analizar métricas luego de cambiar configuraciones para validar impacto.

El tuneo frecuente de los parámetros del pool y timeouts puede ayudar a maximizar rendimiento y estabilidad.

Conclusión

La gestión de conexiones es un pilar fundamental para el buen rendimiento de aplicaciones database-centric. Identificar la configuración óptima, monitorear métricas, detectar y solventar problemas tempranamente evitarán inestabilidad y degradación. Knex.js y otros clientes modernos proveenexcelentes primitivas para un manejo eficiente de conexiones.

Happy coding! :D

Photo by michael podger on Unsplash