做编程的 网站有哪些内容,asp php jsp网站开发,wordpress手机页面,天津网站建站公司在维护和优化 PostgreSQL 数据库时#xff0c;采集并监控数据库的各种静态和动态指标非常重要。这些指标包括数据库的配置信息、资源使用情况、性能指标等#xff0c;能够帮助数据库管理员及时发现并解决潜在的问题#xff0c;从而提高数据库的稳定性和性能。本文提供了一系…在维护和优化 PostgreSQL 数据库时采集并监控数据库的各种静态和动态指标非常重要。这些指标包括数据库的配置信息、资源使用情况、性能指标等能够帮助数据库管理员及时发现并解决潜在的问题从而提高数据库的稳定性和性能。本文提供了一系列 SQL 查询用于获取 PostgreSQL 数据库的各项重要指标和配置信息。
1. PostgreSQL 版本信息 查询命令 SELECT version();返回 PostgreSQL 数据库的版本信息。
2. 配置参数
2.1 shared_buffers 查询命令 SHOW shared_buffers;返回 shared_buffers 的配置值。
2.2 max_connections 查询命令 SHOW max_connections;返回 max_connections 的配置值。
3. 数据库大小 查询命令 SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;返回每个数据库的名称及其大小。
4. 数据库年龄 查询命令 SELECT datname, age(datfrozenxid) AS age
FROM pg_database;返回每个数据库的名称及其年龄以事务ID表示。
5. 活动会话 查询命令 SELECT datname, count(*) AS num_connections
FROM pg_stat_activity
GROUP BY datname;返回每个数据库当前的活动会话数量。
6. 会话详细信息 查询命令 SELECT pid, usename, datname, application_name, client_addr, client_port, backend_start, state, query
FROM pg_stat_activity;返回当前所有活动会话的详细信息。
7. 数据库大小和索引大小 查询命令 SELECTdatname,pg_size_pretty(pg_database_size(datname)) AS db_size,pg_size_pretty(pg_indexes_size(datname)) AS index_size
FROMpg_database;返回每个数据库的大小和索引大小。
8. 检查点信息 查询命令 SELECT checkpoint_time, current_setting(checkpoint_completion_target) AS checkpoint_completion_target
FROM pg_stat_bgwriter;返回最近检查点的时间以及 checkpoint_completion_target 的配置值。
9. 真空和分析信息 查询命令 SELECT schemaname, relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC LIMIT 10;返回用户表的最后一次 VACUUM 和 ANALYZE 操作的时间。
10. 索引使用情况 查询命令 SELECT relname AS table_name, indexrelname AS index_name, idx_scan AS index_scans
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname pg_indexes.indexname
WHERE schemaname public;返回用户表的索引使用情况。
11. 表膨胀
查询命令SELECT schemaname, tablename, pg_size_pretty(total_bytes) AS total_size,pg_size_pretty(heap_bytes) AS heap_size,pg_size_pretty(toast_bytes) AS toast_size,pg_size_pretty(index_bytes) AS index_size
FROM (SELECT *, total_bytes - heap_bytes - toast_bytes - index_bytes AS table_bytesFROM (SELECT c.oid, nspname AS schemaname, relname AS tablename,pg_total_relation_size(c.oid) AS total_bytes,pg_relation_size(c.oid) AS heap_bytes,COALESCE(pg_total_relation_size(reltoastrelid), 0) AS toast_bytes,COALESCE(SUM(pg_total_relation_size(i.indexrelid)), 0) AS index_bytesFROM pg_class cLEFT JOIN pg_namespace n ON n.oid c.relnamespaceLEFT JOIN pg_index i ON i.indrelid c.oidWHERE c.relkind rGROUP BY 1, 2, 3, 4, 5, 6) a
) b
ORDER BY total_bytes DESC LIMIT 10;返回每个表的总大小、堆大小、TOAST 大小和索引大小。
12. 数据库年龄
查询命令SELECT datname, age(datfrozenxid) AS age
FROM pg_database;返回每个数据库的名称及其年龄。
13. 活动连接数
查询命令SELECT count(*) FROM pg_stat_activity WHERE state active;返回当前活动的连接数。
好的以下是新增的 10 项 PostgreSQL 数据库的采集项及其对应的 SQL 查询
14. 缓存命中率 查询命令 SELECT sum(blks_hit) / (sum(blks_hit) sum(blks_read)) AS cache_hit_ratio
FROM pg_stat_database;返回缓存命中率接近 1 表示缓存利用率高。
15. 每秒事务数 (TPS) 查询命令 SELECTdate_trunc(second, now()) AS time,xact_commit xact_rollback AS tps
FROMpg_stat_database;返回当前时间每秒事务数TPS。
16. 每秒查询数 (QPS) 查询命令 SELECTdate_trunc(second, now()) AS time,sum(numbackends) AS qps
FROMpg_stat_database;返回当前时间每秒查询数QPS。
17. 死锁数量 查询命令 SELECT count(*) AS deadlocks
FROM pg_stat_database_conflicts
WHERE conflict_type deadlock;返回数据库中的死锁数量。
18. 当前锁定情况 查询命令 SELECT locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted
FROM pg_locks
WHERE NOT granted;返回当前未授予的锁定情况。
19. 缓冲区大小 查询命令 SELECTpg_size_pretty(pg_total_relation_size(relid)) AS buffer_size
FROMpg_statio_user_tables;返回每个用户表的缓冲区大小。
20. 查询计划缓存命中率 查询命令 SELECTround((blks_hit - blks_read)::numeric / blks_hit, 4) AS plan_cache_hit_ratio
FROMpg_stat_database;返回查询计划缓存命中率。
21. 最长查询时间 查询命令 SELECTmax(now() - query_start) AS longest_query_time
FROMpg_stat_activity
WHERE state active;返回当前活动查询的最长查询时间。
22. 最大事务数 查询命令 SELECTmax(now() - xact_start) AS max_transaction_age
FROMpg_stat_activity
WHERE state active;返回当前活动事务的最大事务时间。
23. 索引扫描数 查询命令 SELECTschemaname,relname,indexrelname,idx_scan
FROMpg_stat_user_indexes
ORDER BYidx_scan DESC
LIMIT 10;返回索引扫描次数最多的前 10 个索引。
结论
通过上述 SQL 查询您可以全面地获取 PostgreSQL 数据库的各项静态和动态指标。这些信息有助于数据库的深入监控、维护和优化确保数据库的高效运行和稳定性。
参考资料
PostgreSQL DocumentationPrometheus DocumentationPostgres Exporter
