Re: pg_stat_lwlock wait time view

On Wed, Aug 24, 2016 at 4:23 AM, Haribabu Kommi
<kommi(dot)haribabu(at)gmail(dot)com> wrote:
> There was some discussion earlier in adding pg_stat_lwlock view in [1].
> The main objections which I observed for that patch was showing LWLOCK
> information to the user that don't understand what this lock used for and etc.
>
> Currently as part of wait_event information in pg_stat_activity the LWLOCK
> information is available to the user and the details of LWLOCk's that are
> used in PostgreSQL are also listed in the documentation and with their
> purpose.
>
> So I feel it may be worth to add this view to find out the wait times of the
> LWLOCK's. This information can be useful to find out the bottlenecks
> around LWLOCK's in production environments. But adding the timing calculations
> may cause performance problem. Is there any need of writing this stats
> information to file? As this just provides the wait time information.
>
> Based on the performance impact with the additional timing calculations,
> we can decide the view default behavior, Are there any objections to the
> concept?

There have been some other recent threads on extending the wait event
stuff. If you haven't already read those, you should, because the
issues are closely related. I think that timing LWLock waits will be
quite expensive. I believe that what the Postgres Pro folks want to
do is add up the wait times or maybe keep a history of waits (though
maybe I'm wrong about that), but showing them in pg_stat_activity is
another idea. That's likely to add some synchronization overhead
which might be even greater in this case than for a feature that just
publishes accumulated times, but maybe it's all a drop in the bucket
compared to the cost of calling gettimeofday() in the first place.

Personally, my preferred solution is still to have a background worker
that samples the published wait events and rolls up statistics, but
I'm not sure I've convinced anyone else. It could report the number
of seconds since it detected a wait event other than the current one,
which is not precisely the same thing as tracking the length of the
current wait but it's pretty close. I don't know for sure what's best
here - I think some experimentation and dialog is needed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company