Re: RFC: replace pg_stat_activity.waiting with something more descriptive

Hi all

On Thu, Jun 25, 2015 at 12:37 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> 2. Add 2 new columns to pg_stat_activity
> waiting_resource - true for waits other heavy wait locks, false
> otherwise
> wait_event - description code for the wait event
>
> 3. Add new view 'pg_stat_wait_event' with following info:
> pid - process id of this backend
> waiting - true for any form of wait, false otherwise
> wait_event_type - Heavy Weight Lock, Light Weight Lock, I/O wait, etc
> wait_event - Lock (Relation), Lock (Relation Extension), etc

Personally I think, that tracking waits is a not a good idea for
pg_stat_activity (at least in that straight-forward manner). One
process can wait for lots of things between 2 sampling of
pg_stat_activity and that sampling can be pretty useless.

My approach (about which Ive had a talk mentioned by Jim and which I
hope to finalize and submit within a few days) is a bit different and
I believe is more useful:

1. Some sort of histogram of top waits within entire database by pid.
That will be an approximate one, because I hardly believe there is a
possibility to make a precise one without significant overhead.

2. Some cyclic buffer of more precise wait statistic inside each
worker. Sampling may be turned on if we see some issues in histogram
(1) and want to have some more details.

> Do you think 2nd or 3rd could be viable way to proceed for this feature?
>
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com

--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik(at)postgresql-consulting(dot)com