PostgreSQL 慢查询获取

获取慢查询的方法

        方法一 ：开启慢查询日志

        方法二 ：使用pg_stat_statementes扩展(推荐)

        方法三 : 捕获当前连接中的查询

开启慢查询日志

        postgresql.conf

        log_destination = 'csvlog'           #日志基础设置

        logging_collector = on                #日志基础设置(重启生效)

        log_directory = 'pg_log'              #日志基础设置

        log_filename = 'postgresql-%Y-%m-%d.log'   #日志基础设置

        log_file_mode = 0600                 #日志基础设置

        log_truncate_on_rotation = off   #日志基础设置

        log_rotation_age = 1d                #日志基础设置

        log_rotation_size = 0                 #日志基础设置

        log_statement = none                #需要记录的语句，默认只记录错误日志。none,ddl,mod,all

        log_min_duration_statement = 500   #慢查询最小时长，毫秒。log_statement=all同时设置时失效

        shared_preload_libraries = 'auto_explain'       #只需要编译，不需要安装扩展

        auto_explain.log_min_duration = 1s               #超过时长的慢查询，给出执行计划

        postgres=# select pg_reload_conf();

        针对某个用户或数据说库进行设置

        postgres=# alter database db_name set log_min_duration_statement=5000;

        postgres=# alter user user_name set log_min_duration_statement=1000;

pg_stat_statementes扩展(推荐)

        pg_stat_statements 模块提供了跟踪服务器执行的所有SQL语句的执行统计信息的方法。如果想要开启模块，必须在配置文件中将 pg_stat_statements 添加到 shared_preload_libraries中。因为它需要额外的共享内存，所以必须重启服务添加或删除。当 pg_stat_statements 被加载，会跟踪服务器所有的数据库的统计信息。

        为了安全，只有superuser和 pg_read_all_stats role 用户可以访问 SQL text 和 queryid。其他用户可以访问 statistics。

        根据内部哈希计算具有相同的查询结构，可计划查询（即SELECT，INSERT，UPDATE和DELETE）就会合并到单个pg_stat_statements条目中。 通常，如果两个查询在语义上等效，则除了查询中出现的文字常量的值之外，它们将被视为相同。 但是，实用命令（即所有其他命令）严格地根据其文本查询字符串进行比较。

安装配置及使用

安装

        cd pg_soft/contrib/pg_stat_statements

        make && make install

        postgres=# create extension pg_stat_statements;

重要配置

        shared_preload_libraries='auto_explain,pg_stat_statements'

        log_min_duration_statement = 100     #慢查询最小时长，毫秒

        track_activity_query_size = 10000     #SQL文本的最大长度

        pg_stat_statements.max = 10000     #跟踪模块中最多保留多少条统计信息，通过LRU算法。

        pg_stat_statements.track = all      #all包括函数内的SQL, top不包含函数内的sql), none

        pg_stat_statements.track_utility = true     #是否跟踪非DML语句 (例如DDL，DCL)

        pg_stat_statements.save = true     #表示当pg停止时，把信息存入磁盘文件。

使用

#重置统计信息

select pg_stat_statements_reset() ;

#最慢的TOP10

SELECT * FROM pg_stat_statements order by total_time desc limit 10;

pg_stat_statements 字段代表的含义

名字	参考	描述
userid	pg_authid.oid	执行该语句的用户的OID
dbid	pg_database.oid	执行该语句的数据库的OID
query		有代表性的语句的文本
calls		执行的次数
total_time		该语句花费的总时间，以毫秒计
rows		该语句恢复或影响的行的总数
shared_blks_hit		该语句命中的共享块缓存的总数
shared_blks_read		该语句读取的共享块的总数
shared_blks_dirtied		该语句弄脏的共享块的总数
shared_blks_written		该语句写入的共享块的总数
local_blks_hit		该语句命中的本地块缓存的总数
local_blks_read		该语句读取的本地块的总数
local_blks_dirtied		该语句弄脏的本地块的总数
local_blks_written		该语句写入的本地块的总数
temp_blks_read		该语句读取的临时块的总数
temp_blks_written		该语句写入的临时块的总数
blk_read_time		该语句读取块花费的总时间，以毫秒计 （如果启用了track_io_timing，否则为0）
blk_write_time		该语句写入块花费的总时间，以毫秒计 （如果启用了track_io_timing，否则为0）

捕获当前连接中的查询

select *

from pg_stat_activity

where state<>'idle' and now()-query_start > interval '1 s' order by query_start ;

若有收获，就点个赞吧