HighgoDB查询慢SQL和阻塞SQL

#开启日志记录


logging_collector = on


#设置日志输出格式，格式有stderr（默认）, csvlog , syslog


log_destination = 'csvlog’


#设置日志存放位置，下面设置表示日志存放在$PGDATA下hgdb_log日志中


log_directory = 'hgdb_log’


#设置日志截断


log_truncate_on_rotation = on


#设置日志的名称


log_filename = 'highgodb_%d.log’


#设置跟踪的SQL语句级别，级别包含none（默认，只记录出错信息）, ddl, mod, all


log_statement = all


#记录执行超过以下时间的SQL语句，单位毫秒


log_min_duration_statement = 5000






数据库日志保留数量通过设置参数log_truncate_on_rotation和log_filename进行控制，参数log_truncate_on_rotation设置为on后，通过log_filename控制日志保留时间，常用设置如下


log_filename = 'highgodb-%I.log'    #多保存12小时的日志,每小时一个文件


log_filename = 'highgodb-%H.log'    #多保存24小时的日志,每小时一个文件


log_filename = 'highgodb-%w.log'    #多保存一周的日志,每天一个文件


log_filename = 'highgodb-%d.log'    #多保存一个月的日志,每天一个文件


log_filename = 'highgodb-%j.log'    #多保存一年的日志,每天一个文件

CREATE TABLE highgodb_log


(  log_time timestamp(3) with time zone,


  user_name text,


  database_name text,


  process_id integer,


  connection_from text,


  session_id text,


  session_line_num bigint,


  command_tag text,


  session_start_time timestamp with time zone,


  virtual_transaction_id text,


  transaction_id bigint,


  error_severity text,


  sql_state_code text,


  message text,


  detail text,


  hint text,


  internal_query text,


  internal_query_pos integer,


  context text,


  query text,


  query_pos integer,


  location text,


  application_name text,


  PRIMARY KEY (session_id, session_line_num)


);

（左右滑动查看完整内容）

使用如下语句将日志导入到数据库


COPY highgodb_log FROM '/path/to/highgodb-1.csv' WITH csv;

（左右滑动查看完整内容）

使用SQL语句进行查询，此处给出的示例是按执行时间排序，由于执行时间信息存放在message列中，需要对该列进行截取才能进行排序，根据日志语言，需要将“执行时间”和截取字符量进行修改。


select log_time,database_name,user_name,application_name,substr(message, 7,8),message from hgdblog where message like '

（左右滑动查看完整内容）

2.查询阻塞会话的SQL


语句如下

with    


t_wait as    


(    


  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   


  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,    


  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   


    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted   


),   


t_run as   


(   


  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,   


  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,   


  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name   


    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted   


),   


t_overlap as   


(   


  select r.* from t_wait w join t_run r on   


  (   


    r.locktype is not distinct from w.locktype and   


    r.database is not distinct from w.database and   


    r.relation is not distinct from w.relation and   


    r.page is not distinct from w.page and   


    r.tuple is not distinct from w.tuple and   


    r.virtualxid is not distinct from w.virtualxid and   


    r.transactionid is not distinct from w.transactionid and   


    r.classid is not distinct from w.classid and   


    r.objid is not distinct from w.objid and   


    r.objsubid is not distinct from w.objsubid and   


    r.pid <> w.pid   


  )    


),    


t_unionall as    


(    


  select r.* from t_overlap r    


  union all    


  select w.* from t_wait w    


)    


select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,   


string_agg(   


'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||   


'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||   


'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||    


'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||    


'SQL (Current SQL in Transaction): '||chr(10)||  


case when query is null then 'NULL' else query::text end,    


chr(10)||'--------'||chr(10)    


order by    


  (  case mode    


    when 'INVALID' then    


    when 'AccessShareLock' then 1   


    when 'RowShareLock' then 2   


    when 'RowExclusiveLock' then 3   


    when 'ShareUpdateExclusiveLock' then 4   


    when 'ShareLock' then 5   


    when 'ShareRowExclusiveLock' then 6   


    when 'ExclusiveLock' then 7   


    when 'AccessExclusiveLock' then 8   


    else    


  end  ) desc,   


  (case when granted then  else 1 end)  


) as lock_conflict  


from t_unionall   


group by   


locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;

（左右滑动查看完整内容）

执行结果如下，如有大量锁时，可以使用\x进行行列转换，查看起来更方便。

highgo-# locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;  


   locktype    | datname  | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid |                                                                     lock_conflict                                                                     


---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------


 transactionid     | highgo     |          |      |       |            | 760            |         |       |          | Pid: 24355                                                                                                                                           +


               |          |          |      |       |            |               |         |       |          | Lock_Granted: true , Mode: ExclusiveLock , FastPath: false , VirtualTransaction: 5/527 , Session_State: idle in transaction                          +


               |          |          |      |       |            |               |         |       |          | Username: postgres , Database: postgres , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql                                               +


               |          |          |      |       |            |               |         |       |          | Xact_Start: 2021-01-28 15:15:46.810264+08 , Query_Start: 2021-01-28 15:16:06.108277+08 , Xact_Elapse: 00:00:55.57579 , Query_Elapse: 00:00:36.277777 +


               |          |          |      |       |            |               |         |       |          | SQL (Current SQL in Transaction):                                                                                                                    +


               |          |          |      |       |            |               |         |       |          | delete from host ;                                                                                                                                   +


               |          |          |      |       |            |               |         |       |          | --------                                                                                                                                             +


               |          |          |      |       |            |               |         |       |          | Pid: 24383                                                                                                                                           +


               |          |          |      |       |            |               |         |       |          | Lock_Granted: false , Mode: ShareLock , FastPath: false , VirtualTransaction: 4/14615 , Session_State: active                                        +


               |          |          |      |       |            |               |         |       |          | Username: postgres , Database: postgres , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql                                               +


               |          |          |      |       |            |               |         |       |          | Xact_Start: 2021-01-28 15:16:13.619987+08 , Query_Start: 2021-01-28 15:16:31.022072+08 , Xact_Elapse: 00:00:28.766067 , Query_Elapse: 00:00:11.363982+


               |          |          |      |       |            |               |         |       |          | SQL (Current SQL in Transaction):                                                                                                                    +


               |          |          |      |       |            |               |         |       |          | delete from host ;


(1 row)