MySQL datasource pool problem
jota Mar 15, 2012 8:26 AMI have a system in production and after some days (3-4) database pool can't obtain a connection. Stack trace shows:
javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not open connection
22:00:32,246 ERROR [stderr] (Thread-202) at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:166)
22:00:32,268 ERROR [stderr] (Thread-202) at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:230)
....
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not open connection
22:00:32,321 ERROR [stderr] (Thread-202) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361)
22:00:32,324 ERROR [stderr] (Thread-202) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289)
...
Caused by: org.hibernate.exception.GenericJDBCException: Could not open connection
22:00:32,400 ERROR [stderr] (Thread-202) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:52)
22:00:32,403 ERROR [stderr] (Thread-202) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
22:00:32,405 ERROR [stderr] (Thread-202) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
...
Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/jdbc/municipios
22:00:32,485 ERROR [stderr] (Thread-202) at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:137)
22:00:32,487 ERROR [stderr] (Thread-202) at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSour
...
Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/jdbc/municipios
22:00:32,521 ERROR [stderr] (Thread-202) at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390)
22:00:32,524 ERROR [stderr] (Thread-202) at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManag
...
Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])
22:00:32,537 ERROR [stderr] (Thread-202) at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377)
22:00:32,541 ERROR [stderr] (Thread-202) at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:498
Pool configuraton is:
<datasource jndi-name="java:/jdbc/municipios" pool-name="municipios_Pool">
<connection-url>jdbc:mysql://localhost:3306/municipios</connection-url>
<driver>mysql</driver>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<pool>
<min-pool-size>10</min-pool-size>
<max-pool-size>200</max-pool-size>
<prefill>true</prefill>
<flush-strategy>EntirePool</flush-strategy>
</pool>
<security>
<user-name>xxx</user-name>
<password>xxx</password>
</security>
<statement>
<track-statements>TRUE</track-statements>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<share-prepared-statements>true</share-prepared-statements>
</statement>
</datasource>
When I check active connections in pool (using cli) and real connections to database (show processlist in mysql) numbers are not equal after some running time (from night to next morning). Example:
MySQL show processlist: 5 (even when datasource minimum is set to 10)
Pool statistics:
{
"outcome" => "success",
"result" => {
"ActiveCount" => "58",
"AvailableCount" => "146",
"AverageBlockingTime" => "0",
"AverageCreationTime" => "20",
"CreatedCount" => "74",
"DestroyedCount" => "16",
"MaxCreationTime" => "356",
"MaxUsedCount" => "58",
"MaxWaitTime" => "6",
"TimedOut" => "16",
"TotalBlockingTime" => "22",
"TotalCreationTime" => "1525"
}
}
I suppose the exception throws when ActiveCount reaches 200, but real database connections should always be equal to ActiveCount ... or not?
Database calls are made from stateless EJB. Most using a persistence context:
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="municipios">
<jta-data-source>java:/jdbc/municipios</jta-data-source>
<class>.....</class>
....
</persistence-unit>
</persistence>
Some services uses an injected (@resource) datasource to the same pool and some other (a couple of them) uses a second pool. These services uses both connections (to both databases) and, as they are read-only and I don't want to use XA, they are marked as @TransactionAttribute=NotSupported
All connections are closed in code (I use <track-statements>TRUE</track-statements> to be sure)
System is in production a I have to flush all connections every day to keep it running.
I need some help,
Thanxs in advance,
J