Re: pg_stat_lwlock wait time view - Mailing list pgsql-hackers
From | Satoshi Nagayasu |
---|---|
Subject | Re: pg_stat_lwlock wait time view |
Date | |
Msg-id | CAA8sozdm7T1Hibwy3ucb6O012fzGwDfmvCJ1aZzrH7BVmReMxg@mail.gmail.com Whole thread Raw |
In response to | Re: pg_stat_lwlock wait time view (Haribabu Kommi <kommi.haribabu@gmail.com>) |
List | pgsql-hackers |
2016-08-25 13:46 GMT+09:00 Haribabu Kommi <kommi.haribabu@gmail.com>: > On Thu, Aug 25, 2016 at 6:57 AM, Robert Haas <robertmhaas@gmail.com> wrote: >> On Wed, Aug 24, 2016 at 4:23 AM, Haribabu Kommi >> <kommi.haribabu@gmail.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. Is there any other option (rather than gettimeofday()) to measure elapsed time with lower overhead? I've heard about the RDTSC feature (hardware counter) supported by the recent processors, and have found a few articles [1] [2] on its lower overhead than gettimeofday(). [1] http://stackoverflow.com/questions/15623343/using-cpu-counters-versus-gettimeofday [2] http://stackoverflow.com/questions/6498972/faster-equivalent-of-gettimeofday I'm not sure how we can benefit from it so far, because I'm not familiar with this facility and of course I don't have the numbers. In addition to that, I guess it would bring some portability issues. But I'm still curious to know more about these stuff. Anyone has some experiences on it? >> 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. It looks a sort of accuracy-performance trade-off. So, I think the use-cases matter here to get a better design. I guess that's the reason why llya is looking for feature requests from DBA in another thread [3]. [3] https://www.postgresql.org/message-id/CAG95seUAQVj09KzLwU%2Bz1B-GqdMqerzEkPFR3hn0q88XzMq-PA@mail.gmail.com Regards, -- Satoshi Nagayasu <snaga@uptime.jp>
pgsql-hackers by date: