public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bertrand Drouvot <[email protected]>
To: Michael Paquier <[email protected]>
Cc: Jeff Davis <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Subject: Re: Adding locks statistics
Date: Tue, 12 Aug 2025 09:37:55 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAKAnmm+USTr5MeKuyrAvbCjHqbSFbZ+czJsV7fPYt9nECQkf-A@mail.gmail.com>
<[email protected]>
<[email protected]>
Hi,
On Tue, Aug 12, 2025 at 08:44:58AM +0900, Michael Paquier wrote:
> On Mon, Aug 11, 2025 at 02:53:58PM -0700, Jeff Davis wrote:
> > On Mon, 2025-08-11 at 13:54 -0400, Greg Sabino Mullane wrote:
> > > Great idea. +1. Here is a quick overall review to get things started.
> >
> > Can you describe your use case? I'd like to understand whether this is
> > useful for users, hackers, or both.
Thanks for looking at it!
I provided a few examples in the initial email ([1]):
"
It can be used for example for:
1. checking if "waits" is close to "requests". Then it means you usually have to
wait before acquiring the lock, which means you may have a concurrency issue.
2. lock_timeout and deadlock_timeout tuning (lock_timeout is visible only in the
logs if log_min_error_statement is set appropriately).
3. checking the "requests"/"fastpath" ratio to see if "max_locks_per_transaction"
needs tuning (see c4d5cb71d2).
"
Do these seem like useful use cases?
> - Is there any decision-making where these numbers would help? These
> decisions would shape in tweaking the configuration of the server or
> the application to as we move from a "bad" number trend to a "good"
> number trend.
Right, I think that could help for lock_timeout and deadlock_timeout tuning.
> - What would be good numbers? In this case, most likely a threshold
> reached over a certain period of time.
Also I think it's more a matter of ratio: waits/requests and requests/fastpath
for example.
> - Would these new stats overlap with similar statistics gathered in
> the system, creating duplication and bloat in the pgstats for no real
> gain?
I don't think there is currently stats that overlap with those.
> Looking at the patch, the data gathered is this, and I don't think
> that we have metrics gathered in the system to get an idea of the
> contention in this area, for timeouts and deadlocks:
> + PgStat_Counter requests;
> + PgStat_Counter waits;
> + PgStat_Counter timeouts;
> + PgStat_Counter deadlock_timeouts;
> + PgStat_Counter deadlocks;
> + PgStat_Counter fastpath;
>
> Isn't the "deadlock_timeout" one an overlap between timeout and
> deadlock?
I don't think so because:
- deadlock_timeout and lock_timeout are 2 distincts GUCs
- you could reach the deadlock_timeout without actually producing a deadlock
> Regarding the fastpath locking, you'd want to optimize the fastpath to
> be close to the number of requests. If you had these numbers, one
> thing that I could see a DBA do is tune max_locks_per_transaction,
> which is what influences the per-backend limit of fast-path locks,
> with FastPathLockGroupsPerBackend.
Exactly.
> Using static counters for the pending data is going to be necessary
> when these are called in critical sections, which is I guess why
> you've done it this way, right?
Yes and there is no need to over complicate this stuff as the max size is
known: LOCKTAG_LAST_TYPE + 1.
[1]: https://www.postgresql.org/message-id/aIyNxBWFCybgBZBS%40ip-10-97-1-34.eu-west-3.compute.internal
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
view thread (43+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Adding locks statistics
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox