Long running database connections block other connections.
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
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: