PostgreSQL pg_profile 收集快照

#PostgreSQL #pg_profile #收集快照

1 背景知识

本文介绍如何手动生成快照和自动快照。

2 自动生成快照

su - postgres 
crontab -e

//输入内容：
*/30 * * * *   . /home/postgres/.bash_profile;psql -U postgres -d testdb -c 'SELECT profile.take_sample()' > /dev/null 2>&1

3 手动生成快照

#postgres>
psql -U postgres -d testdb

#testdb>
SELECT * FROM take_sample();

4 显示快照列表

#testdb>
SELECT * FROM show_sample();

5 .v_sample_timings 视图

5.1 快照收集时所花费的时间

通过 [[#5 .v_sample_timings 视图]] 可以很轻松的观察到那一部分的统计信息收集的最慢。

seLECT * 
FROM profile.v_sample_timings 
Where sampling_event='total' 
ORDER BY sample_time DESC;

server_name | sample_id |      sample_time       | sampling_event | time_spent  
-------------+-----------+------------------------+----------------+-------------
 omega       |         7 | 2024-06-27 14:00:02+08 | total          | 00:00:01.99
 local       |       338 | 2024-06-27 14:00:02+08 | total          | 00:00:01.77
 local       |       337 | 2024-06-27 13:30:02+08 | total          | 00:00:01.71
 omega       |         6 | 2024-06-27 13:30:02+08 | total          | 00:00:01.94

5.2 v_sample_timings 视图字段说明

字段	说明
total	生成快照所花费的时间。
connect	建立连接所花费的时间。
get server environment	获取GUC 参数、扩展所花费的时间。
collect database stats	获取 pg_stat_database 视图以获取有关数据库的统计信息所花费的时间。
calculate database stats	计算自上一个快照以来数据库的diff统计信息所花费的时间。
collect tablespace stats	获取pg_tablespace视图以获取有关表空间的统计信息所花费的时间。
collect statement stats	使用 PostgreSQL pg_stat_statements 和 PostgreSQL pg_stat_kcache 扩展名收集语句的统计信息花费的时间。
query pg_stat_bgwriter	获取 pg_stat_bgwriter 视图收集集群统计信息所花费的时间。
query pg_stat_wal	获取 pg_stat_wal 视图收集集群 WAL 统计信息所花费的时间。
query pg_stat_archiver	获取 pg_stat_archiver 视图收集集群统计信息所花费的时间。
collect object stats	获取有关数据库对象的统计信息所花的时间。
maintain repository	执行支持例程所花的时间。
calculate tablespace stats	计算表空间的变化统计量所花的时间。
calculate object stats	计算数据库对象的变化的统计信息。
calculate tables stats	计算所有数据库表的差分统计信息。
calculate indexes stats	计算所有数据库索引的差分统计信息。
calculate functions stats	计算所有数据库函数的差分统计量。
calculate cluster stats	计算聚类差分统计量。
calculate archiver stats	计算存档差分统计量。
delete obsolete samples	删除过时的基线和示例。

5.3 collect object stats 字段所收集的信息

db：dbname collect tables stats ： 收集有关 dbname 数据库的表的统计信息。
db：dbname collect limited table sizes : 禁用大小收集时收集抽 vacuum 或按 sequece scan 的表大小。
db：dbname collect indexes stats : 收集有关 dbname 数据库索引的统计信息。
db：dbname collect limited index sizes : 禁用大小收集时收集已抽vacuum表的索引大小。
db：dbname collect functions stats : 收集有关 dbname 数据库函数的统计信息。

6 快照的保留策略

pg_profile.max_sample_age 参数能够指定保留快照的天数，超过 7 天的快照将会被删除。如果要手动删除快照，请使用 delete_samples 函数。

6.1 查看快照列表

SELECT profile.show_samples('omega',7);

6.2 删除快照列表

SELECT profile.delete_samples('omega',1,10);

6.3 查看快照列表

SELECT profile.show_samples('omega');

SELECT profile.delete_samples('omega',tstzrange'[2024-06-27 14:30,2024-06-28 15:30]');

delete_samples 
----------------
             48
(1 row)