Re: Background Processes and reporting - Mailing list pgsql-hackers

From Vladimir Borodin
Subject Re: Background Processes and reporting
Date
Msg-id E80B5F56-FA1E-47FE-B7A3-9C7C1DFC43F2@simply.name
Whole thread Raw
In response to Background Processes and reporting  (Andres Freund <andres@anarazel.de>)
Responses Re: Background Processes and reporting
List pgsql-hackers

11 марта 2016 г., в 22:16, Andres Freund <andres@anarazel.de> написал(а):

Hi,

We now have "Provide much better wait information in pg_stat_activity"
and "Add a generic command progress reporting facility" making it easier
to provide insight into the system.


While working on the writeback control / checkpoint sorting patch I'd
the following statement in BufferSync()'s main loop:

   fprintf(stderr, "\33[2K\rto_scan: %d, scanned: %d, %%processed: %.2f, %%writeouts: %.2f",
           num_to_scan, num_processed,
           (((double) num_processed) / num_to_scan) * 100,
           ((double) num_written / num_processed) * 100);

which basically printed the progress of a checkpoint, and some
additional detail to stderr. Quite helpful to see whether progress is
"unsteady".

Obviously that's not something that could be committed.

So I'm wondering how we can make it possible to use the aforementioned
"progress reporting facility" to monitor checkpoint progress. To which
Robert replied on IM:
"it wouldn't quite help with that because the checkpointer doesn't show
up as a regular backend"


It seems rather worthwhile to think about how we can expand the coverage
of progress tracking to other types of background processes.


Similarly for the wait event stuff - checkpointer, wal writer,
background writer are in many cases processes that very often are
blocked on locks, IO and such.  Thus restricting the facility to
database connected processes seems like a loss.

It was many times stated in threads about waits monitoring [0, 1, 2] and supported by different people, but ultimately waits information was stored in PgBackendStatus. Can’t we think one more time about implementation provided by Ildus and Alexander here [3]? That implementation included 1. waits monitoring for all process, 2. ability to trace waits of a particular process to file, 3. wait events history with sampling every N ms and 4. configurable measurement of wait timings. It has much more features, has been used in production on 100+ databases (patched 9.4) and gives wide opportunities for further development. Of course, huge work should be done to rebase across current master and cleanup but IMHO it is much better approach. Seems that current implementation doesn’t give reasonable ways to implement all that features and it is really sad.




Greetings,

Andres Freund


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


--
May the force be with you…

pgsql-hackers by date:

Previous
From: Joel Jacobson
Date:
Subject: Re: [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.
Next
From: Robert Haas
Date:
Subject: Re: [COMMITTERS] pgsql: Provide much better wait information in pg_stat_activity.