Too many YF connections to the database
Answered
Hi guys, I have a Postgresql 10 database that allows a maximum 100 connections.
My Yellowfin currently has 78 connections to this database and 48 of them has this query:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
24 of them has this query:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1- Why do we have this many connections of these types?
2- What is the meaning of these connections?
3- How do we get rid of these connections? (Is it possible?)
| Application Version: | 8.0.4 |
| Build: | 20191216 |
| Java Version: | 1.8.0_252 |
| Operating System: | Linux 4.19.112+ (amd64) |
The same question
Hi Vefa,
Thank you for reaching out to us on this. I would suggest these these connections are being kept open after a query is executed for the time specified on the data source as the Refresh Time, so reducing this should see these clear out faster.
Are these queries being sent by themselves or preceding other queries? I will need to double check this, but I believe these are part of keeping the connections alive for the Refresh period. Do you have verify sources or volatile sources enabled? I am assuming these are connections to a data source, not the Yellowfin database, is that correct?
Cheers,
Neal
Hi Vefa,
Thank you for reaching out to us on this. I would suggest these these connections are being kept open after a query is executed for the time specified on the data source as the Refresh Time, so reducing this should see these clear out faster.
Are these queries being sent by themselves or preceding other queries? I will need to double check this, but I believe these are part of keeping the connections alive for the Refresh period. Do you have verify sources or volatile sources enabled? I am assuming these are connections to a data source, not the Yellowfin database, is that correct?
Cheers,
Neal
Hi Neal,
We already have a reduced Refresh Time setting because of another need. It is currently 1. (It was 3 as a default value) And I think we can not make it smaller than 1.
I do not know if these queries are preceding other queries. Do you know if I can check this?
We do not have verify sources and volatile sources enabled.
These are connections to a data source, not the YF repository database.
Hi Neal,
We already have a reduced Refresh Time setting because of another need. It is currently 1. (It was 3 as a default value) And I think we can not make it smaller than 1.
I do not know if these queries are preceding other queries. Do you know if I can check this?
We do not have verify sources and volatile sources enabled.
These are connections to a data source, not the YF repository database.
Hi Vefa,
Apologies for the delay in my reply. I can confirm that this queries get fired by the initial JDBC connection to the datasource and then subsequent connection refreshes. This means they are directly related to the number of database connections Yellowfin is using at any given time, so if you have reduced the refresh time to 1 then I don't believe there is a way to have these connections disconnect sooner.
Are you experiencing performance based issues with this number of connections? Is there a specific issue you are trying to address by reducing the number of connections?
Cheers,
Neal
Hi Vefa,
Apologies for the delay in my reply. I can confirm that this queries get fired by the initial JDBC connection to the datasource and then subsequent connection refreshes. This means they are directly related to the number of database connections Yellowfin is using at any given time, so if you have reduced the refresh time to 1 then I don't believe there is a way to have these connections disconnect sooner.
Are you experiencing performance based issues with this number of connections? Is there a specific issue you are trying to address by reducing the number of connections?
Cheers,
Neal
Hi Neal, once we changed database connection isolation level from 'Read uncommitted' to 'Read committed' we got rid of these connections.
It was blocking us making new connections to the database because these YF connections were using all available slots. We need to connect to the database for development purposes.
So, the problem is solved now.
Thanks for the help!
Hi Neal, once we changed database connection isolation level from 'Read uncommitted' to 'Read committed' we got rid of these connections.
It was blocking us making new connections to the database because these YF connections were using all available slots. We need to connect to the database for development purposes.
So, the problem is solved now.
Thanks for the help!
Hi Vefa,
Glad you were able to work that out. I will note this down for future reference with Postgres.
Cheers,
Neal
Hi Vefa,
Glad you were able to work that out. I will note this down for future reference with Postgres.
Cheers,
Neal
Replies have been locked on this page!