Long running database connections block other connections.

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Long running database connections block other connections.

rvdm
Hi Syncopeeds,

Once in a while (once every 1 or 2 months) Syncope will hang in the production environment of one of our clients because the connection pool is exhausted. We can see errors like this in the logging:


2016-12-17 03:09:23,030 | ERROR | o-8443-exec-8398 | org.apache.syncope.core.rest.controller.AbstractController |  | Exception thrown by REST methods
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is <openjpa-2.2.2-r422266:1468616 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: [http-bio-8443-exec-8398] Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:20; busy:20; idle:0; lastwait:10000].
Caused by: org.apache.openjpa.persistence.PersistenceException: [http-bio-8443-exec-8398] Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:20; busy:20; idle:0; lastwait:10000].

When this happens a lot of database connections are open to Mysql like (via mysql show processlist) :

lots of these:

time | state | info
13052 | Waiting for table flush | SELECT t0.id, t0.changePwdDate, t0.cipherAlgorithm, t0.creationDate, t0.failedLogins, t0.lastLoginDa


and this:
157911 | Sending data | SELECT COUNT(subject_id) FROM (SELECT u.subject_id FROM (SELECT DISTINCT subject_id FROM user_search

157911 seconds is about 43 hours.

Once this last process is killed everything will work as usual.

Do you have any ideas what could cause this problem? Or how to better tune the database connections? What does this query do (it is not used by one of our reports) ?


We are using Syncope 1.1.8 and MySQL 5.6.23 with tomcat-7.0.50 on OpenJDK version "1.7.0_101". The database contains 10256457 (10 million) SyncopeUser records. The database is used for small CRUD transactions via REST and long running reporting both directly via the mysql client and the Syncope Console.

Tomcat is configured with the following database resource:

     <Resource name="jdbc/syncopeDataSource"
                  auth="Container"
                  type="javax.sql.DataSource"
                  driverClassName="com.mysql.jdbc.Driver"
                  username=“foo”
                  password=“bar”
                  url="jdbc:mysql://foobar:3306/foobar?connectTimeout=10000&amp;socketTimeout=1800000"
                  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
                  testWhileIdle="true"
                  testOnBorrow="true"
                  testOnReturn="true"
                  validationQuery="SELECT 1 FROM DUAL"
                  validationInterval="30000"
                  validationQueryTimeout="10"
                  maxActive="20"
                  minIdle="2"
                  maxWait="10000"
                  initialSize="10"
                  removeAbandonedTimeout="20000"
                  removeAbandoned="true"
                  logAbandoned="true"
                  timeBetweenEvictionRunsMillis="5000"
                  minEvictableIdleTimeMillis="5000"
                  jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"/>

SocketTimeout is set to a high value otherwise queries via the Syncope Console will timeout.

Thanks in advance,

Rob
Java Developer
Finalist
Loading...