• Stars
    star
    454
  • Rank 96,083 (Top 2 %)
  • Language
    Perl
  • License
    Other
  • Created almost 5 years ago
  • Updated 2 months ago

Reviews

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

Repository Details

Query Performance Monitoring Tool for PostgreSQL

postgresql-11-pgdg-package postgresql-12-pgdg-package postgresql-13-pgdg-package postgresql-14-pgdg-package postgresql-15-pgdg-package

Code coverage Forum

pg_stat_monitor: Query Performance Monitoring Tool for PostgreSQL

Table of Contents

Overview

NOTE: The latest stable releases can be found underneath Releases.

The pg_stat_monitor is a Query Performance Monitoring tool for PostgreSQL. It attempts to provide a more holistic picture by providing much-needed query performance insights in a single view.

pg_stat_monitor provides improved insights that allow database users to understand query origins, execution, planning statistics and details, query information, and metadata. This significantly improves observability, enabling users to debug and tune query performance. pg_stat_monitor is developed on the basis of pg_stat_statements as its more advanced replacement.

While pg_stat_statements provides ever-increasing metrics, pg_stat_monitor aggregates the collected data, saving user efforts for doing it themselves. pg_stat_monitor stores statistics in configurable time-based units โ€“ buckets. This allows focusing on statistics generated for shorter time periods and makes query timing information such as max/min/mean time more accurate.

NOTE: Because of these differences in data processing, memory blocks and WAL (Write Ahead Logs) related statistics data are displayed inconsistently when both pg_stat_monitor and pg_stat_statements are used together.

To learn about other features, available in pg_stat_monitor, see the Features section and the User Guide.

pg_stat_monitor supports PostgreSQL versions 11 and above. It is compatible with both PostgreSQL provided by PostgreSQL Global Development Group (PGDG) and Percona Distribution for PostgreSQL.

The RPM (for RHEL and CentOS) and the DEB (for Debian and Ubuntu) packages are available from Percona repositories for PostgreSQL versions 11, 12, 13, 14 and 15.

The RPM packages are also available in the official PostgreSQL (PGDG) yum repositories.

Supported versions

The pg_stat_monitor should work on the latest version of both Percona Distribution for PostgreSQL and PostgreSQL, but is only tested with these versions:

Distribution Version Provider
Percona Distribution for PostgreSQL 11, 12, 13, 14 and 15 Percona
PostgreSQL 11, 12, 13, 14 and 15 PostgreSQL Global Development Group (PGDG)

Features

pg_stat_monitor simplifies query observability by providing a more holistic view of query from performance, application and analysis perspectives. This is achieved by grouping data in configurable time buckets that allow capturing of load and performance information for smaller time windows. So performance issues and patterns can be identified based on time and workload.

  • Time Interval Grouping: Instead of supplying one set of ever-increasing counts, pg_stat_monitor computes stats for a configured number of time intervals - time buckets. This allows for much better data accuracy, especially in the case of high resolution or unreliable networks.
  • Multi-Dimensional Grouping: While pg_stat_statements groups counters by userid, dbid, queryid, pg_stat_monitor uses a more detailed group for higher precision. This allows a user to drill down into the performance of queries.
  • Capture Actual Parameters in the Queries: pg_stat_monitor allows you to choose if you want to see queries with placeholders for parameters or actual parameter data. This simplifies debugging and analysis processes by enabling users to execute the same query.
  • Query Plan: Each SQL is now accompanied by its actual plan that was constructed for its execution. Thatโ€™s a huge advantage if you want to understand why a particular query is slower than expected.
  • Tables Access Statistics for a Statement: This allows us to easily identify all queries that accessed a given table. This set is at par with the information provided by the pg_stat_statements.
  • Histogram: Visual representation is very helpful as it can help identify issues. With the help of the histogram function, one can now view a timing/calling data histogram in response to an SQL query. And yes, it even works in psql.

Documentation

The following are useful links in pg_stat_monitor documentation:

  1. User guide
  2. Comparing pg_stat_monitor and pg_stat_statements
  3. pg_stat_monitor view reference
  4. Release notes
  5. Contributing guide

Supported platforms

The PostgreSQL YUM repository supports pg_stat_monitor for all supported versions for the following platforms:

  • Red Hat Enterprise/Rocky/CentOS/Oracle Linux 7 and 8
  • Fedora 33 and 34

Find the list of supported platforms for pg_stat_monitor within Percona Distribution for PostgreSQL on the Percona Release Lifecycle Overview page.

Installation guidelines

You can install pg_stat_monitor from the following sources:

Installing from Percona repositories

To install pg_stat_monitor from Percona repositories, you need to use the percona-release repository management tool.

  1. Install percona-release following the instructions relevant to your operating system
  2. Enable Percona repository:
percona-release setup ppgXX

Replace XX with the desired PostgreSQL version. For example, to install pg_stat_monitor for PostgreSQL 15, specify ppg15.

  1. Install pg_stat_monitor package
    • For Debian and Ubuntu:
      apt-get install percona-pg-stat-monitor15
    • For RHEL and CentOS:
      yum install percona-pg-stat-monitor15

Installing from PostgreSQL yum repositories

Install the PostgreSQL repositories following the instructions in the Linux downloads (Red Hat family) chapter in PostgreSQL documentation.

Install pg_stat_monitor:

dnf install -y pg_stat_monitor_<VERSION>

Replace the VERSION variable with the PostgreSQL version you are using (e.g. specify pg_stat_monitor_15 for PostgreSQL 15)

Installing from PGXN

You can install pg_stat_monitor from PGXN (PostgreSQL Extensions Network) using the PGXN client.

Use the following command:

pgxn install pg_stat_monitor

Configuration

You can find the configuration parameters of the pg_stat_monitor extension in the pg_stat_monitor_settings view. To change the default configuration, specify new values for the desired parameters using the GUC (Grant Unified Configuration) system. To learn more, refer to the Configuration parameters section of the documentation.

Setup

You can enable pg_stat_monitor when your postgresql instance is not running.

pg_stat_monitor needs to be loaded at the start time. The extension requires additional shared memory; therefore, add the pg_stat_monitor value for the shared_preload_libraries parameter and restart the postgresql instance.

Use the ALTER SYSTEMcommand from psql terminal to modify the shared_preload_libraries parameter.

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_monitor';

NOTE: If youโ€™ve added other modules to the shared_preload_libraries parameter (for example, pg_stat_statements), list all of them separated by commas for the ALTER SYSTEM command.

โš ๏ธ For PostgreSQL 13 and earlier versions,pg_stat_monitor must follow pg_stat_statements. For example, ALTER SYSTEM SET shared_preload_libraries = 'foo, pg_stat_statements, pg_stat_monitor'.

In PostgreSQL 14, you can specify pg_stat_statements and pg_stat_monitor in any order. However, due to the extensions' architecture, if both pg_stat_statements and pg_stat_monitor are loaded, only the last listed extension captures utility queries, CREATE TABLE, Analyze, etc. The first listed extension captures most common queries like SELECT, UPDATE, INSERT, but does not capture utility queries.

Thus, to collect the whole statistics with pg_stat_monitor, we recommend to specify the extensions as follows: ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements, pg_stat_monitor'.

Start or restart the postgresql instance to apply the changes.

  • On Debian and Ubuntu:
sudo systemctl restart postgresql.service
  • On Red Hat Enterprise Linux and CentOS:
sudo systemctl restart postgresql-15

Create the extension using the CREATE EXTENSION command. Using this command requires the privileges of a superuser or a database owner. Connect to psql as a superuser for a database and run the following command:

CREATE EXTENSION pg_stat_monitor;

This allows you to see the stats collected by pg_stat_monitor.

By default, pg_stat_monitor is created for the postgres database. To access the statistics from other databases, you need to create the extension for every database.

-- Select some of the query information, like client_ip, username and application_name etc.

postgres=# SELECT application_name, userid AS user_name, datname AS database_name, substr(query,0, 50) AS query, calls, client_ip
           FROM pg_stat_monitor;
 application_name | user_name | database_name |                       query                       | calls | client_ip
------------------+-----------+---------------+---------------------------------------------------+-------+-----------
 psql             | vagrant   | postgres      | SELECT application_name, userid::regrole AS user_ |     1 | 127.0.0.1
 psql             | vagrant   | postgres      | SELECT application_name, userid AS user_name, dat |     3 | 127.0.0.1
 psql             | vagrant   | postgres      | SELECT application_name, userid AS user_name, dat |     1 | 127.0.0.1
 psql             | vagrant   | postgres      | SELECT application_name, userid AS user_name, dat |     8 | 127.0.0.1
 psql             | vagrant   | postgres      | SELECT bucket, substr(query,$1, $2) AS query, cmd |     1 | 127.0.0.1
(5 rows)

To learn more about pg_stat_monitor features and usage, see the User Guide. To view all other data elements provided by pg_stat_monitor, please see the reference.

Building from source

To build pg_stat_monitor from source code, you require the following:

  • git
  • make
  • gcc
  • postgresql-devel | postgresql-server-dev-all

You can download the source code of the latest release of pg_stat_monitor from the releases page on GitHub or using git:

git clone git://github.com/Percona/pg_stat_monitor.git

Compile and install the extension

cd pg_stat_monitor
make USE_PGXS=1
make USE_PGXS=1 install

Uninstall pg_stat_monitor

To uninstall pg_stat_monitor, do the following:

  1. Disable statistics collection. From the psql terminal, run the following command:

    ALTER SYSTEM SET pg_stat_monitor.pgsm_enable = 0;
  2. Drop pg_stat_monitor extension:

    DROP EXTENSION pg_stat_monitor;
  3. Remove pg_stat_monitor from the shared_preload_libraries configuration parameter:

    ALTER SYSTEM SET shared_preload_libraries = '';

    Important: If the shared_preload_libraries parameter includes other modules, specify them all for the ALTER SYSTEM SET command to keep using them.

  4. Restart the postgresql instance to apply the changes. The following command restarts PostgreSQL 15. Replace the version value with the one you are using.

    • On Debian and Ubuntu:
    sudo systemctl restart postgresql.service
    • On Red Hat Enterprise Linux and CentOS:
    sudo systemctl restart postgresql-15

How we work

We follow the OneFlow git branching scheme to maintain the ongoing development and stable releases.

The concept of the OneFlow model is, that we do have a single long-lived branch which simplifies the versioning scheme and day-to-day operations that we have to perform.

What branches do exist?

  • Feature branch $ git checkout -b feature/my-feature main
  • Release branch $ git checkout -b release/1.1.0 8330ecd
  • Hotfix branch $ git checkout -b hotfix/1.0.2 1.0.1

How to contribute

We welcome and strongly encourage community participation and contributions, and are always looking for new members that are as dedicated to serving the community as we are.

The Contributing Guide contains the guidelines on how you can contribute.

Report a bug

If you would like to suggest a new feature / an improvement or you found a bug in pg_stat_monitor, please submit the report to the Percona Jira issue tracker.

Refer to the Submit a bug report or a feature request section for bug reporting guidelines.

Support, discussions and forums

We welcome your feedback on your experience with pg_stat_monitor. Join our technical forum for help with pg_stat_monitor.

License

This project is licensed under the same open liberal terms and conditions as the PostgreSQL project itself. Please refer to the LICENSE file for more details.

Copyright notice

  • Portions Copyright ยฉ 2018-2023, Percona LLC and/or its affiliates
  • Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
  • Portions Copyright (c) 1994, The Regents of the University of California

More Repositories

1

grafana-dashboards

PMM dashboards for database monitoring
JavaScript
2,656
star
2

percona-xtrabackup

Open source hot backup tool for InnoDB and XtraDB databases
C++
1,159
star
3

mongodb_exporter

A Prometheus exporter for MongoDB including sharding, replication and storage engines
Go
1,146
star
4

percona-server

Percona Server
C++
1,127
star
5

percona-toolkit

Percona Toolkit: a collection of advanced open source command-line tools.
Perl
878
star
6

tokudb-engine

Percona TokuDB is a high-performance, write optimized, compressing, transactional storage engine for Percona Server. Issue tracker: https://tokutek.atlassian.net/browse/DB/ Wiki: https://github.com/Percona/tokudb-engine/wiki Downloads:
C++
649
star
7

pmm

Percona Monitoring and Management: an open source database monitoring, observability and management tool
Go
587
star
8

PerconaFT

PerconaFT is a high-performance, transactional key-value store
C++
553
star
9

percona-xtradb-cluster-operator

Percona Operator for MySQL based on Percona XtraDB Cluster
Go
511
star
10

percona-xtradb-cluster

A High Scalability Solution for MySQL Clustering and High Availability
C++
340
star
11

percona-server-mongodb-operator

Percona Operator for MongoDB
Go
321
star
12

percona-docker

Collection of Dockerfiles for Percona software. See individual directories for more details.
Shell
295
star
13

percona-postgresql-operator

Percona Operator for PostgreSQL
Go
261
star
14

percona-backup-mongodb

Percona Backup for MongoDB
Go
256
star
15

go-mysql

Go packages for MySQL
Go
231
star
16

pmm-server

PMM Server
186
star
17

proxysql-admin-tool

ProxySQL Admin
Shell
143
star
18

percona-monitoring-plugins

Percona Monitoring Plugins
PHP
134
star
19

percona-server-mysql-operator

Percona Operator for MySQL
Go
129
star
20

percona-helm-charts

Collection of Helm charts for Percona Kubernetes Operators.
Mustache
111
star
21

proxysql_exporter

Prometheus exporter for ProxySQL performance data.
Go
105
star
22

everest

Percona Everest is a cloud-native database platform to deploy and manage enterprise-grade PostgreSQL, MongoDB and MySQL database clusters.
TypeScript
100
star
23

xtradb-cluster-tutorial

PXC Tutorial (Deprecated)
Ruby
77
star
24

pmm-client

PMM Client for 1.x
Go
65
star
25

pmm-doc

Percona Monitoring and Management (PMM) Technical Documentation
HTML
41
star
26

Percona-TokuBackup

C++
39
star
27

pmm-managed

pmm-managed manages configuration of PMM server components and exposes API for that.
Go
36
star
28

tokudb-backup-plugin

Percona TokuBackup -- moved to Percona Server, but this is kept for the commit history
C++
36
star
29

pmm-agent

pmm-agent for PMM 2.x.
Go
35
star
30

orchestrator

Go
30
star
31

awesome-pmm

A curated list of awesome Percona Monitoring and Management (PMM) free and opensource software, libraries and resources.
29
star
32

jemalloc

C
26
star
33

everest-doc

Everest Doc
HTML
25
star
34

everest-operator

Percona Everest Operator
Go
25
star
35

community

Percona Community repository. Join us on Percona.community and the Percona Forums at forums.percona.com.
HTML
23
star
36

qan-agent

Query Analytics Agent for PMM
Go
22
star
37

promconfig

Go package for Prometheus configuration file parsing and generation without dependencies.
Go
22
star
38

sysbench-scripts

Lua
21
star
39

support-snippets

Snippets Collections from support
Shell
20
star
40

psmdb-docs

Percona Server for MongoDB documentation
HTML
20
star
41

grafana-app

Percona app for Grafana
JavaScript
19
star
42

percona-everest-backend

Go
17
star
43

psmysql-docs

Percona Server for MySQL
HTML
17
star
44

qan-api

Query Analytics API for PMM 1.x
Go
16
star
45

pxc_scheduler_handler

Go
14
star
46

innodb-data-recovery-tool-docs

Documentation for innodb-data-recovery-tool
Python
13
star
47

training

(Deprecated) Repository for publicly available training resources. Please access the public materials using our website.
13
star
48

qan-app

Query Analytics Web App for PMM
TypeScript
12
star
49

mongodb-orchestration-tools

Golang tools for MongoDB container orchestration
Go
12
star
50

percona-repositories

RPM and DEB files for Percona repositories
Shell
11
star
51

pmm-admin

pmm-admin for PMM 2.x.
Go
11
star
52

go-pool

A simple, persistent resource pool
Go
11
star
53

pxc-docs

Percona XtraDB Cluster
HTML
11
star
54

postgresql-docs

Percona Distribution for PostgreSQL
HTML
11
star
55

qan-api2

qan-api for PMM 2.x.
Go
11
star
56

pxb-docs

Percona XtraBackup for MySQL
HTML
10
star
57

pmm-qa

PMM Quality Assurance
Shell
10
star
58

pmgo

Interfaces for mgo's structures
Go
10
star
59

backup-community

C
10
star
60

pmm-infra

Infrastructure as code for PMM (build and demo)
HCL
10
star
61

roadmap

Public Percona Roadmaps
10
star
62

k8spsmdb-docs

Percona Operator for MongoDB Documentation
HTML
9
star
63

proxysql-packaging

packaging for proxysql
Shell
8
star
64

openstack_ansible

Python
8
star
65

percona-everest-frontend

TypeScript
8
star
66

dbaas-operator

k8s Operator for PMM DBaaS
Go
7
star
67

postgres-packaging

Shell
6
star
68

k8spxc-docs

Documentation for Percona Operator for MySQL based on Percona XtraDB Cluster
HTML
6
star
69

percona-everest-cli

Go
6
star
70

tokudb-percona-server-5.6

TokuDB tests for Percona Server 5.6
Shell
6
star
71

pbm-docs

HTML
6
star
72

documentation

Percona documentation repository
Python
6
star
73

ab

Benchmark tools
Shell
5
star
74

tex-templates

Tex templates for presentation slides and more
TeX
5
star
75

pgsm-docs

pg_stat_monitor for PostgreSQL
HTML
5
star
76

k8spg-docs

Percona Operator for PostgreSQL Documentation
HTML
5
star
77

tokudb-docs

Percona TokuDB documentation
Python
5
star
78

pdmysql-docs

Percona Distribution for MySQL documentation
HTML
5
star
79

exporter_shared

Shared code for Percona Prometheus exporters
Go
4
star
80

pmm-dump

Export metrics or QAN data from or import them to PMM
Go
4
star
81

pmm-demo

Benchmark and sysload generators for pmmdemo.percona.com.
Shell
4
star
82

distmongo-docs

CSS
4
star
83

training-aws

PHP
4
star
84

dbaas-api

Makefile
4
star
85

proxysql-admin-tool-doc

HTML
3
star
86

replication-manager

Shell
3
star
87

debian-percona-xtradb-cluster-5.6

Repo for debian/ubuntu inclusion
C++
3
star
88

training-mymovies

Percona's My-Movies and Scoreboard application for use in Percona training classrooms.
PHP
3
star
89

jenkins-docker-images

Shell
3
star
90

pmm-update

PMM Update is a tool for updating packages and OS configuration for Percona Monitoring and Management Server.
Go
3
star
91

pmm-ui-tests

PMM UI end-to-end tests
JavaScript
3
star
92

sysbench-packaging

Packaging scripts for sysbench
Shell
3
star
93

saas

Platform (SaaS) public API definitions
Go
3
star
94

debian-percona-server-5.6

Repo for debian/ubuntu inclusion
C++
3
star
95

pid-watchdog

pid-watchdog is a free open source utility for managing and monitoring processes on a UNIX system. pid-watchdog conducts automatic maintenance and repair and can execute meaningful causal actions in error situations. Golang analog of http://mmonit.com/monit/ proccess scheduler. Can be used as PID 1 in docker.
Shell
3
star
96

tokumx-docs

Percona TokuMX documentation
Python
3
star
97

repo-config-docs

HTML
3
star
98

cloud-tools

PCT documentation
2
star
99

everest-catalog

Go
2
star
100

drtools

drtools
2
star