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)

);

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 0   

    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 0   

  end  ) desc,   

  (case when granted then 0 else 1 end)  

) as lock_conflict  

from t_unionall   

group by   

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

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)