Date: Tue, 12 Aug 2025 09:37:55 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Jeff Davis <pgsql@j-davis.com>,
	Greg Sabino Mullane <htamfids@gmail.com>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Adding locks statistics
Message-ID: <aJsLc+S62xAbjncC@ip-10-97-1-34.eu-west-3.compute.internal>
References: <aIyNxBWFCybgBZBS@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAKAnmm+USTr5MeKuyrAvbCjHqbSFbZ+czJsV7fPYt9nECQkf-A@mail.gmail.com>
 <87c3170d0645cec732f0d7b2969c75db1b3c86c6.camel@j-davis.com>
 <aJqAei83uRWV93bM@paquier.xyz>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <aJqAei83uRWV93bM@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/aJsLc%2BS62xAbjncC%40ip-10-97-1-34.eu-west-3.compute.internal>
Precedence: bulk

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