2

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
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Mkp
  • 95
  • 1
  • 2
  • 8
  • I guess validationQuery is executed on pooled connection right before it is returned to the caller. Could it be possible that there are enough valid connections in pool and these invalid connections ware never allocated. Theoretically, wondering if making db pool busy helps us here, in which case validation would fail for these connections and get dropped (by creating new connections). – giddi May 03 '18 at 01:26
  • I think because connection throws SQLException, data source still consider it as validation connection. Can someone point where to find implementation of tomcat datasource – Mkp May 03 '18 at 07:17
  • Are you using Firebird SuperServer or Classic Server or SuperClassic? If I'm not mistaken, the error _"Insufficient memory to allocate page buffer cache"_ should only occur with Classic Server or SuperClassic, and only when the connection is initially allocated (or maybe on the first connection to a database with SuperServer). That would mean that you get this error when a new connection is allocated. It also means that your server has too little memory for the page buffer size and the number of connections used. Reduce the page buffer size, or try to reduce the number of connections. – Mark Rotteveel May 03 '18 at 09:54
  • thanks @MarkRotteveel . When I create new connection using Groovy SQL which internally uses DriverManager to create connection (at the same application state without any restart), that new connection works fine, so I thought when datasource connection fails as part of validationQuery run it will create new connection using DriverManager (As per the implementation in org.apache.tomcat.jdbc.pool.ConnectionPool) but some reason whenever i use that datasource it fails with above error, so it not cleating healthy connection anymore. After server restart it started working – Mkp May 03 '18 at 10:58
  • Could you update your question to include the full Firebird version and execution mode used (SuperServer, ClassicServer or SuperClassic) and the version of Jaybird used? And please, also include the full exception stacktrace. – Mark Rotteveel May 03 '18 at 12:20
  • jaybird-jdk17-3.0.2.jar, Firebird server architecture info is not available with me as we are connecting to external party – Mkp May 03 '18 at 12:42
  • The stacktrace shows the error occurs while the connection pool is creating a **new** connection. The problem is not (directly) at the Java side of things. If the connection pool cannot create a new connection, it simply cannot satisfy your request, the problem doesn't have to do with the validation query. You'll need to ask the external party what is wrong with their Firebird server. It sounds like it has run out of memory, and/or - if CS/SC server mode - you are allocating too many connections (which leads to the server running out of memory). – Mark Rotteveel May 03 '18 at 18:27
  • @MarkRotteveel Thanks mark. yes process ran fine with 10 connections but in multithreaded env, 10 connections may not be feasibly solution for me. Thanks a lot. – Mkp May 03 '18 at 19:02
  • It might be best to work together with that external party. The problem is likely one of using a too big page buffer size (this is either a global Firebird setting, per database setting or per connection setting). The default is very small. So either they are running Firebird with very little memory or they have configured the page buffer cache way too large. In CS/SC mode this cache is reserved per connection, not per database. – Mark Rotteveel May 04 '18 at 06:56
  • Consider updating the question to still contain (mostly) the same contents, but perhaps a different title and description, so others can benefit from your experience. Right now, this looks to be Tomcat/validationQuery-related, when it's really Firebird-related. – Christopher Schultz May 06 '18 at 14:34

1 Answers1

0

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)
  • Database-specific setting (see gstat -h output)
  • Connection property isc_dpb_num_buffers/num_buffers

With 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:

  • Reduce the amount of connections needed (with a good connection pool and small units of work, you may be surprised how few connections you need)
  • Reduce the amount of pages allocated to the cache
  • Increase the memory available to the Firebird process
  • Reduce the page size by backing up and restoring the database with a smaller page size
  • Switch to SuperServer mode instead of Classic/SuperClassic

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197