Re: pg_stat_lwlocks view - lwlocks statistics

Hi all,

I've modified the pg_stat_lwlocks patch to be able to work with
the latest PostgreSQL Git code.

This patch provides:
pg_stat_lwlocks New system view to show lwlock statistics.
pg_stat_get_lwlocks() New function to retrieve lwlock statistics.
pg_stat_reset_lwlocks() New function to reset lwlock statistics.

Please try it out.

Regards,

2012/06/26 5:29, Satoshi Nagayasu wrote:
> Hi all,
>
> I've been working on a new system view, pg_stat_lwlocks, to observe
> LWLock, and just completed my 'proof-of-concept' code that can work
> with version 9.1.
>
> Now, I'd like to know the possibility of this feature for future
> release.
>
> With this patch, DBA can easily determine a bottleneck around lwlocks.
> --------------------------------------------------
> postgres=# SELECT * FROM pg_stat_lwlocks ORDER BY time_ms DESC LIMIT 10;
> lwlockid | calls | waits | time_ms
> ----------+--------+-------+---------
> 49 | 193326 | 32096 | 23688
> 8 | 3305 | 133 | 1335
> 2 | 21 | 0 | 0
> 4 | 135188 | 0 | 0
> 5 | 57935 | 0 | 0
> 6 | 141 | 0 | 0
> 7 | 24580 | 1 | 0
> 3 | 3282 | 0 | 0
> 1 | 41 | 0 | 0
> 9 | 3 | 0 | 0
> (10 rows)
>
> postgres=#
> --------------------------------------------------
>
> In this view,
> 'lwlockid' column represents LWLockId used in the backends.
> 'calls' represents how many times LWLockAcquire() was called.
> 'waits' represents how many times LWLockAcquire() needed to wait
> within it before lock acquisition.
> 'time_ms' represents how long LWLockAcquire() totally waited on
> a lwlock.
>
> And lwlocks that use a LWLockId range, such as BufMappingLock or
> LockMgrLock, would be grouped and summed up in a single record.
> For example, lwlockid 49 in the above view represents LockMgrLock
> statistics.
>
> Now, I know there are some considerations.
>
> (1) Performance
>
> I've measured LWLock performance both with and without the patch,
> and confirmed that this patch does not affect the LWLock perfomance
> at all.
>
> pgbench scores with the patch:
> tps = 900.906658 (excluding connections establishing)
> tps = 908.528422 (excluding connections establishing)
> tps = 903.900977 (excluding connections establishing)
> tps = 910.470595 (excluding connections establishing)
> tps = 909.685396 (excluding connections establishing)
>
> pgbench scores without the patch:
> tps = 909.096785 (excluding connections establishing)
> tps = 894.868712 (excluding connections establishing)
> tps = 910.074669 (excluding connections establishing)
> tps = 904.022770 (excluding connections establishing)
> tps = 895.673830 (excluding connections establishing)
>
> Of course, this experiment was not I/O bound, and the cache hit ratio
> was>99.9%.
>
> (2) Memory space
>
> In this patch, I added three new members to LWLock structure
> as uint64 to collect statistics.
>
> It means that those members must be held in the shared memory,
> but I'm not sure whether it's appropriate.
>
> I think another possible option is holding those statistics
> values in local (backend) process memory, and send them through
> the stat collector process (like other statistics values).
>
> (3) LWLock names (or labels)
>
> Now, pg_stat_lwlocks view shows LWLockId itself. But LWLockId is
> not easy for DBA to determine actual lock type.
>
> So, I want to show LWLock names (or labels), like 'WALWriteLock'
> or 'LockMgrLock', but how should I implement it?
>
> Any comments?
>
> Regards,

--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp