• Stars
    star
    150
  • Rank 247,323 (Top 5 %)
  • Language
    C
  • License
    Other
  • Created about 8 years ago
  • Updated 17 days ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Tool for query progress monitoring in PostgreSQL

Build Status codecov

pg_query_state

The pg_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 in patches/ directory and tagged with suffix of PostgreSQL version number.

Overview

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.

Use cases

Using this module there can help in the following things:

  • detect a long query (along with other monitoring tools)
  • overwatch the query execution

Installation

To install pg_query_state, please apply corresponding patches custom_signal_(PG_VERSION).patch and runtime_explain_(PG_VERSION).patch (or runtime_explain.patch for PG version <= 10.0) in patches/ directory to reqired stable version of PostgreSQL and rebuild PostgreSQL.

Then execute this in the module's directory:

make install USE_PGXS=1

Add module name to the shared_preload_libraries parameter in postgresql.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!

Tests

Test using parallel sessions with Python 3+ compatible script:

python3 tests/pg_qs_test_runner.py [OPTION]...

prerequisite packages:

  • psycopg2 version 2.6 or later
  • PyYAML version 3.11 or later
  • progressbar2 for stress test progress reporting

options:

  • - -host --- postgres server host, default value is localhost
  • - -port --- postgres server port, default value is 5432
  • - -database --- database name, default value is postgres
  • - -user --- user name, default value is postgres
  • - -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 in Docker using:

export LEVEL=hardcore
export USE_TPCDS=1
export PG_VERSION=12

./mk_dockerfile.sh

docker-compose build
docker-compose run tests

There are different test levels: hardcore, nightmare (runs tests under valgrind) and stress (runs tests under TPC-DS load).

Function pg_query_state

pg_query_state(
        integer     pid,
        verbose     boolean DEFAULT FALSE,
        costs       boolean DEFAULT FALSE,
        timing      boolean DEFAULT FALSE,
        buffers     boolean DEFAULT FALSE,
        triggers    boolean DEFAULT FALSE,
        format      text    DEFAULT 'text'
) returns TABLE (
    pid             integer,
    frame_number    integer,
    query_text      text,
    plan            text,
    leader_pid      integer
)

extracts the current query state from backend with specified pid. 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 of pg_query_state has type TABLE (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 by pid. Each worker refers to leader through leader_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 between frame_number starting from zero, query_text and plan 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 message timing statistics disabled;
  • buffers --- print buffers usage, if collecting of buffers statistics is turned off on called side resulting output will contain WARNING message buffers 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 from pg_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 message permission denied.

Configuration settings

There are several user-accessible GUC 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_state completely, default value is true
  • pg_query_state.enable_timing --- collect timing data for each node, default value is false
  • pg_query_state.enable_buffers --- collect buffers usage, default value is false

This parameters is set on called side before running any queries whose states are attempted to extract. Warning: if pg_query_state.enable_timing is turned off the calling side cannot get time statistics, similarly for pg_query_state.enable_buffers parameter.

Examples

Set maximum number of parallel workers on gather node equals 2:

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=# \x
postgres=# select * from pg_query_state(49265);
-[ RECORD 1 ]+-------------------------------------------------------------------------------------------------------------------------
pid          | 49265
frame_number | 0
query_text   | select count(*) from foo join bar on foo.c1=bar.c1;
plan         | Finalize Aggregate (Current loop: actual rows=0, loop number=1)                                                         +
             |   ->  Gather (Current loop: actual rows=0, loop number=1)                                                               +
             |         Workers Planned: 2                                                                                              +
             |         Workers Launched: 2                                                                                             +
             |         ->  Partial Aggregate (Current loop: actual rows=0, loop number=1)                                              +
             |               ->  Nested Loop (Current loop: actual rows=12, loop number=1)                                             +
             |                     Join Filter: (foo.c1 = bar.c1)                                                                      +
             |                     Rows Removed by Join Filter: 5673232                                                                +
             |                     ->  Parallel Seq Scan on foo (Current loop: actual rows=12, loop number=1)                          +
             |                     ->  Seq Scan on bar (actual rows=500000 loops=11) (Current loop: actual rows=173244, loop number=12)
leader_pid   | (null)
-[ RECORD 2 ]+-------------------------------------------------------------------------------------------------------------------------
pid          | 49324
frame_number | 0
query_text   | <parallel query>
plan         | Partial Aggregate (Current loop: actual rows=0, loop number=1)                                                          +
             |   ->  Nested Loop (Current loop: actual rows=10, loop number=1)                                                         +
             |         Join Filter: (foo.c1 = bar.c1)                                                                                  +
             |         Rows Removed by Join Filter: 4896779                                                                            +
             |         ->  Parallel Seq Scan on foo (Current loop: actual rows=10, loop number=1)                                      +
             |         ->  Seq Scan on bar (actual rows=500000 loops=9) (Current loop: actual rows=396789, loop number=10)
leader_pid   | 49265
-[ RECORD 3 ]+-------------------------------------------------------------------------------------------------------------------------
pid          | 49323
frame_number | 0
query_text   | <parallel query>
plan         | Partial Aggregate (Current loop: actual rows=0, loop number=1)                                                          +
             |   ->  Nested Loop (Current loop: actual rows=11, loop number=1)                                                         +
             |         Join Filter: (foo.c1 = bar.c1)                                                                                  +
             |         Rows Removed by Join Filter: 5268783                                                                            +
             |         ->  Parallel Seq Scan on foo (Current loop: actual rows=11, loop number=1)                                      +
             |         ->  Seq Scan on bar (actual rows=500000 loops=10) (Current loop: actual rows=268794, loop number=11)
leader_pid   | 49265

In example above working backend spawns two parallel workers with pids 49324 and 49323. Their leader_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 to Nested 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);
-[ RECORD 1 ]+------------------------------------------------------------------------------------------------------------------
pid          | 49265
frame_number | 0
query_text   | select n_join_foo_bar();
plan         | Result (Current loop: actual rows=0, loop number=1)
leader_pid   | (null)
-[ RECORD 2 ]+------------------------------------------------------------------------------------------------------------------
pid          | 49265
frame_number | 1
query_text   | SELECT (select count(*) from foo join bar on foo.c1=bar.c1)
plan         | Result (Current loop: actual rows=0, loop number=1)                                                              +
             |   InitPlan 1 (returns $0)                                                                                        +
             |     ->  Aggregate (Current loop: actual rows=0, loop number=1)                                                   +
             |           ->  Nested Loop (Current loop: actual rows=51, loop number=1)                                          +
             |                 Join Filter: (foo.c1 = bar.c1)                                                                   +
             |                 Rows Removed by Join Filter: 51636304                                                            +
             |                 ->  Seq Scan on bar (Current loop: actual rows=52, loop number=1)                                +
             |                 ->  Materialize (actual rows=1000000 loops=51) (Current loop: actual rows=636355, loop number=52)+
             |                       ->  Seq Scan on foo (Current loop: actual rows=1000000, loop number=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');
-[ RECORD 1 ]+------------------------------------------------------------
pid          | 49265
frame_number | 0
query_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)
-[ RECORD 2 ]+------------------------------------------------------------
pid          | 49265
frame_number | 1
query_text   | SELECT (select count(*) from foo join bar on foo.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)

Feedback

Do not hesitate to post your issues, questions and new ideas at the issues page.

Authors

Maksim Milyutin
Alexey Kondratov [email protected] Postgres Professional Ltd., Russia

More Repositories

1

rum

RUM access method - inverted index with additional information in posting lists
C
725
star
2

pg_probackup

Backup and recovery manager for PostgreSQL
Python
711
star
3

jsquery

JsQuery – json query language with GIN indexing support
C
702
star
4

pg_pathman

Partitioning tool for PostgreSQL
C
583
star
5

zson

ZSON is a PostgreSQL extension for transparent JSONB compression
C
539
star
6

aqo

Adaptive query optimization for PostgreSQL
C
428
star
7

imgsmlr

Similar images search for PostgreSQL
C
255
star
8

mamonsu

Python
186
star
9

vops

C
165
star
10

postgres_cluster

Various experiments with PostgreSQL clustering
C
151
star
11

pg_wait_sampling

Sampling based statistics of wait events
C
144
star
12

testgres

Testing framework for PostgreSQL and its extensions
Python
141
star
13

hunspell_dicts

Hunspell dictionaries for PostgreSQL
TSQL
63
star
14

pg_credereum

Prototype of PostgreSQL extension bringing some properties of blockchain to the relational DBMS
C
62
star
15

sr_plan

Save and restore query plans in PostgreSQL
C
61
star
16

mmts

multimaster
C
57
star
17

raft

Raft protocol implementation in C
C
49
star
18

ptrack

Block-level incremental backup engine for PostgreSQL
C
45
star
19

pg_trgm_pro

C
44
star
20

sqljson

C
38
star
21

postgresql.pthreads

Port of postgresql for pthreads
C
31
star
22

postgresql.builtin_pool

Version of PostgreSQL with built-in connection pooling
C
29
star
23

pg_dtm

Distributed transaction manager
C
27
star
24

postgrespro

Postgres Professional fork of PostgreSQL
C
27
star
25

lsm3

LSM tree implementation based on standard B-Tree
C
26
star
26

lsm

RocksDB FDW for PostgreSQL
C
24
star
27

tsvector2

Extended tsvector type for PostgreSQL
C
20
star
28

pg_backtrace

Show backtrace for errors and signals
C
20
star
29

pgwininstall

PostgreSQL Windows installer
Roff
19
star
30

monq

MonQ - PostgreSQL extension for MongoDB-like queries to jsonb data
C
17
star
31

pg_tsparser

pg_tsparser - parser for text search
C
16
star
32

pgsphere

PgSphere provides spherical data types, functions, operators, and indexing for PostgreSQL.
C
16
star
33

hstore_ops

Better operator class for hstore: smaller index and faster @> queries.
C
16
star
34

undam

Undo storage implementation
C
15
star
35

pg_logging

PostgreSQL logging interface
C
15
star
36

pg_ycsb

YCSB-like benchmark for pgbench
PLpgSQL
15
star
37

tsexample

Example of custom postgresql full text search parser, dictionaries and configuration
C
14
star
38

libblobstamper

Framework for Structure Aware Fuzzing. Allows to build own stamps that would convert pulp-data that came from fuzzer to data with structure you need
C++
14
star
39

pg_oltp_bench

Extension and scripts to run analogue of sysbench OLTP test using pgbench
PLpgSQL
13
star
40

pg_grab_statement

PostgreSQL extension for recoding workload of specific database
C
12
star
41

tsexact

PostgreSQL fulltext search addon
C
11
star
42

jsonbd

JSONB compression method for PostgreSQL
C
10
star
43

rusmorph

Russian morphological dictionary (rusmorph) for Postgres based on libmorph library: https://github.com/big-keva/libmorph
C++
10
star
44

pg_parallizator

C
9
star
45

memstat

C
9
star
46

plantuner

C
8
star
47

pg_pageprep

PostgreSQL extension which helps to prepare heap pages for migration to 64bit XID page format (PostgresPro Enterprise)
C
8
star
48

wildspeed

C
7
star
49

pgbouncer

C
6
star
50

bztree

C++
6
star
51

pg_pathman_build

Prerequisites for pg_pathman building
Shell
5
star
52

snapfs

Fast recoverry and snapshoting
C
4
star
53

pq2jdbc

Java
4
star
54

jsonb_schema

Store jsonb schema separately from data
C
4
star
55

postgrespro-os-templates

Packer templates for building minimal baseboxes
Shell
3
star
56

pg_variables

Session wide variables for PostgreSQL
C
3
star
57

pg_hint_plan

C
2
star
58

pgpro_redefinition

PLpgSQL
2
star
59

snowball_ext

The Snowball dictionary template extension for PostgreSQL
C
2
star
60

jsonb_plpython

PLpgSQL
1
star
61

dict_regex

C
1
star
62

pg-mark

Postgres benchmarking framework
R
1
star
63

anyarray

contrib package for working with 1-D arrays
C
1
star
64

libpq_compression

C
1
star