[HACKERS] [PATCH v2] Progress command to monitor progression of long runningSQL queries - Mailing list pgsql-hackers
| From | Remi Colinet |
|---|---|
| Subject | [HACKERS] [PATCH v2] Progress command to monitor progression of long runningSQL queries |
| Date | |
| Msg-id | CADdR5ny_0dFwnD+suBnV1Vz6NDKbFHeWoV1EDv9buhDCtc3aAA@mail.gmail.com Whole thread Raw |
| Responses |
Re: [HACKERS] [PATCH v2] Progress command to monitor progression oflong running SQL queries
(David Fetter <david@fetter.org>)
Re: [HACKERS] [PATCH v2] Progress command to monitor progression oflong running SQL queries (Pavel Stehule <pavel.stehule@gmail.com>) Re: [HACKERS] [PATCH v2] Progress command to monitor progression oflong running SQL queries (Michael Paquier <michael.paquier@gmail.com>) Re: [HACKERS] [PATCH v2] Progress command to monitor progression oflong running SQL queries (Amit Kapila <amit.kapila16@gmail.com>) |
| List | pgsql-hackers |
Hello,
This is version 2 of the new command name PROGRESS which I wrote in order to monitor long running SQL queries in a Postgres backend process.
====================
The purpose of the command is to monitor long running SQL queries in a backend process to evaluate when they will complete.
First, the user needs to find the pid of the backend process which is executing the long running SQL query for which we want to monitor progression.
This search may be based on the SQL query, the session, the terminal, using the view pg_stat_activity. This view provides the best method to identify the query to be monitored.
First, the user needs to find the pid of the backend process which is executing the long running SQL query for which we want to monitor progression.
This search may be based on the SQL query, the session, the terminal, using the view pg_stat_activity. This view provides the best method to identify the query to be monitored.
Once the pid of the backend is determined, user may run:
psql> PROGRESS [VERBOSE|(FORMAT TEXT|JSON|XML|INLINE)] PID_OF_BACKEND
The output will show a table similar to the one of the EXPLAIN command but with further details about the progression of execution of each node which may take a long time to be executed.
Such nodes are SeqScan (Sequential Scans), IndexScan (Index Scans), Sort (long sorts which require disk tapes mecanism), TupleStore (Store on disks of tuple tables), Limit, HashJoinTable which use one disk files to perform HashJoin.
Other nodes use the previous nodes. For instance, Material nodes use TupleStore nodes.
Such nodes are SeqScan (Sequential Scans), IndexScan (Index Scans), Sort (long sorts which require disk tapes mecanism), TupleStore (Store on disks of tuple tables), Limit, HashJoinTable which use one disk files to perform HashJoin.
Other nodes use the previous nodes. For instance, Material nodes use TupleStore nodes.
The PROGRESS command can be used with the psql \watch command. For instance, user may run:
psql> \watch PROGRESS PID_OF_BACKEND
This is handy to view progression of SQL queries.
Use case=======
A use case is shown in the below example based on a table named t_10m with at least 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,10000000) AS id, md5(random()::text) AS md5;
ANALYSE t_10m;
ANALYSE is needed to have statistics updated. These are used to compare rows fetched with total number of rows and report percentage of execution of query nodes.
If ANALYSE is not run against relations used by the monitored query, statistics may be inaccurate.
1/ Start a first psql session to run a long SQL query:
[pgadm@rco ~]$ psql -A -d test
psql (10devel)
Type "help" for help.
test=#
[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
test=# \o out
Start a long running query:
test=# select * from t_10M order by md5;
test=# select * from t_10M order by md5;
2/ In a second psql session, list the backend pid(s) and their matching SQL query
[pgadm@rco ~]$ psql -d test
psql (10devel)
Type "help" for help.
test=# select * from prg;
pid | query
-------+----------------------
26306 |
26309 |
26311 | select * from t_10m order by md5;
26312 | select * from t_10m order by md5;
26313 | select * from t_10m order by md5;
26330 | select * from prg;
26304 |
26303 |
26305 |
(9 rows)
test=#
[pgadm@rco ~]$ psql -d test
psql (10devel)
Type "help" for help.
test=# select * from prg;
pid | query
-------+----------------------
26306 |
26309 |
26311 | select * from t_10m order by md5;
26312 | select * from t_10m order by md5;
26313 | select * from t_10m order by md5;
26330 | select * from prg;
26304 |
26303 |
26305 |
(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.
Above example is a parallel SQL query. Lowest pid is the main backend of the query.
test=# PROGRESS 26311;
------------------------------
status: <query running>
query: select * from t_10m order by md5;
time used (s): 20
Gather Merge
worker child: 26312
-> Sort => rows r/w merge 0/0 sort 0/1895721 0% tape blocks 10903
Sort Key: md5
-> Parallel Seq Scan on t_10m => rows 1903441/4166657 45% => blks 49924/83334 59%
worker child: 26313
-> Sort => rows r/w merge 0/0 sort 0/1967521 0% tape blocks 11314
Sort Key: md5
-> Parallel Seq Scan on t_10m => rows 1975561/4166657 47% => blks 49927/83334 59%
worker parent: 26311
-> Sort on tapes writing => rows r/w merge 0/0 sort 0/2075590 0% tape blocks 11935
Sort Key: md5
-> Parallel Seq Scan on t_10m => rows 2111550/4166657 50% => blks 49928/83334 59%
total disk space used (MB) 266
(17 rows)
test=#
The query of the monitored backend is:
test=# select * from t_10M order by md5;
test=# select * from t_10M order by md5;
Because the table has 10 millions of rows, the sort is done on tapes.
The output shows the progression in terms of:
- rows with:
- already fetched rows,
- total rows to be fetched,
- blocks with:
- already fetched blocks
- total blocks to be fetched
Each node may have different output types.
Progression is reported in terms of rows, blocks, bytes, and percentages.
Progression is reported in terms of rows, blocks, bytes, and percentages.
Sort nodes show tapes blocks being used and step phase (merge/sort).
Implementation of the command
========================
The design of the 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, and whose reason is PROCSIG_PROGRESS defined in src/include/storage/procsignal
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, and whose reason is PROCSIG_PROGRESS defined in src/include/storage/procsignal
- the monitored backend receives the signal with the reason of the signal (progression request) 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 must be in a consistent state without partial nodes under alllocation or freeing. This is enforced by flags added in the executor. The progression is only collected if the backend is in the function of ExecutorRun(). If execution tree is in a consistent state, it is dumped in shared memory pages allocated at the start of the server. Then, the monitored backend set a latch on which the monitoring backend is waiting for. It then continues executing its SQL query.
- the monitoring backend collects the share memory data dumped, 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 progression is only computed upon receiving the progress request which is supposed to be seldom used.
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.
Parallel queries can also be monitored. The same mecanism is used to monitor child workers with a slight difference: the main worker requests the child progression directly in order to dump the whole progress tree in shared memory.
=====
The patch for version 2 is attached to the current email.
The patch has been rebased on latest commits.
The patch has been rebased on latest commits.
The patch is also available at : https://github.com/colinet/
The diff stat of the patch is:
[root@rco pg]# git diff --stat master..
contrib/auto_explain/auto_
contrib/postgres_fdw/
src/backend/access/heap/
src/backend/access/transam/
src/backend/commands/
src/backend/commands/explain.
src/backend/commands/prepare.
src/backend/commands/
src/backend/commands/report.
src/backend/executor/
src/backend/executor/
src/backend/executor/
src/backend/executor/
src/backend/executor/
src/backend/executor/
src/backend/executor/
src/backend/nodes/bitmapset.
src/backend/nodes/outfuncs.c
src/backend/parser/gram.y
src/backend/postmaster/
src/backend/storage/file/
src/backend/storage/file/fd.
src/backend/storage/ipc/ipci.
src/backend/storage/ipc/
src/backend/storage/ipc/
src/backend/storage/lmgr/
src/backend/storage/lmgr/
src/backend/tcop/postgres.c
src/backend/tcop/pquery.c
src/backend/tcop/utility.c
src/backend/utils/init/
src/backend/utils/sort/
src/backend/utils/sort/
src/backend/utils/sort/
src/include/access/parallel.
src/include/commands/explain.
src/include/commands/prepare.
src/include/commands/report.
src/include/executor/
src/include/executor/
src/include/foreign/fdwapi.h
src/include/miscadmin.h
src/include/nodes/bitmapset.
src/include/nodes/execnodes.
src/include/nodes/extensible.
src/include/nodes/nodes.h
src/include/nodes/parsenodes.
src/include/nodes/plannodes.
src/include/parser/kwlist.h
src/include/pgstat.h
src/include/storage/buffile.
src/include/storage/fd.h
src/include/storage/
src/include/storage/
src/include/tcop/pquery.h
src/include/utils/logtape.h
src/include/utils/tuplesort.
src/include/utils/tuplestore.
58 files changed, 5972 insertions(+), 2619 deletions(-)
[root@rco pg]#
The progress command can be used with the watch command of psql making it more handy to monitor a long running query.
The default format of the PROGRESS command is text inline. It can be set as json, xml, or text as for EXPLAIN command.
Use cases
========
========
Some further examples of use are shown below in the test_v2.txt file with parallel queries, sorts, hashes, scans, json format output.
TODO
=====
=====
Monitor progress of utilities commands such as CREATE INDEX which may take a long time to complete.
Write documentation
Write documentation
Changelog
========
========
v2:
Added JSON, XML, TEXT INLINE format output
Added JSON, XML, TEXT INLINE format output
Added time consumed to run SQL queries
Added SQL query being monitored
Added support for parallel queries
Added disk used by the queries
Rebased on 9th May 2017 commits.
v1:
Initial version with only TEXT format reporting
v1:
Initial version with only TEXT format reporting
Any suggestion, comment or feedback is welcome.
Attachment
pgsql-hackers by date: