pg_stat_monitor

1. Overview

pg_stat_monitor is a query performance monitoring extension for PostgreSQL developed by Percona. It collects statistics in a single view, including query text, timing, and wait events. It is fully compatible with IvorySQL.

2. Installation

2.1. Prerequisites

pg_stat_monitor must be loaded at server start. Add it to shared_preload_libraries in postgresql.conf:

shared_preload_libraries = 'pg_stat_monitor'

2.2. Source Code Installation

Please ensure that IvorySQL 5.4 or above is installed and pg_config is available in PATH.

$ git clone https://github.com/percona/pg_stat_monitor.git
$ cd pg_stat_monitor
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install

3. Create Extension and Verify

Connect to the database with psql and execute the following commands:

ivorysql=# CREATE EXTENSION pg_stat_monitor;
CREATE EXTENSION

ivorysql=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_monitor';
      name       | default_version | installed_version |       comment
-----------------+-----------------+-------------------+--------------------
 pg_stat_monitor | 2.1             | 2.1               | pg_stat_monitor

4. Query Statistics

After enabling the extension, query statistics are collected in the pg_stat_monitor view:

ivorysql=# SELECT bucket_start_time, query, calls, total_exec_time
           FROM pg_stat_monitor
           ORDER BY total_exec_time DESC
           LIMIT 5;