Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Remi Colinet]

That's a good point.

A command is more straightforward because it targets only one backend.
The user is supposed to know which backend pid is taking a long time to
complete based on pg_stat_activity().

This is somehow the same approach as EXPLAIN command.
But the use is limited to psql utility. And this adds one more command.

I see 2 possible choices:

1 - either convert the command into a table.
This is the way it is done on Oracle database with v$session_longops view.
Obviously, this requires probing the status of each backend. This
inconvenient can be mitigated by using a threeshold of a few seconds before
considering a backend progression. v$session_longops only reports long
running queries after at least 6 seconds of execution.
This is less efficient that targeting directly a given pid or backend id.
But this is far better for SQL.

2 - either convert the command into a function
The advantage of a function is that it can accept parameters. So parameters
could be the pid of the backend, the verbosity level, the format (text,
json, ).
This would not reduce the options of the current command. And then a view
could be created on top of the function.


May be a mix of both a function with parameters and a view created on the
function is the solution.

Regards
Remi

2017-05-06 5:57 GMT+02:00 Jaime Casanova :

> On 5 May 2017 at 22:38, Vinayak Pokale  wrote:
> >
> > On Mon, Apr 17, 2017 at 9:09 PM, Remi Colinet 
> > wrote:
> >>
> >> Hello,
> >>
> >> I've implemented a new command named PROGRESS to monitor progression of
> >> long running SQL queries in a backend process.
> >>
> > Thank you for the patch.
> >
>
> sorry if i'm bikeshedding to soon but... why a command instead of a
> function?
> something like pg_progress_backend() will be in sync with
> pg_cancel_backend()/pg_terminate_backend() and the result of such a
> function could be usable by a tool to examine a slow query status
>
> --
> Jaime Casanova  www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Remi Colinet]

Do you have more details about the failed tests?

Regards,
Remi

2017-05-06 5:38 GMT+02:00 Vinayak Pokale :

>
> On Mon, Apr 17, 2017 at 9:09 PM, Remi Colinet 
> wrote:
>
>> Hello,
>>
>> I've implemented a new command named PROGRESS to monitor progression of
>> long running SQL queries in a backend process.
>>
>> Thank you for the patch.
>
> I am testing your patch but after applying your patch other regression
> test failed.
>
> $ make installcheck
> 13 of 178 tests failed.
>
> Regards,
> Vinayak
>

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Jaime Casanova]

On 5 May 2017 at 22:38, Vinayak Pokale  wrote:
>
> On Mon, Apr 17, 2017 at 9:09 PM, Remi Colinet 
> wrote:
>>
>> Hello,
>>
>> I've implemented a new command named PROGRESS to monitor progression of
>> long running SQL queries in a backend process.
>>
> Thank you for the patch.
>

sorry if i'm bikeshedding to soon but... why a command instead of a function?
something like pg_progress_backend() will be in sync with
pg_cancel_backend()/pg_terminate_backend() and the result of such a
function could be usable by a tool to examine a slow query status

-- 
Jaime Casanova  www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Vinayak Pokale]

On Mon, Apr 17, 2017 at 9:09 PM, Remi Colinet 
wrote:

> Hello,
>
> I've implemented a new command named PROGRESS to monitor progression of
> long running SQL queries in a backend process.
>
> Thank you for the patch.

I am testing your patch but after applying your patch other regression test
failed.

$ make installcheck
13 of 178 tests failed.

Regards,
Vinayak

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Remi Colinet]

2017-04-19 18:41 GMT+02:00 Maksim Milyutin :

> On 19.04.2017 17:13, Remi Colinet wrote:
>
>> Maksim,
>>
>>
>> 2017-04-18 20:31 GMT+02:00 Maksim Milyutin > >:
>>
>> On 18.04.2017 17:39, Remi Colinet wrote:
>>
>>
>> Regarding the queryDesc state of SQL query upon receiving a
>> request to
>> report its execution progress, it does not bring any issue. The
>> request
>> is noted when the signal is received by the monitored backend.
>> Then, the
>> backend continues its execution code path. When interrupts are
>> checked
>> in the executor code, the request will be dealt.
>>
>>
>> Yes, interrupts are checked in the CHECK_FOR_INTERRUPTS entries.
>>
>> When the request is being dealt, the monitored backend will stop
>> its
>> execution and report the progress of the SQL query. Whatever is
>> the
>> status of the SQL query, progress.c code checks the status and
>> report
>> either that the SQL query does not have a valid status, or
>> otherwise the
>> current execution state of the SQL query.
>>
>> SQL query status checking is about:
>> - idle transaction
>> - out of transaction status
>> - null planned statement
>> - utility command
>> - self monitoring
>>
>> Other tests can be added if needed to exclude some SQL query
>> state. Such
>> checking is done in void HandleProgressRequest(void).
>> I do not see why a SQL query progression would not be possible
>> in this
>> context. Even when the queryDescc is NULL, we can just report a
>> > transaction> output. This is currently the case with the patch
>> suggested.
>>
>>
>> It's interesting question - how much the active query is in a usable
>> state on the stage of execution. Tom Lane noticed that
>> CHECK_FOR_INTERRUPTS doesn't give us 100% guarantee about full
>> consistency [1].
>>
>>
>> I wonder what you mean about usable state.
>>
>>
> A usable query state is suitable for analysis, IOW we have consistent
> QueryDesc object. This term was introduced by Tom Lane in [1]. I suppose he
> meant the case when a query fails with error and before transaction aborts
> we bump into *CHECK_FOR_INTERRUPTS* in the place where QueryDesc may be
> inconsistent and further reading from it will give us invalid result.
>

I could indeed trigger a segmentation fault because the nodes of the tree
may be under freeing. Some node may be partially filled for instance. But
each node can be checked against null pointer once the monitored backend is
no more executing its query and is dumping its progress state. So this is
not a big deal in fact.


>
>
> Currently, the code suggested tests the queryDesc pointer and all the
>> sub nodes pointers in order to detect NULL pointers. When the progress
>> report is collected by the backend, this backend does the collect and
>> consequently does not run the query. So the query tree is not being
>> modified. At this moment, whatever is the query state, we can manage to
>> deal with its static state. It is only a tree which could also be just a
>> NULL pointer in the most extreme case. Such case is dealt in the current
>> code.
>>
>>
> Perhaps the deep checking of QueryDesc would allow us to consider it as
> consistent.
>
>
> 1. https://www.postgresql.org/message-id/24182.1472745492%40sss.pgh.pa.us
>
>
> --
> Maksim Milyutin
> Postgres Professional: http://www.postgrespro.com
> Russian Postgres Company
>

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Maksim Milyutin]

On 19.04.2017 17:13, Remi Colinet wrote:

Maksim,


2017-04-18 20:31 GMT+02:00 Maksim Milyutin >:

On 18.04.2017 17:39, Remi Colinet wrote:


Regarding the queryDesc state of SQL query upon receiving a
request to
report its execution progress, it does not bring any issue. The
request
is noted when the signal is received by the monitored backend.
Then, the
backend continues its execution code path. When interrupts are
checked
in the executor code, the request will be dealt.


Yes, interrupts are checked in the CHECK_FOR_INTERRUPTS entries.

When the request is being dealt, the monitored backend will stop its
execution and report the progress of the SQL query. Whatever is the
status of the SQL query, progress.c code checks the status and
report
either that the SQL query does not have a valid status, or
otherwise the
current execution state of the SQL query.

SQL query status checking is about:
- idle transaction
- out of transaction status
- null planned statement
- utility command
- self monitoring

Other tests can be added if needed to exclude some SQL query
state. Such
checking is done in void HandleProgressRequest(void).
I do not see why a SQL query progression would not be possible
in this
context. Even when the queryDescc is NULL, we can just report a
 output. This is currently the case with the patch
suggested.


It's interesting question - how much the active query is in a usable
state on the stage of execution. Tom Lane noticed that
CHECK_FOR_INTERRUPTS doesn't give us 100% guarantee about full
consistency [1].


I wonder what you mean about usable state.



A usable query state is suitable for analysis, IOW we have consistent 
QueryDesc object. This term was introduced by Tom Lane in [1]. I suppose 
he meant the case when a query fails with error and before transaction 
aborts we bump into *CHECK_FOR_INTERRUPTS* in the place where QueryDesc 
may be inconsistent and further reading from it will give us invalid result.




Currently, the code suggested tests the queryDesc pointer and all the
sub nodes pointers in order to detect NULL pointers. When the progress
report is collected by the backend, this backend does the collect and
consequently does not run the query. So the query tree is not being
modified. At this moment, whatever is the query state, we can manage to
deal with its static state. It is only a tree which could also be just a
NULL pointer in the most extreme case. Such case is dealt in the current
code.



Perhaps the deep checking of QueryDesc would allow us to consider it as 
consistent.



1. https://www.postgresql.org/message-id/24182.1472745492%40sss.pgh.pa.us

--
Maksim Milyutin
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


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

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Remi Colinet]

Following on previous email

I have added below some use cases which I find very relevant when we need
to know the progress of a SQL query.
The command can be used by any SQL query (select, update, delete, insert).


The tables used have been created with :

CREATE TABLE T_1M (id integer, md5 text);
INSERT INTO T_1M SELECT generate_series(1,100) AS id,
md5(random()::text) AS md5;

CREATE TABLE T_10M ( id integer, md5 text);
INSERT INTO T_10M SELECT generate_series(1,1000) AS id,
md5(random()::text) AS md5;


All the different leaf node types are implemented.



1/ Parallel select with sort (no index)
===

=> Terminal running the long SQL query:
test=# select * from t_10M order by md5;

=> Terminal monitoring SQL query progression:
test=# select pid,query from pg_stat_activity ;
  pid  |  query
---+--
  8062 |
  8065 |
 19605 | select pid,query from pg_stat_activity ;
 20830 | select * from t_10M order by md5;
 20832 | select * from t_10M order by md5;
 20833 | select * from t_10M order by md5;
  8060 |
  8059 |
  8061 |
(9 rows)

test=# PROGRESS 20830
test-# ;
 PLAN PROGRESS

 Gather Merge
   ->  Sort=> dumping tuples to tapes / merging tapes
 rows r/w merge 2167923/2167908 rows r/w effective 0/3514320 0%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=#
test=#
test=# PROGRESS 20830;
   PLAN PROGRESS

 Gather Merge
   ->  Sort=> final merge sort on tapes
 rows r/w merge 4707198/4691167 rows r/w effective 16016/3514320 0%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=# PROGRESS 20830;
PLAN
PROGRESS
-
 Gather Merge
   ->  Sort=> final merge sort on tapes
 rows r/w merge 4809857/4691167 rows r/w effective 118675/3514320 3%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=# PROGRESS 20830;
PLAN
PROGRESS
-
 Gather Merge
   ->  Sort=> final merge sort on tapes
 rows r/w merge 4883715/4691167 rows r/w effective 192533/3514320 5%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=# PROGRESS 20830;
PLAN
PROGRESS
-
 Gather Merge
   ->  Sort=> final merge sort on tapes
 rows r/w merge 4948381/4691167 rows r/w effective 257199/3514320 7%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=# PROGRESS 20830;
PLAN
PROGRESS
-
 Gather Merge
   ->  Sort=> final merge sort on tapes
 rows r/w merge 5022137/4691167 rows r/w effective 330955/3514320 9%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=# PROGRESS 20830;
PLAN
PROGRESS
--
 Gather Merge
   ->  Sort=> final merge sort on tapes
 rows r/w merge 5079083/4691167 rows r/w effective 387901/3514320
11%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=# PROGRESS 20830;
PLAN
PROGRESS
--
 Gather Merge
   ->  Sort=> final merge sort on tapes
 rows r/w merge 5144499/4691167 rows r/w effective 453317/3514320
12%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 3514321/4166700 84%
(5 rows)

test=# PROGRESS 20830;
PLAN PROGRESS
--
 
(1 row)

test=#

SQL query was interrupted before completion


2/ Insert into table
=

=> Terminal running the long SQL query:
test=# INSERT INTO T_10M SELECT generate_series(1001, 1200)  AS id,
md5(random()::text) AS md5;

=> Terminal monitoring SQL query progression:
test=# PROGRESS 20830;
PLAN PROGRESS
--
 
(1 row)

test=#
test=# PROGRESS 20830;
 PLAN PROGRESS
---
 Insert => rows 718161
   ->  ProjectSet
 ->  Result
(3 rows)

test=# PROGRESS 20830;
 PLAN PROGRESS

 Insert => rows 1370255
   ->  ProjectSet
 ->  Result
(3 rows)

test=# PROGRESS 20830;
 PLAN PROGRESS

 Insert => rows 

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Remi Colinet]

Maksim,


2017-04-18 20:31 GMT+02:00 Maksim Milyutin :

> On 18.04.2017 17:39, Remi Colinet wrote:
>
>> Hello Maksim,
>>
>> The core implementation I suggested for the new PROGRESS command uses
>> different functions from the one used by EXPLAIN for its core
>> implementation.
>> Some source code is shared with EXPLAIN command. But this shared code is
>> only related to quals, properties, children, subPlans and few other nodes.
>>
>> All other code for PROGRESS is new code.
>>
>> I don't believe explain.c code can be fully shared with the one of the
>> new PROGRESS command. These 2 commands have different purposes.
>> The core code used for the new PROGRESS command is very different from
>> the core code used for EXPLAIN.
>>
>>
> Perhaps you will be forced to duplicate significant snippets of
> functionality from explain.c into your progress.c.
>

Currently, few code is duplicated between EXPLAIN and PROGRESS commands.
The duplicated code could be moved to file src/backend/commands/report.c
which is used to gather shared code between the 2 commands. I will try to
complete this code sharing as much as possible.

The main point is that PROGRESS uses the same design pattern as EXPLAIN by
parsing the query tree. The work horse of the PROGRESS command is
ProgressNode() which calls recursively sub nodes until we reach leaf nodes
such as SeqScan, IndexScan, TupleStore, Sort, Material, ... . EXPLAIN
command uses a similar work horse with function ExplainNode() which
eventually calls different leaf nodes.

Some of the leaf nodes which are common to the 2 commands have been put in
the file src/backend/commands/report.c. May be some further code sharing is
also possible for the work horse by using a template function which would
call EXPLAIN specific leaf node functions or PROGRESS specific leaf node
functions.


>
>
>> Regarding the queryDesc state of SQL query upon receiving a request to
>> report its execution progress, it does not bring any issue. The request
>> is noted when the signal is received by the monitored backend. Then, the
>> backend continues its execution code path. When interrupts are checked
>> in the executor code, the request will be dealt.
>>
>>
> Yes, interrupts are checked in the CHECK_FOR_INTERRUPTS entries.
>
> When the request is being dealt, the monitored backend will stop its
>> execution and report the progress of the SQL query. Whatever is the
>> status of the SQL query, progress.c code checks the status and report
>> either that the SQL query does not have a valid status, or otherwise the
>> current execution state of the SQL query.
>>
>> SQL query status checking is about:
>> - idle transaction
>> - out of transaction status
>> - null planned statement
>> - utility command
>> - self monitoring
>>
>> Other tests can be added if needed to exclude some SQL query state. Such
>> checking is done in void HandleProgressRequest(void).
>> I do not see why a SQL query progression would not be possible in this
>> context. Even when the queryDescc is NULL, we can just report a > transaction> output. This is currently the case with the patch suggested.
>>
>>
> It's interesting question - how much the active query is in a usable state
> on the stage of execution. Tom Lane noticed that CHECK_FOR_INTERRUPTS
> doesn't give us 100% guarantee about full consistency [1].
>

I wonder what you mean about usable state.

Currently, the code suggested tests the queryDesc pointer and all the sub
nodes pointers in order to detect NULL pointers. When the progress report
is collected by the backend, this backend does the collect and consequently
does not run the query. So the query tree is not being modified. At this
moment, whatever is the query state, we can manage to deal with its static
state. It is only a tree which could also be just a NULL pointer in the
most extreme case. Such case is dealt in the current code.


>
> So far, I've found this new command very handy. It allows to evaluate
>> the time needed to complete a SQL query.
>>
>>
> Could you explain how you get the percent of execution for nodes of plan
> tree and overall for query?
>

The progress of execution of the query is computed as follows at 2
different places for each leaf node type (Scan, IndexScan, Sort, Material,
TupleStore, ...):

- one place in the executor code, or in access methods code, or in sort
utilities code, used during the execution of the SQL query in which
following values are counted for instance: rows R/W, blocks, R/W, tapes R/W
used for sort, tuple store R/W, ... . Some of these values are already
computed in the current Postgresql official source code. Some other values
had to be added and collected.

- one place in the leaf function of each node type (ProgressScan(),
ProgressSort(), ...) in which percents are computed and are then dumped
together with raw values collected during execution, in the report. The
dump details can be selected with the VERBOSE option of the PROGRESS
command (For 

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Maksim Milyutin]

On 18.04.2017 17:39, Remi Colinet wrote:

Hello Maksim,

The core implementation I suggested for the new PROGRESS command uses
different functions from the one used by EXPLAIN for its core
implementation.
Some source code is shared with EXPLAIN command. But this shared code is
only related to quals, properties, children, subPlans and few other nodes.

All other code for PROGRESS is new code.

I don't believe explain.c code can be fully shared with the one of the
new PROGRESS command. These 2 commands have different purposes.
The core code used for the new PROGRESS command is very different from
the core code used for EXPLAIN.



Perhaps you will be forced to duplicate significant snippets of 
functionality from explain.c into your progress.c.




Regarding the queryDesc state of SQL query upon receiving a request to
report its execution progress, it does not bring any issue. The request
is noted when the signal is received by the monitored backend. Then, the
backend continues its execution code path. When interrupts are checked
in the executor code, the request will be dealt.



Yes, interrupts are checked in the CHECK_FOR_INTERRUPTS entries.


When the request is being dealt, the monitored backend will stop its
execution and report the progress of the SQL query. Whatever is the
status of the SQL query, progress.c code checks the status and report
either that the SQL query does not have a valid status, or otherwise the
current execution state of the SQL query.

SQL query status checking is about:
- idle transaction
- out of transaction status
- null planned statement
- utility command
- self monitoring

Other tests can be added if needed to exclude some SQL query state. Such
checking is done in void HandleProgressRequest(void).
I do not see why a SQL query progression would not be possible in this
context. Even when the queryDescc is NULL, we can just report a  output. This is currently the case with the patch suggested.



It's interesting question - how much the active query is in a usable 
state on the stage of execution. Tom Lane noticed that 
CHECK_FOR_INTERRUPTS doesn't give us 100% guarantee about full 
consistency [1].



So far, I've found this new command very handy. It allows to evaluate
the time needed to complete a SQL query.



Could you explain how you get the percent of execution for nodes of plan 
tree and overall for query?



A further improvement would be to report the memory, disk and time
resources used by the monitored backend. An overuse of memory, disk and
time resources can prevent the SQL query to complete.



This functionality is somehow implemented in explain.c. You can see my 
patch to this file [2]. I only manipulate runtime statistics (data in 
the structure 'Instrumentation') to achieve the printing state of 
running query.



1. https://www.postgresql.org/message-id/24182.1472745492%40sss.pgh.pa.us
2. 
https://github.com/postgrespro/pg_query_state/blob/master/runtime_explain.patch


--
Maksim Milyutin
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


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

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Remi Colinet]

Hello Maksim,

The core implementation I suggested for the new PROGRESS command uses
different functions from the one used by EXPLAIN for its core
implementation.
Some source code is shared with EXPLAIN command. But this shared code is
only related to quals, properties, children, subPlans and few other nodes.

All other code for PROGRESS is new code.

I don't believe explain.c code can be fully shared with the one of the new
PROGRESS command. These 2 commands have different purposes.
The core code used for the new PROGRESS command is very different from the
core code used for EXPLAIN.

I only extracted some common code from explain.c and put it in report.c
which is used by progress.c.This code is valid for Plan and PlanState.

The code shared is:

ReportPreScanNode() renamed from ExplainPreScanNode()
ReportBeginOutput() renamed from ExplainBeginOutput()
ReportEndOutput() renamed from ExplainEndOutput()
ReportOpenGroup() ...
ReportProperties() ...
ReportPropertyText() ...
ReportHasChildren() ...
ReportSubPlans() ...
ReportMemberNodes() ...
ReportCustomChildren() ...
ReportCloseGroup() ...

ExplainState has been renamed ReportState.

Regarding the queryDesc state of SQL query upon receiving a request to
report its execution progress, it does not bring any issue. The request is
noted when the signal is received by the monitored backend. Then, the
backend continues its execution code path. When interrupts are checked in
the executor code, the request will be dealt.

When the request is being dealt, the monitored backend will stop its
execution and report the progress of the SQL query. Whatever is the status
of the SQL query, progress.c code checks the status and report either that
the SQL query does not have a valid status, or otherwise the current
execution state of the SQL query.

SQL query status checking is about:
- idle transaction
- out of transaction status
- null planned statement
- utility command
- self monitoring

Other tests can be added if needed to exclude some SQL query state. Such
checking is done in void HandleProgressRequest(void).
I do not see why a SQL query progression would not be possible in this
context. Even when the queryDescc is NULL, we can just report a  output. This is currently the case with the patch suggested.

So far, I've found this new command very handy. It allows to evaluate the
time needed to complete a SQL query.

A further improvement would be to report the memory, disk and time
resources used by the monitored backend. An overuse of memory, disk and
time resources can prevent the SQL query to complete.

Best regards
Remi


2017-04-18 15:00 GMT+02:00 Maksim Milyutin :

> Hi!
>
>
> On 17.04.2017 15:09, Remi Colinet wrote:
>
>> Hello,
>>
>> I've implemented a new command named PROGRESS to monitor progression of
>> long running SQL queries in a backend process.
>>
>>
>> Use case
>> ===
>>
>> A use case is shown in the below example based on a table named t_10m
>> with 10 millions rows.
>>
>> The table has been created with :
>>
>> CREATE TABLE T_10M ( id integer, md5 text);
>> INSERT INTO T_10M SELECT generate_series(1,1000) AS id,
>> md5(random()::text) AS md5;
>>
>> 1/ Start a first psql session to run long SQL queries:
>>
>> [pgadm@rco ~]$ psql -A -d test
>> psql (10devel)
>> Type "help" for help.
>> test=#
>>
>> The option -A is used to allow rows to be output without formatting work.
>>
>> Redirect output to a file in order to let the query run without terminal
>> interaction:
>> test=# \o out
>>
>> Start a long running query:
>> test=# select * from t_10M order by md5;
>>
>> 2/ In a second psql session, list the backend pid and their SQL query
>>
>> [pgadm@rco ~]$ psql -d test
>> psql (10devel)
>> Type "help" for help.
>>
>> test=# select pid, query from pg_stat_activity ;
>>   pid  |   query
>> ---+---
>>  19081 |
>>  19084 |
>>  19339 | select pid, query from pg_stat_activity ;
>>  19341 | select * from t_10m order by md5;
>>  19727 | select * from t_10m order by md5;
>>  19726 | select * from t_10m order by md5;
>>  19079 |
>>  19078 |
>>  19080 |
>> (9 rows)
>>
>> test=#
>>
>> Chose the pid of the backend running the long SQL query to be monitored.
>> Above example is a parallel SQL query. Lowest pid is the main backend of
>> the query.
>>
>> test=# PROGRESS 19341;
>>PLAN
>> PROGRESS
>> 
>> ---
>>  Gather Merge
>>->  Sort=> dumping tuples to tapes
>>  rows r/w merge 0/0 rows r/w effective 0/2722972 0%
>>  Sort Key: md5
>>  ->  Parallel Seq Scan on t_10m => rows 2751606/3954135 69% blks
>> 125938/161222 78%
>> (5 rows)
>>
>> test=#
>>
>> The query of the monitored backend is:
>> test=# select * from t_10M order by md5;
>>
>> Because the table has 10 millions of rows, the sort is done on tapes.
>>
>>
>> Design of the 

Re: [HACKERS] [PATCH] New command to monitor progression of long running queries

[Maksim Milyutin]

Hi!

On 17.04.2017 15:09, Remi Colinet wrote:

Hello,

I've implemented a new command named PROGRESS to monitor progression of
long running SQL queries in a backend process.


Use case
===

A use case is shown in the below example based on a table named t_10m
with 10 millions rows.

The table has been created with :

CREATE TABLE T_10M ( id integer, md5 text);
INSERT INTO T_10M SELECT generate_series(1,1000) AS id,
md5(random()::text) AS md5;

1/ Start a first psql session to run long SQL queries:

[pgadm@rco ~]$ psql -A -d test
psql (10devel)
Type "help" for help.
test=#

The option -A is used to allow rows to be output without formatting work.

Redirect output to a file in order to let the query run without terminal
interaction:
test=# \o out

Start a long running query:
test=# select * from t_10M order by md5;

2/ In a second psql session, list the backend pid and their SQL query

[pgadm@rco ~]$ psql -d test
psql (10devel)
Type "help" for help.

test=# select pid, query from pg_stat_activity ;
  pid  |   query
---+---
 19081 |
 19084 |
 19339 | select pid, query from pg_stat_activity ;
 19341 | select * from t_10m order by md5;
 19727 | select * from t_10m order by md5;
 19726 | select * from t_10m order by md5;
 19079 |
 19078 |
 19080 |
(9 rows)

test=#

Chose the pid of the backend running the long SQL query to be monitored.
Above example is a parallel SQL query. Lowest pid is the main backend of
the query.

test=# PROGRESS 19341;
   PLAN
PROGRESS
---
 Gather Merge
   ->  Sort=> dumping tuples to tapes
 rows r/w merge 0/0 rows r/w effective 0/2722972 0%
 Sort Key: md5
 ->  Parallel Seq Scan on t_10m => rows 2751606/3954135 69% blks
125938/161222 78%
(5 rows)

test=#

The query of the monitored backend is:
test=# select * from t_10M order by md5;

Because the table has 10 millions of rows, the sort is done on tapes.


Design of the command
=

The design of the patch/command is:
- the user issue the "PROGRESS pid" command from a psql session. The pid
is the one of the backend which runs the SQL query for which we want to
get a progression report. It can be determined from the view
pg_stat_activity.
- the monitoring backend, upon receiving the "PROGRESS pid" command from
psql utility used in step above, sends a signal to the backend whose
process pid is the one provided in the PROGRESS command.
- the monitored backend receives the signal and notes the request as for
any interrupt. Then, it continues its execution of its SQL query until
interrupts can be serviced.
- when the monitored process can service the interrupts, it deals with
the progress request by collecting its execution tree with the execution
progress of each long running node. At this time, the SQL query is no
more running. The progression of each node is calculated during the
execution of the SQL query which is at this moment stopped. The
execution tree is dumped in shared memory pages allocated at the start
of the server. Then, the monitored backend set a latch on which the
monitoring process is waiting for. It then continues executing its SQL
query.
- the monitoring backend collects the share memory data dumped by the
monitored backed, and sends it to its psql session, as a list of rows.

The command PROGRESS does not incur any slowness on the running query
because the execution progress is only computed upon receiving the
progress request which is supposed to be seldom used.

The code heavily reuses the one of the explain command. In order to
share as much code as possible with the EXPLAIN command, part of the
EXPLAIN code which deals with reporting quals for instance, has been
moved to a new report.c file in the src/backend/commands folder. This
code in report.c is shared between explain.c source code and PROGRESS
command source code which is in progress.c file.

The progression reported by PROGRESS command is given in terms of rows,
blocks, bytes and percents. The values displayed depend on the node type
in the execution plan.

The current patch implements all the possible nodes which could take a
lot of time:
- Sequential scan nodes with rows and block progress (node type
T_SeqScan, T_SampleScan, T_BitmapHeaepScan, T_SubqueryScan,
T_FunctionScan, T_ValuesScan, T_CteScan, T_WorkTableScan)
- Tuple id scan node with rows and blocks progress (T_TidScan)
- Limit node with rows progress (T_Limit)
- Foreign and custom scan with rows and blocks progress (T_ForeignScan,
T_CustomScan)
- Index scan, index only scan and bitmap index scan with rows and blocks
progress


Use cases


Some further examples of use are shown below in the test_v1.txt file.


What do you make of this idea/patch?

Does it make sense?

Any suggestion is welcome.

The current patch is still work in progress. It is meanwhile stable. It