StatsMgr
offcial repo is at Codeberg: https://codeberg.org/Data-Bene/StatsMgr
StatsMgr is text based and we will not debug based on a screenshort, even less with video recording, so remove this part. |
||
---|---|---|
expected | ||
sql | ||
CONTRIBUTING.md | ||
LICENSE.md | ||
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 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
- 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.
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.