StatsMgr offcial repo is at Codeberg: https://codeberg.org/Data-Bene/StatsMgr
This repository has been archived on 2025-01-07. You can view files and clone it, but cannot push or open issues or pull requests.
Find a file
Cédric Villemain 5aa78e8d6b Remove screenshot/video from bug report
StatsMgr is text based and we will not debug based on a screenshort,
even less with video recording, so remove this part.
2025-01-07 08:06:44 +01:00
expected Address round 1 of feedback 2024-12-30 17:12:34 -08:00
sql Initial overhaul of error messages and comments for typos, clarity of phrasing, and consistency 2024-12-28 11:41:05 -08:00
CONTRIBUTING.md Remove screenshot/video from bug report 2025-01-07 08:06:44 +01:00
LICENSE.md Specify legal & trading names in all documents as per Cat & Andrea 2025-01-01 17:56:25 -08:00
Makefile Add more SQL API and overall cleanup 2024-12-10 09:04:44 +01:00
README.statsmgr.md Add LICENSE.md containing the PostgreSQL License 2024-12-28 23:19:07 -08:00
statsmgr--0.1-alpha.sql Initial overhaul of error messages and comments for typos, clarity of phrasing, and consistency 2024-12-28 11:41:05 -08:00
statsmgr-pg17.h Specify legal & trading names in all documents as per Cat & Andrea 2025-01-01 17:56:25 -08:00
statsmgr.c Specify legal & trading names in all documents as per Cat & Andrea 2025-01-01 17:56:25 -08:00
statsmgr.control init extension StatsMgr 2024-11-25 10:22:38 +01:00
statsmgr.h Specify legal & trading names in all documents as per Cat & Andrea 2025-01-01 17:56:25 -08: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 intervals
  • keep history of the N last snapshots
  • C and SQL API

Requirements

StatsMgr requires PostgreSQL 17 at minimum.

Installation

StatMgr leverages advanced features from PostgreSQL 17. You can start using it without modifying the 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.

Licensing

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

Contributing

Contributions are welcome! For instructions on contributing to this extension, please check out CONTRIBUTING.md.

For any additional questions, feature requests, or suggestions, please contact Data Bene.