I am struggling to understand how a data source runs validation. I have a pool of connections which runs query on Firebird database, but there are few connection in pool which throws error like
Insufficient memory to allocate page buffer cache [SQLState:HY013, ISC error code:335544691
I'm not sure why data source does not invalidate them using the validation query. The data source I created is not container managed, so not sure if that is the reason the validation query is not being called.
I created a data source bean and register it on spring beans like below.
builder.beans {
"${beanName}"(org.apache.tomcat.jdbc.pool.DataSource) {
driverClassName = "${configuration.driver}"
url = configuration.connectionUrlPrefix
username = configuration.userName
password = configuration.password
maxActive = properties.maxActive
maxIdle = properties.maxIdle
minIdle = properties.minIdle
initialSize = properties.initialSize
maxWait = properties.maxWait
validationQuery = properties.validationQuery
validationInterval = properties.validationInterval
testWhileIdle = properties.testWhileIdle
testOnBorrow = properties.testOnBorrow
logAbandoned = properties.logAbandoned
removeAbandoned = properties.removeAbandoned
removeAbandonedTimeout = properties.removeAbandonedTimeout
timeBetweenEvictionRunsMillis = properties.timeBetweenEvictionRunsMillis
minEvictableIdleTimeMillis = properties.minEvictableIdleTimeMillis
}
}
Stacktrace:
2018-05-02 11:30:52,766 [ajp-bio-8012-exec-7] ERROR StackTrace - Full Stack Trace:
java.sql.SQLException: Insufficient memory to allocate page buffer cache [SQLState:HY013, ISC error code:335544691]
at org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:498)
at org.firebirdsql.gds.ng.FbExceptionBuilder.toFlatSQLException(FbExceptionBuilder.java:299)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readStatusVector(AbstractWireOperations.java:135)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.processOperation(AbstractWireOperations.java:199)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readSingleResponse(AbstractWireOperations.java:166)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readResponse(AbstractWireOperations.java:150)
at org.firebirdsql.gds.ng.wire.AbstractWireOperations.readGenericResponse(AbstractWireOperations.java:252)
at org.firebirdsql.gds.ng.wire.version10.V10WireOperations.authReceiveResponse(V10WireOperations.java:52)
at org.firebirdsql.gds.ng.wire.version10.V10Database.authReceiveResponse(V10Database.java:566)
at org.firebirdsql.gds.ng.wire.version10.V10Database.attachOrCreate(V10Database.java:110)
at org.firebirdsql.gds.ng.wire.version10.V10Database.attach(V10Database.java:80)
at org.firebirdsql.jca.FBManagedConnection.<init>(FBManagedConnection.java:144)
at org.firebirdsql.jca.FBManagedConnectionFactory.createManagedConnection(FBManagedConnectionFactory.java:520)
at org.firebirdsql.jca.FBStandAloneConnectionManager.allocateConnection(FBStandAloneConnectionManager.java:65)
at org.firebirdsql.jdbc.FBDataSource.getConnection(FBDataSource.java:117)
at org.firebirdsql.jdbc.FBDriver.connect(FBDriver.java:137)
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:278)
at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:712)
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:646)
at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:468)
at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:145)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103)
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:127)
at javax.sql.DataSource$getConnection.call(Unknown Source)
at
The error itself means that Firebird tried to allocate memory for the page buffer cache, but couldn't (because the OS ran out of memory, or it reached the maximum amount of memory available to the process). The problem is not related to the validation query, the stacktrace shows it happens while creating a new connection. And if the pool can't allocate a new connection to service your request, it will give up.
Based on our exchange in the comments, it looks like this Firebird server is configured in Classic Server (CS) or SuperClassic(SC) mode. In this mode the page buffer cache is per connection, and not per database (in SuperServer (SS) mode it is per database). As a result the more connections, the higher the memory consumption.
This would indicate that either you are allocating too many connections, or the number of cache pages configured is too high (the most specific config - if set - applies):
firebird.conf setting DefaultDbCachePages) is set too high (default is 75 for CS/SC and 2048 for SS)gstat -h output)isc_dpb_num_buffers/num_buffersWith Classic/SuperClassic this leads to the allocation of NCachePages * Pagesize bytes of memory per connection, where Pagesize is the page size of the database (usually 8 or 16 kilobytes).
For example, with the default of 75 buffers for CS/SC with a 16kb page size, each connection takes 1228800 (1.2 MB) of memory for the cache), so 100 connections need 122 MB (ignoring other memory needs outside of cache).
On the other hand if this setting has been changed (either globally, on the database or with a per connection setting) to, for example, 9999 pages, then each connection takes 163 MB of memory, and 100 connections would need 16GB.
To solve this problem, you'll need to take one or more of the following steps:
The last two can lead to significant changes in performance (possibly positive, possibly negative), so those should be tested carefully.
As a workaround, if you can't get the owner to change the configuration on short notice, consider adding the connection property num_buffers=75 (or a similar low number) to your connection properties.