Re: pg_stat_lwlock wait time view

On Thu, Aug 25, 2016 at 6:57 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Aug 24, 2016 at 4:23 AM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>>
>> 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.

Yes, I agree this is an issue for the cases where the wait time is smaller
than the logic that is added to calculate the wait time. Even if we use
clock_gettime with CLOCK_REALTIME_COARSE there will be some
overhead, as this clock method is 8 times faster than gettimeofday
but not that accurate in result. May be we can use the clock_getime
instead of gettimeofday in this case, as we may not needed the fine-grained
value.

> 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.

Yes, using of background worker can reduce the load of adding all the
wait time calculations in the main backend. I can give a try by modifying
direct calculation approach and background worker (may be pg_stat_collector)
to find the wait time based on the stat messages that are received from
main backend related to wait start and wait end.

I am not sure with out getting any signal or message from main backend,
how much accurate the data can be gathered from a background worker.

Regards,
Hari Babu
Fujitsu Australia