- Notifications
You must be signed in to change notification settings - Fork26
Tool for query progress monitoring in PostgreSQL
License
postgrespro/pg_query_state
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Thepg_query_state module provides facility to know the current state of query execution on working backend. To enable this extension you have to patch the stable version of PostgreSQL, recompile it and deploy new binaries. All patch files are located inpatches/ directory and tagged with suffix of PostgreSQL version number.
Each nonutility query statement (SELECT/INSERT/UPDATE/DELETE) after optimization/planning stage is translated into plan tree which is kind of imperative representation of SQL query execution algorithm. EXPLAIN ANALYZE request allows to demonstrate execution statistics gathered from each node of plan tree (full time of execution, number rows emitted to upper nodes, etc). But this statistics is collected after execution of query. This module allows to show actual statistics of query running gathered from external backend. At that, format of resulting output is almost identical to ordinal EXPLAIN ANALYZE. Thus users are able to track of query execution in progress.
In fact, this module is able to explore external backend and determine its actual state. Particularly it's helpful when backend executes a heavy query and gets stuck.
Using this module there can help in the following things:
- detect a long query (along with other monitoring tools)
- overwatch the query execution
To installpg_query_state, please apply corresponding patchescustom_signal_(PG_VERSION).patch andruntime_explain_(PG_VERSION).patch (orruntime_explain.patch for PG version <= 10.0) from thepatches/ directory to reqired stable version of PostgreSQL and rebuild PostgreSQL.
To do this, run the following commands from the postgresql directory:
patch -p1 < path_to_pg_query_state_folder/patches/runtime_explain_(PG_VERSION).patchpatch -p1 < path_to_pg_query_state_folder/patches/custom_signals_(PG_VERSION).patchThen execute this in the module's directory:
make install USE_PGXS=1To execute the command correctly, make sure you have the PATH or PG_CONFIG variable set.
export PATH=path_to_your_bin_folder:$PATH# orexport PG_CONFIG=path_to_your_bin_folder/pg_configAdd module name to theshared_preload_libraries parameter inpostgresql.conf:
shared_preload_libraries = 'pg_query_state'It is essential to restart the PostgreSQL instance. After that, execute the following query in psql:
CREATE EXTENSION pg_query_state;
Done!
Test using parallel sessions with Python 3+ compatible script:
python3 tests/pg_qs_test_runner.py [OPTION]...
prerequisite packages:
psycopg2version 2.6 or laterPyYAMLversion 3.11 or laterprogressbar2for stress test progress reporting
options:
- - -host --- postgres server host, default value islocalhost
- - -port --- postgres server port, default value is5432
- - -database --- database name, default value ispostgres
- - -user --- user name, default value ispostgres
- - -password --- user's password, default value is empty
- - -tpc-ds-setup --- setup database to run TPC-DS benchmark
- - -tpc-ds-run --- runs only stress tests on TPC-DS benchmark
Or run all tests inDocker using:
export LEVEL=hardcoreexport USE_TPCDS=1export PG_VERSION=12./mk_dockerfile.shdocker-compose builddocker-compose run tests
There are different test levels:hardcore,nightmare (runs tests undervalgrind) andstress (runs tests underTPC-DS load).
pg_query_state(integer pid, verboseboolean DEFAULT FALSE, costsboolean DEFAULT FALSE, timingboolean DEFAULT FALSE, buffersboolean DEFAULT FALSE, triggersboolean DEFAULT FALSE, formattext DEFAULT'text') returns TABLE ( pidinteger, frame_numberinteger, query_texttext, plantext, leader_pidinteger)
extracts the current query state from backend with specifiedpid. Since parallel query can spawn multiple workers and function call causes nested subqueries so that state of execution may be viewed as stack of running queries, return value ofpg_query_state has typeTABLE (pid integer, frame_number integer, query_text text, plan text, leader_pid integer). It represents tree structure consisting of leader process and its spawned workers identified bypid. Each worker refers to leader throughleader_pid column. For leader process the value of this column is null. The state of each process is represented as stack of function calls. Each frame of that stack is specified as correspondence betweenframe_number starting from zero,query_text andplan with online statistics columns.
Thus, user can see the states of main query and queries generated from function calls for leader process and all workers spawned from it.
In process of execution some nodes of plan tree can take loops of full execution. Therefore statistics for each node consists of two parts: average statistics for previous loops just like in EXPLAIN ANALYZE output and statistics for current loop if node have not finished.
Optional arguments:
verbose--- use EXPLAIN VERBOSE for plan printing;costs--- add costs for each node;timing--- print timing data for each node, if collecting of timing statistics is turned off on called side resulting output will contain WARNING messagetiming statistics disabled;buffers--- print buffers usage, if collecting of buffers statistics is turned off on called side resulting output will contain WARNING messagebuffers statistics disabled;triggers--- include triggers statistics in result plan trees;format--- EXPLAIN format to be used for plans printing, possible values: {text,xml,json,yaml}.
If callable backend is not executing any query the function prints INFO message about backend's state taken frompg_stat_activity view if it exists there.
Warning: Calling role have to be superuser or member of the role whose backend is being called. Otherwise function prints ERROR messagepermission denied.
There are several user-accessibleGUC variables designed to toggle the whole module and the collecting of specific statistic parameters while query is running:
pg_query_state.enable--- disable (or enable)pg_query_statecompletely, default value istruepg_query_state.enable_timing--- collect timing data for each node, default value isfalsepg_query_state.enable_buffers--- collect buffers usage, default value isfalse
This parameters is set on called side before running any queries whose states are attempted to extract.Warning: ifpg_query_state.enable_timing is turned off the calling side cannot get time statistics, similarly forpg_query_state.enable_buffers parameter.
Set maximum number of parallel workers ongather node equals2:
postgres=# set max_parallel_workers_per_gather = 2;
Assume one backend with pid = 49265 performs a simple query:
postgres=# select pg_backend_pid(); pg_backend_pid----------------49265(1 row)postgres=# select count(*) from foo join bar on foo.c1=bar.c1;
Other backend can extract intermediate state of execution that query:
postgres=# \xpostgres=# select * from pg_query_state(49265);-[ RECORD1 ]+-------------------------------------------------------------------------------------------------------------------------pid |49265frame_number |0query_text |selectcount(*)from foojoin baronfoo.c1=bar.c1;plan | Finalize Aggregate (Current loop: actual rows=0, loopnumber=1)+ |-> Gather (Current loop: actual rows=0, loopnumber=1)+ | Workers Planned:2+ | Workers Launched:2+ |-> Partial Aggregate (Current loop: actual rows=0, loopnumber=1)+ |-> Nested Loop (Current loop: actual rows=12, loopnumber=1)+ |Join Filter: (foo.c1=bar.c1)+ | Rows Removed byJoin Filter:5673232+ |-> Parallel Seq Scanon foo (Current loop: actual rows=12, loopnumber=1)+ |-> Seq Scanon bar (actual rows=500000 loops=11) (Current loop: actual rows=173244, loopnumber=12)leader_pid | (null)-[ RECORD2 ]+-------------------------------------------------------------------------------------------------------------------------pid |49324frame_number |0query_text |<parallel query>plan | Partial Aggregate (Current loop: actual rows=0, loopnumber=1)+ |-> Nested Loop (Current loop: actual rows=10, loopnumber=1)+ |Join Filter: (foo.c1=bar.c1)+ | Rows Removed byJoin Filter:4896779+ |-> Parallel Seq Scanon foo (Current loop: actual rows=10, loopnumber=1)+ |-> Seq Scanon bar (actual rows=500000 loops=9) (Current loop: actual rows=396789, loopnumber=10)leader_pid |49265-[ RECORD3 ]+-------------------------------------------------------------------------------------------------------------------------pid |49323frame_number |0query_text |<parallel query>plan | Partial Aggregate (Current loop: actual rows=0, loopnumber=1)+ |-> Nested Loop (Current loop: actual rows=11, loopnumber=1)+ |Join Filter: (foo.c1=bar.c1)+ | Rows Removed byJoin Filter:5268783+ |-> Parallel Seq Scanon foo (Current loop: actual rows=11, loopnumber=1)+ |-> Seq Scanon bar (actual rows=500000 loops=10) (Current loop: actual rows=268794, loopnumber=11)leader_pid |49265
In example above working backend spawns two parallel workers with pids49324 and49323. Theirleader_pid column's values clarify that these workers belong to the main backend.Seq Scan node has statistics on passed loops (average number of rows delivered toNested Loop and number of passed loops are shown) and statistics on current loop. Other nodes has statistics only for current loop as this loop is first (loop number = 1).
Assume first backend executes some function:
postgres=# select n_join_foo_bar();
Other backend can get the follow output:
postgres=# select * from pg_query_state(49265);-[ RECORD1 ]+------------------------------------------------------------------------------------------------------------------pid |49265frame_number |0query_text |select n_join_foo_bar();plan | Result (Current loop: actual rows=0, loopnumber=1)leader_pid | (null)-[ RECORD2 ]+------------------------------------------------------------------------------------------------------------------pid |49265frame_number |1query_text |SELECT (selectcount(*)from foojoin baronfoo.c1=bar.c1)plan | Result (Current loop: actual rows=0, loopnumber=1)+ | InitPlan1 (returns $0)+ |-> Aggregate (Current loop: actual rows=0, loopnumber=1)+ |-> Nested Loop (Current loop: actual rows=51, loopnumber=1)+ |Join Filter: (foo.c1=bar.c1)+ | Rows Removed byJoin Filter:51636304+ |-> Seq Scanon bar (Current loop: actual rows=52, loopnumber=1)+ |-> Materialize (actual rows=1000000 loops=51) (Current loop: actual rows=636355, loopnumber=52)+ |-> Seq Scanon foo (Current loop: actual rows=1000000, loopnumber=1)leader_pid | (null)
First row corresponds to function call, second - to query which is in the body of that function.
We can get result plans in different format (e.g.json):
postgres=# select * from pg_query_state(pid := 49265, format := 'json');-[ RECORD1 ]+------------------------------------------------------------pid |49265frame_number |0query_text |select*from n_join_foo_bar();plan | {+ |"Plan": {+ |"Node Type":"Function Scan",+ |"Parallel Aware": false,+ |"Function Name":"n_join_foo_bar",+ |"Alias":"n_join_foo_bar",+ |"Current loop": {+ |"Actual Loop Number":1,+ |"Actual Rows":0+ | }+ | }+ | }leader_pid | (null)-[ RECORD2 ]+------------------------------------------------------------pid |49265frame_number |1query_text |SELECT (selectcount(*)from foojoin baronfoo.c1=bar.c1)plan | {+ |"Plan": {+ |"Node Type":"Result",+ |"Parallel Aware": false,+ |"Current loop": {+ |"Actual Loop Number":1,+ |"Actual Rows":0+ | },+ |"Plans": [+ | {+ |"Node Type":"Aggregate",+ |"Strategy":"Plain",+ |"Partial Mode":"Simple",+ |"Parent Relationship":"InitPlan",+ |"Subplan Name":"InitPlan 1 (returns $0)",+ |"Parallel Aware": false,+ |"Current loop": {+ |"Actual Loop Number":1,+ |"Actual Rows":0+ | },+ |"Plans": [+ | {+ |"Node Type":"Nested Loop",+ |"Parent Relationship":"Outer",+ |"Parallel Aware": false,+ |"Join Type":"Inner",+ |"Current loop": {+ |"Actual Loop Number":1,+ |"Actual Rows":610+ | },+ |"Join Filter":"(foo.c1 = bar.c1)",+ |"Rows Removed by Join Filter":610072944,+ |"Plans": [+ | {+ |"Node Type":"Seq Scan",+ |"Parent Relationship":"Outer",+ |"Parallel Aware": false,+ |"Relation Name":"bar",+ |"Alias":"bar",+ |"Current loop": {+ |"Actual Loop Number":1,+ |"Actual Rows":611+ | }+ | },+ | {+ |"Node Type":"Materialize",+ |"Parent Relationship":"Inner",+ |"Parallel Aware": false,+ |"Actual Rows":1000000,+ |"Actual Loops":610,+ |"Current loop": {+ |"Actual Loop Number":611,+ |"Actual Rows":73554+ | },+ |"Plans": [+ | {+ |"Node Type":"Seq Scan",+ |"Parent Relationship":"Outer",+ |"Parallel Aware": false,+ |"Relation Name":"foo",+ |"Alias":"foo",+ |"Current loop": {+ |"Actual Loop Number":1,+ |"Actual Rows":1000000+ | }+ | }+ | ]+ | }+ | ]+ | }+ | ]+ | }+ | ]+ | }+ | }leader_pid | (null)
Do not hesitate to post your issues, questions and new ideas at theissues page.
Maksim Milyutin
Alexey Kondratova.kondratov@postgrespro.ru Postgres Professional Ltd., Russia
About
Tool for query progress monitoring in PostgreSQL
Topics
Resources
License
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Packages0
Uh oh!
There was an error while loading.Please reload this page.