WSO2 products — how to identify slow queries

All WSO2 products have a common place to configure datasources they use. WSO2 uses Apache Tomcat JDBC pooling for connection pooling. Sometimes in production there can be instances where connection pool gets exhausted. Below log will be a clear indication of such an exhaust.

Caused by: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [pool-32-thread-12754] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000].
 at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:674)
 at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:188)
 at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)

Here connection pool size is 100, and all connections are occupied. The new statement has no connection to borrow.

At this stage, we need to see why all connections are occupied. There can be two reasons for this.

1. Connections are not released back to the pool (connection leak)
2. Connection occupying rate is greater than connection release rate. In some scenarios this can be directly related with message rates.
3. The statements are executed slowly. Maybe the query statement is inefficient, or the DB is slow.

• The records in DB are not indexed
• There is a lot of records in the DB, making the query inefficient
• DB server is overloaded (load average is high)
• The connection between WSO2 server and DB server is slow

Connection leak

In case of a connection leak, the connections are not closed after executing the statements. It should be fixed in the code.

Connections are not enough

Judging from how connections are used we can extend the JDBC connection pool size. You need to set following parameter.

maxActive = 200

Identify slow connections

To identify slow connections we can use slow query report. Configure following property under data source configuration. Following config will log any SQL statement which is taking more that 200ms to execute. It is safe to apply this config for production as 200ms is a considerable time for an query to execute.

<jdbcInterceptors>SlowQueryReport(threshold=200)</jdbcInterceptors>

Evict connections that are held longer

It will be a precaution to forcefully close the connection which is held by a query in an unnecessary manner. The following config will forcefully close any connection that is taking more than 5 minutes to execute. This may reduce the probability of having connections not released back to idle pool.

Further, on forcefully closing the connection it will log information (stack trace etc) so that we can identify what is the culprit code point or the SQL statement.

Thus it is recommended to have below config in production.

<logAbandoned>true</logAbandoned>
<removeAbandoned>true</removeAbandoned>
<removeAbandonedTimeout>300</removeAbandonedTimeout>

These configurations can be applied to <product_HOME>/repository/conf/datasources/masterdatasource.xml file which is the common place for configuring datasources for WSO2 products. If you are adding them using management console still the relevant controls are available.