Skip to content
logo
Percona Product Documentation
pg-stat-monitor
Initializing search
    percona/postgresql-docs
    percona/postgresql-docs
    • Percona Distribution for PostgreSQL 14 Documentation
        • Overview
        • Install on Debian and Ubuntu
        • Install on RHEL and derivatives
        • Enable Percona Distribution for PostgreSQL extensions
        • Repositories overview
      • Migrate from PostgreSQL to Percona Distribution for PostgreSQL
      • Upgrading Percona Distribution for PostgreSQL from 13 to 14
      • Minor Upgrade of Percona Distribution for PostgreSQL
      • pg-stat-monitor
        • Overview
          • Views
            • pg_stat_monitor view
            • pg_stat_monitor_settings view (dropped)
        • Installation
        • Setup
        • Usage
        • Changing the configuration
        • High availability
        • Deploying on Debian or Ubuntu
        • Deploying on RHEL or CentOS
        • Testing the Patroni PostgreSQL Cluster
        • Backup and disaster recovery
        • Deploying backup and disaster recovery solution in Percona Distribution for PostgreSQL
      • LDAP authentication
    • Uninstall
      • Release notes index
      • Percona Distribution for PostgreSQL 14.7 (2023-03-23)
      • Percona Distribution for PostgreSQL 14.6 (2022-11-23)
      • Percona Distribution for PostgreSQL 14.5 (2022-09-05)
      • Percona Distribution for PostgreSQL 14.4 (2022-06-27)
      • Percona Distribution for PostgreSQL 14.3 (2022-06-01)
      • Percona Distribution for PostgreSQL 14.2 Second Update (2022-05-05)
      • Percona Distribution for PostgreSQL 14.2 Update (2022-04-14)
      • Percona Distribution for PostgreSQL 14.2 (2022-03-16)
      • Percona Distribution for PostgreSQL 14.1 Second Update (2021-12-07)
      • Percona Distribution for PostgreSQL 14.1 Update (2021-12-02)
      • Percona Distribution for PostgreSQL 14.1 (2021-11-22)
    • Licensing

    • Overview
      • Views
        • pg_stat_monitor view
        • pg_stat_monitor_settings view (dropped)
    • Installation
    • Setup
    • Usage
    • Changing the configuration

    pg_stat_monitor¶

    Note

    This document describes the functionality of pg_stat_monitor 2.0.0.

    Overview¶

    pg_stat_monitor is a Query Performance Monitoring tool for PostgreSQL. It collects various statistics data such as query statistics, query plan, SQL comments and other performance insights. The collected data is aggregated and presented in a single view. This allows you to view queries from performance, application and analysis perspectives.

    pg_stat_monitor groups statistics data and writes it in a storage unit called bucket. The data is added and stored in a bucket for the defined period – the bucket lifetime. This allows you to identify performance issues and patterns based on time.

    You can specify the following:

    • The number of buckets. Together they form a bucket chain.
    • Bucket size. This is the amount of shared memory allocated for buckets. Memory is divided equally among buckets.
    • Bucket lifetime.

    When a bucket lifetime expires, pg_stat_monitor resets all statistics and writes the data in the next bucket in the chain. When the last bucket’s lifetime expires, pg_stat_monitor returns to the first bucket.

    Important

    The contents of the bucket will be overwritten. In order not to lose the data, make sure to read the bucket before pg_stat_monitor starts writing new data to it.

    Views¶

    pg_stat_monitor view¶

    The pg_stat_monitor view contains all the statistics collected and aggregated by the extension. This view contains one row for each distinct combination of metrics and whether it is a top-level statement or not (up to the maximum number of distinct statements that the module can track). For details about available metrics, refer to the pg_stat_monitor view reference.

    The following are the primary keys for pg_stat_monitor:

    • bucket
    • userid
    • datname
    • queryid
    • client_ip
    • planid
    • application_name

    A new row is created for each key in the pg_stat_monitor view.

    For security reasons, only superusers and members of the pg_read_all_stats role are allowed to see the SQL text, client_ip and queryid of queries executed by other users. Other users can see the statistics, however, if the view has been installed in their database.

    pg_stat_monitor_settings view (dropped)¶

    Starting with version 2.0.0, the pg_stat_monitor_settings view is deprecated and removed. All pg_stat_monitor configuration parameters are now available though the pg_settings view using the following query:

    SELECT name, setting, unit, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, pending_restart FROM pg_settings WHERE name LIKE '%pg_stat_monitor%';
    

    For backward compatibility, you can create the pg_stat_monitor_settings view using the following SQL statement:

    CREATE VIEW pg_stat_monitor_settings
    
    AS
    
    SELECT *
    
    FROM pg_settings
    
    WHERE name like 'pg_stat_monitor.%';
    

    In pg_stat_monitor version 1.1.1 and earlier, the pg_stat_monitor_settings view shows one row per pg_stat_monitor configuration parameter. It displays configuration parameter name, value, default value, description, minimum and maximum values, and whether a restart is required for a change in value to be effective.

    To learn more, see the Changing the configuration section.

    Installation¶

    This section describes how to install pg_stat_monitor from Percona repositories. To learn about other installation methods, see the Installation section in the pg_stat_monitor documentation.

    Preconditions:

    To install pg_stat_monitor from Percona repositories, you need to subscribe to them. To do this, you must have the percona-release repository management tool up and running.

    To install pg_stat_monitor, run the following commands:

    1. Enable the repository

      $ sudo percona-release setup ppg14
      
    2. Update the local cache

      $ sudo apt update
      
    3. Install the package:

      $ sudo apt-get install percona-pg-stat-monitor14
      
    1. Enable the repository

      $ sudo percona-release setup ppg14
      
    2. Install the package:

      $ sudo yum install percona-pg-stat-monitor11
      

    Setup¶

    pg_stat_monitor requires additional setup in order to use it with PostgreSQL. The setup steps are the following:

    1. Add pg_stat_monitor in the shared_preload_libraries configuration parameter.

      The recommended way to modify PostgreSQL configuration file is using the ALTER SYSTEM command. Connect to psql and use the following command:

      ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_monitor';
      

      The parameter value is written to the postgresql.auto.conf file which is read in addition with postgresql.conf file.

      Note

      To use pg_stat_monitor together with pg_stat_statements, specify both modules separated by commas for the ALTER SYSTEM SET command.

      The order of modules is important: pg_stat_monitor must be specified after pg_stat_statements:

      ALTER SYSTEM SET shared_preload_libraries = ‘pg_stat_statements, pg_stat_monitor’
      
    2. Start or restart the postgresql instance to enable pg_stat_monitor. Use the following command for restart:

      $ sudo systemctl restart postgresql.service
      
      $ sudo systemctl restart postgresql-14
      
    3. Create the extension. Connect to psql and use the following command:

      CREATE EXTENSION pg_stat_monitor;
      

      By default, the extension is created against the postgres database. You need to create the extension on every database where you want to collect statistics.

    Tip

    To check the version of the extension, run the following command in the psql session:

    SELECT pg_stat_monitor_version();
    

    Usage¶

    For example, to view the IP address of the client application that made the query, run the following command:

    SELECT DISTINCT userid::regrole, pg_stat_monitor.datname, substr(query,0, 50) AS query, calls, bucket, bucket_start_time, queryid, client_ip
    FROM pg_stat_monitor, pg_database
    WHERE pg_database.oid = oid;
    
      userid  | datname  |                       query                       | calls | client_ip
    ----------+----------+---------------------------------------------------+-------+-----------
     postgres | postgres | select bucket, bucket_start_time, query,calls fro |     1 | 127.0.0.1
     postgres | postgres | SELECT c.relchecks, c.relkind, c.relhasindex, c.r |     1 | 127.0.0.1
     postgres | postgres | SELECT  userid,  total_time, min_time, max_time,  |     1 | 127.0.0.1
    

    Find more usage examples in the pg_stat_monitor user guide.

    Changing the configuration¶

    Run the following query to list available configuration parameters.

    SELECT name,description FROM pg_stat_monitor_settings;
    

    Output

    name                      |                            description
    -----------------------------------------------+-------------------------------------------------------------------
     pg_stat_monitor.pgsm_max                      | Sets the maximum number of statements tracked by pg_stat_monitor.
     pg_stat_monitor.pgsm_query_max_len            | Sets the maximum length of query.
     pg_stat_monitor.pgsm_enable                   | Enable/Disable statistics collector.
     pg_stat_monitor.pgsm_track_utility            | Selects whether utility commands are tracked.
     pg_stat_monitor.pgsm_normalized_query         | Selects whether save query in normalized format.
     pg_stat_monitor.pgsm_max_buckets              | Sets the maximum number of buckets.
     pg_stat_monitor.pgsm_bucket_time              | Sets the time in seconds per bucket.
     pg_stat_monitor.pgsm_histogram_min            | Sets the time in millisecond.
     pg_stat_monitor.pgsm_histogram_max            | Sets the time in millisecond.
     pg_stat_monitor.pgsm_histogram_buckets        | Sets the maximum number of histogram buckets
     pg_stat_monitor.pgsm_query_shared_buffer      | Sets the maximum size of shared memory in (MB) used for query tracked by pg_stat_monitor.
     pg_stat_monitor.pgsm_overflow_target          | Sets the overflow target for pg_stat_monitor
     pg_stat_monitor.pgsm_enable_query_plan        | Enable/Disable query plan monitoring
     pg_stat_monitor.pgsm_track_planning           | Selects whether planning statistics are tracked.
    

    You can change a parameter by setting a new value in the configuration file. Some parameters require server restart to apply a new value. For others, configuration reload is enough. Refer to the configuration parameters of the pg_stat_monitor documentation for the parameters’ description, how you can change their values and if the server restart is required to apply them.

    As an example, let’s set the bucket lifetime from default 60 seconds to 100 seconds. Use the ALTER SYSTEM command:

    ALTER SYSTEM set pg_stat_monitor.pgsm_bucket_time = 100;
    

    Restart the server to apply the change:

    $ sudo systemctl restart restart postgresql.service
    
    $ sudo systemctl restart postgresql-14
    

    Verify the updated parameter:

    SELECT name, value
      FROM pg_stat_monitor_settings
      WHERE name = 'pg_stat_monitor.pgsm_bucket_time';
    
                     name               | value
      ----------------------------------+-------
       pg_stat_monitor.pgsm_bucket_time |   100
    

    See also

    pg_stat_monitor Documentation

    Percona Blog:

    • pg_stat_monitor: A New Way Of Looking At PostgreSQL Metrics
    • Improve PostgreSQL Query Performance Insights with pg_stat_monitor

    Contact Us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: March 23, 2023
    Created: June 4, 2021
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.