Re: Adding locks statistics

Bertrand Drouvot <bertranddrouvot.pg@gmail.com>

From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>

To: Jeff Davis <pgsql@j-davis.com>

Cc: Michael Paquier <michael@paquier.xyz>, Greg Sabino Mullane <htamfids@gmail.com>, pgsql-hackers@lists.postgresql.org

Date: 2025-08-13T03:55:41Z

Lists: pgsql-hackers

Hi,

On Tue, Aug 12, 2025 at 08:42:48AM -0700, Jeff Davis wrote:
> On Tue, 2025-08-12 at 09:37 +0000, Bertrand Drouvot wrote:
> > 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?
> 
> Those seem plausibly useful,

Thanks for sharing your thoughts about the use cases above.

> and I wanted to hear more from others.

> For instance, a view could be helpful to diagnose concurrency issues,
> but I think that's worth discussing in more detail to see what kinds of
> issues it can help with and how it complements other approaches. I
> suspect when we get into the details, different people (or different
> situations) would want slightly different information out of that view.

Fully agree, getting input from others definitely helps validate the approach
and see if the current design needs some changes.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Fix injection point detach timing problem in TAP test for lock stats
- 3284e3f63cf0 19 (unreleased) landed
Use single LWLock for lock statistics in pgstats
- 404a17c155ac 19 (unreleased) landed
Add tests for lock statistics, take two
- 557a9f1e3e62 19 (unreleased) landed
Remove isolation test lock-stats
- 7c64d56fd976 19 (unreleased) landed
Avoid including clog.h in proc.h
- f227b7b20c36 19 (unreleased) landed
Don't include storage/lock.h in so many headers
- 2102ebb1953f 19 (unreleased) landed
Add support for lock statistics in pgstats
- 4019f725f5d4 19 (unreleased) landed
Move some code blocks in lock.c and proc.c
- a90d86518243 19 (unreleased) landed
Move declarations related to locktags from lock.h to new locktag.h
- 322bab79744d 19 (unreleased) landed