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
