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 

--
View this message in context: 
http://syncope-user.1051894.n5.nabble.com/Long-running-database-connections-block-other-connections-tp5708858.html
Sent from the syncope-user mailing list archive at Nabble.com.