Re: pg_stat_lwlocks view - lwlocks statistics, round 2 - Mailing list pgsql-hackers

On Sun, Oct 14, 2012 at 7:52 PM, Satoshi Nagayasu <snaga@uptime.jp> wrote:
> (2012/10/14 13:26), Fujii Masao wrote:
>>
>> On Sun, Oct 14, 2012 at 10:46 AM, Satoshi Nagayasu <snaga@uptime.jp>
>> wrote:
>>>
>>> HEAD
>>> ====
>>> number of transactions actually processed: 3439971
>>> tps = 57331.891602 (including connections establishing)
>>> tps = 57340.932324 (excluding connections establishing)
>>
>> <snip>
>>>
>>> pg_stat_lwlocks patch (reporting disabled)
>>> ==========================================
>>> number of transactions actually processed: 3429370
>>> tps = 57155.286475 (including connections establishing)
>>> tps = 57163.996943 (excluding connections establishing)
>>>
>>> So, I think some additional hack to reduce reporting is needed.
>>> Would it be acceptable in terms of the performance?
>>
>>
>> The tracing lwlock usage seems to still cause a small performance
>> overhead even if reporting is disabled. I believe some users would
>> prefer to avoid such overhead even if pg_stat_lwlocks is not available.
>> It should be up to a user to decide whether to trace lwlock usage, e.g.,
>> by using trace_lwlock parameter, I think.
>
>
> Frankly speaking, I do not agree with disabling performance
> instrument to improve performance. DBA must *always* monitor
> the performance metrix when having such heavy workload.
>
> But it's ok to add a parameter to switch enable/disable it.
> Any other comments?

I thought that pg_stat_lwlocks would be mostly used for the debugging
purpose. IOW most users would disable that in production system. But,
if you think it should be enabled even in production system and most
users would usually improve performance by using that view, I think that
you need to reconsider the lwlockid in pg_stat_lwlocks view. It's hard
to understand what kind of lwlock each lwlockid indicates. Which means
that it's hard for most user to investigate something from current
pg_stat_lwlocks. You might need to expose the lwlock name and its
short description....

>>>>      Another comment is; local_calls/waits/time_ms are really required?
>>>>      I'm not sure how those info would help the performance debugging.
>>>
>>>
>>>
>>> I think there are some needs to observe/determine how your test
>>> query is affected by the other workload from the other sessions.
>>> So, splitting local and shared statistics would be nice to have.
>>> Just my thought though.
>>
>>
>> What I don't like is that a session can see only local stats of its own
>> session. It's hard to monitor local stats. Imagine the case where you'd
>> like to monitor local stats of each pgbench session. To monitor such
>> stats, you need to modify pgbench so that its each session monitors
>> its own local stats. Even if you run a monitoring software, it cannot
>> collect those stats because they don't belong to the session that it uses.
>
>
> Ok. I'm waiting more comments from others.
> Dropping it is easy for me, but any other comments? Josh?

What I was thinking useful is to make the stats collector collect also local
stats and report them for each backend ID or distinct query (e.g., adding
such stats into pg_stat_statements, though this would not be good idea).
Which enables every session to see any local stats.

Regards,

-- 
Fujii Masao