Concurrent queries are slow or fail in Watson Query

Watson Query does connection pooling and when connection pools become full, performance can be negatively impacted.

Symptoms

There are many different reasons why concurrent queries might be slow or fail. For example, when the connection pool becomes full. In this case, the Watson Query logs contain Reached max pool size warnings as shown in the following example.

2021-04-13 03:06:27.541~616290520 DatabaseConnector ---------------> ******* DB Connections checker:
    Reached max pool size: 20, for url: jdbc:derby://172.30.217.136:6415/gaiandb6415;ssl=off;create=true - waiting 600000ms

Resolving the problem

To work around this issue, increase Watson Query configuration parameters MAX_POOLSIZES or MAX_DS_POOLSIZES.

MAX_POOLSIZES is the maximum pool size for inter-node connection.
MAX_DS_POOLSIZES is the maximum pool size for data sources.

To check current Watson Query configuration parameter values, run the following query.

select * from dvsys.listconfig where id = 'MAX_POOLSIZES'

select * from dvsys.listconfig where id = 'MAX_DS_POOLSIZES'

To modify parameters, use the dvsys.setconfigproperty stored procedure as shown in the following example.

call dvsys.setconfigproperty('MAX_POOLSIZES', 'nn', '',?,?)

call dvsys.setconfigproperty('MAX_DS_POOLSIZES', 'nn', '',?,?)

Note: Set the 'nn' parameter to a value that is greater than the current value. Keep the maximum number of allowed connections supported by your remote data sources in mind when you set MAX_DS_POOLSIZES.