From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Abdullah Ergin <abdullaherginwork@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Postgresql - Pgbouncer Connection and Query Performance Problem
Date: Wed, 03 Apr 2024 08:24:47 +0200
Message-ID: <bf84d8ea3086056bcd19aa6fa04d9d894c7f8102.camel@cybertec.at> (raw)
In-Reply-To: <CAJZxWEONnhdqr_76kkeQVLrjxRv-M6CxM7kyJvvLTmoscSuS_g@mail.gmail.com>
References: <CAJZxWENRnzd4z_5HfTmU=PUnm-vRREmTA35ykj97KrJ5bRunzA@mail.gmail.com>
<0adf6b95e08946113f12415e6f95fc74d2bc1a06.camel@cybertec.at>
<CAJZxWEONnhdqr_76kkeQVLrjxRv-M6CxM7kyJvvLTmoscSuS_g@mail.gmail.com>
On Tue, 2024-04-02 at 17:04 +0300, Abdullah Ergin wrote:
> [large connection pool size]
> Thank you for the information. What would you recommend as the value
> for this parameter? Would 100 be too large of a number? Or maybe 50?
That depends entirely on your hardware and the database workload.
Perhaps reading this article can help you:
https://www.cybertec-postgresql.com/en/estimating-connection-pool-size-with-postgresql-database-stat...> Additionally, before lowering these parameters, I had a lot of "LWLocks"
> in my database. Normally, I know that these lightweight locks don't cause
> significant issues, but during the slowdown, I was consistently seeing
> 70-80 LWLocks. After lowering the parameters and restarting pooling,
> these locks disappeared. Is there any correlation?
Very likely yes.
If you see many LWLocks, that will cause a significant performance issue.
Essentially, it is contention inside the database.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, abdullaherginwork@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Postgresql - Pgbouncer Connection and Query Performance Problem
In-Reply-To: <bf84d8ea3086056bcd19aa6fa04d9d894c7f8102.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox