原 PG中记录慢SQL语句的方法
Tags: 原创PGPostgreSQL慢SQLlog_min_duration_statement
简介
在PostgreSQL中,可以通过启用慢查询日志来记录执行时间较长的SQL语句,然后通过外部表来查询告警日志即可。
步骤一
log_min_duration_statement:设置记录SQL执行时间的阈值(以毫秒为单位)。只有执行时间超过此阈值的SQL语句才会记录在日志中。设置为0
将记录所有语句的执行时间,设置为较高的值(如5000)将只记录执行时间超过5秒的语句。
编辑postgresql.conf
并设置如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | alter system set log_destination='csvlog'; alter system set logging_collector = on; alter system set log_directory = 'pg_log'; alter system set log_filename = 'postgresql-%a.log'; alter system set log_truncate_on_rotation = on; alter system set log_statement = 'ddl'; alter system set log_connections = 'off'; alter system set log_disconnections = 'off'; alter system set log_min_duration_statement = 5000; show logging_collector; show log_directory; show log_filename; show log_truncate_on_rotation; show log_statement; show log_connections; show log_disconnections; show log_min_duration_statement; SELECT pg_reload_conf(); sudo systemctl restart postgresql select * from pg_settings where name in ('logging_collector','log_directory','log_filename','log_truncate_on_rotation'); |