In theory, the post should be of interest to those who are not indifferent to the topic of monitoring and finding problems in PostgreSQL - system administrators, DBA, SRE, DBRE.
- In pg_stat_activity added a new field query_id. The field contains the request ID similar to the one in pg_stat_statements . Thus, using a trio of datid / userid / query_id fields, you can attach pg_stat_statements and view the accumulated statistics for a specific type of query. A funny nuance - the names of the fields for the merge between pg_stat_activity and pg_stat_statements are different.
Caution, textselect a.*, s.* from pg_stat_activity a inner join pg_stat_statements s on (a.datid = s.dbid AND a.usesysid = s.userid AND a.query_id = s.queryid) where a.pid = 1001291; -[ RECORD 1 ]-------+----------------------------------------------------------- datid | 16413 datname | pgbench pid | 1001291 leader_pid | usesysid | 10 usename | postgres application_name | pgbench client_addr | client_hostname | client_port | -1 backend_start | 2021-05-22 10:15:57.299468+05 xact_start | 2021-05-22 10:16:25.566151+05 query_start | 2021-05-22 10:16:25.566623+05 state_change | 2021-05-22 10:16:25.566763+05 wait_event_type | wait_event | state | idle in transaction backend_xid | 237577 backend_xmin | query_id | 2517686606037258902 query | SELECT abalance FROM pgbench_accounts WHERE aid = 1715456; backend_type | client backend userid | 10 dbid | 16413 toplevel | t queryid | 2517686606037258902 query | SELECT abalance FROM pgbench_accounts WHERE aid = $1 plans | 0 total_plan_time | 0 min_plan_time | 0 max_plan_time | 0 mean_plan_time | 0 stddev_plan_time | 0 calls | 209439 total_exec_time | 4251.98569499987 min_exec_time | 0.005414 max_exec_time | 0.435581 mean_exec_time | 0.020301785698938563 stddev_exec_time | 0.005889254053319066 rows | 209439 shared_blks_hit | 884097 shared_blks_read | 0 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 wal_records | 149 wal_fpi | 2 wal_bytes | 9870
- Pg_stat_activity now also shows WAL archiver in the process list. So far there is not much information, so it is not particularly informative and there is room for further development.
- In pg_stat_activity for wal sender processes (participating in replication), the query field displays the replication protocol command. This small improvement allows tracking replication commands between master and replicas, previously it was only possible through logs with the log_replication_commands parameter enabled.
- The waitstart field has been added to pg_locks - the time from which the wait began. The field allows you to get the timeout without attaching pg_stat_activity. On the one hand, it seems to be convenient, but to take the query text, you still need to attach pg_stat_activity. But for use as a metric, it is quite suitable. in this case, the query text may not be interesting.
Looks like that# select pid,mode,now()-waitstart as wait_time from pg_locks where not granted; -[ RECORD 1 ]-------------- pid | 1068094 mode | ShareLock wait_time | 00:00:12.669753 -[ RECORD 2 ]-------------- pid | 1068093 mode | ShareLock wait_time | 00:00:14.789208
- pg_stat_database. session_time, active_time, idle_in_transaction_time â , . â ( state), , () . â sessions, sessions_abandoned, sessions_fatal, sessions_killed . .
- pg_stat_progress_copy. COPY. 1) (pg_dump), 2) COPY, 3) /.
-[ RECORD 1 ]----+---------- pid | 1068106 datid | 16413 datname | pgbench relid | 17612 command | COPY FROM type | FILE bytes_processed | 30998528 bytes_total | 195764221 tuples_processed | 313263 tuples_excluded | 0
- pg_stat_wal â WAL. 13 . 13- pg_stat_statements . ( ).
-[ RECORD 1 ]----+------------------------------ wal_records | 40811237 wal_fpi | 1551923 wal_bytes | 13744020096 wal_buffers_full | 509935 wal_write | 1177449 wal_sync | 666045 wal_write_time | 26449.751 wal_sync_time | 10956905.427 stats_reset | 2021-05-21 10:33:39.009804+05
- â pg_stat_replication_slots. (PUBLICATIONS/SUBSCRIPTIONS, Debezium), pg_replication_slots.
- toplevel pg_stat_statements. pg_stat_statements , . , â . - , . .
- pg_stat_statements_info. â pg_stat_statements. , pg_stat_statements , . pg_stat_statements.max.
- pg_backend_memory_contexts â . . .
- , .
pg_log_backend_memory_contexts() â , , pg_backend_memory_contexts.
, â ( ) , , . . ( , ). , . , .
- pg_prepared_statements â generic_plans, custom_plans â . , .
- pg_get_wal_replay_pause_state(). pg_is_wal_replay_paused(). . â not paused, pause requested, paused.
- log_recovery_conflict_waits â WAL . , -.
- pg_lsn . pg_lsn WAL â . pg_lsn .
,pgbench=# select '1/8000000'::pg_lsn + 16777216; -[ RECORD 1 ]------- ?column? | 1/9000000 pgbench=# select '1/8000000'::pg_lsn - 16777216; -[ RECORD 1 ]------- ?column? | 1/7000000
- - autovacuum autoanalyze. log_autovacuum_min_duration.
-2021-05-22 10:50:48.000 +05 1005664 @ from [vxid:4/309623 txid:0] [] LOG: automatic vacuum of table "pgbench.public.pgbench_accounts": index scans: 1 pages: 0 removed, 65600 remain, 0 skipped due to pins, 0 skipped frozen tuples: 1936414 removed, 2000605 remain, 566 are dead but not yet removable, oldest xmin: 253998 buffer usage: 92201 hits, 108672 misses, 129131 dirtied index scan needed: 58623 pages from table (89.36% of total) had 1961508 dead item identifiers removed index "pgbench_accounts_pkey": pages: 10970 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 3.522 MB/s, avg write rate: 4.185 MB/s I/O Timings: read=392.361 write=1964.360 system usage: CPU: user: 2.92 s, system: 1.79 s, elapsed: 241.07 s WAL usage: 195815 records, 72916 full page images, 308792606 bytes
That's all.
In conclusion, I want to make an announcement of the event - PG Day'21 Russia will take place online on July 8 and 9 . It will be a two-day PostgreSQL fan hangout with 12 talks from domestic and foreign speakers. Participation is free. Acceptance of papers is also open until June 7th
That's all, thank you for your attention!