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.
It was wrongly exiting, failing to output rows. Test updated accordingly. |
||
---|---|---|
expected | ||
sql | ||
Makefile | ||
README.statsmgr.md | ||
statsmgr--0.1-alpha.sql | ||
statsmgr-pg17.h | ||
statsmgr.c | ||
statsmgr.control | ||
statsmgr.h |
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
- Install the extension in the desired database:
CREATE EXTENSION statsmgr;
Permanent Installation
- Add
statsmgr
toshared_preload_libraries
in yourpostgresql.conf
file:shared_preload_libraries = 'statsmgr'
- 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 snapshotstick_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
-
Main Worker:
- Manages lifecycle and initialization of
StatsMgr
. - Periodically spawns snapshot workers.
- Manages lifecycle and initialization of
-
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:
- Extend the
StatsMgrKindHandler
structure with a new kind. - 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.