Hello,
Does anybody have experiences and best practices of using PgBouncer with managing Semarchy database connections? The usage of PgBouncer is something that Azure suggests as part of their documentation for Azure Database for PostgreSQL so would be interesting to hear experiences about it and if it improved the database performance or database connection reliability?
Br,
Juhani
Best Answer
S
Stéphanie FOURRIER
said
about 1 year ago
Hi everyone, I'm trying to summarize the inputs we got from Hilaire in a separate email thread for future readers.
We do have some customers relying on PgBouncer. Our advice would be to rely on pgBouncer to deal with the stock of connections and leverage its central position, and keep a low idleTimeout in the Hikari pools.
Sample proposal in your specific case
The idleTimeout can safely be decreased to 10000 (10 seconds, minimum value). This could avoid keeping a handle on a severed connection for too long, and hopefully get rid of the "connection reset by peer" errors.
I'd also suggest a non-zero value for keepAliveTime, to make sure idle connections that stay in the pool (to keep connection count above minIdle) are checked every now and then. I'd start with 2 mins (120000).
I'd also try to keep minIdle low but greater than 1 (around 10% of maximumPoolSize).
Don't use higher values for minIdle unless you stop using bgBouncer.
In the end, you decided to not use PgBouncer as you found the right Hikari property values to solve your specific issue.
Thank you for your question and apologies for delay in getting to it. Semarchy xDM product comes with an inbuilt connection pool. You can use the documentation link to setup and open a Support ticket if you run into any issues. I do want to point out though having a connection pool for a very small user base might be detrimental to performance. So it would be good to know:
Your total user base.
Your peak usage connection count.
If you will be using read replicas to scale reads.
The answers to above questions might be helpful in determining the best use of connection pools and its settings.
Thanks,
Sandeep
S
Stéphanie FOURRIER
said
about 1 year ago
Answer
Hi everyone, I'm trying to summarize the inputs we got from Hilaire in a separate email thread for future readers.
We do have some customers relying on PgBouncer. Our advice would be to rely on pgBouncer to deal with the stock of connections and leverage its central position, and keep a low idleTimeout in the Hikari pools.
Sample proposal in your specific case
The idleTimeout can safely be decreased to 10000 (10 seconds, minimum value). This could avoid keeping a handle on a severed connection for too long, and hopefully get rid of the "connection reset by peer" errors.
I'd also suggest a non-zero value for keepAliveTime, to make sure idle connections that stay in the pool (to keep connection count above minIdle) are checked every now and then. I'd start with 2 mins (120000).
I'd also try to keep minIdle low but greater than 1 (around 10% of maximumPoolSize).
Don't use higher values for minIdle unless you stop using bgBouncer.
In the end, you decided to not use PgBouncer as you found the right Hikari property values to solve your specific issue.
Juhani Saarinen
Hi everyone, I'm trying to summarize the inputs we got from Hilaire in a separate email thread for future readers.
We do have some customers relying on PgBouncer. Our advice would be to rely on pgBouncer to deal with the stock of connections and leverage its central position, and keep a low idleTimeout in the Hikari pools.
The idleTimeout can safely be decreased to 10000 (10 seconds, minimum value). This could avoid keeping a handle on a severed connection for too long, and hopefully get rid of the "connection reset by peer" errors.
I'd also suggest a non-zero value for keepAliveTime, to make sure idle connections that stay in the pool (to keep connection count above minIdle) are checked every now and then. I'd start with 2 mins (120000).
I'd also try to keep minIdle low but greater than 1 (around 10% of maximumPoolSize).
Don't use higher values for minIdle unless you stop using bgBouncer.
Stéphanie.
- Oldest First
- Popular
- Newest First
Sorted by Oldest FirstSandeep Bhatia
Hi Juhani,
Thank you for your question and apologies for delay in getting to it. Semarchy xDM product comes with an inbuilt connection pool. You can use the documentation link to setup and open a Support ticket if you run into any issues. I do want to point out though having a connection pool for a very small user base might be detrimental to performance. So it would be good to know:
Stéphanie FOURRIER
Hi everyone, I'm trying to summarize the inputs we got from Hilaire in a separate email thread for future readers.
We do have some customers relying on PgBouncer. Our advice would be to rely on pgBouncer to deal with the stock of connections and leverage its central position, and keep a low idleTimeout in the Hikari pools.
The idleTimeout can safely be decreased to 10000 (10 seconds, minimum value). This could avoid keeping a handle on a severed connection for too long, and hopefully get rid of the "connection reset by peer" errors.
I'd also suggest a non-zero value for keepAliveTime, to make sure idle connections that stay in the pool (to keep connection count above minIdle) are checked every now and then. I'd start with 2 mins (120000).
I'd also try to keep minIdle low but greater than 1 (around 10% of maximumPoolSize).
Don't use higher values for minIdle unless you stop using bgBouncer.
Stéphanie.
-
Deployment History Date
-
Username in Tomcat Access Logs
-
Is It Possible to Perform Automatic Authentication with The User's Windows Account?
-
Where is the documentation for Version 5.2.5?
-
On Prem Semarchy Authentication using Azure AD?
-
Delete old models
-
Sync production model version with dev
-
Recreate a dev environment. Any side effect?
-
Indexes on xDM database tables
-
What logging technology is used in the Semarchy xDM platform?
See all 60 topics