For monitoring the performance of the database, it is recommended to utilize the capabilities of the pg_profile package.
pg_profile is an extension that accumulates query statistics from the database and compiles them into snapshots. This functionality is built on the pg_stat_statements statistics and allows identification of 'heavy' queries, most popular objects, and other information.
The extension captures and stores snapshots at intervals, enabling a view of statistics for specific time periods. The report format is in HTML.Current statistics collection settings:
- Snapshot execution frequency (configured in pg_cron): 10 minutes
- Number of objects in snapshots (pg_profile.topn): 20
- Snapshot retention time (pg_profile.retention): 7 days
Procedure for working with the extension.
1. View the list of monitored servers.
select * from profile.servers;
2. View the existing snapshots.
select * from profile.samples order by sample_time desc;
3. How to take a statistics snapshot using SQL:
select profile.take_sample();
4. Generating a report based on the snapshots:
select profile.get_report(<имя сервера>, <стартовый снимок>, <конечный снимок>);
• example:
select profile.get_report(2, 864, 865);
5. Generating a report for a time interval:
select profile.get_report(2, tstzrange(now()-interval '1 day',now()));
• пример:
select profile.get_report(2, tstzrange('2024-01-29 09:00:00','2024-01-29 18:00:00'));
6. Generating a comparative report:
select from profile.get_diffreport( <имя сервера>, <старт1>, <конец1>, <старт2>, <конец2> )
7. Saving the report to an HTML file using SQL:
copy (select profile.get_report(2, 864,865)) to 'report_100,200.html';
