StatsMgr is a PostgreSQL extension designed for efficient and organized management of statistics. It leverages background workers and shared memory to snapshot, manage, and query various statistics kinds, including WAL, SLRU, checkpointing, and others. The extension supports dynamic initialization, shared memory utilization, and SQL interfaces for querying and managing statistics data.
Find a file
Cédric Villemain de21839fee Fix fetch stats output when worker down
It was wrongly exiting, failing to output rows. Test updated
accordingly.
2024-12-10 10:46:07 +01:00
expected Fix fetch stats output when worker down 2024-12-10 10:46:07 +01:00
sql Add more SQL API and overall cleanup 2024-12-10 09:04:44 +01:00
Makefile Add more SQL API and overall cleanup 2024-12-10 09:04:44 +01:00
README.statsmgr.md Fix fetch stats output when worker down 2024-12-10 10:46:07 +01:00
statsmgr--0.1-alpha.sql Add more SQL API and overall cleanup 2024-12-10 09:04:44 +01:00
statsmgr-pg17.h Add more SQL API and overall cleanup 2024-12-10 09:04:44 +01:00
statsmgr.c Fix fetch stats output when worker down 2024-12-10 10:46:07 +01:00
statsmgr.control init extension StatsMgr 2024-11-25 10:22:38 +01:00
statsmgr.h Several fixup and add ticks timestamps 2024-11-27 21:48:57 +01:00

StatsMgr: PostgreSQL Statistics Management Extension

StatsMgr is a PostgreSQL extension designed for efficient and organized management of statistics. It leverages background workers and shared memory to snapshot, manage, and query various statistics kinds, including WAL, SLRU, checkpointing, and others. The extension supports dynamic initialization, shared memory utilization, and SQL interfaces for querying and managing statistics data.

Main Features

  • snapshot PostgreSQL cumulative statistics at fixed interval
  • keep history of the N last snapshots
  • C and SQL API

Requirements

StatsMgr requires PostgreSQL 17 minimum.

Installation

StatMgr leverages advanced features from PostgreSQL 17 and you can start using it without modifying PostgreSQL configuration (see Temporary Installation just below).

If you're happy with the extension, you can then apply the Permanent Installation, or ensure the StatsMgr background worker is started automaticaly by other means (see Usage).

Temporary Installation

  1. Install the extension in the desired database:
    CREATE EXTENSION statsmgr;
    

Permanent Installation

  1. Add statsmgr to shared_preload_libraries in your postgresql.conf file:
    shared_preload_libraries = 'statsmgr'
    
  2. Restart PostgreSQL for the changes to take effect.

Usage

SQL Functions

StatsMgr provides SQL functions to start/stop workers, reset, execute and get snapshots.

Worker Management

  • Start the main background worker:

    SELECT statsmgr_start_main_worker();
    
  • Stop the main background worker:

    SELECT statsmgr_stop_main_worker();
    
  • Reset all snapshots:

    SELECT statsmgr_reset();
    
  • Manually trigger a snapshot of all registered statistics:

    SELECT statsmgr_snapshot();
    

Query Statistics

All snapshots contain the attributes from the stats in PostgreSQL (see pg_stat_io and others), as well as 2 extra attributes:

  • tick, a key to reference snapshots
  • tick_tz, a timestamp with time zone of the tick used to reference snapshots.

Query specific snapshots with the following functions:

  • Archiver Stats:
    SELECT * FROM statsmgr_archiver();
    
  • BGWriter Stats:
    SELECT * FROM statsmgr_bgwriter();
    
  • Checkpointer Stats:
    SELECT * FROM statsmgr_checkpointer();
    
  • IO Stats:
    SELECT * FROM statsmgr_io();
    
  • SLRU Stats:
    SELECT * FROM statsmgr_slru();
    
  • WAL Stats:
    SELECT * FROM statsmgr_wal();
    

Architecture Overview

Background Workers

  1. Main Worker:

    • Manages lifecycle and initialization of StatsMgr.
    • Periodically spawns snapshot workers.
  2. Snapshot Worker:

    • Handles the actual snapshotting of registered statistics kinds.
    • Stores snapshots in dynamic shared memory for concurrent access.

Shared Memory Management

  • Uses one main Dynamic Shared Memory (DSM) segment to manage ticks (and locks).
  • Uses one Dynamic Shared Memory (DSM) segment for each kind of statistics.
  • Employs locks to ensure safe concurrent access and updates.

Statistics Handling

Statistics kinds supported include:

  • WAL
  • SLRU
  • BGWriter
  • Checkpointer
  • Archiver
  • IO

Each statistics kind is registered with:

  • A handler for fetching and managing statistics.
  • Shared memory structures for storing historical snapshots.

Developer Notes

Extensibility

To support additional statistics kinds:

  1. Extend the StatsMgrKindHandler structure with a new kind.
  2. Register handlers for fetching and managing the new kind of statistics.

TODOs

  • Align the initial tick with clock 00:00 for better synchronization ?
  • Dynamically manage GUC updates for more flexibility.

Licensing

This extension is licensed under the PostgreSQL License. For details, see the LICENSE file in the source repository.

Contribution

Contributions are welcome! If you have any questions, feature requests, or suggestions, please contact Data Bene.