Start a new topic
Answered

Using PgBouncer with Semarchy PostgreSQL database

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

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. 

Stéphanie.

 


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:

  • 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

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. 

Stéphanie.

 

Login to post a comment