NETWAYS, profile picture
Uploaded byNETWAYS
283 views

OSMC 2021 | pg_stat_monitor: A cool extension for better database (PostgreSQL) monitoring

The document discusses pg_stat_monitor, an extension for PostgreSQL that provides enhanced monitoring of database performance by capturing detailed statistics on queries. It explains the importance of various data points for identifying poor performance due to 'bad queries' and outlines how to install and configure the extension effectively. Additionally, it includes information about the monitoring capabilities and data collection methods used by pg_stat_monitor.

Software◦
In this document
Powered by AI

Introduction to pg_stat_monitor, a PostgreSQL extension for improved database monitoring.

Introduction of key authors and contributors including Ibrar Ahmed, Peter Zaitsev, and Denys Kondratenko.

Overview of common database performance issues, emphasizing the prevalence of bad queries.

Methods for source data collection; discusses system state sampling and event counting, highlighting pg_stat_monitor's approach.

Significance of capturing parameters, response times, and query execution for performance analysis.

Steps to download and install pg_stat_monitor, including git commands and settings in postgresql.conf.

Details on pg_stat_monitor's SQL version, comments, and its built version information.

Descriptions of the additional columns in the pg_stat_monitor for tracking various query statistics.

Configuration options for pg_stat_monitor, including memory limits and enabling query tracking.

Demonstration of how query data is tracked in buckets along with queries and their call counts.

Techniques to fetch detailed query data including user IDs, database names, and IP addresses.

Demonstration of setting normalized queries on and off in pg_stat_monitor.

Error handling and logging for queries in pg_stat_monitor highlighting common SQL errors.

Collection of query timing statistics and presentation of histograms for analysis.

Statistics regarding query planning, including calls and rows retrieved.

Views on executing queries and retrieving related query plans from pg_stat_monitor.

Showing a common function and its data usage along with top queries.

Different types of queries executed over pg_stat_monitor and their respective counts.

Schema details for pg_stat_monitor including all columns and types tracked in the view.

Comparative performance metrics of PostgreSQL database sizes and TPS using pg_stat_monitor.

Integration of pg_stat_monitor into Percona Monitoring and Management (PMM) platform.

Instructions for providing feedback or reporting bugs in pg_stat_monitor tool.

Download to read offline
pg_stat_monitor A cool extension for better database (PostgreSQL) monitoring
© 2021 Percona Ibrar Ahmed - The Author ● Software industries since 1998 ● Open Source Contribution other than PostgreSQL ○ Cross platform implementation of FTP for Google Chrome ○ Testing Framework for Google Chromium Project ○ Cross Platform Telnet application ● Working on PostgreSQL Since 2006 ● EnterpriseDB ○ Associate Software Architect core Database Engine 2006-2009 ○ Software Architect core Database Engine 2011 - 2016 ○ Senior Software Architect core Database Engine 2016 – 2018 ● Percona ○ Senior Software Architect core Database Engine 2018 – Present 2 Twitter: @ibrar_ahmad LinkedIn: ibrarahmed74 pgelephant.com
© 2021 Percona Peter Zaitsev - The Mentor ● CEO, Percona ● Database Performance Geek ● Know enough PostgreSQL to be dangerous 3 Twitter: @PeterZaitsev LinkedIn: peterzaitsev
© 2021 Percona Denys Kondratenko ● Engineering Manager in Percona ○ Percona Monitoring and Management ● Past experience around distributed storage as eng mgr ○ SUSE - Ceph ○ Seagate - Lustre ● Monitoring experience in Massive Solutions ● Embedded engineer in Mindspeed before that ● love linux, run full Wayland, openSUSEway (sway) 4 github: denisok LinkedIn: kondratenko https://per.co.na/discord
© 2021 Percona | Confidential Database Performance Basics ● Percona - 15+ Years Open Source Database Performance Passion ● Most Common Reason of Poor Performance “Bad Queries” ● Users often do not even know they have such queries ● When they may not know source of such queries ● Or why they are bad and how to fix them 5
© 2021 Percona | Confidential Sourcing the Data ● System State Sampling ○ Limited Overhead ○ Limited Accuracy ○ Can have measurement Artifacts ● Event Counting ○ Risk of high overhead ○ Very Good accuracy ● pg_stat_monitor is Event Counting Approach 6
© 2021 Percona | Confidential Why Some Data Points are Important ? ● Actual parameters in the statements ○ So you can run explain or modify the query and see if it improves ● Time Bucketing ○ High Fidelity stats, Better Network Quality Tolerance ● Multi-Dimensional grouping, like Client IP ○ Security Analyses. Spot where workload which should not be running runs ● Capture Failed Queries ○ Some poorly Performing Queries never complete (ERROR, WARNING and LOG error levels). Catch errors in your application ● Capture Touched Relation (tables accessed by the statement) ○ Easily spot all queries touching given relation 7
© 2021 Percona | Confidential Why Some Data Points are Important ? ● Response Time Distribution (histograms) ○ “Average” query execution time does not tell the whole story ● Bucketing by Different Query Plans ○ Because Different Query Plans can have drastically difference performance behavior ● Plan Information ○ Having Plan for actual query executions is helpful with unstable plans ● Query Hierarchy ○ When Query is called from Stored Routine both query and caller are very meaningful 8
© 2021 Percona Download pg_stat_monitor Source code ○ git clone https://github.com/percona/pg_stat_monitor.git ○ cd pg_stat_monitor ○ make USE_PGXS=1 install Packages ○ Percona ■ https://www.percona.com/downloads/postgresql-distribution-13 ■ https://repo.percona.com/ppg-13/yum/release/7/RPMS/x86_64/ ○ PostgreSQL yum repositories ■ https://yum.postgresql.org/packages/ ○ PostgreSQL Extensions Network (PGXN) ■ pgxn install pg_stat_monitor 9
© 2021 Percona Install pg_stat_monitor ● pg_stat_monitor library can be loaded by setting shared_preload_libraries in postgresql.conf file. shared_preload_libraries = 'pg_stat_monitor' # (change requires restart) ● The same parameter shared_preload_libraries can be changed using ALTER SYSTEM command. postgres=# ALTER SYSTEM SET shared_preload_libraries=pg_stat_monitor; ALTER SYSTEM ● Restart the server # sudo systemctl restart postgresql* ● Create the extension using the create extension command. postgres=# CREATE EXTENSION pg_stat_monitor; CREATE EXTENSION 10
© 2021 Percona SQL version for pg_stat_monitor # pg_stat_monitor extension comment = 'The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_ statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram in formation.' default_version = '1.0' module_pathname = '$libdir/pg_stat_monitor' relocatable = true $ ls -lrt *.sql -rw-r--r-- 1 vagrant vagrant 6756 Oct 28 19:18 pg_stat_monitor--1.0.sql 11
© 2021 Percona Build version for pg_stat_monitor postgres=# SELECT pg_stat_monitor_version(); pg_stat_monitor_version ------------------------- 1.0.0-beta-3 (1 row) 12
© 2021 Percona pg_stat_monitor additional columns 13 Column Type Description bucket integer Data collection unit. The number shows what bucket in a chain a record belongs to bucket_start_time timestamp with time zone The start time of the bucket client_ip inet The IP address of a client that run the query planid text An internally generated ID of a query plan query_plan text The sequence of steps used to execute a query. This parameter is available only when the pgsm_enable_query_plan is enabled. top_query text Shows the top query used in a statement top_queryid text Shows the top query id used in a statement application_name text Shows the name of the application connected to the database relations text[] The list of tables involved in the query
© 2021 Percona pg_stat_monitor additional columns 14 Column Type Description cmd_type integer Type of the query executed cmd_type_text text elevel integer Shows the error level of a query (WARNING, ERROR, LOG) sqlcode integer The IP address of a client that run the query message text The error message resp_calls text[] Call histogram cpu_user_time double precision The time (in ms) the CPU spent on running the query cpu_sys_time double precision The time (in ms) the CPU spent on executing the kernel code state_code bigint Shows the state code of a query state text The state message
© 2021 Percona postgres=# SELECT * FROM pg_stat_monitor_settings; name | value | default_value | description | minimum | maximum | restart ------------------------------------------+--------+---------------+----------------------------------------------------+---------+------------+--------- pg_stat_monitor.pgsm_max | 100 | 100 | Sets the maximum size of shared memory in (MB).... | 1 | 1000 | 1 pg_stat_monitor.pgsm_query_max_len | 1024 | 1024 | Sets the maximum length of query. | 1024 | 2147483647 | 1 pg_stat_monitor.pgsm_enable | 1 | 1 | Enable/Disable statistics collector. | 0 | 0 | 0 pg_stat_monitor.pgsm_track_utility | 1 | 1 | Selects whether utility commands are tracked. | 0 | 0 | 0 pg_stat_monitor.pgsm_normalized_query | 0 | 1 | Selects whether save query in normalized format. | 0 | 0 | 0 pg_stat_monitor.pgsm_max_buckets | 10 | 10 | Sets the maximum number of buckets. | 1 | 10 | 1 pg_stat_monitor.pgsm_bucket_time | 60 | 60 | Sets the time in seconds per bucket. | 1 | 2147483647 | 1 pg_stat_monitor.pgsm_histogram_min | 0 | 0 | Sets the time in millisecond. | 0 | 2147483647 | 1 pg_stat_monitor.pgsm_histogram_max | 100000 | 100000 | Sets the time in millisecond. | 10 | 2147483647 | 1 pg_stat_monitor.pgsm_histogram_buckets | 10 | 10 | Sets the maximum number of histogram buckets | 2 | 2147483647 | 1 pg_stat_monitor.pgsm_query_shared_buffer | 20 | 20 | Sets the maximum size of shared memory in (MB) | 1 | 10000 | 1 pg_stat_monitor.pgsm_overflow_target | 0 | 1 | Sets the overflow target for pg_stat_monitor | 0 | 1 | 1 pg_stat_monitor.pgsm_enable_query_plan | 0 | 0 | Enable/Disable query plan monitoring | 0 | 0 | 0 pg_stat_monitor.pgsm_track_planning | 1 | 1 | Selects whether planning statistics are tracked. | 0 | 0 | 0 (14 rows) 15
© 2021 Percona name | value | default_value | description | minimum | maximum | restart ------------------------------------------+--------+---------------+----------------------------------------------------+---------+------------+--------- pg_stat_monitor.pgsm_max | 100 | 100 | Sets the maximum size of shared memory in (MB).... | 1 | 1000 | 1 ● Name ○ pg_stat_monitor.pgsm_max ● Value ○ 100 ● Default Value ○ 100 ● Description ○ Sets the maximum size of shared memory in (MB) used for statement's metadata tracked by pg_stat_monitor. ● Minimum ○ 1 ● Maximum ○ 1000 ● Restart ○ 1 16
© 2021 Percona Buckets SELECT bucket, bucket_start_time, substr(query,0,50)|| '...' AS query, calls FROM pg_stat_monitor; bucket | bucket_start_time | query | calls -------+---------------------+--------------------------- +----- 0 | 2020-10-14 09:30:00 | INSERT INTO pgbench_tell...| 2 1 | 2020-10-14 09:30:30 | INSERT INTO pgbench_bran...| 1 1 | 2020-10-14 09:30:30 | SELECT relname, relkind ...| 1 1 | 2020-10-14 09:30:30 | SELECT queryid, bucket, ...| 1 2 | 2020-10-14 09:31:00 | CREATE table pgbench_his...| 1 2 | 2020-10-14 09:31:00 | SELECT queryid, bucket, ...| 1 2 | 2020-10-14 09:31:00 | SELECT queryid, bucket ...| 1 (7 rows) 17 b u c k e t 4 Bucket 3 Bucket 2 Bucket 0 B u c k e t 1 Bucket Start Time B u c k e t T i m e Aggregates
© 2021 Percona Query information postgres=# SELECT userid, datname, queryid, substr(query,0, 50) AS query, calls FROM pg_stat_monitor; userid | datname | queryid | query | calls ---------+----------+------------------+---------------------------------------------------+------- vagrant | postgres | 939C2F56E1F6A174 | END | 561 vagrant | postgres | 2A4437C4905E0E23 | SELECT abalance FROM pgbench_accounts WHERE aid = | 561 vagrant | postgres | 4EE9ED0CDF143477 | SELECT userid, datname, queryid, substr(query,$1 | 1 vagrant | postgres | 8867FEEB8A5388AC | vacuum pgbench_branches | 1 vagrant | postgres | 41D1168FB0733CAB | select count(*) from pgbench_branches | 1 vagrant | postgres | E5A889A8FF37C2B1 | UPDATE pgbench_accounts SET abalance = abalance + | 561 vagrant | postgres | 4876BBA9A8FCFCF9 | truncate pgbench_history | 1 vagrant | postgres | 22B76AE84689E4DC | INSERT INTO pgbench_history (tid, bid, aid, delta | 561 vagrant | postgres | F6DA9838660825CA | vacuum pgbench_tellers | 1 vagrant | postgres | 214646CE6F9B1A85 | BEGIN | 561 vagrant | postgres | 27462943E814C5B5 | UPDATE pgbench_tellers SET tbalance = tbalance + | 561 vagrant | postgres | 4F66D46F3D4151E | SELECT userid, dbid, queryid, substr(query,0, 50 | 1 vagrant | postgres | 6A02C123488B95DB | UPDATE pgbench_branches SET bbalance = bbalance + | 561 (13 rows) 18
© 2021 Percona Query information postgres=# SELECT application_name, client_ip, substr(query,0,100) AS query FROM pg_stat_monitor; application_name | client_ip | query ------------------+-----------+----------------------------------------------------------------------------- pgbench | 127.0.0.1 | truncate pgbench_history pgbench | 127.0.0.1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 pgbench | 127.0.0.1 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 pgbench | 127.0.0.1 | BEGIN; pgbench | 127.0.0.1 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3 pgbench | 127.0.0.1 | END; pgbench | 127.0.0.1 | vacuum pgbench_branches pgbench | 127.0.0.1 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 pgbench | 127.0.0.1 | vacuum pgbench_tellers pgbench | 127.0.0.1 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 pgbench | 127.0.0.1 | select o.n, p.partstrat, pg_catalog.count(i.inhparent) from pg_catalog.pg_ psql | 127.0.0.1 | SELECT application_name, client_ip, substr(query,$1,$2) as query FROM pg_s pgbench | 127.0.0.1 | select count(*) from pgbench_branches (13 rows) 19
© 2021 Percona Query Monitoring postgres=# SET pg_stat_monitor.pgsm_normalized_query = true; postgres=# SELECT a FROM foo where a = 10; a --- (0 rows) postgres=# SELECT queryid, substr(query,0,50)|| '...' AS query,calls FROM pg_stat_monitor; queryid | query | calls ------------------+------------------------------------------------------+------- 55F88A754A1BC5FF | SELECT a from foo where a = $1... | 1 postgres=# SET pg_stat_monitor.pgsm_normalized_query = false; postgres=# SELECT a,2 FROM foo where a = 10; a | ?column? ---+---------- (0 rows) postgres=# SELECT queryid, substr(query,0,50)|| '...' AS query,calls FROM pg_stat_monitor; queryid | query | calls ------------------+------------------------------------------------------+------- EF380BA0410F35EC | SELECT a,2 from foo where a = 10;... | 1 20
© 2021 Percona Error Logging SELECT decode_error_level(elevel) AS elevel, sqlcode, query, message FROM pg_stat_monitor WHERE elevel != 0; elevel | sqlcode | query | message --------+----------+-----------------------+---------------------------------- ERROR | 16908420 | SELECT * FROM pg_foo; | relation "pg_foo" does not exist ERROR | 33816706 | SELECT 1/0; | division by zero 21
© 2021 Percona Query Timing Histogram postgres=# SELECT substr(query,0,40) || '... ' AS query, calls, resp_calls FROM pg_stat_monitor LIMIT 5; query | calls | resp_calls ---------------------------------------------+-------+------------------------ SELECT * FROM pg_stat_database;... | 24 | {23,0,1,0,0,0,0,0,0,0} select extract(epoch from current_times... | 24 | {24,0,0,0,0,0,0,0,0,0} SELECT /* pmm-agent:pgstatmonitor */ pg... | 1 | {1,0,0,0,0,0,0,0,0,0} SELECT pg_database.datname,tmp.mode,COA... | 24 | {24,0,0,0,0,0,0,0,0,0} SELECT /* pmm-agent:pgstatmonitor */ "p... | 1 | {1,0,0,0,0,0,0,0,0,0} (5 rows) 22
© 2021 Percona Query Timing Histogram SELECT resp_calls, query FROM pg_stat_monitor; resp_calls | query --------------------------------------------------+---------------------------------------------- {1," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"} | select client_ip, query from pg_stat_monitor {3," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 1"} | select * from pg_stat_monitor_reset() {3," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 1"} | SELECT * FROM foo SELECT * FROM histogram(0, 'F44CD1B4B33A47AF') AS a(range TEXT, freq INT, bar TEXT); range | freq | bar --------------------+------+-------------------------------- (0 - 3)} | 2 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ (3 - 10)} | 0 | (10 - 31)} | 1 | ■■■■■■■■■■■■■■■ (31 - 100)} | 0 | (100 - 316)} | 0 | (316 - 1000)} | 0 | (1000 - 3162)} | 0 | (3162 - 10000)} | 0 | (10000 - 31622)} | 0 | (31622 - 100000)} | 0 | (10 rows) 23
© 2021 Percona Planning Statistics SELECT queryid, plans_calls, rows_retrieved, substr(query,0,40) || '... ' AS query, calls FROM pg_stat_monitor; 24
© 2021 Percona Planning Statistics queryid | plans_calls | rows_retrieved | query | calls ------------------+-------------+----------------+-----------------------------+------- BA6EC88C00347CF7 | 0 | 0 | truncate table pgbench_... | 2 1B12EDE3C70B8F88 | 100 | 100 | insert into pgbench_tel... | 100 3FD5C490B83F760D | 0 | 0 | create table pgbench_br... | 2 B1B991EE89D61CB6 | 0 | 0 | create table pgbench_ac... | 2 29A99577F1695D28 | 0 | 11000000 | copy pgbench_accounts ... | 2 772222CE7E8765 | 0 | 0 | alter table pgbench_bra... | 2 D59D5F4391AA6B3 | 10 | 10 | insert into pgbench_bra... | 10 25
© 2021 Percona Query Plan: SELECT substr(query,0,50) AS query FROM pg_stat_monitor LIMIT 10; query | -------------------------------------------------- + SELECT abalance FROM pgbench_accounts WHERE aid = | | SELECT substr(query,$1,$2),calls, planid,query_pl | 26
© 2021 Percona Query Plan: SELECT substr(query,0,50) AS query_plan FROM pg_stat_monitor LIMIT 10; query_plan ---------------------------------------------------------------------------+ Index Scan using pgbench_accounts_pkey on pgbench_accounts | Index Cond: (aid = 102232) | Limit | -> Subquery Scan on pg_stat_monitor | -> Result | -> Sort | Sort Key: p.bucket_start_time | -> Hash Join | Hash Cond: (p.dbid = d.oid) | -> Function Scan on pg_stat_monitor_internal p | ... 27
© 2021 Percona Top Query CREATE OR REPLACE function add2(int, int) RETURNS INTEGER AS $$ BEGIN return (select $1 + $2); END; $$ language plpgsql; SELECT add2(1,2); add2 ------ 3 (1 row) 28
© 2021 Percona Top Query SELECT queryid, top_queryid, query, top_query FROM pg_stat_monitor; queryid | top_queryid | query | top_query ------------------+------------------+--------------------------+-------------- 762B99349F6C7F31 | 7209043A6BB0BCD5 | SELECT (select $1 + $2) | SELECT add2($1,$2) 7209043A6BB0BCD5 | | SELECT add2($1,$2) | (3 rows) 29
© 2021 Percona Query / Command Type SELECT bucket, substr(query,0, 50) AS query, cmd_type_text FROM pg_stat_monitor WHERE elevel = 0; bucket | query | cmd_type_text --------+---------------------------------------------------+---------- 4 | END | 4 | SELECT abalance FROM pgbench_accounts WHERE aid = | SELECT 4 | vacuum pgbench_branches | 4 | select count(*) from pgbench_branches | SELECT 4 | UPDATE pgbench_accounts SET abalance = abalance + | UPDATE 4 | truncate pgbench_history | 4 | INSERT INTO pgbench_history (tid, bid, aid, delta | INSERT 5 | SELECT relations query FROM pg_stat_monitor | SELECT 9 | SELECT bucket, substr(query,$1, $2) AS query, cmd | 30
© 2021 Percona pg_stat_monitor: Others postgres=# d pg_stat_monitor View "public.pg_stat_monitor" Column | Type | ---------------------+------------------+ bucket | bigint | bucket_start_time | text | userid | regrole | datname | name | client_ip | inet | queryid | text | top_queryid | text | query | text | comments | text | 31 planid | text | query_plan | text | top_query | text | application_name | text | relations | text[] | cmd_type | integer | cmd_type_text | text | elevel | integer | sqlcode | text | message | text | calls | bigint | total_time | double precision | min_time | double precision |
© 2021 Percona pg_stat_monitor: Others max_time | double precision | mean_time | double precision | stddev_time | double precision | rows_retrieved | bigint | plans_calls | bigint | plan_total_time | double precision | plan_min_time | double precision | plan_max_time | double precision | plan_mean_time | double precision | shared_blks_hit | bigint | shared_blks_read | bigint | shared_blks_dirtied | bigint | shared_blks_written | bigint | local_blks_hit | bigint | 32 local_blks_read | bigint | local_blks_dirtied | bigint | local_blks_written | bigint | temp_blks_read | bigint | temp_blks_written | bigint | blk_read_time | double precision | blk_write_time | double precision | resp_calls | text[] | cpu_user_time | double precision | cpu_sys_time | double precision | wal_records | bigint | wal_fpi | bigint | wal_bytes | numeric | state_code | bigint | state | text |
© 2021 Percona 33 Vanila PostgreSQL pg_stat_statment pg_stat_monitor Database Size TPS 1.6GB 3256 16GB 840 160GB 608 Database Size TPS 1.6GB 3268 16GB 843 160GB 610 Database Size TPS 1.6GB 3262 16GB 844 160GB 611 Performance Metrics
© 2021 Percona | Confidential pg_stat_monitor in PMM 34
© 2021 Percona | Confidential pg_stat_monitor in PMM 35
© 2021 Percona | Confidential pg_stat_monitor in PMM 36
© 2021 Percona | Confidential pg_stat_monitor in PMM 37
© 2021 Percona pg_stat_monitor add service to PMM # pmm-admin add postgresql --query-source=pgstatmonitor 38
© 2021 Percona Feedback ● If you found a bug or have a feature request in pg_stat_monitor, please submit the report to the https://jira.percona.com/projects/PG/issues ● If there is no existing report, submit your report following these steps: ○ Sign in to Jira issue tracker. You will need to create an account if you do not have one. ○ In the Summary, Description, Steps To Reproduce, Affects Version fields describe the problem you have detected. As a general rule of thumb, try to create bug reports that are: ● Reproducible: describe the steps to reproduce the problem. ● Unique: check if there already exists a JIRA ticket to describe the problem. ● Scoped to a Single Bug: only report one bug in one JIRA ticket. 39
Question

More Related Content

PDF
HES2011 - Tarjei Mandt – Kernel Pool Exploitation on Windows 7
byHackito Ergo Sum
 
PDF
Cilium - BPF & XDP for containers
byDocker, Inc.
 
PDF
eBPF Trace from Kernel to Userspace
bySUSE Labs Taipei
 
PDF
Linux Preempt-RT Internals
by哲豪 康哲豪
 
PDF
Redis at LINE
byLINE Corporation
 
PDF
Patroni: Kubernetes-native PostgreSQL companion
byAlexander Kukushkin
 
PDF
High Availability PostgreSQL with Zalando Patroni
byZalando Technology
 
PDF
KVM環境におけるネットワーク速度ベンチマーク
byVirtualTech Japan Inc.
 
HES2011 - Tarjei Mandt – Kernel Pool Exploitation on Windows 7
byHackito Ergo Sum
 
Cilium - BPF & XDP for containers
byDocker, Inc.
 
eBPF Trace from Kernel to Userspace
bySUSE Labs Taipei
 
Linux Preempt-RT Internals
by哲豪 康哲豪
 
Redis at LINE
byLINE Corporation
 
Patroni: Kubernetes-native PostgreSQL companion
byAlexander Kukushkin
 
High Availability PostgreSQL with Zalando Patroni
byZalando Technology
 
KVM環境におけるネットワーク速度ベンチマーク
byVirtualTech Japan Inc.
 

What's hot

PDF
Introduction to the LLVM Compiler System
byzionsaint
 
PDF
Patroni - HA PostgreSQL made easy
byAlexander Kukushkin
 
PDF
Linux Performance Profiling and Monitoring
byGeorg Schönberger
 
PDF
Modern Kernel Pool Exploitation: Attacks and Techniques
byMichael Scovetta
 
PPTX
Linux Kernel Booting Process (2) - For NLKB
byshimosawa
 
PPTX
Building and managing complex dependencies pipeline using Apache Oozie
byDataWorks Summit/Hadoop Summit
 
PDF
Best Practices of HA and Replication of PostgreSQL in Virtualized Environments
byJignesh Shah
 
PDF
Introducing the Apache Flink Kubernetes Operator
byFlink Forward
 
PDF
Tips of Malloc & Free
byTetsuyuki Kobayashi
 
PDF
Qemu & KVM Guide #1 (intro & basic)
byJungIn Jung
 
PDF
Running Kafka On Kubernetes With Strimzi For Real-Time Streaming Applications
byLightbend
 
PDF
Anatomy of the loadable kernel module (lkm)
byAdrian Huang
 
PDF
Evolution of MongoDB Replicaset and Its Best Practices
byMydbops
 
PPTX
Windows Kernel Exploitation : This Time Font hunt you down in 4 bytes
byPeter Hlavaty
 
PPTX
Linux Initialization Process (2)
byshimosawa
 
PDF
HPC Phys-20201203
byMITSUNARI Shigeo
 
PDF
MyRocks Deep Dive
byYoshinori Matsunobu
 
PDF
FreeBSD and Drivers
byKernel TLV
 
PDF
10分で分かるLinuxブロックレイヤ
byTakashi Hoshino
 
PDF
The Linux Block Layer - Built for Fast Storage
byKernel TLV
 
Introduction to the LLVM Compiler System
byzionsaint
 
Patroni - HA PostgreSQL made easy
byAlexander Kukushkin
 
Linux Performance Profiling and Monitoring
byGeorg Schönberger
 
Modern Kernel Pool Exploitation: Attacks and Techniques
byMichael Scovetta
 
Linux Kernel Booting Process (2) - For NLKB
byshimosawa
 
Building and managing complex dependencies pipeline using Apache Oozie
byDataWorks Summit/Hadoop Summit
 
Best Practices of HA and Replication of PostgreSQL in Virtualized Environments
byJignesh Shah
 
Introducing the Apache Flink Kubernetes Operator
byFlink Forward
 
Tips of Malloc & Free
byTetsuyuki Kobayashi
 
Qemu & KVM Guide #1 (intro & basic)
byJungIn Jung
 
Running Kafka On Kubernetes With Strimzi For Real-Time Streaming Applications
byLightbend
 
Anatomy of the loadable kernel module (lkm)
byAdrian Huang
 
Evolution of MongoDB Replicaset and Its Best Practices
byMydbops
 
Windows Kernel Exploitation : This Time Font hunt you down in 4 bytes
byPeter Hlavaty
 
Linux Initialization Process (2)
byshimosawa
 
HPC Phys-20201203
byMITSUNARI Shigeo
 
MyRocks Deep Dive
byYoshinori Matsunobu
 
FreeBSD and Drivers
byKernel TLV
 
10分で分かるLinuxブロックレイヤ
byTakashi Hoshino
 
The Linux Block Layer - Built for Fast Storage
byKernel TLV
 

Similar to OSMC 2021 | pg_stat_monitor: A cool extension for better database (PostgreSQL) monitoring

PDF
Monitoring Postgres at Scale | PostgresConf US 2018 | Lukas Fittl
byCitus Data
 
PDF
Advanced Postgres Monitoring
byDenish Patel
 
PDF
Webinar slides: An Introduction to Performance Monitoring for PostgreSQL
bySeveralnines
 
PDF
pg_proctab: Accessing System Stats in PostgreSQL
byCommand Prompt., Inc
 
PDF
pg_proctab: Accessing System Stats in PostgreSQL
byMark Wong
 
PDF
Creating PostgreSQL-as-a-Service at Scale
bySean Chittenden
 
PDF
pg_proctab: Accessing System Stats in PostgreSQL
byCommand Prompt., Inc
 
PDF
pg_proctab: Accessing System Stats in PostgreSQL
byMark Wong
 
PDF
Explain this!
byFabio Telles Rodriguez
 
PDF
query_tuning.pdf
byssuserf99076
 
PDF
pg_proctab: Accessing System Stats in PostgreSQL
byMark Wong
 
PDF
PGConf APAC 2018 - Monitoring PostgreSQL at Scale
byPGConf APAC
 
PDF
8.4 Upcoming Features
byPostgreSQL Experts, Inc.
 
PDF
Tracing and profiling my sql (percona live europe 2019) draft_1
byValerii Kravchuk
 
PDF
Aplicações 10x a 100x mais rápida com o postgre sql
byFabio Telles Rodriguez
 
PDF
Deep dive into PostgreSQL statistics.
byAlexey Lesovsky
 
PDF
Troubleshooting PostgreSQL with pgCenter
byAlexey Lesovsky
 
PDF
How does PostgreSQL work with disks: a DBA's checklist in detail. PGConf.US 2015
byPostgreSQL-Consulting
 
PPTX
Elegant database tuning
byScott Mead
 
PDF
Nancy CLI. Automated Database Experiments
byNikolay Samokhvalov
 
Monitoring Postgres at Scale | PostgresConf US 2018 | Lukas Fittl
byCitus Data
 
Advanced Postgres Monitoring
byDenish Patel
 
Webinar slides: An Introduction to Performance Monitoring for PostgreSQL
bySeveralnines
 
pg_proctab: Accessing System Stats in PostgreSQL
byCommand Prompt., Inc
 
pg_proctab: Accessing System Stats in PostgreSQL
byMark Wong
 
Creating PostgreSQL-as-a-Service at Scale
bySean Chittenden
 
pg_proctab: Accessing System Stats in PostgreSQL
byCommand Prompt., Inc
 
pg_proctab: Accessing System Stats in PostgreSQL
byMark Wong
 
Explain this!
byFabio Telles Rodriguez
 
query_tuning.pdf
byssuserf99076
 
pg_proctab: Accessing System Stats in PostgreSQL
byMark Wong
 
PGConf APAC 2018 - Monitoring PostgreSQL at Scale
byPGConf APAC
 
8.4 Upcoming Features
byPostgreSQL Experts, Inc.
 
Tracing and profiling my sql (percona live europe 2019) draft_1
byValerii Kravchuk
 
Aplicações 10x a 100x mais rápida com o postgre sql
byFabio Telles Rodriguez
 
Deep dive into PostgreSQL statistics.
byAlexey Lesovsky
 
Troubleshooting PostgreSQL with pgCenter
byAlexey Lesovsky
 
How does PostgreSQL work with disks: a DBA's checklist in detail. PGConf.US 2015
byPostgreSQL-Consulting
 
Elegant database tuning
byScott Mead
 
Nancy CLI. Automated Database Experiments
byNikolay Samokhvalov
 

Recently uploaded

DOCX
https://www.crscalculatorca.com
byhostproso
 
PPTX
Softaken JSON Converter for Smother and Secure JSON files Conversion
byDemetrio Parrilla
 
PPTX
MovingCloud360 – Elevating Business with Busy Software.pptx
byMoving Cloud
 
PPTX
Cyber-Security-Essentials (21AK1A0217).pptx
byGaganGagan44
 
PDF
Devoxx Belgium 2025 - Making significant Software Architecture decisions
byBert Jan Schrijver
 
PPTX
Empower Your Workforce with Remote Cloud PC Solutions
byvDesk.Works
 
PPTX
Safe Confined Space Entry Monitoring_ Singapore Experts.pptx
byprasadexiga0
 
PPTX
Developer cloud for full-stack apps - seenode
byseenode
 
PDF
The Y Combinator, a Lightning Talk from GoLab 2025
byEleanor McHugh
 
PPTX
How John started to like TDD (instead of hating it) (JavaCro, October'25)
byNacho Cougil
 
PDF
Smarter Test Maintenance At Scale, presented by Applitools
byApplitools
 
PDF
Where Does Your Data Go When You Put In Into A Database_.pdf
byDave Stokes
 
PDF
Exploring Large Language Models for Analyzing and Improving Method Names in S...
byUniversity of Hawai‘i at Mānoa
 
PDF
Blockchain development & Blockchain Solutions
byTetiana Riabova
 
PDF
Future-Proofing the PMO - Strategic Portfolio Management for 2026 and Beyond
byOnePlan Solutions
 
PPTX
Digital_Divide_Brief_Presentation is good
byprofessorkayongo
 
DOCX
Custom Search Web Part for SharePoint - Smarter, Faster, Personalized Search
bySharepoint Designs
 
PPTX
Declarative Process Mining with MINERful, Reloaded
byCecilia Iacometta
 
PDF
API Testing Lessons from the Insurance Domain
byPeter Thomas
 
PDF
A Gopher's Guide to *NIX Plumbing [workshop remixes!]
byEleanor McHugh
 
https://www.crscalculatorca.com
byhostproso
 
Softaken JSON Converter for Smother and Secure JSON files Conversion
byDemetrio Parrilla
 
MovingCloud360 – Elevating Business with Busy Software.pptx
byMoving Cloud
 
Cyber-Security-Essentials (21AK1A0217).pptx
byGaganGagan44
 
Devoxx Belgium 2025 - Making significant Software Architecture decisions
byBert Jan Schrijver
 
Empower Your Workforce with Remote Cloud PC Solutions
byvDesk.Works
 
Safe Confined Space Entry Monitoring_ Singapore Experts.pptx
byprasadexiga0
 
Developer cloud for full-stack apps - seenode
byseenode
 
The Y Combinator, a Lightning Talk from GoLab 2025
byEleanor McHugh
 
How John started to like TDD (instead of hating it) (JavaCro, October'25)
byNacho Cougil
 
Smarter Test Maintenance At Scale, presented by Applitools
byApplitools
 
Where Does Your Data Go When You Put In Into A Database_.pdf
byDave Stokes
 
Exploring Large Language Models for Analyzing and Improving Method Names in S...
byUniversity of Hawai‘i at Mānoa
 
Blockchain development & Blockchain Solutions
byTetiana Riabova
 
Future-Proofing the PMO - Strategic Portfolio Management for 2026 and Beyond
byOnePlan Solutions
 
Digital_Divide_Brief_Presentation is good
byprofessorkayongo
 
Custom Search Web Part for SharePoint - Smarter, Faster, Personalized Search
bySharepoint Designs
 
Declarative Process Mining with MINERful, Reloaded
byCecilia Iacometta
 
API Testing Lessons from the Insurance Domain
byPeter Thomas
 
A Gopher's Guide to *NIX Plumbing [workshop remixes!]
byEleanor McHugh
 

OSMC 2021 | pg_stat_monitor: A cool extension for better database (PostgreSQL) monitoring

  • 1.
    pg_stat_monitor A cool extensionfor better database (PostgreSQL) monitoring
  • 2.
    © 2021 Percona IbrarAhmed - The Author ● Software industries since 1998 ● Open Source Contribution other than PostgreSQL ○ Cross platform implementation of FTP for Google Chrome ○ Testing Framework for Google Chromium Project ○ Cross Platform Telnet application ● Working on PostgreSQL Since 2006 ● EnterpriseDB ○ Associate Software Architect core Database Engine 2006-2009 ○ Software Architect core Database Engine 2011 - 2016 ○ Senior Software Architect core Database Engine 2016 – 2018 ● Percona ○ Senior Software Architect core Database Engine 2018 – Present 2 Twitter: @ibrar_ahmad LinkedIn: ibrarahmed74 pgelephant.com
  • 3.
    © 2021 Percona PeterZaitsev - The Mentor ● CEO, Percona ● Database Performance Geek ● Know enough PostgreSQL to be dangerous 3 Twitter: @PeterZaitsev LinkedIn: peterzaitsev
  • 4.
    © 2021 Percona DenysKondratenko ● Engineering Manager in Percona ○ Percona Monitoring and Management ● Past experience around distributed storage as eng mgr ○ SUSE - Ceph ○ Seagate - Lustre ● Monitoring experience in Massive Solutions ● Embedded engineer in Mindspeed before that ● love linux, run full Wayland, openSUSEway (sway) 4 github: denisok LinkedIn: kondratenko https://per.co.na/discord
  • 5.
    © 2021 Percona| Confidential Database Performance Basics ● Percona - 15+ Years Open Source Database Performance Passion ● Most Common Reason of Poor Performance “Bad Queries” ● Users often do not even know they have such queries ● When they may not know source of such queries ● Or why they are bad and how to fix them 5
  • 6.
    © 2021 Percona| Confidential Sourcing the Data ● System State Sampling ○ Limited Overhead ○ Limited Accuracy ○ Can have measurement Artifacts ● Event Counting ○ Risk of high overhead ○ Very Good accuracy ● pg_stat_monitor is Event Counting Approach 6
  • 7.
    © 2021 Percona| Confidential Why Some Data Points are Important ? ● Actual parameters in the statements ○ So you can run explain or modify the query and see if it improves ● Time Bucketing ○ High Fidelity stats, Better Network Quality Tolerance ● Multi-Dimensional grouping, like Client IP ○ Security Analyses. Spot where workload which should not be running runs ● Capture Failed Queries ○ Some poorly Performing Queries never complete (ERROR, WARNING and LOG error levels). Catch errors in your application ● Capture Touched Relation (tables accessed by the statement) ○ Easily spot all queries touching given relation 7
  • 8.
    © 2021 Percona| Confidential Why Some Data Points are Important ? ● Response Time Distribution (histograms) ○ “Average” query execution time does not tell the whole story ● Bucketing by Different Query Plans ○ Because Different Query Plans can have drastically difference performance behavior ● Plan Information ○ Having Plan for actual query executions is helpful with unstable plans ● Query Hierarchy ○ When Query is called from Stored Routine both query and caller are very meaningful 8
  • 9.
    © 2021 Percona Downloadpg_stat_monitor Source code ○ git clone https://github.com/percona/pg_stat_monitor.git ○ cd pg_stat_monitor ○ make USE_PGXS=1 install Packages ○ Percona ■ https://www.percona.com/downloads/postgresql-distribution-13 ■ https://repo.percona.com/ppg-13/yum/release/7/RPMS/x86_64/ ○ PostgreSQL yum repositories ■ https://yum.postgresql.org/packages/ ○ PostgreSQL Extensions Network (PGXN) ■ pgxn install pg_stat_monitor 9
  • 10.
    © 2021 Percona Installpg_stat_monitor ● pg_stat_monitor library can be loaded by setting shared_preload_libraries in postgresql.conf file. shared_preload_libraries = 'pg_stat_monitor' # (change requires restart) ● The same parameter shared_preload_libraries can be changed using ALTER SYSTEM command. postgres=# ALTER SYSTEM SET shared_preload_libraries=pg_stat_monitor; ALTER SYSTEM ● Restart the server # sudo systemctl restart postgresql* ● Create the extension using the create extension command. postgres=# CREATE EXTENSION pg_stat_monitor; CREATE EXTENSION 10
  • 11.
    © 2021 Percona SQLversion for pg_stat_monitor # pg_stat_monitor extension comment = 'The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_ statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram in formation.' default_version = '1.0' module_pathname = '$libdir/pg_stat_monitor' relocatable = true $ ls -lrt *.sql -rw-r--r-- 1 vagrant vagrant 6756 Oct 28 19:18 pg_stat_monitor--1.0.sql 11
  • 12.
    © 2021 Percona Buildversion for pg_stat_monitor postgres=# SELECT pg_stat_monitor_version(); pg_stat_monitor_version ------------------------- 1.0.0-beta-3 (1 row) 12
  • 13.
    © 2021 Percona pg_stat_monitoradditional columns 13 Column Type Description bucket integer Data collection unit. The number shows what bucket in a chain a record belongs to bucket_start_time timestamp with time zone The start time of the bucket client_ip inet The IP address of a client that run the query planid text An internally generated ID of a query plan query_plan text The sequence of steps used to execute a query. This parameter is available only when the pgsm_enable_query_plan is enabled. top_query text Shows the top query used in a statement top_queryid text Shows the top query id used in a statement application_name text Shows the name of the application connected to the database relations text[] The list of tables involved in the query
  • 14.
    © 2021 Percona pg_stat_monitoradditional columns 14 Column Type Description cmd_type integer Type of the query executed cmd_type_text text elevel integer Shows the error level of a query (WARNING, ERROR, LOG) sqlcode integer The IP address of a client that run the query message text The error message resp_calls text[] Call histogram cpu_user_time double precision The time (in ms) the CPU spent on running the query cpu_sys_time double precision The time (in ms) the CPU spent on executing the kernel code state_code bigint Shows the state code of a query state text The state message
  • 15.
    © 2021 Percona postgres=#SELECT * FROM pg_stat_monitor_settings; name | value | default_value | description | minimum | maximum | restart ------------------------------------------+--------+---------------+----------------------------------------------------+---------+------------+--------- pg_stat_monitor.pgsm_max | 100 | 100 | Sets the maximum size of shared memory in (MB).... | 1 | 1000 | 1 pg_stat_monitor.pgsm_query_max_len | 1024 | 1024 | Sets the maximum length of query. | 1024 | 2147483647 | 1 pg_stat_monitor.pgsm_enable | 1 | 1 | Enable/Disable statistics collector. | 0 | 0 | 0 pg_stat_monitor.pgsm_track_utility | 1 | 1 | Selects whether utility commands are tracked. | 0 | 0 | 0 pg_stat_monitor.pgsm_normalized_query | 0 | 1 | Selects whether save query in normalized format. | 0 | 0 | 0 pg_stat_monitor.pgsm_max_buckets | 10 | 10 | Sets the maximum number of buckets. | 1 | 10 | 1 pg_stat_monitor.pgsm_bucket_time | 60 | 60 | Sets the time in seconds per bucket. | 1 | 2147483647 | 1 pg_stat_monitor.pgsm_histogram_min | 0 | 0 | Sets the time in millisecond. | 0 | 2147483647 | 1 pg_stat_monitor.pgsm_histogram_max | 100000 | 100000 | Sets the time in millisecond. | 10 | 2147483647 | 1 pg_stat_monitor.pgsm_histogram_buckets | 10 | 10 | Sets the maximum number of histogram buckets | 2 | 2147483647 | 1 pg_stat_monitor.pgsm_query_shared_buffer | 20 | 20 | Sets the maximum size of shared memory in (MB) | 1 | 10000 | 1 pg_stat_monitor.pgsm_overflow_target | 0 | 1 | Sets the overflow target for pg_stat_monitor | 0 | 1 | 1 pg_stat_monitor.pgsm_enable_query_plan | 0 | 0 | Enable/Disable query plan monitoring | 0 | 0 | 0 pg_stat_monitor.pgsm_track_planning | 1 | 1 | Selects whether planning statistics are tracked. | 0 | 0 | 0 (14 rows) 15
  • 16.
    © 2021 Percona name| value | default_value | description | minimum | maximum | restart ------------------------------------------+--------+---------------+----------------------------------------------------+---------+------------+--------- pg_stat_monitor.pgsm_max | 100 | 100 | Sets the maximum size of shared memory in (MB).... | 1 | 1000 | 1 ● Name ○ pg_stat_monitor.pgsm_max ● Value ○ 100 ● Default Value ○ 100 ● Description ○ Sets the maximum size of shared memory in (MB) used for statement's metadata tracked by pg_stat_monitor. ● Minimum ○ 1 ● Maximum ○ 1000 ● Restart ○ 1 16
  • 17.
    © 2021 Percona Buckets SELECTbucket, bucket_start_time, substr(query,0,50)|| '...' AS query, calls FROM pg_stat_monitor; bucket | bucket_start_time | query | calls -------+---------------------+--------------------------- +----- 0 | 2020-10-14 09:30:00 | INSERT INTO pgbench_tell...| 2 1 | 2020-10-14 09:30:30 | INSERT INTO pgbench_bran...| 1 1 | 2020-10-14 09:30:30 | SELECT relname, relkind ...| 1 1 | 2020-10-14 09:30:30 | SELECT queryid, bucket, ...| 1 2 | 2020-10-14 09:31:00 | CREATE table pgbench_his...| 1 2 | 2020-10-14 09:31:00 | SELECT queryid, bucket, ...| 1 2 | 2020-10-14 09:31:00 | SELECT queryid, bucket ...| 1 (7 rows) 17 b u c k e t 4 Bucket 3 Bucket 2 Bucket 0 B u c k e t 1 Bucket Start Time B u c k e t T i m e Aggregates
  • 18.
    © 2021 Percona Queryinformation postgres=# SELECT userid, datname, queryid, substr(query,0, 50) AS query, calls FROM pg_stat_monitor; userid | datname | queryid | query | calls ---------+----------+------------------+---------------------------------------------------+------- vagrant | postgres | 939C2F56E1F6A174 | END | 561 vagrant | postgres | 2A4437C4905E0E23 | SELECT abalance FROM pgbench_accounts WHERE aid = | 561 vagrant | postgres | 4EE9ED0CDF143477 | SELECT userid, datname, queryid, substr(query,$1 | 1 vagrant | postgres | 8867FEEB8A5388AC | vacuum pgbench_branches | 1 vagrant | postgres | 41D1168FB0733CAB | select count(*) from pgbench_branches | 1 vagrant | postgres | E5A889A8FF37C2B1 | UPDATE pgbench_accounts SET abalance = abalance + | 561 vagrant | postgres | 4876BBA9A8FCFCF9 | truncate pgbench_history | 1 vagrant | postgres | 22B76AE84689E4DC | INSERT INTO pgbench_history (tid, bid, aid, delta | 561 vagrant | postgres | F6DA9838660825CA | vacuum pgbench_tellers | 1 vagrant | postgres | 214646CE6F9B1A85 | BEGIN | 561 vagrant | postgres | 27462943E814C5B5 | UPDATE pgbench_tellers SET tbalance = tbalance + | 561 vagrant | postgres | 4F66D46F3D4151E | SELECT userid, dbid, queryid, substr(query,0, 50 | 1 vagrant | postgres | 6A02C123488B95DB | UPDATE pgbench_branches SET bbalance = bbalance + | 561 (13 rows) 18
  • 19.
    © 2021 Percona Queryinformation postgres=# SELECT application_name, client_ip, substr(query,0,100) AS query FROM pg_stat_monitor; application_name | client_ip | query ------------------+-----------+----------------------------------------------------------------------------- pgbench | 127.0.0.1 | truncate pgbench_history pgbench | 127.0.0.1 | SELECT abalance FROM pgbench_accounts WHERE aid = $1 pgbench | 127.0.0.1 | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2 pgbench | 127.0.0.1 | BEGIN; pgbench | 127.0.0.1 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3 pgbench | 127.0.0.1 | END; pgbench | 127.0.0.1 | vacuum pgbench_branches pgbench | 127.0.0.1 | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2 pgbench | 127.0.0.1 | vacuum pgbench_tellers pgbench | 127.0.0.1 | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2 pgbench | 127.0.0.1 | select o.n, p.partstrat, pg_catalog.count(i.inhparent) from pg_catalog.pg_ psql | 127.0.0.1 | SELECT application_name, client_ip, substr(query,$1,$2) as query FROM pg_s pgbench | 127.0.0.1 | select count(*) from pgbench_branches (13 rows) 19
  • 20.
    © 2021 Percona QueryMonitoring postgres=# SET pg_stat_monitor.pgsm_normalized_query = true; postgres=# SELECT a FROM foo where a = 10; a --- (0 rows) postgres=# SELECT queryid, substr(query,0,50)|| '...' AS query,calls FROM pg_stat_monitor; queryid | query | calls ------------------+------------------------------------------------------+------- 55F88A754A1BC5FF | SELECT a from foo where a = $1... | 1 postgres=# SET pg_stat_monitor.pgsm_normalized_query = false; postgres=# SELECT a,2 FROM foo where a = 10; a | ?column? ---+---------- (0 rows) postgres=# SELECT queryid, substr(query,0,50)|| '...' AS query,calls FROM pg_stat_monitor; queryid | query | calls ------------------+------------------------------------------------------+------- EF380BA0410F35EC | SELECT a,2 from foo where a = 10;... | 1 20
  • 21.
    © 2021 Percona ErrorLogging SELECT decode_error_level(elevel) AS elevel, sqlcode, query, message FROM pg_stat_monitor WHERE elevel != 0; elevel | sqlcode | query | message --------+----------+-----------------------+---------------------------------- ERROR | 16908420 | SELECT * FROM pg_foo; | relation "pg_foo" does not exist ERROR | 33816706 | SELECT 1/0; | division by zero 21
  • 22.
    © 2021 Percona QueryTiming Histogram postgres=# SELECT substr(query,0,40) || '... ' AS query, calls, resp_calls FROM pg_stat_monitor LIMIT 5; query | calls | resp_calls ---------------------------------------------+-------+------------------------ SELECT * FROM pg_stat_database;... | 24 | {23,0,1,0,0,0,0,0,0,0} select extract(epoch from current_times... | 24 | {24,0,0,0,0,0,0,0,0,0} SELECT /* pmm-agent:pgstatmonitor */ pg... | 1 | {1,0,0,0,0,0,0,0,0,0} SELECT pg_database.datname,tmp.mode,COA... | 24 | {24,0,0,0,0,0,0,0,0,0} SELECT /* pmm-agent:pgstatmonitor */ "p... | 1 | {1,0,0,0,0,0,0,0,0,0} (5 rows) 22
  • 23.
    © 2021 Percona QueryTiming Histogram SELECT resp_calls, query FROM pg_stat_monitor; resp_calls | query --------------------------------------------------+---------------------------------------------- {1," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"} | select client_ip, query from pg_stat_monitor {3," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 1"} | select * from pg_stat_monitor_reset() {3," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 0"," 1"} | SELECT * FROM foo SELECT * FROM histogram(0, 'F44CD1B4B33A47AF') AS a(range TEXT, freq INT, bar TEXT); range | freq | bar --------------------+------+-------------------------------- (0 - 3)} | 2 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ (3 - 10)} | 0 | (10 - 31)} | 1 | ■■■■■■■■■■■■■■■ (31 - 100)} | 0 | (100 - 316)} | 0 | (316 - 1000)} | 0 | (1000 - 3162)} | 0 | (3162 - 10000)} | 0 | (10000 - 31622)} | 0 | (31622 - 100000)} | 0 | (10 rows) 23
  • 24.
    © 2021 Percona PlanningStatistics SELECT queryid, plans_calls, rows_retrieved, substr(query,0,40) || '... ' AS query, calls FROM pg_stat_monitor; 24
  • 25.
    © 2021 Percona PlanningStatistics queryid | plans_calls | rows_retrieved | query | calls ------------------+-------------+----------------+-----------------------------+------- BA6EC88C00347CF7 | 0 | 0 | truncate table pgbench_... | 2 1B12EDE3C70B8F88 | 100 | 100 | insert into pgbench_tel... | 100 3FD5C490B83F760D | 0 | 0 | create table pgbench_br... | 2 B1B991EE89D61CB6 | 0 | 0 | create table pgbench_ac... | 2 29A99577F1695D28 | 0 | 11000000 | copy pgbench_accounts ... | 2 772222CE7E8765 | 0 | 0 | alter table pgbench_bra... | 2 D59D5F4391AA6B3 | 10 | 10 | insert into pgbench_bra... | 10 25
  • 26.
    © 2021 Percona QueryPlan: SELECT substr(query,0,50) AS query FROM pg_stat_monitor LIMIT 10; query | -------------------------------------------------- + SELECT abalance FROM pgbench_accounts WHERE aid = | | SELECT substr(query,$1,$2),calls, planid,query_pl | 26
  • 27.
    © 2021 Percona QueryPlan: SELECT substr(query,0,50) AS query_plan FROM pg_stat_monitor LIMIT 10; query_plan ---------------------------------------------------------------------------+ Index Scan using pgbench_accounts_pkey on pgbench_accounts | Index Cond: (aid = 102232) | Limit | -> Subquery Scan on pg_stat_monitor | -> Result | -> Sort | Sort Key: p.bucket_start_time | -> Hash Join | Hash Cond: (p.dbid = d.oid) | -> Function Scan on pg_stat_monitor_internal p | ... 27
  • 28.
    © 2021 Percona TopQuery CREATE OR REPLACE function add2(int, int) RETURNS INTEGER AS $$ BEGIN return (select $1 + $2); END; $$ language plpgsql; SELECT add2(1,2); add2 ------ 3 (1 row) 28
  • 29.
    © 2021 Percona TopQuery SELECT queryid, top_queryid, query, top_query FROM pg_stat_monitor; queryid | top_queryid | query | top_query ------------------+------------------+--------------------------+-------------- 762B99349F6C7F31 | 7209043A6BB0BCD5 | SELECT (select $1 + $2) | SELECT add2($1,$2) 7209043A6BB0BCD5 | | SELECT add2($1,$2) | (3 rows) 29
  • 30.
    © 2021 Percona Query/ Command Type SELECT bucket, substr(query,0, 50) AS query, cmd_type_text FROM pg_stat_monitor WHERE elevel = 0; bucket | query | cmd_type_text --------+---------------------------------------------------+---------- 4 | END | 4 | SELECT abalance FROM pgbench_accounts WHERE aid = | SELECT 4 | vacuum pgbench_branches | 4 | select count(*) from pgbench_branches | SELECT 4 | UPDATE pgbench_accounts SET abalance = abalance + | UPDATE 4 | truncate pgbench_history | 4 | INSERT INTO pgbench_history (tid, bid, aid, delta | INSERT 5 | SELECT relations query FROM pg_stat_monitor | SELECT 9 | SELECT bucket, substr(query,$1, $2) AS query, cmd | 30
  • 31.
    © 2021 Percona pg_stat_monitor:Others postgres=# d pg_stat_monitor View "public.pg_stat_monitor" Column | Type | ---------------------+------------------+ bucket | bigint | bucket_start_time | text | userid | regrole | datname | name | client_ip | inet | queryid | text | top_queryid | text | query | text | comments | text | 31 planid | text | query_plan | text | top_query | text | application_name | text | relations | text[] | cmd_type | integer | cmd_type_text | text | elevel | integer | sqlcode | text | message | text | calls | bigint | total_time | double precision | min_time | double precision |
  • 32.
    © 2021 Percona pg_stat_monitor:Others max_time | double precision | mean_time | double precision | stddev_time | double precision | rows_retrieved | bigint | plans_calls | bigint | plan_total_time | double precision | plan_min_time | double precision | plan_max_time | double precision | plan_mean_time | double precision | shared_blks_hit | bigint | shared_blks_read | bigint | shared_blks_dirtied | bigint | shared_blks_written | bigint | local_blks_hit | bigint | 32 local_blks_read | bigint | local_blks_dirtied | bigint | local_blks_written | bigint | temp_blks_read | bigint | temp_blks_written | bigint | blk_read_time | double precision | blk_write_time | double precision | resp_calls | text[] | cpu_user_time | double precision | cpu_sys_time | double precision | wal_records | bigint | wal_fpi | bigint | wal_bytes | numeric | state_code | bigint | state | text |
  • 33.
    © 2021 Percona 33 VanilaPostgreSQL pg_stat_statment pg_stat_monitor Database Size TPS 1.6GB 3256 16GB 840 160GB 608 Database Size TPS 1.6GB 3268 16GB 843 160GB 610 Database Size TPS 1.6GB 3262 16GB 844 160GB 611 Performance Metrics
  • 34.
    © 2021 Percona| Confidential pg_stat_monitor in PMM 34
  • 35.
    © 2021 Percona| Confidential pg_stat_monitor in PMM 35
  • 36.
    © 2021 Percona| Confidential pg_stat_monitor in PMM 36
  • 37.
    © 2021 Percona| Confidential pg_stat_monitor in PMM 37
  • 38.
    © 2021 Percona pg_stat_monitoradd service to PMM # pmm-admin add postgresql --query-source=pgstatmonitor 38
  • 39.
    © 2021 Percona Feedback ●If you found a bug or have a feature request in pg_stat_monitor, please submit the report to the https://jira.percona.com/projects/PG/issues ● If there is no existing report, submit your report following these steps: ○ Sign in to Jira issue tracker. You will need to create an account if you do not have one. ○ In the Summary, Description, Steps To Reproduce, Affects Version fields describe the problem you have detected. As a general rule of thumb, try to create bug reports that are: ● Reproducible: describe the steps to reproduce the problem. ● Unique: check if there already exists a JIRA ticket to describe the problem. ● Scoped to a Single Bug: only report one bug in one JIRA ticket. 39
  • 40.