如何捕捉postgresql数据库中的慢SQL和高频SQL_postgresql 慢sql

简介

       在数据库开发过程往往由于业务的不断更新，数据库在配置、架构上不断更新生成了许多冗余代码，在没有在优化语句的情况下，可能会在数据库运行中，未知情况下产生执行时间较长的SQL语句。此时就需要我们通过捕捉慢SQL，定向优化语句，缩短工时，维持数据库高可用属性。

        关于高频SQL。在一个WX群中有人问到一个问题，由于在一个很短时间内，一个SQL的高频执行，占用数据库较高资源，引起了数据库性能波动导致故障，但是由于执行时间较短，没有记录日志。所以无法知道是什么语句。定时抓pg_stat_Activty也有一个问题，由于执行时间较短，如果抓取时间设置也很短，同样也会占用较高数据库资源，无异于杀鸡取卵。抓取pg_stat_Activty时间设置过长就无法抓取到高频执行的SQL。以下文章为大家解读如何找出慢SQL和高频SQL。

pg_stat_statements

本文基于 PostgreSQL 15.3 on x86_64-pc-linux-gnu版本为大家解读

postgresql自带了pg_stat_statements拓展，提供了一种跟踪服务器执行的所有 SQL 语句的计划和执行统计信息的方法。

该拓展必须通过在postgresql.conf的配置shared_preload_libraries=‘pg_stat_statements’来载入，因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。

安装pg_stat_statements拓展

 create extension pg_stat_statements;

指定 配置文件中指定shared_preload_libraries参数的值，为插件分配共享缓存区。

 如果创建显示没有此拓展的控制文件，需要进入安装包的contrib目录 进行make && make install 重新构建一下

 在此目录内进行进行make && make install构建操作 

 

 然后回到psql客户端再次创建pg_stat_statements拓展便可。

pg_stat_statements拓展提供了一个视图和两个函数；

pg_stat_statements视图

     在官网对以上视图的注释中涉及到两个参数（pg_stat_statements.track_planning、track_io_timing）的配置，才可显示对应的列值。开启这两个参数，会对数据库性能产生明显的压力。并且该拓展要求与pg_stat_statements.max成比例的额外共享内存。注意只要该模块被载入就会消耗这么多的内存，即便pg_stat_statements.track被设置为none。

pg_stat_statements涉及参数

均需配置在postgresql.conf文件中

拓展关联函数

安装完pg_stat_statements拓展后，还有两个函数也会随着载入pg_stat_statements_reset，pg_stat_statements

pg_stat_statements_reset函数

pg_stat_statements视图中的指标值是一个累计值，可以执行此函数，使得视图的指标值重新开始计数。

pg_stat_statements（boolean）函数

使用select pg_stat_statements（false）;   pg_stat_statements视图query列的值展示为空值。

捕捉慢SQL示例

捕捉数据库中的慢SQL ，一般用在集体调度作业的时候。

方法1

使用插件pg_stat_statments;

create extension pg_stat_statments；--创建插件

配置postgresql.conf参数shared_preload_libraries=‘pg_stat_statements’

编辑捕捉程序

--创建表结构
create table pg_text_maxtime as select *  from pg_stat_statements;
 
--创建函数
CREATE OR REPLACE FUNCTION sp_pg_text_maxtime() 
RETURNS varchar
AS
$$
declare
return_value varchar;
begin
return_value :='执行完成';
insert into pg_text_maxtime
(userid                
,dbid                  
,toplevel              
,queryid               
,query                 
,plans                 
,total_plan_time       
,min_plan_time         
,max_plan_time         
,mean_plan_time        
,stddev_plan_time      
,calls                 
,total_exec_time       
,min_exec_time         
,max_exec_time         
,mean_exec_time        
,stddev_exec_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         
,blk_write_time        
,temp_blk_read_time    
,temp_blk_write_time   
,wal_records           
,wal_fpi               
,wal_bytes             
,jit_functions         
,jit_generation_time   
,jit_inlining_count    
,jit_inlining_time     
,jit_optimization_count
,jit_optimization_time 
,jit_emission_count    
,jit_emission_time)     
select *  from pg_stat_statements where max_exec_time>600000  ----(过滤出10分钟以上SQL)
and not exists (select 1 from pg_text_maxtime a where a.query=query ); --(过滤重复语句，如果执行时长超过10分钟甚至更长时间，记录的执行时间就可能不是最新最长的。可以忽略本条件。)
RETURN return_value;
end;
$$
LANGUAGE plpgsql;

定时捕捉

select sp_pg_text_maxtime();\watch 700  --7分钟执行一次，

新建一个窗口插入测试代码

select pg_stat_statements_reset(); --重置pg_stat_statements视图的指标值,这里刷新了之后，视图会从此刻重新计算。
select pg_sleep(801);

 待睡眠结束后，最近的一次函数执行后可以查看结果表。

select *  from  pg_text_maxtime;

 测试代码已经被记录。

方法2

也可以开启日志收集器，

配置log_min_duration_statement=60000 。如果指定值时没有单位，则以毫秒为单位。将这个参数设置为零将打印所有语句的执行时间。 设置为 -1（默认值）将停止记录语句持续时间;

记录会被记录在log_directory参数配置的路径中，文件名由log_filename参数定义，文件格式由log_destination 定义。捕捉语句类型由log_statement 参数定义在postgresql抓取数据库的DML DDL语句文章对这个参数有做介绍，本文不再累述。

log_directory='log/'
logging_collector=on
log_destination='csvlog'
log_filename='log'
log_min_duration_statement=60000  --记录时长超过一分钟的SQL

 执行测试代码

select pg_sleep(60);

 此时日志文件就已经记录测试代码。

捕捉高频SQL示例

在简介中介绍了关于高频SQL 的问题。

使用pg_stat_statements插件;

配置参数shared_preload_libraries = 'pg_stat_statements'  分配内存空间；

 配置pg_stat_statements.track=all（函数中执行的语句一并抓取）

此处增加使用select pg_stat_statements_reset();函数 对视图的各项指标清零

--创建表结构
create table pg_text_maxtime as select *  from pg_stat_statements where 1=2;
alter table pg_text_maxtime add column ct_calss integer; --增加一列计算SQL总共的执行次数
 
--创建函数
CREATE OR REPLACE FUNCTION sp_pg_text_maxtime() 
RETURNS varchar
AS
$$
declare
return_value varchar;
begin
return_value :='执行完成';
insert into pg_text_maxtime
(userid                
,dbid                  
,toplevel              
,queryid               
,query                 
,plans                 
,total_plan_time       
,min_plan_time         
,max_plan_time         
,mean_plan_time        
,stddev_plan_time      
,calls   
,total_exec_time       
,min_exec_time         
,max_exec_time         
,mean_exec_time        
,stddev_exec_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         
,blk_write_time        
,temp_blk_read_time    
,temp_blk_write_time   
,wal_records           
,wal_fpi               
,wal_bytes             
,jit_functions         
,jit_generation_time   
,jit_inlining_count    
,jit_inlining_time     
,jit_optimization_count
,jit_optimization_time 
,jit_emission_count    
,jit_emission_time
,ct_calss   --增加一列计算SQL总共的执行次数
)     
select a.*,sum(a.calls)over(partition by a.queryid) as ct_calss  from pg_stat_statements a where calls>10 ; ----(过滤出10次以上SQL)
PERFORM pg_stat_statements_reset();--单位时间内统计完后，各项指标清零。重新计数
RETURN return_value;
 
end;
$$
LANGUAGE plpgsql;

这里同一句SQL 不同的人执行虽然queryid不会变，但是执行的用户不同 会导致calls会分开统计。这里增加一个开窗函数，用于统计单位时间内同一句SQL执行的总次数。

开启捕捉监控

select sp_pg_text_maxtime();\watch 10

新建窗口执行测试代码

DO $$
DECLARE
    v_ct INTEGER := 0;
BEGIN
    WHILE v_ct < 50 LOOP
        EXECUTE 'SELECT current_date; SELECT now();';
        v_ct := v_ct + 1;
    END LOOP;
END;
$$;

查看目标表已经捕捉到对应的高频SQL  

此方法只针对，单一SQL的高频执行有效果 ，嵌套在存储过程中的SQL也可以捕捉到。对于不同的SQL高频执行，便无法捕捉。

操作过程中有不了解的，欢迎私信提问。