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.
On Sun, Jun 9, 2019 at 8:25 PM Oscar Nalin <[email protected]>
wrote:
> 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.
>>
>> On Sat, Jun 8, 2019 at 7:06 PM Oscar Nalin <[email protected]> wrote:
>>
>>> 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.
>>>>
>>>> On Sat, Jun 8, 2019 at 4:26 PM Oscar Nalin <[email protected]>
>>>> wrote:
>>>>
>>>>> 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?
>>>>>
>>>>> Den lördag 8 juni 2019 kl. 02:45:34 UTC+2 skrev 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 <[email protected]>
>>>>>>> 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 [email protected].
>>>>>>>> To view this discussion on the web visit
>>>>>>>> https://groups.google.com/d/msgid/dropwizard-user/237a3003-1430-4468-a4e3-5bcbdaa4cd74%40googlegroups.com
>>>>>>>> <https://groups.google.com/d/msgid/dropwizard-user/237a3003-1430-4468-a4e3-5bcbdaa4cd74%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>>>>> .
>>>>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>>>>
>>>>>>> --
>>>>> 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 [email protected].
>>>>> To view this discussion on the web visit
>>>>> https://groups.google.com/d/msgid/dropwizard-user/8f16d874-8335-441a-9638-4eee74382057%40googlegroups.com
>>>>> <https://groups.google.com/d/msgid/dropwizard-user/8f16d874-8335-441a-9638-4eee74382057%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>> .
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>>
>>>> --
>>> 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 [email protected].
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/dropwizard-user/dd4422e7-8065-4268-b19f-6570eba4333b%40googlegroups.com
>>> <https://groups.google.com/d/msgid/dropwizard-user/dd4422e7-8065-4268-b19f-6570eba4333b%40googlegroups.com?utm_medium=email&utm_source=footer>
>>> .
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>> --
> 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 [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/dropwizard-user/9109de30-77df-4549-8135-b89673330a91%40googlegroups.com
> <https://groups.google.com/d/msgid/dropwizard-user/9109de30-77df-4549-8135-b89673330a91%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/dropwizard-user/CAHq0KsZP4MzjoFxuTo1DRnkGTrfYPj_7vHA7H0EwCbXkELSFpg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
