postgresql-pg_stat_statements

来源：互联网发布：网络病毒的危害编辑：程序博客网时间：2024/05/21 08:45

修改postgresql.conf文件，并重启postgreSQL

shared_preload_libraries = 'pg_stat_statements'pg_stat_statements.max = 1000                   //跟踪的最大语句数量，缺省是1000条pg_stat_statements.track = all                  //控制那些语句会被追踪，可选top（缺省），all和none

创建pg_stat_statements

create extension pg_stat_statements;     //创建pg_stat_statements extensionCREATE EXTENSION\d pg_stat_statements;                   //查看视图结构          View "public.pg_stat_statements"       Column        |       Type       | Modifiers---------------------+------------------+----------- userid              | oid              | dbid                | oid              | query               | text             | calls               | bigint           | total_time          | double precision | rows                | bigint           | shared_blks_hit     | bigint           | shared_blks_read    | bigint           | shared_blks_dirtied | bigint           | shared_blks_written | bigint           | local_blks_hit      | bigint           | local_blks_read     | bigint           | local_blks_dirtied  | bigint           | local_blks_written  | bigint           | temp_blks_read      | bigint           | temp_blks_written   | bigint           | blk_read_time       | double precision | blk_write_time      | double precision |

查找执行最慢的语句（查最慢10条SQL语句）：

select query,calls,total_time,(total_time/calls) as average, rows,100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) as hit_percent from pg_stat_statements order by average desc limit 10;

为使输出精简，也可执行以下语句（最慢2条语句):

select query,(total_time/calls) as average, 100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) as hit_percent from pg_stat_statements order by average desc limit 2;

阅读全文

0 0