pt-snap-statements

Summary

Displays by calculating the difference between the statistics of two difference time of SQL statements.

If it has specified options value, it can be displayed to sort of each specific items.

Make sure you have pg_stat_statements of contrib module.

And also make sure to enable track_io_timing option.

Usage

pt-snap-statements [option...] [interval]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-s, --sort=KEY
-l
-t, --top=NUMBER
-R, --reset
--help

-h, --host Specifies the connecting the PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST.``localhost`` will be used if no other value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting the PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PostgreSQL database. If not specified, to set the value of PGUSER. Vars USER will be used if no other value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. Database name as database user name will be used if no other value has been defined in PGDATABASE.

-s (not implemented) Specifies the sort item. KEY can take one of following values: CALLS, T_TIME, ROWS, B_HIT, B_READ, B_DIRT, B_WRTN, R_TIME, W_TIME

-l (not implemented) Displays with detailed every block classification (Shared buffer, Local buffer, Temporary buffer). If not specified, it is displayed the total number of shared buffer, local buffer and temporary buffer.

-t, --top Specifies the number of displayed queries. If not specified, it is displayed the all queries.

-R, --reset It initializes the statistical information of``pg_stat_statements`` view.

Output Items

USER Username of query run
DBNAME Database name of query run
QUERYID Query ID of query run (Hexadecimal)
QUERY Query performed (Display up to 30 characters)
CALLS Number of times of query run
T_TIME Total number of times of query run (Millisecond)
ROWS Total number of rows that has received the obtain or influence
B_HIT Total number of blocks read from the buffer at the time blocks read
B_READ Total number of blocks read from the disk at the time blocks read
B_DIRT Total number of pages that page has been updated by the query
B_WRTN Total number of blocks that are written to disk by the query
R_TIME Total time of block read from the disk (Millisecond) ( Make sure to enable track_io_timing parameter )
W_TIME Total time of block write to the disk (Millisecond) ( Make sure to enable track_io_timing parameter )

Examples

Connects to the postgres database, and the SQL statements executed in 5 seconds is sorted in descending order of total execution time (T_TIME), and displays all.

$ pt-snap-statements -d postgres 5
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
|  USER |  DBNAME  | QUERYID  |             QUERY              | CALLS | T_TIME | ROWS | B_HIT | B_READ | B_DIRT | B_WRTN | R_TIME | W_TIME |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
| snaga | postgres | 80053daf | UPDATE pgbench_branches SET bb |   677 |  12007 |  677 |  9160 |      1 |      1 |      0 | 0.0    | 0.0    |
| snaga | postgres | 1675159e | UPDATE pgbench_tellers SET tba |   681 |   7648 |  681 |  3403 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | ec088219 | UPDATE pgbench_accounts SET ab |   684 |    530 |  684 |  2289 |    585 |    568 |      0 | 125.9  | 0.0    |
| snaga | postgres | 198383d  | SELECT abalance FROM pgbench_a |   682 |     73 |  682 |  2080 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | da8cc6f  | INSERT INTO pgbench_history (t |   676 |     34 |  676 |   704 |     12 |     10 |      0 | 0.0    | 0.0    |
| snaga | postgres | d4e6bf94 | BEGIN;                         |   684 |      4 |    0 |     0 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | a81672e  | END;                           |   671 |      3 |    0 |     0 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | 8caa574  | select count(*) from pgbench_b |     1 |      0 |    1 |     4 |      0 |      0 |      0 | 0.0    | 0.0    |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
$

User snaga connects to the postgres database of the PostgreSQL server running on the port 5433 of host 192.168.1.101, and the SQL statements executed in 5 seconds is sorted in descending order of total execution time (T_TIME), and the top 5 are displayed.

$ pt-snap-statements --host 192.168.1.101 -p 5433 -U snaga -d postgres -t 5 5
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
|  USER |  DBNAME  | QUERYID  |             QUERY              | CALLS | T_TIME | ROWS | B_HIT | B_READ | B_DIRT | B_WRTN | R_TIME | W_TIME |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
| snaga | postgres | 80053daf | UPDATE pgbench_branches SET bb |   503 |   9953 |  503 |  8430 |     14 |      7 |      0 | 0.6    | 0.0    |
| snaga | postgres | 1675159e | UPDATE pgbench_tellers SET tba |   508 |   6483 |  508 |  2551 |     10 |      9 |      0 | 0.3    | 0.0    |
| snaga | postgres | ec088219 | UPDATE pgbench_accounts SET ab |   511 |    560 |  511 |  1424 |    698 |    477 |      7 | 91.0   | 12.1   |
| snaga | postgres | 198383d  | SELECT abalance FROM pgbench_a |   511 |     93 |  511 |  1550 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | da8cc6f  | INSERT INTO pgbench_history (t |   503 |     20 |  503 |   530 |     13 |     11 |      0 | 0.1    | 0.0    |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
$