Phantom JDBI health checks

[Radomir Djurdjevic]

Hi guys,

I have an issue with a health check query that appears approximately 10 times each time connection is established, although I've never set this check explicitly. It's the validation query:

/* Health Check */ SELECT 1

In the config.yml I use for running the service there is no valiidationQuery property set, which makes me wonder where is this health check coming from. I do not have any other health checks put in place, so it can't be coming from the health module.

Does anyone have an idea what could be making the health check?

I am using:

• Java 8
• PostgreSQL 11
• DropWizard 1.3.5
• JDBI3

Thanks a lot,

Rasha

[Ryan Kennedy]

I believe this is the default liveness check for the connection pool to determine if a connection in the pool is still “good” or not. 

--
You received this message because you are subscribed to the Google Groups "dropwizard-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-us...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dropwizard-user/237a3003-1430-4468-a4e3-5bcbdaa4cd74%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Radomir Djurdjevic]

Is there a way to get this default liveness check under control somehow? It is taking up space from regular clients trying to connect...

On Saturday, June 8, 2019 at 2:35:29 AM UTC+2, Ryan Kennedy wrote:

I believe this is the default liveness check for the connection pool to determine if a connection in the pool is still “good” or not. 

On Fri, Jun 7, 2019 at 5:30 PM Radomir Djurdjevic <rax...@gmail.com> wrote:

Hi guys,

I have an issue with a health check query that appears approximately 10 times each time connection is established, although I've never set this check explicitly. It's the validation query:

/* Health Check */ SELECT 1

In the config.yml I use for running the service there is no valiidationQuery property set, which makes me wonder where is this health check coming from. I do not have any other health checks put in place, so it can't be coming from the health module.

Does anyone have an idea what could be making the health check?

I am using:

• Java 8
• PostgreSQL 11
• DropWizard 1.3.5
• JDBI3

Thanks a lot,

Rasha

--
You received this message because you are subscribed to the Google Groups "dropwizard-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to dropwiz...@googlegroups.com.

[Oscar Nalin]

How is your connection pool configured? Can you post the configuration values you are using?

The validation query that you are seeing is the default one. Look at the java docs to see all default values: https://www.dropwizard.io/1.3.12/dropwizard-db/apidocs/index.html

Is the validation query running 10 times when the connection is created or when you are requesting a connection from the connection pool?

[Radomir Djurdjevic]

Hi,

Here is the section of configuration.yml relevant for the database (access data excluded):

database:
  driverClass: org.postgresql.Driver

  # the username
  # the password
  # the JDBC URL

  properties:
    charSet: UTF-8

  maxWaitForConnection: 1s
#  validationQuery: "/* MyService Health Check */ SELECT 1"

  validationQueryTimeout: 3s
  minSize: 8
  maxSize: 32
  checkConnectionWhileIdle: false
  evictionInterval: 10s
  minIdleTime: 1 minute

I have commented the validationQuery property, so the default value is used.

Small correction from my side: the validation query runs 8 (not 10 as I mistakenly said before) times, which equals to minSize property. These connections are created on application start, but also then every time a new HTTP request comes in which uses some of the generated JDBI DAOs. All validation queries are switch state quickly to idle but are not cleaned up. 

I am tempted to set up Hikari connection pool, but seeing that other queries seem to be closed properly and only the validation query is an issue, I would rather resolve that first before inserting an additional component to the system.

I appreciate the feedback.

Regards, R.

To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-us...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dropwizard-user/8f16d874-8335-441a-9638-4eee74382057%40googlegroups.com.

[Oscar Nalin]

I'm fairly sure that this line in your configuration file is what causing your problem:

checkConnectionWhileIdle: false

Your connection pool will thus have this configuration (your conf. + default values):

checkConnectionWhileIdle: false

checkConnectionOnBorrow: false

checkConnectionOnConnect: true

checkConnectionOnReturn: false

This means that the only time a connection is validated is when it is added the first time to the connection pool. When the connection pool validation runs every 30 seconds (default value), it will not validate your idle connections. 

I think what is happening is that when you are requesting a connection from the connection pool, the pool will consider your 8 connections dead and thus will create 8 new ones and return one of them yo your application. These are the 8 queries you are seeing, when the connection pool is adding new connections to the pool. 

So to conclude, try to have checkConnectionWhileIdle: true (default value), and see if that helps.

Does that make sense?

Den lördag 8 juni 2019 kl. 17:13:46 UTC+2 skrev Radomir Djurdjevic:

Hi,

Here is the section of configuration.yml relevant for the database (access data excluded):

database:
  driverClass: org.postgresql.Driver

  # the username
  # the password
  # the JDBC URL

  properties:
    charSet: UTF-8

  maxWaitForConnection: 1s
#  validationQuery: "/* MyService Health Check */ SELECT 1"

  validationQueryTimeout: 3s
  minSize: 8
  maxSize: 32
  checkConnectionWhileIdle: false
  evictionInterval: 10s
  minIdleTime: 1 minute

I have commented the validationQuery property, so the default value is used.

Small correction from my side: the validation query runs 8 (not 10 as I mistakenly said before) times, which equals to minSize property. These connections are created on application start, but also then every time a new HTTP request comes in which uses some of the generated JDBI DAOs. All validation queries are switch state quickly to idle but are not cleaned up. 

I am tempted to set up Hikari connection pool, but seeing that other queries seem to be closed properly and only the validation query is an issue, I would rather resolve that first before inserting an additional component to the system.

I appreciate the feedback.

Regards, R.

[Radomir Djurdjevic]

Yes, it does make sense, thanks for the advice. Unfortunately it didn't work out for me so well, issue continues to persist, connections are being created for the validation query and they are never released. Last time it got up to 80 idle connections used by the query. There must be something about JDBI3 I don't fully understand - I set maxSize to 32 and it still somehow manages to get to 80 connections. 

I'm using the default configuration now, but without success.

I would appreciate it if someone could point me in the right direction.

Thanks, R.

To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-us...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dropwizard-user/dd4422e7-8065-4268-b19f-6570eba4333b%40googlegroups.com.

[Oscar Nalin]

Can you post a complete (but as minimal as possible) code example?

Den söndag 9 juni 2019 kl. 02:21:11 UTC+2 skrev Radomir Djurdjevic:

Yes, it does make sense, thanks for the advice. Unfortunately it didn't work out for me so well, issue continues to persist, connections are being created for the validation query and they are never released. Last time it got up to 80 idle connections used by the query. There must be something about JDBI3 I don't fully understand - I set maxSize to 32 and it still somehow manages to get to 80 connections. 

I'm using the default configuration now, but without success.

I would appreciate it if someone could point me in the right direction.

Thanks, R.

[Radomir Djurdjevic]

Not sure which part of the code are you after, but I'll try to make it as traceable as possible. This is the current state of the validation queries in the database:

my_db=# select count(*) from pg_stat_activity where query = '/* MyService Health Check */ SELECT 1';
-[ RECORD 1 ]
count | 8

As soon as some HTTP call is made to the server (that makes a call to the database), number of validation queries doubles:

my_db=# select count(*) from pg_stat_activity where query = '/* MyService Health Check */ SELECT 1';
-[ RECORD 1 ]
count | 16

What concerns me is that these connections then remain there for quite a while, in idle state, without being properly reused. With the next HTTP call that requires some method from the DAO call, another 8 connections pop up:

my_db=# select count(*) from pg_stat_activity where query = '/* MyService Health Check */ SELECT 1';
-[ RECORD 1 ]
count | 24

A connection used by the "real" method within the HTTP request is quickly terminated after request is successfully processed. So my biggest issue is that number of connections can go over maxSize specified in the config file, and that most of these connections are these queries '/* MyService Health Check */ SELECT 1'; all of which are in idle state.

This is my current config:

database:
  # the name of your JDBC driver
  driverClass: org.postgresql.Driver
  properties:
    charSet: UTF-8

  validationQuery: "/* MyService Health Check */ SELECT 1"

  minSize: 8
  maxSize: 32

What other part of the code could help you with the analysis? I would be more than happy to share.

Thanks R.

To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-us...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dropwizard-user/9109de30-77df-4549-8135-b89673330a91%40googlegroups.com.

[Radomir Djurdjevic]

In case anyone comes across this particular issue, here is what it was causing it for me, and how I fixed it.

Cause of the issue lied in the missing @Singleton annotation. Since JDBIFactory was extended and the provide() method had to be overridden in order to create valid Jdbi instances, the method had to be marked as a singleton explicitly in order to avoid it being called multiple times.

@Override
@Singleton
public Jdbi provide() {
    Jdbi jdbi = super.provide().installPlugin(new PostgresPlugin());
    return jdbi;
}

As the method wasn't annotated with @Singleton, it was being run with every call to the DAO layer, which in turn kept creating 8 new connections (equal to minSize property of the connection pool, taken from the application config file) on each call.

Since I use HK2 for DI, I needed an additional library (https://github.com/alex-shpak/dropwizard-hk2bundle) to make things work. The JDBIFactory class comes from the library and the provide() method is the one I had to override.