Skip to content

Latest commit

 

History

History
71 lines (45 loc) · 2.13 KB

README.md

File metadata and controls

71 lines (45 loc) · 2.13 KB

aurora-statspack

Aurora Statspack to monitor performance on Aurora compatible with PostgreSQL

Created by Santiago Villa, last modified on Jan 03, 2023

This is a package to capture Aurora performance statistics in historical tables inside a new schema: statspack.

Statspack Setup

Run statspack_setup.sql script into the target Aurora PostgreSQL database to create all the Statspack objects.

Statspack Tables and Procedures

Tables:

- statspack.hist_active_sessions_waits
- statspack.hist_indexes_with_nulls
- statspack.hist_pg_settings
- statspack.hist_pg_stat_all_tables
- statspack.hist_pg_stat_database
- statspack.hist_pg_stat_statements
- statspack.hist_pg_users
- statspack.hist_snapshots
- statspack.hist_stat_system_waits
- statspack.statspack_config

Procedures:

-- Take Aurora snapshot from live views and functions
call statspack.statspack_snapshot();

-- Remove specific snapshot from Statspack schema
call statspack.statspack_remove_snapshot(1);

-- Remove snapshots based on retention configuration
call statspack.statspack_cleanup();

Setting up automatic Statspack jobs using pg_cron

Create snapshot job
	SELECT cron.schedule('Statspack Snapshot', '*/10 * * * *', 'call statspack.statspack_snapshot()');

	NOTE: this example will take one snapshot every 10 minutes

Create Statspack purging job
	SELECT cron.schedule('Statspack Cleanup', '0 0 * * *', 'call statspack.statspack_cleanup()');

	NOTE: retention days is set on statspack_config table

Monitoring jobs and logs

-- Check which Statpack jobs are scheduled on pg_cron
SELECT * from cron.job WHERE command like '%statspack%';

-- Check Statpack jobs execution in the log table
SELECT * from cron.job_run_details WHERE command like '%statspack%' order by end_time desc limit 10;

Remove snapshot job

  • SELECT cron.unschedule ('Statspack Snapshot');

Remove Statspack purging job

  • SELECT cron.unschedule ('Statspack Cleanup');

Aurora Statspack Report

  • Connect to the target Aurora DB and execute the statspack report (statspack_report.sql). i.e. postgres=> \i statspack_report.sql