Optimizar pool de conexiones de base de datos
- Daniel Payero
Analytics
Hay casos donde se cuenta con una única instancia de Thuban y se tienen volumen muy alto de transacciones concurrentes, especialmente se dan en ambientes con un alto grado de integración por servicios web. Esto puede llevar a un Connection Pool Exhaustion de las conexiones a la base de datos.
Los escenarios más comunes donde se llega a un Connection Pool Exhaustion son:
Alta cantidad de solicitudes concurrentes: Se da cuando la aplicación sufre un pico de tráfico y consumo de hilos de manera simultánea que solicitan conexiones a la base de datos. En estos casos la cantidad de conexiones puede ser insuficiente llevando a un engolamiento de solicitudes y eventual caída del sistema.
Consultas de larga duración: Consultas que toman mucho tiempo en ejecutarse y capturan conexiones por tiempos extendidos de tiempo reduciendo así la cantidad de conexiones disponibles para otros.
Fugas de conexiones: Esto se da cuándo las conexiones no son correctamente cerradas y se mantienen ocupadas por un tiempo mayor. Esto reduce la cantidad disponible y con el tiempo lleva a un agotamiento de conexiones disponibles.
Tamaño del Pool mal configurado: El tamaño máximo de conexiones a tomar y mantener de la base de datos para se utilizados es inferior al requerido para el volumen que la aplicación van a manejar. Esto lleva a que la cantidad de conexiones se agote rápidamente.
Deadlocks: La presencia de deadlocks en la base de datos causa que las transacciones se frenen y las conexiones queden tomadas por períodos largos e indefinidos de tiempo.
Recursos inadecuados del motor de base de datos: Si los recursos del motor de base de datos (CPU, memory, etc.) son insuficientes, esto llevará a que las conexiones no sean gestionadas de forma eficiente llevando a una performance lenta que prolonga el tiempo que la conexión esta tomada.
Si en la instancia de Thuban se produce este Connection Pool Exhaustion se puede entonces aplicar la siguiente configuración recomendada para soportar alto volumen. Para aplicarla se deberá incorporar el siguiente Bean en el user-application-context.xml de Thuban y reiniciar la aplicación para que tome la nueva configuración.
Tomar el adecuado según el motor de base de datos que se está utilizando.
Microsoft SQL Server / MySQL / Sybase / PostgreSQL
<bean id="thubanDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="acquireIncrement" value="10"/>
<property name="maxPoolSize" value="80"/>
<property name="minPoolSize" value="30"/>
<property name="maxConnectionAge" value="14400"/> <!-- 4 hours -->
<property name="maxIdleTime" value="3600"/> <!-- 1 hour -->
<property name="idleConnectionTestPeriod" value="1800"/> <!-- 30 minutes -->
<property name="maxIdleTimeExcessConnections" value="1200"/> <!-- 20 minutes -->
<property name="numHelperThreads" value="6"/>
<property name="testConnectionOnCheckin" value="true"/>
<property name="testConnectionOnCheckout" value="true" />
<property name="preferredTestQuery" value="SELECT 1"/>
<property name="acquireRetryAttempts" value="30"/>
<property name="acquireRetryDelay" value="1000"/>
<property name="checkoutTimeout" value="5000"/> <!-- 5 seconds -->
<property name="unreturnedConnectionTimeout" value="1800"/> <!-- 30 minutes -->
</bean>Oracle
<bean id="thubanDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="acquireIncrement" value="10"/>
<property name="maxPoolSize" value="80"/>
<property name="minPoolSize" value="30"/>
<property name="maxConnectionAge" value="14400"/> <!-- 4 hours -->
<property name="maxIdleTime" value="3600"/> <!-- 1 hour -->
<property name="idleConnectionTestPeriod" value="1800"/> <!-- 30 minutes -->
<property name="maxIdleTimeExcessConnections" value="1200"/> <!-- 20 minutes -->
<property name="numHelperThreads" value="6"/>
<property name="testConnectionOnCheckin" value="true"/>
<property name="testConnectionOnCheckout" value="true" />
<property name="preferredTestQuery" value="select 1 from dual"/>
<property name="acquireRetryAttempts" value="30"/>
<property name="acquireRetryDelay" value="1000"/>
<property name="checkoutTimeout" value="5000"/> <!-- 5 seconds -->
<property name="unreturnedConnectionTimeout" value="1800"/> <!-- 30 minutes -->
</bean>Otras consideraciones
Es important analizar el escenario antes de tomar un acción, especialmente para identificar el causante más probable del Connection Pool Exhaustion. Si bien esta solución servirá para todas las causas, es importante prestar atención a las siguientes, ya que en ellas, esta configuración solo será un paliativo que ataque el síntoma pero no la causa.
Consultas de larga duración: Se deberán evaluar cuáles son estás consultas y determinar si es correcto que demoren ese tiempo. De no serlo, es recomendable atacar dichas queries y optimizarlas. Incluso de estar OK, tal vez hay lugar para mejorarlas y que no sean tan extensas.
Fugas de conexiones: Thuban garantiza que todas las conexiones internas están siendo gestionadas de forma correcta. Si se detecta que este es el caso, se deberán revisar los desarrollos a medida y las integraciones externas para verificar que correcto cierre de las conexiones.
Deadlocks: Aquí habría que verificar el origen del deadlock para verificar que nos un proceso mal diseñado. Si se producen por volumen se puede probar primero con la siguiente recomendación: Activar read_committed_snapshot en MS SQL Server
Recursos inadecuados del motor de base de datos: Se debería primero atacar la falta de recursos del motor.
Tener en cuenta que esta configuración tiene un costo en uso de memoria y recursos de la aplicación.