A minimalist extension and utility pair for time series analysis and visualization of PostgreSQL internal statistics.
Project description
pg_statviz
pg_statviz
is a minimalist extension and utility pair for time series analysis and visualization
of PostgreSQL internal statistics.
Created for snapshotting PostgreSQL's cumulative and dynamic statistics and performing time series analysis on them. The accompanying utility can produce visualizations for selected time ranges on the stored stats snapshots, enabling the user to track PostgreSQL performance over time and potentially perform tuning or troubleshooting.
Design Philosophy
Designed with the K.I.S.S. and
UNIX philosophies in mind, pg_statviz
aims to be
a modular, minimal and unobtrusive tool that does only what it's meant for: create snapshots
of PostgreSQL statistics for visualization and analysis. To this end, a utility is provided for
retrieving the stored snapshots and creating with them simple visualizations using
Matplotlib.
Installation
Extension
The extension can be installed like this, for example inside psql
:
CREATE EXTENSION pg_statviz;
This will create the needed tables and functions under schema pgstatviz
(note the lack of
underscore in the schema name).
Utility
The visualization utility can be installed from PyPi:
pip install pg_statviz
Requirements
Python 3.9+ is required for the visualization utility.
Usage
The extension can be used by superusers, or any user that has pg_monitor
role privileges. To take
a snapshot, e.g. from psql
:
SELECT pgstatviz.snapshot();
NOTICE: created pg_statviz snapshot
snapshot
-------------------------------
2023-01-27 11:04:58.055453+00
(1 row)
Older snapshots and their associated data can be removed using any time expression. For example, to remove data more than 90 days old:
DELETE FROM pgstatviz.snapshots
WHERE snapshot_tstamp < CURRENT_DATE - 90;
Or all snapshots can be removed like this:
SELECT pgstatviz.delete_snapshots();
NOTICE: truncating table "snapshots"
NOTICE: truncate cascades to table "conf"
NOTICE: truncate cascades to table "buf"
NOTICE: truncate cascades to table "conn"
NOTICE: truncate cascades to table "lock"
NOTICE: truncate cascades to table "wait"
NOTICE: truncate cascades to table "wal"
NOTICE: truncate cascades to table "db"
delete_snapshots
------------------
(1 row)
The pg_monitor
role can be assigned to any user:
GRANT pg_monitor TO myuser;
Scheduling
Periodic snapshots can be set up with any job scheduler. For example with cron
:
crontab -e -u postgres
Inside the postgres
user's crontab, add this line to take a snapshot every 15 minutes:
*/15 * * * * psql -c "SELECT pgstatviz.snapshot()" >/dev/null 2>&1
Visualization
The visualization utility can be called like a PostgreSQL command line tool:
pg_statviz --help
usage: pg_statviz [--help] [--version] [-d DBNAME] [-h HOSTNAME] [-p PORT] [-U USERNAME] [-W]
[-D FROM TO] [-O OUTPUTDIR]
{analyze,buf,cache,checkp,conn,lock,tuple,wait,wal} ...
run all analysis modules
positional arguments:
{analyze,buf,cache,checkp,conn,tuple,wait,wal}
analyze run all analysis modules
buf run buffers written analysis module
cache run cache hit ratio analysis module
checkp run checkpoint analysis module
conn run connection count analysis module
lock run locks analysis module
tuple run tuple count analysis module
wait run wait events analysis module
wal run WAL generation analysis module
options:
--help
--version show program's version number and exit
-d DBNAME, --dbname DBNAME
database name to analyze (default: 'myuser')
-h HOSTNAME, --host HOSTNAME
database server host or socket directory (default: '/var/run/postgresql')
-p PORT, --port PORT database server port (default: '5432')
-U USERNAME, --username USERNAME
database user name (default: 'myuser')
-W, --password force password prompt (should happen automatically) (default: False)
-D FROM TO, --daterange FROM TO
date range to be analyzed in ISO 8601 format e.g. 2023-01-01T00:00
2023-01-01T23:59 (default: [])
-O OUTPUTDIR, --outputdir OUTPUTDIR
output directory (default: -)
Specific module usage
pg_statviz conn --help
usage: pg_statviz conn [-h] [-d DBNAME] [--host HOSTNAME] [-p PORT] [-U USERNAME] [-W]
[-D FROM TO] [-O OUTPUTDIR] [-u [USERS ...]]
run connection count analysis module
options:
-h, --help show this help message and exit
-d DBNAME, --dbname DBNAME
database name to analyze (default: 'myuser')
--host HOSTNAME database server host or socket directory (default: '/var/run/postgresql')
-p PORT, --port PORT database server port (default: '5432')
-U USERNAME, --username USERNAME
database user name (default: 'myuser')
-W, --password force password prompt (should happen automatically) (default: False)
-D FROM TO, --daterange FROM TO
date range to be analyzed in ISO 8601 format e.g. 2023-01-01T00:00
2023-01-01T23:59 (default: [])
-O OUTPUTDIR, --outputdir OUTPUTDIR
output directory (default: -)
-u [USERS ...], --users [USERS ...]
user name(s) to plot in analysis (default: [])
Example:
pg_statviz buf --host localhost -d postgres -U postgres -D 2023-01-24T23:00 2023-01-26
Produces:
Export data
Data from pg_statviz
internal tables can be exported to a tab separated values (TSV) file for use
by other tools:
psql -c "COPY pgstatviz.conn TO STDOUT CSV HEADER DELIMITER E'\t'" > conn.tsv
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.