Re: profiling
On 14/01/2021 12:14, Павел Еремин wrote: Hello. I am interested in the question of profiling. As far as I understand, this issue has not been resolved in postgres. And I see the urgency of this problem. Are there any postgres development plans for this functionality? What exactly do you mean by profiling? You can profile internal C functions with 'perf' or 'gprof' or similar. For profiling applications using PostgreSQL, pg_stat_statement() is very helpful. And there is a plugin for profiling PL/pgSQL functions, IIRC pgAdmin has a GUI for that. - Heikki
profiling
Hello. I am interested in the question of profiling. As far as I understand, this issue has not been resolved in postgres. And I see the urgency of this problem. Are there any postgres development plans for this functionality? regards, Eremin Pavel.
Re: Wait profiling
On Fri, Jul 10, 2020 at 10:37 PM Alvaro Herrera wrote: > > On 2020-Jul-10, Daniel Wood wrote: > > > After nearly 5 years does something like the following yet exist? > > https://www.postgresql.org/message-id/559d4729.9080...@postgrespro.ru > > Yes, we have pg_stat_activity.wait_events which implement pretty much > what Ildus describes there. > > > 1) An option to "explain" to produce a wait events profile. > > postgres=# explain (analyze, waitprofile) update pgbench_accounts set > > bid=bid+1 where aid < 200; > > ... > > Execution time: 23111.231 ms > > There's an out-of-core extension, search for pg_wait_sampling. I > haven't tested it yet ... I use it, and I know multiple people that are also using it (or about to, it's currently being packaged) in production. It's working quite well and is compatible with pg_stat_statements' queryid. You can see some examples of dashboards that can be built on top of this extension at https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_wait_sampling.html.
Re: Wait profiling
On 2020-Jul-10, Daniel Wood wrote: > After nearly 5 years does something like the following yet exist? > https://www.postgresql.org/message-id/559d4729.9080...@postgrespro.ru Yes, we have pg_stat_activity.wait_events which implement pretty much what Ildus describes there. > 1) An option to "explain" to produce a wait events profile. > postgres=# explain (analyze, waitprofile) update pgbench_accounts set > bid=bid+1 where aid < 200; > ... > Execution time: 23111.231 ms There's an out-of-core extension, search for pg_wait_sampling. I haven't tested it yet ... -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Wait profiling
After nearly 5 years does something like the following yet exist? https://www.postgresql.org/message-id/559d4729.9080...@postgrespro.ru I feel that it would be useful to have the following two things. One PG enhancement and one standard extension. 1) An option to "explain" to produce a wait events profile. postgres=# explain (analyze, waitprofile) update pgbench_accounts set bid=bid+1 where aid < 200; ... Execution time: 23111.231 ms 62.6% BufFileRead 50.0% CPU 9.3% LWLock It uses a PG timer to do this. 2) An extension based function like: select pg_wait_profile(pid, nSeconds, timerFrequency) to return the same thing for an already running query. Useful if you want examine some already long running query that is taking too long. Neither of these would be doing the heavy weight pg_stat_activity but directly poll the wait event in PROC. I've already coded the EXPLAIN option. Furthermore, can't we just remove the following "IF" test from pgstat_report_wait_{start,end}? if (!pgstat_track_activities || !proc) return; Just do the assignment of wait_event_info always. We should use a dummy PGPROC assigned to MyProc until we assign the one in the procarray in shared memory. That way we don't need the "!proc" test. About the only thing I'd want to verify is whether wait_event_info is on the same cache lines as anything else having to do with snapshots. If I recall correctly the blanks lines above I've used to make this more readable will disappear. :-( - Dan Wood