ΠŸΠΎΠΏΡ‹Ρ‚ΠΊΠ° ΡΠΎΠ·Π΄Π°Ρ‚ΡŒ Π°Π½Π°Π»ΠΎΠ³ ASH для PostgreSQL

ΠŸΠΎΡΡ‚Π°Π½ΠΎΠ²ΠΊΠ° Π·Π°Π΄Π°Ρ‡ΠΈ

Для ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·Π°Ρ†ΠΈΠΈ запросов PostgreSQL, ΠΎΡ‡Π΅Π½ΡŒ трСбуСтся Π²ΠΎΠ·ΠΌΠΎΠΆΠ½ΠΎΡΡ‚ΡŒ Π°Π½Π°Π»ΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ ΠΈΡΡ‚ΠΎΡ€ΠΈΡŽ активности, Π² частности – оТидания, Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΊΠΈ, статистика Ρ‚Π°Π±Π»ΠΈΡ†.

Π˜ΠΌΠ΅ΡŽΡ‰ΠΈΠ΅ΡΡ возмоТности

Π˜Π½ΡΡ‚Ρ€ΡƒΠΌΠ΅Π½Ρ‚ Π°Π½Π°Π»ΠΈΠ·Π° историчСской Π½Π°Π³Ρ€ΡƒΠ·ΠΊΠΈ ΠΈΠ»ΠΈ Β«AWR для PostgresΒ»: ΠΎΡ‡Π΅Π½ΡŒ интСрСсноС Ρ€Π΅ΡˆΠ΅Π½ΠΈΠ΅, Π½ΠΎ, Π½Π΅Ρ‚ истории pg_stat_activity ΠΈ pg_locks.

Π Π°ΡΡˆΠΈΡ€Π΅Π½ΠΈΠ΅ pgsentinel :
«Π’ся накоплСнная информация хранится Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π² ΠΎΠΏΠ΅Ρ€Π°Ρ‚ΠΈΠ²Π½ΠΎΠΉ памяти, Π° потрСбляСмый ΠΎΠ±ΡŠΡ‘ΠΌ памяти рСгулируСтся количСством послСдних Ρ…Ρ€Π°Π½ΠΈΠΌΡ‹Ρ… записСй.

ДобавляСтся ΠΏΠΎΠ»Π΅ queryid β€” Ρ‚ΠΎΡ‚ самый queryid ΠΈΠ· Ρ€Π°ΡΡˆΠΈΡ€Π΅Π½ΠΈΡ pg_stat_statements (трСбуСтся ΠΏΡ€Π΅Π΄Π²Π°Ρ€ΠΈΡ‚Π΅Π»ΡŒΠ½Π°Ρ установка).«

Π­Ρ‚ΠΎ ΠΊΠΎΠ½Π΅Ρ‡Π½ΠΎ сильно Π±Ρ‹ ΠΏΠΎΠΌΠΎΠ³Π»ΠΎ, Π½ΠΎ самая Π½Π΅ΠΏΡ€ΠΈΡΡ‚Π½ΠΎΡΡ‚ΡŒ ΠΈΠΌΠ΅Π½Π½ΠΎ ΠΏΠ΅Ρ€Π²Ρ‹ΠΉ ΠΏΡƒΠ½ΠΊΡ‚ β€œΠ’ΡΡ накоплСнная информация хранится Ρ‚ΠΎΠ»ΡŒΠΊΠΎ Π² ΠΎΠΏΠ΅Ρ€Π°Ρ‚ΠΈΠ²Π½ΠΎΠΉ памяти ”, Ρ‚.Π΅. ΠΈΠΌΠ΅Π΅ΠΌ мСсто ΠΈΠΌΠΏΠ°ΠΊΡ‚ Π½Π° Ρ†Π΅Π»Π΅Π²ΡƒΡŽ Π±Π°Π·Ρƒ. К Ρ‚ΠΎΠΌΡƒ, ΠΆΠ΅ Π½Π΅Ρ‚ истории Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΎΠΊ ΠΈ статистики Ρ‚Π°Π±Π»ΠΈΡ†. Π’.Π΅. Ρ€Π΅ΡˆΠ΅Π½ΠΈΠ΅ Π²ΠΎΠΎΠ±Ρ‰Π΅ говоря Π½Π΅ΠΏΠΎΠ»Π½ΠΎΠ΅: β€œΠ“ΠΎΡ‚ΠΎΠ²ΠΎΠ³ΠΎ ΠΏΠ°ΠΊΠ΅Ρ‚Π° для установки ΠΏΠΎΠΊΠ° Π½Π΅Ρ‚. ΠŸΡ€Π΅Π΄Π»Π°Π³Π°Π΅Ρ‚ΡΡ ΡΠΊΠ°Ρ‡Π°Ρ‚ΡŒ исходники ΠΈ ΡΠΎΠ±Ρ€Π°Ρ‚ΡŒ Π±ΠΈΠ±Π»ΠΈΠΎΡ‚Π΅ΠΊΡƒ ΡΠ°ΠΌΠΎΡΡ‚ΠΎΡΡ‚Π΅Π»ΡŒΠ½ΠΎ. ΠŸΡ€Π΅Π΄Π²Π°Ρ€ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎ трСбуСтся ΡƒΡΡ‚Π°Π½ΠΎΠ²ΠΈΡ‚ΡŒ Β«develΒ»-ΠΏΠ°ΠΊΠ΅Ρ‚ для своСго сСрвСра ΠΈ Π² ΠΏΠ΅Ρ€Π΅ΠΌΠ΅Π½Π½ΡƒΡŽ PATH ΠΏΡ€ΠΎΠΏΠΈΡΠ°Ρ‚ΡŒ ΠΏΡƒΡ‚ΡŒ Π΄ΠΎ pg_config.”.

Π’ ΠΎΠ±Ρ‰Π΅ΠΌ – Π²ΠΎΠ·Π½ΠΈ ΠΌΠ½ΠΎΠ³ΠΎ, Π° Π² случаС ΡΠ΅Ρ€ΡŒΠ΅Π·Π½Ρ‹Ρ… ΠΏΡ€ΠΎΠ΄Π°ΠΊΡˆΠ½ Π±Π°Π·, ΠΌΠΎΠΆΠ΅Ρ‚ Π±Ρ‹Ρ‚ΡŒ, ΠΈ Π½Π΅ Π±ΡƒΠ΄Π΅Ρ‚ возмоТности Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ Π΄Π΅Π»Π°Ρ‚ΡŒ с сСрвСром. НуТно ΠΎΠΏΡΡ‚ΡŒ, ΠΏΡ€ΠΈΠ΄ΡƒΠΌΡ‹Π²Π°Ρ‚ΡŒ, Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ своС.

ΠŸΡ€Π΅Π΄ΡƒΠΏΡ€Π΅ΠΆΠ΄Π΅Π½ΠΈΠ΅.

Π’ силу довольно большого объСма ΠΈ Π² связи с Π½Π΅Π·Π°Π²Π΅Ρ€ΡˆΠ΅Π½ΠΈΠ΅ΠΌ ΠΏΠ΅Ρ€ΠΈΠΎΠ΄Π° тСстирования, ΡΡ‚Π°Ρ‚ΡŒΡ носит Π² основном ΠΎΠ·Π½Π°ΠΊΠΎΠΌΠΈΡ‚Π΅Π»ΡŒΠ½Ρ‹ΠΉ Ρ…Π°Ρ€Π°ΠΊΡ‚Π΅Ρ€, скорСС ΠΊΠ°ΠΊ Π½Π°Π±ΠΎΡ€ тСзисов ΠΈ ΠΏΡ€ΠΎΠΌΠ΅ΠΆΡƒΡ‚ΠΎΡ‡Π½Ρ‹Ρ… Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚ΠΎΠ².
Π‘ΠΎΠ»Π΅Π΅ ΠΏΠΎΠ΄Ρ€ΠΎΠ±Π½Ρ‹ΠΉ ΠΌΠ°Ρ‚Π΅Ρ€ΠΈΠ°Π», Π±ΡƒΠ΄Π΅Ρ‚ ΠΏΠΎΠ΄Π³ΠΎΡ‚ΠΎΠ²Π»Π΅Π½ ΠΏΠΎΠ·ΠΆΠ΅, ΠΏΠΎ частям

ЭскизныС трСбования ΠΊ Ρ€Π΅ΡˆΠ΅Π½ΠΈΡŽ

НСобходимо Ρ€Π°Π·Ρ€Π°Π±ΠΎΡ‚Π°Ρ‚ΡŒ инструмСнт ΠΏΠΎΠ·Π²ΠΎΠ»ΡΡŽΡ‰ΠΈΠΉ Ρ…Ρ€Π°Π½ΠΈΡ‚ΡŒ:

Π˜ΡΡ‚ΠΎΡ€ΠΈΡŽ прСдставлСния pg_stat_activity
Π˜ΡΡ‚ΠΎΡ€ΠΈΡŽ Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΎΠΊ сСссий, ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΡ прСдставлСниС pg_locks

Π’Ρ€Π΅Π±ΠΎΠ²Π°Π½ΠΈΠ΅ ΠΊ Ρ€Π΅ΡˆΠ΅Π½ΠΈΡŽβ€“ΠΌΠΈΠ½ΠΈΠΌΠΈΠ·ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ влияниС Π½Π° Ρ†Π΅Π»Π΅Π²ΡƒΡŽ Π±Π°Π·Ρƒ Π΄Π°Π½Π½Ρ‹Ρ….

ΠžΠ±Ρ‰Π°Ρ идСя–агСнт сбора Π΄Π°Π½Π½Ρ‹Ρ… запускаСтся Π½Π΅ Π² Ρ†Π΅Π»Π΅Π²ΠΎΠΉ Π±Π°Π·Π΅, Π° Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ… ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³Π° ΠΊΠ°ΠΊ сСрвис systemd. Π”Π°, Π²ΠΎΠ·ΠΌΠΎΠΆΠ½Ρ‹ Π½Π΅ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ ΠΏΠΎΡ‚Π΅Ρ€ΠΈ Π΄Π°Π½Π½Ρ‹Ρ…, Π½ΠΎ это Π½Π΅ ΠΊΡ€ΠΈΡ‚ΠΈΡ‡Π½ΠΎ, для отчСтности, Π·Π°Ρ‚ΠΎ Π½Π΅Ρ‚ ΠΈΠΌΠΏΠ°ΠΊΡ‚Π° Π½Π° Ρ†Π΅Π»Π΅Π²ΡƒΡŽ Π±Π°Π·Ρƒ ΠΏΠΎ памяти ΠΈ мСсту Π½Π° дискС. А Π² случаС использования ΠΏΡƒΠ»Π° соСдинСний, ΠΈΠΌΠΏΠ°ΠΊΡ‚ ΠΏΠΎ ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚Π΅Π»ΡŒΡΠΊΠΈΠΌ процСссам ΠΌΠΈΠ½ΠΈΠΌΠ°Π»Π΅Π½.

Π­Ρ‚Π°ΠΏΡ‹ Ρ€Π΅Π°Π»ΠΈΠ·Π°Ρ†ΠΈΠΈ

1.БСрвисныС Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹

Для хранСния Ρ‚Π°Π±Π»ΠΈΡ† ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ ΠΎΡ‚Π΄Π΅Π»ΡŒΠ½Π°Ρ схСма, Ρ‡Ρ‚ΠΎ Π±Ρ‹ Π½Π΅ Π·Π°Ρ‚Ρ€ΡƒΠ΄Π½ΡΡ‚ΡŒ Π°Π½Π°Π»ΠΈΠ· основных ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Π½Π½Ρ‹Ρ… Ρ‚Π°Π±Π»ΠΈΡ†.

DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;

Π’Π°ΠΆΠ½ΠΎ: схСма создаСтся Π½Π΅ Π² Ρ†Π΅Π»Π΅Π²ΠΎΠΉ Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ…, Π° Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ… ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³Π°.

Π˜ΡΡ‚ΠΎΡ€ΠΈΡ прСдставлСния pg_stat_activity

Для хранСния Ρ‚Π΅ΠΊΡƒΡ‰ΠΈΡ… снимков прСдставлСния pg_stat_activity ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ Ρ‚Π°Π±Π»ΠΈΡ†Π°

activity_hist.history_pg_stat_activity :

--ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY
DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity;
CREATE TABLE activity_hist.history_pg_stat_activity
(
  timepoint timestamp without time zone ,
  datid             oid  , 
  datname           name ,
  pid               integer,
  usesysid          oid    ,
  usename           name   ,
  application_name  text   ,
  client_addr       inet   ,
  client_hostname   text   ,
  client_port       integer,
  backend_start     timestamp with time zone ,
  xact_start        timestamp with time zone ,
  query_start       timestamp with time zone ,
  state_change      timestamp with time zone ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,  
  queryid           bigint
);

Для ускорСния вставки – Π½ΠΈΠΊΠ°ΠΊΠΈΡ… индСксов ΠΈ ΠΎΠ³Ρ€Π°Π½ΠΈΡ‡Π΅Π½ΠΈΠΉ.

Для хранСния нСпосрСдствСнно истории ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ сСкционированная Ρ‚Π°Π±Π»ΠΈΡ†Π°:

activity_hist.archive_pg_stat_activity :

DROP TABLE IF EXISTS activity_hist.archive_pg_stat_activity;
CREATE TABLE activity_hist.archive_pg_stat_activity
(
  timepoint timestamp without time zone ,
  datid             oid  , 
  datname           name ,
  pid               integer,
  usesysid          oid    ,
  usename           name   ,
  application_name  text   ,
  client_addr       inet   ,
  client_hostname   text   ,
  client_port       integer,
  backend_start     timestamp with time zone ,
  xact_start        timestamp with time zone ,
  query_start       timestamp with time zone ,
  state_change      timestamp with time zone ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,
  queryid           bigint
)
PARTITION BY RANGE (timepoint);

ΠŸΠΎΡΠΊΠΎΠ»ΡŒΠΊΡƒ Π² Π΄Π°Π½Π½ΠΎΠΌ случаС Π½Π΅Ρ‚ Ρ‚Ρ€Π΅Π±ΠΎΠ²Π°Π½ΠΈΠΉ ΠΏΠΎ скорости вставки, созданы Π½Π΅ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ индСксы, для ускорСния создания ΠΎΡ‚Ρ‡Π΅Ρ‚ΠΎΠ².

Π˜ΡΡ‚ΠΎΡ€ΠΈΡ Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΎΠΊ сСссий

Для хранСния Ρ‚Π΅ΠΊΡƒΡ‰ΠΈΡ… снимков Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΎΠΊ сСссий ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ Ρ‚Π°Π±Π»ΠΈΡ†Π°:

activity_hist.history_locking :

--ACTIVITY_HIST.HISTORY_LOCKING
DROP TABLE IF EXISTS activity_hist.history_locking;
CREATE TABLE activity_hist.history_locking
(
	timepoint timestamp without time zone ,
	locktype text ,
	relation oid ,
	mode text ,
	tid xid ,
	vtid text ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean
);

Π’Π°ΠΊΠΆΠ΅, для ускорСния вставки – Π½ΠΈΠΊΠ°ΠΊΠΈΡ… индСксов ΠΈ ΠΎΠ³Ρ€Π°Π½ΠΈΡ‡Π΅Π½ΠΈΠΉ.

Для хранСния нСпосрСдствСнно истории ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ сСкционированная Ρ‚Π°Π±Π»ΠΈΡ†Π°:

activity_hist.archive_locking:

DROP TABLE IF EXISTS activity_hist.archive_locking;
CREATE TABLE activity_hist.archive_locking
(
	timepoint timestamp without time zone ,
	locktype text ,
	relation oid ,
	mode text ,
	tid xid ,
	vtid text ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean	
)
PARTITION BY RANGE (timepoint);

ΠŸΠΎΡΠΊΠΎΠ»ΡŒΠΊΡƒ Π² Π΄Π°Π½Π½ΠΎΠΌ случаС Π½Π΅Ρ‚ Ρ‚Ρ€Π΅Π±ΠΎΠ²Π°Π½ΠΈΠΉ ΠΏΠΎ скорости вставки, созданы Π½Π΅ΠΊΠΎΡ‚ΠΎΡ€Ρ‹Π΅ индСксы, для ускорСния создания ΠΎΡ‚Ρ‡Π΅Ρ‚ΠΎΠ².

2.Π—Π°ΠΏΠΎΠ»Π½Π΅Π½ΠΈΠ΅ Ρ‚Π΅ΠΊΡƒΡ‰Π΅ΠΉ истории

Для нСпосрСдствСнного сбора снимков прСдставлСния ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ bash скрипт, Π·Π°ΠΏΡƒΡΠΊΠ°ΡŽΡ‰ΠΈΠΉ plpgsql Ρ„ΡƒΠ½ΠΊΡ†ΠΈΡŽ.

get_current_activity.sh

#!/bin/bash
#########################################################
#get_current_activity.sh

ERROR_FILE='/home/demon/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S)
host=$1
s_name=$2
s_pass=$3

psql  -A -t -q -v ON_ERROR_STOP=1 -c "SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )" >/dev/null 2>$ERROR_FILE

line_count=`cat $ERROR_FILE | wc -l`
if [[ $line_count != '0' ]];
then
    rm -f /home/demon/*.err >/dev/null 2>/dev/null
	cp $ERROR_FILE $ERROR_FILE'.err' >/dev/null 2>/dev/null  
fi
rm $ERROR_FILE >/dev/null 2>/dev/null
exit 0

plpgsql функция ΠΏΠΎ dblink обращаСтся ΠΊ прСдставлСниям Π² Ρ†Π΅Π»Π΅Π²ΠΎΠΉ Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ… ΠΈ вставляСт строки Π² сСрвисныС Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ Π² Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ… ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³Π°.

get_current_activity.sql

CREATE OR REPLACE FUNCTION activity_hist.get_current_activity( current_host text , current_s_name text , current_s_pass text ) RETURNS BOOLEAN AS $$
DECLARE 
  database_rec record;
  dblink_str text ;
BEGIN   

	EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||current_host||' port=5432 dbname=postgres'||
	                                         ' user='||current_s_name||' password='||current_s_pass|| ' '')';



--------------------------------------------------------------------
--GET pg_stat_activity stats
	INSERT INTO activity_hist.history_pg_stat_activity
	(
		SELECT * FROM dblink('LINK1',
			'SELECT 
			now() , 
			datid             , 
			datname           ,
			pid               ,
			usesysid              ,
			usename              ,
			application_name     ,
			client_addr          ,
			client_hostname      ,
			client_port       ,
			backend_start         ,
			xact_start            ,
			query_start           ,
			state_change          ,
			wait_event_type    ,                     
			wait_event         ,                   
			state              ,                  
			backend_xid         ,                 
			backend_xmin        ,                
			query              ,               
			backend_type   			
		FROM pg_stat_activity
		') 
		AS t (
		    timepoint 		  timestamp without time zone ,			
			datid             oid  , 
			datname           name ,
			pid               integer,
			usesysid          oid    ,
			usename           name   ,
			application_name  text   ,
			client_addr       inet   ,
			client_hostname   text   ,
			client_port       integer,
			backend_start     timestamp with time zone ,
			xact_start        timestamp with time zone ,
			query_start       timestamp with time zone ,
			state_change      timestamp with time zone ,
			wait_event_type   text ,                     
			wait_event        text ,                   
			state             text ,                  
			backend_xid       xid  ,                 
			backend_xmin      xid  ,                
			query             text ,               
			backend_type      text 			
		)
	);

---------------------------------------	
--ACTIVITY_HIST.HISTORY_LOCKING	
	INSERT INTO activity_hist.history_locking
	(
		SELECT * FROM dblink('LINK1',
			'SELECT 
			now() , 
			lock.locktype,
			lock.relation,
			lock.mode,
			lock.transactionid as tid,
			lock.virtualtransaction as vtid,
			lock.pid,
			pg_blocking_pids(lock.pid), 
			lock.granted
			FROM 	pg_catalog.pg_locks lock LEFT JOIN pg_catalog.pg_database db ON db.oid = lock.database
			WHERE NOT lock.pid = pg_backend_pid()	
		') 
		AS t (
			timepoint timestamp without time zone ,
			locktype text ,
			relation oid , 
			mode text ,
			tid xid ,
			vtid text ,
			pid integer ,
			blocking_pids integer[] ,
			granted boolean
		)
	);
	PERFORM dblink_disconnect('LINK1');
	
	RETURN TRUE ;
END
$$ LANGUAGE plpgsql;

Для сбора снимков прСдставлСния ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ systemd service, ΠΈ Π΄Π²Π° скрипта:

pg_current_activity.service

# /etc/systemd/system/pg_current_activity.service
[Unit]
Description=Collect history of pg_stat_activity , pg_locks 
Wants=pg_current_activity.timer

[Service]
Type=forking
StartLimitIntervalSec=0
ExecStart=/home/postgres/pgutils/demon/get_current_activity.sh 10.124.70.40 postgres postgres

[Install]
WantedBy=multi-user.target

pg_current_activity.timer

# /etc/systemd/system/pg_current_activity.timer
[Unit]
Description=Run pg_current_activity.sh every 1 second
Requires=pg_current_activity.service

[Timer]
Unit=pg_current_activity.service
OnCalendar=*:*:0/1
AccuracySec=1

[Install]
WantedBy=timers.target

НазначаСм ΠΏΡ€Π°Π²Π° скриптам:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

ЗапускаСм сСрвис:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

Π’Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ, история прСдставлСний собираСтся Π² Π²ΠΈΠ΄Π΅ СТСсСкундных снимков. ΠšΠΎΠ½Π΅Ρ‡Π½ΠΎ, Ссли всС ΠΎΡΡ‚Π°Π²ΠΈΡ‚ΡŒ ΠΊΠ°ΠΊ Π΅ΡΡ‚ΡŒ, Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ ΠΎΡ‡Π΅Π½ΡŒ быстро увСличатся Π² Ρ€Π°Π·ΠΌΠ΅Ρ€Π°Ρ… ΠΈ Π±ΠΎΠ»Π΅Π΅ ΠΌΠ΅Π½Π΅Π΅ продуктивная Ρ€Π°Π±ΠΎΡ‚Π° станСт Π½Π΅Π²ΠΎΠ·ΠΌΠΎΠΆΠ½Π°.

НуТно ΠΎΡ€Π³Π°Π½ΠΈΠ·ΠΎΠ²Π°Ρ‚ΡŒ Π°Ρ€Ρ…ΠΈΠ²ΠΈΡ€ΠΎΠ²Π°Π½ΠΈΠ΅ Π΄Π°Π½Π½Ρ‹Ρ….

3.АрхивированиС истории

Для архивация ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΡƒΠ΅Ρ‚ΡΡ сСкционированныС Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ archive*.

НовыС сСкции ΡΠΎΠ·Π΄Π°ΡŽΡ‚ΡΡ ΠΊΠ°ΠΆΠ΄Ρ‹ΠΉ час, ΠΏΡ€ΠΈ этом старыС Π΄Π°Π½Π½Ρ‹Π΅ ΠΈΠ· Ρ‚Π°Π±Π»ΠΈΡ† history* ΡƒΠ΄Π°Π»ΡΡŽΡ‚ΡΡ, Ρ‚Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ, Ρ€Π°Π·ΠΌΠ΅Ρ€ Ρ‚Π°Π±Π»ΠΈΡ† history* сильно Π½Π΅ мСняСтся ΠΈ ΡΠΊΠΎΡ€ΠΎΡΡ‚ΡŒ вставки Π½Π΅ дСградируСтся со Π²Ρ€Π΅ΠΌΠ΅Π½Π΅ΠΌ.

Π‘ΠΎΠ·Π΄Π°Π½ΠΈΠ΅ Π½ΠΎΠ²Ρ‹Ρ… сСкций выполняСтся plpgsql Ρ„ΡƒΠ½ΠΊΡ†ΠΈΠ΅ΠΉ activity_hist.archive_current_activity. Алгоритм Ρ€Π°Π±ΠΎΡ‚Ρ‹ ΠΎΡ‡Π΅Π½ΡŒ прост( Π½Π° ΠΏΡ€ΠΈΠΌΠ΅Ρ€Π΅ сСкции для Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ archive_pg_stat_activity).

Π‘ΠΎΠ·Π΄Π°Ρ‘ΠΌ ΠΈ заполняСм Π½ΠΎΠ²ΡƒΡŽ ΡΠ΅ΠΊΡ†ΠΈΡŽ

EXECUTE format(
'CREATE TABLE ' || partition_name || 
' PARTITION OF activity_hist.archive_pg_stat_activity FOR VALUES FROM ( %L ) TO ( %L ) ' , 
to_char(date_trunc('year', partition_min_range ),'YYYY')||'-'||
to_char(date_trunc('month', partition_min_range ),'MM')||'-'||
to_char(date_trunc('day', partition_min_range ),'DD')||' '||
to_char(date_trunc('hour', partition_min_range ),'HH24')||':00', 
to_char(date_trunc('year', partition_max_range ),'YYYY')||'-'||
to_char(date_trunc('month', partition_max_range ),'MM')||'-'||
to_char(date_trunc('day', partition_max_range ),'DD')||' '||
to_char(date_trunc('hour', partition_max_range ),'HH24')||':00'
);

INSERT INTO activity_hist.archive_pg_stat_activity
(
	SELECT 	* 
	FROM 	activity_hist.history_pg_stat_activity
	WHERE 	timepoint BETWEEN partition_min_range AND partition_max_range 		
);

Π‘ΠΎΠ·Π΄Π°Π΅ΠΌ индСксы

EXECUTE format	(
'CREATE INDEX '||index_name||
' ON '||partition_name||' ( wait_event_type , backend_type , timepoint )' 
);

EXECUTE format	('CREATE INDEX '||index_name||
' ON '||partition_name||' ( wait_event_type , backend_type , timepoint , queryid )' 
);

УдаляСм старыС Π΄Π°Π½Π½Ρ‹Π΅ ΠΈΠ· Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

РазумССтся пСриодичСски, старыС сСкции, Π·Π° Π½Π΅Π½Π°Π΄ΠΎΠ±Π½ΠΎΡΡ‚ΡŒΡŽ ΡƒΠ΄Π°Π»ΡΡŽΡ‚ΡΡ.

Π‘Π°Π·ΠΎΠ²Ρ‹Π΅ ΠΎΡ‚Ρ‡Π΅Ρ‚Ρ‹

БобствСнно для Ρ‡Π΅Π³ΠΎ всС это дСлаСтся. Для получСния ΠΎΡ‚Ρ‡Π΅Ρ‚ΠΎΠ² ΠΎΡ‡Π΅Π½ΡŒ ΠΎΡ‚Π΄Π°Π»Π΅Π½Π½ΠΎ, ΠΏΡ€ΠΈΠ±Π»ΠΈΠΆΠ΅Π½Π½ΠΎ Π½Π°ΠΏΠΎΠΌΠΈΠ½Π°ΡŽΡ‰ΠΈΡ… AWR Oracle.

Π’Π°ΠΆΠ½ΠΎ Π΄ΠΎΠ±Π°Π²ΠΈΡ‚ΡŒ, для получСния ΠΎΡ‚Ρ‡Π΅Ρ‚ΠΎΠ² Π½Π΅ΠΎΠ±Ρ…ΠΎΠ΄ΠΈΠΌΠΎ ΠΏΠΎΡΡ‚Ρ€ΠΎΠΈΡ‚ΡŒ связь ΠΌΠ΅ΠΆΠ΄Ρƒ прСдставлСниями pg_stat_activity ΠΈ pg_stat_statements. Π’Π°Π±Π»ΠΈΡ†Ρ‹ ΡΠ²ΡΠ·Ρ‹Π²Π°ΡŽΡ‚ΡΡ ΠΏΡƒΡ‚Π΅ΠΌ добавлСния Π² Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ ‘history_pg_stat_activity’, ‘archive_pg_stat_activity’ столбца ‘queryid’. Бпособ добавлСния значСния столбца, Π²Ρ‹Ρ…ΠΎΠ΄ΠΈΡ‚ Π·Π° Ρ€Π°ΠΌΠΊΠΈ Π΄Π°Π½Π½ΠΎΠΉ ΡΡ‚Π°Ρ‚ΡŒΠΈ ΠΈ описан здСсь β€” pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TOTAL CPU TIME FOR QUERIES

Запрос :

WITH hist AS
(
SELECT 
	aa.query ,aa.queryid ,			
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND  pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND backend_type = 'client backend' AND datname != 'postgres' AND	( aa.wait_event_type IS NULL  ) ANDaa.state = 'active'
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 	backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NULL  )AND ha.state = 'active'
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

ΠŸΡ€ΠΈΠΌΠ΅Ρ€:

-------------------------------------------------------------------
| TOTAL CPU TIME FOR QUERIES : 07:47:36
+----+----------------------------------------+--------------------
|   #|                                 queryid|            duration
+----+----------------------------------------+--------------------
|   1|                      389015618226997618|            04:28:58
|   2|                                        |            01:07:29
|   3|                     1237430309438971376|            00:59:38
|   4|                     4710212362688288619|            00:50:48
|   5|                       28942442626229688|            00:15:50
|   6|                     9150846928388977274|            00:04:46
|   7|                    -6572922443698419129|            00:00:06
|   8|                                        |            00:00:01
+----+----------------------------------------+--------------------

TOTAL WAITINGS TIME FOR QUERIES

Запрос :

WITH hist AS
(
SELECT 
	aa.query ,aa.queryid ,			
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	( aa.wait_event_type IS NOT NULL  ) 
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND 
	backend_type = 'client backend' AND datname != 'postgres' AND				
	( ha.wait_event_type IS NOT NULL  )
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC 

ΠŸΡ€ΠΈΠΌΠ΅Ρ€ :

-------------------------------------------------------------------
| TOTAL WAITINGS TIME FOR QUERIES : 21:55:04
+----+----------------------------------------+--------------------
|   #|                                 queryid|            duration
+----+----------------------------------------+--------------------
|   1|                      389015618226997618|            16:19:05
|   2|                                        |            03:47:04
|   3|                     8085340880788646241|            00:40:20
|   4|                     4710212362688288619|            00:13:35
|   5|                     9150846928388977274|            00:12:25
|   6|                       28942442626229688|            00:11:32
|   7|                     1237430309438971376|            00:09:45
|   8|                     2649515222348904837|            00:09:37
|   9|                                        |            00:03:45
|  10|                     3167065002719415275|            00:02:20
|  11|                     5731212217001535134|            00:02:13
|  12|                     8304755792398128062|            00:01:31
|  13|                     2649515222348904837|            00:00:59
|  14|                     2649515222348904837|            00:00:22
|  15|                                        |            00:00:12
|  16|                     3422818749220588372|            00:00:08
|  17|                    -5730801771815999400|            00:00:03
|  18|                    -1473395109729441239|            00:00:02
|  19|                     2404820632950544954|            00:00:02
|  20|                    -6572922443698419129|            00:00:02
|  21|                     2369289265278398647|            00:00:01
|  22|                      180077086776069052|            00:00:01
+----+----------------------------------------+--------------------

WAITINGS FOR QUERIES

Запросы:

WITH hist AS
(
SELECT 
	aa.wait_event_type , aa.wait_event 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	aa.wait_event IS NOT NULL 
GROUP BY aa.wait_event_type , aa.wait_event
UNION 
SELECT 
	ha.wait_event_type , ha.wait_event 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	ha.wait_event IS NOT NULL 
GROUP BY ha.wait_event_type , ha.wait_event		
)
SELECT 	wait_event_type , wait_event 
FROM hist
GROUP BY wait_event_type , wait_event
ORDER BY 1 ASC,2 ASC

----------------------------------------------------------------------

WITH hist AS
(
SELECT 
	aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid ,			
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND
	( aa.wait_event_type = waitings_stat_rec.wait_event_type AND aa.wait_event = waitings_stat_rec.wait_event )
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND 
	backend_type = 'client backend' AND datname != 'postgres' AND				
	( ha.wait_event_type = waitings_stat_rec.wait_event_type AND ha.wait_event = waitings_stat_rec.wait_event )
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

ΠŸΡ€ΠΈΠΌΠ΅Ρ€:

------------------------------------------------
| WAITINGS FOR QUERIES
+-----------------------------------------------
|                      wait_event_type = Client|
|                       wait_event = ClientRead|
|                        Total time  = 00:46:56|
------------------------------------------------
|    #|             queryid|            duration
+-----+--------------------+--------------------
|    1| 8085340880788646241|            00:40:20
|    2|                    |            00:03:45
|    3| 5731212217001535134|            00:01:53
|    4|                    |            00:00:12
|    5| 9150846928388977274|            00:00:09
|    6| 3422818749220588372|            00:00:08
|    7| 1237430309438971376|            00:00:06
|    8|   28942442626229688|            00:00:05
|    9| 4710212362688288619|            00:00:05
|   10|-5730801771815999400|            00:00:03
|   11| 8304755792398128062|            00:00:02
|   12|-6572922443698419129|            00:00:02
|   13|-1473395109729441239|            00:00:02
|   14| 2404820632950544954|            00:00:02
|   15|  180077086776069052|            00:00:01
|   16| 2369289265278398647|            00:00:01

+-----------------------------------------------
|                          wait_event_type = IO|
|                      wait_event = BufFileRead|
|                        Total time  = 00:00:38|
------------------------------------------------
|    #|             queryid|            duration
+-----+--------------------+--------------------
|    1|   28942442626229688|            00:00:38

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

LOCKED PROCESSES HISTORY

Запрос:

SELECT 
MIN(date_trunc('second',timepoint)) AS started , 
	count(*) * interval '1 second' as duration ,
	pid , blocking_pids , relation , mode , locktype 	 
FROM 
	activity_hist.archive_locking al 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY pid , blocking_pids , relation , mode , locktype			
UNION
SELECT 
	MIN(date_trunc('second',timepoint)) AS started , 
	count(*) * interval '1 second' as duration ,
	pid , blocking_pids , relation , mode , locktype
FROM 
	activity_hist.history_locking 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY pid , blocking_pids , relation , mode , locktype			
ORDER BY 1

ΠŸΡ€ΠΈΠΌΠ΅Ρ€:

-------------------------------------------------------------------------------------------------------------------------------------
| LOCKED PROCESSES HISTORY
+-----+----------+--------------------+----------+--------------------+--------------------+--------------------+--------------------
|    #|       pid|             started|  duration|       blocking_pids|            relation|                mode|            locktype
+-----+----------+--------------------+----------+--------------------+--------------------+--------------------+--------------------
|    1|     26224| 2019-09-02 19:32:16|  00:01:45|             {26211}|               16541|     AccessShareLock|            relation
|    2|     26390| 2019-09-02 19:34:03|  00:00:53|             {26211}|               16541|     AccessShareLock|            relation
|    3|     26391| 2019-09-02 19:34:03|  00:00:53|             {26211}|               16541|     AccessShareLock|            relation
|    4|     26531| 2019-09-02 19:35:27|  00:00:12|             {26211}|               16541|     AccessShareLock|            relation
|    5|     27284| 2019-09-02 19:44:02|  00:00:19|             {27276}|               16541|     AccessShareLock|            relation
|    6|     27283| 2019-09-02 19:44:02|  00:00:19|             {27276}|               16541|     AccessShareLock|            relation
|    7|     27286| 2019-09-02 19:44:02|  00:00:19|             {27276}|               16541|     AccessShareLock|            relation
|    8|     27423| 2019-09-02 19:45:24|  00:00:12|             {27394}|               16541|     AccessShareLock|            relation
|    9|     27648| 2019-09-02 19:48:06|  00:00:20|             {27647}|               16541|     AccessShareLock|            relation
|   10|     27650| 2019-09-02 19:48:06|  00:00:20|             {27647}|               16541|     AccessShareLock|            relation
|   11|     27735| 2019-09-02 19:49:08|  00:00:06|             {27650}|               16541| AccessExclusiveLock|            relation
|   12|     28380| 2019-09-02 19:56:03|  00:01:56|             {28379}|               16541|     AccessShareLock|            relation
|   13|     28379| 2019-09-02 19:56:03|  00:00:01|               28377|               16541| AccessExclusiveLock|            relation
|     |          |                    |          |               28376|                    | 

BLOCKING PROCESSES HISTORY

Запросы:

SELECT 
blocking_pids 
FROM 
	activity_hist.archive_locking al 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY blocking_pids 		
UNION
SELECT 
	blocking_pids 
FROM 
	activity_hist.history_locking 
WHERE 
	timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
	NOT granted AND 
	locktype = 'relation' 
GROUP BY blocking_pids 		
ORDER BY 1

---------------------------------------------------------------

SELECT 
	pid , usename , application_name , datname ,
	MIN(date_trunc('second',timepoint)) as started , 
	count(*) * interval '1 second' as duration ,		 
	state , 
	query
				FROM  	activity_hist.archive_pg_stat_activity
				WHERE 	pid= current_pid AND 
						timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') 						 
				GROUP BY pid , usename , application_name , 
						datname , 
						state_change, 
						state , 
						query 
				UNION
				SELECT 
					pid , usename , application_name , datname ,
					MIN(date_trunc('second',timepoint)) as started , 
					count(*) * interval '1 second' as duration ,		 
					state , 
					query
				FROM  	activity_hist.history_pg_stat_activity_for_reports
				WHERE 	pid= current_pid AND 
						timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') 						 
				GROUP BY pid , usename , application_name , 
						datname , 
						state_change, 
						state , 
						query 
				ORDER BY 5 , 1

ΠŸΡ€ΠΈΠΌΠ΅Ρ€:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BLOCKING PROCESSES HISTORY
+----+----------+----------+--------------------+----------+--------------------+--------------------+------------------------------+----------------------------------------
|   #|       pid|   usename|    application_name|   datname|             started|            duration|                         state|                                   query
+----+----------+----------+--------------------+----------+--------------------+--------------------+------------------------------+----------------------------------------
|   1|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:31:54|            00:00:04|                          idle|
|   2|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:31:58|            00:00:06|           idle in transaction|                                  begin;
|   3|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:32:16|            00:01:45|           idle in transaction|                  lock table wafer_data;
|   4|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:35:54|            00:01:23|                          idle|                                 commit;
|   5|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:38:46|            00:00:02|           idle in transaction|                                  begin;
|   6|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:38:54|            00:00:08|           idle in transaction|                  lock table wafer_data;
|   7|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:39:08|            00:42:42|                          idle|                                 commit;
|   8|     26211|     tuser|                psql|      tdb1| 2019-09-03 07:12:07|            00:00:52|                        active|                     select test_del ();

Π Π°Π·Π²ΠΈΡ‚ΠΈΠ΅.

ΠŸΠΎΠΊΠ°Π·Π°Π½Π½Ρ‹Π΅ Π±Π°Π·ΠΎΠ²Ρ‹Π΅ запросы ΠΈ ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌΡ‹Π΅ ΠΎΡ‚Ρ‡Π΅Ρ‚Ρ‹, ΡƒΠΆΠ΅ сильно ΠΎΠ±Π»Π΅Π³Ρ‡Π°ΡŽΡ‚ Тизнь ΠΏΡ€ΠΈ Π°Π½Π°Π»ΠΈΠ·Π΅ ΠΈΠ½Ρ†ΠΈΠ΄Π΅Π½Ρ‚ΠΎΠ² ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΠΈ.
На основС Π±Π°Π·ΠΎΠ²Ρ‹Ρ… запросов, ΠΌΠΎΠΆΠ½ΠΎ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΡ‚ΡŒ ΠΎΡ‚Ρ‡Π΅Ρ‚, ΠΎΡ‚Π΄Π°Π»Π΅Π½Π½ΠΎ ΠΏΡ€ΠΈΠ±Π»ΠΈΠΆΠ΅Π½Π½ΠΎ Π½Π°ΠΏΠΎΠΌΠΈΠ½Π°ΡŽΡ‰ΠΈΠΉ AWR Oracle.
ΠŸΡ€ΠΈΠΌΠ΅Ρ€ сводного ΠΎΡ‚Ρ‡Π΅Ρ‚Π°

+------------------------------------------------------------------------------------
| CONSOLIDATED REPORT FOR ACTIVITY AND WAITINGS . DATETIME : 03.09.2019 14:08
|------------------------------------------------------------------------------------
| HOST :10.124.70.40
| BEGIN_SNAPSHOT :02.09.2019 14:08 END_SNAPSHOT : 03.09.2019 14:00
|------------------------------------------------------------------------------------
| CURRENT DATABASES SIZE  :
| DATABASE :monitor
| SIZE (MB) :1370.00
|------------------------------------------------------------------------------------
| CLUSTER CPU TIME : 19:44:22
| CLUSTER WAITINGS TIME : 78:49:16
|
| SQL DBTIME : 65:53:09
| SQL CPU TIME : 19:05:21
| SQL WAITINGS TIME : 21:50:46
| SQL IOTIME : 20:53:00
| SQL READ TIME : 20:52:55
| SQL WRITE TIME : 00:00:05
|
| SQL CALLS : 311293
-------------------------------------------------------------
| SQL SHARED BLOCKS READS : 13351563334
| SQL SHARED BLOCKS HITS : 2775427045
| SQL SHARED BLOCKS HITS/READS % : 20.79
| SQL SHARED BLOCKS DIRTED : 21105
| SQL SHARED BLOCKS WRITTEN : 3656
|
| SQL TEMPORARY BLOCKS READS : 7464932
| SQL TEMPORARY BLOCKS WRITTEN : 10176024
-------------------------------------------------------------
|
| WAITINGS STATICTICS
|
+------------------------------------------------------------------------------------
| TOP 10 WAITINGS BY TOTAL WAIT TIME FOR SYSTEM PROCESSES
+-----+------------------------------+--------------------+--------------------
|    #|               wait_event_type|          wait_event|            duration
+-----+------------------------------+--------------------+--------------------
|    1|                      Activity| LogicalLauncherMain|            11:21:01
|    2|                      Activity|    CheckpointerMain|            11:20:35
|    3|                      Activity|      AutoVacuumMain|            11:20:31
|    4|                      Activity|       WalWriterMain|            11:19:35
|    5|                      Activity|        BgWriterMain|            10:14:19
|    6|                      Activity|   BgWriterHibernate|            01:06:04
|    7|                      Activity|       WalSenderMain|            00:04:05
|    8|                        Client|         ClientWrite|            00:04:00
|    9|                            IO|        BufFileWrite|            00:02:45
|   10|                        LWLock|      buffer_mapping|            00:02:14
+-----+------------------------------+--------------------+--------------------
| TOP 10 WAITINGS BY TOTAL WAIT TIME FOR CLIENTS PROCESSES
+-----+------------------------------+--------------------+--------------------+----------
|    #|               wait_event_type|          wait_event|            duration|  % dbtime
+-----+------------------------------+--------------------+--------------------+----------
|    1|                          Lock|       transactionid|            11:55:37|      18.1
|    2|                            IO|        DataFileRead|            07:19:43|     11.12
|    3|                        Client|          ClientRead|            00:46:54|      1.19
|    4|                          Lock|            relation|            00:40:37|      1.03
|    5|                        LWLock|      buffer_mapping|            00:31:08|      0.79
|    6|                        LWLock|           buffer_io|            00:22:12|      0.56
|    7|                       Timeout|             PgSleep|            00:10:58|      0.28
|    8|                          Lock|               tuple|            00:01:30|      0.04
|    9|                            IO|        BufFileWrite|            00:01:16|      0.03
|   10|                            IO|         BufFileRead|            00:00:37|      0.02
+-----+------------------------------+--------------------+--------------------+----------
| WAITINGS TYPES BY TOTAL WAIT TIME, FOR SYSTEM PROCESSES
+-----+------------------------------+--------------------
|    #|               wait_event_type|            duration
+-----+------------------------------+--------------------
|    1|                      Activity|            56:46:10
|    2|                            IO|            00:05:13
|    3|                        Client|            00:04:00
|    4|                        LWLock|            00:03:07
+-----+------------------------------+--------------------
| WAITINGS TYPES BY TOTAL WAIT TIME, FOR CLIENTS PROCESSES
+-----+------------------------------+--------------------+--------------------
|    #|               wait_event_type|            duration|            % dbtime
+-----+------------------------------+--------------------+--------------------
|    1|                          Lock|            12:37:44|               19.17
|    2|                            IO|            07:21:40|               11.17
|    3|                        LWLock|            00:53:26|                1.35
|    4|                        Client|            00:46:54|                1.19
|    5|                       Timeout|            00:10:58|                0.28
|    6|                           IPC|            00:00:04|                   0
+-----+------------------------------+--------------------+--------------------
| WAITINGS FOR SYSTEM PROCESSES
+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    #|                 backend_type|    dbname|     wait_event_type|            wait_event|            duration
+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    1| logical replication launcher|          |            Activity|   LogicalLauncherMain|            11:21:01
|    2|                 checkpointer|          |            Activity|      CheckpointerMain|            11:20:35
|    3|          autovacuum launcher|          |            Activity|        AutoVacuumMain|            11:20:31
|    4|                    walwriter|          |            Activity|         WalWriterMain|            11:19:35
|    5|            background writer|          |            Activity|          BgWriterMain|            10:14:19
|    6|            background writer|          |            Activity|     BgWriterHibernate|            01:06:04
|    7|                    walsender|          |            Activity|         WalSenderMain|            00:04:05
|    8|                    walsender|          |              Client|           ClientWrite|            00:04:00
|    9|              parallel worker|      tdb1|                  IO|          BufFileWrite|            00:02:45
|   10|              parallel worker|      tdb1|              LWLock|        buffer_mapping|            00:02:05
|   11|              parallel worker|      tdb1|                  IO|          DataFileRead|            00:01:10
|   12|              parallel worker|      tdb1|                  IO|           BufFileRead|            00:01:05
|   13|              parallel worker|      tdb1|              LWLock|             buffer_io|            00:00:45
|   14|            autovacuum worker|      tdb1|              LWLock|        buffer_mapping|            00:00:09
|   15|                    walwriter|          |                  IO|              WALWrite|            00:00:08
|   16|                    walwriter|          |              LWLock|          WALWriteLock|            00:00:04
|   17|            background writer|          |              LWLock|          WALWriteLock|            00:00:03
|   18|            background writer|          |                  IO|              WALWrite|            00:00:02
|   19|            background writer|          |                  IO|         DataFileWrite|            00:00:02
|   20|                 checkpointer|          |                  IO| ControlFileSyncUpdate|            00:00:01
|   21|            autovacuum worker|      tdb1|              LWLock|             buffer_io|            00:00:01
+-----+-----------------------------+----------+--------------------+----------------------+--------------------
| WAITINGS FOR SQL
+-----+-------------------------+----------+--------------------+--------------------+--------------------+----------
|    #|                  queryid|    dbname|     wait_event_type|          wait_event|            duration|  % dbtime
+-----+-------------------------+----------+--------------------+--------------------+--------------------+----------
|    1|       389015618226997618|      tdb1|                Lock|       transactionid|            09:47:43|     14.87
|    2|       389015618226997618|      tdb1|                  IO|        DataFileRead|            05:47:07|      8.78
|    3|                         |      tdb1|                Lock|       transactionid|            02:07:54|      3.24
|    4|                         |      tdb1|                  IO|        DataFileRead|            01:30:24|      2.29
|    5|      8085340880788646241|      tdb1|              Client|          ClientRead|            00:40:20|      1.02
|    6|       389015618226997618|      tdb1|              LWLock|      buffer_mapping|            00:20:41|      0.52
|    7|       389015618226997618|      tdb1|              LWLock|           buffer_io|            00:17:30|      0.44
|    8|      2649515222348904837|      tdb1|             Timeout|             PgSleep|            00:10:58|      0.28
|    9|      4710212362688288619|      tdb1|                Lock|            relation|            00:10:44|      0.27
|   10|      9150846928388977274|      tdb1|                Lock|            relation|            00:10:24|      0.26
|   11|        28942442626229688|      tdb1|                Lock|            relation|            00:07:48|       0.2
|   12|      1237430309438971376|      tdb1|                Lock|            relation|            00:07:32|      0.19
|   13|                         |      tdb1|              LWLock|      buffer_mapping|            00:04:32|      0.11
|   14|                         |      tdb1|              LWLock|           buffer_io|            00:04:13|      0.11
|   15|                         |      tdb1|              Client|          ClientRead|            00:03:57|       0.1
|   16|      4710212362688288619|      tdb1|              LWLock|      buffer_mapping|            00:02:26|      0.06
|   17|      3167065002719415275|      tdb1|                Lock|            relation|            00:02:20|      0.06
|   18|      5731212217001535134|      tdb1|              Client|          ClientRead|            00:01:53|      0.05
|   19|      1237430309438971376|      tdb1|              LWLock|      buffer_mapping|            00:01:42|      0.04
|   20|       389015618226997618|      tdb1|                Lock|               tuple|            00:01:30|      0.04
|   21|      8304755792398128062|      tdb1|                Lock|            relation|            00:01:29|      0.04
|   22|        28942442626229688|      tdb1|                  IO|        BufFileWrite|            00:01:16|      0.03
|   23|      9150846928388977274|      tdb1|                  IO|        DataFileRead|            00:01:07|      0.03
|   24|        28942442626229688|      tdb1|              LWLock|      buffer_mapping|            00:01:03|      0.03
|   25|      9150846928388977274|      tdb1|              LWLock|      buffer_mapping|            00:00:44|      0.02
|   26|        28942442626229688|      tdb1|                  IO|         BufFileRead|            00:00:37|      0.02
|   27|        28942442626229688|      tdb1|              LWLock|           buffer_io|            00:00:25|      0.01
|   28|      1237430309438971376|      tdb1|                  IO|        DataFileRead|            00:00:24|      0.01
|   29|        28942442626229688|      tdb1|                  IO|        DataFileRead|            00:00:22|      0.01
|   30|      5731212217001535134|      tdb1|                Lock|            relation|            00:00:20|      0.01
|   31|      4710212362688288619|      tdb1|                  IO|        DataFileRead|            00:00:19|      0.01
|   32|      9150846928388977274|      tdb1|              Client|          ClientRead|            00:00:09|         0
|   33|      3422818749220588372|      tdb1|              Client|          ClientRead|            00:00:08|         0
|   34|      1237430309438971376|      tdb1|              Client|          ClientRead|            00:00:06|         0
|   35|       389015618226997618|      tdb1|              LWLock|      buffer_content|            00:00:05|         0
|   36|      4710212362688288619|      tdb1|              Client|          ClientRead|            00:00:05|         0
|   37|      4710212362688288619|      tdb1|              LWLock|           buffer_io|            00:00:04|         0
|   38|        28942442626229688|      tdb1|              Client|          ClientRead|            00:00:04|         0
|   39|        28942442626229688|      tdb1|                 IPC|      ParallelFinish|            00:00:03|         0
|   40|       389015618226997618|      tdb1|                  IO|       DataFileWrite|            00:00:02|         0
|   41|     -5730801771815999400|      tdb1|              Client|          ClientRead|            00:00:02|         0
|   42|      2404820632950544954|      tdb1|              Client|          ClientRead|            00:00:02|         0
|   43|     -6572922443698419129|      tdb1|              Client|          ClientRead|            00:00:02|         0
|   44|      8304755792398128062|      tdb1|              Client|          ClientRead|            00:00:02|         0
|   45|     -1473395109729441239|      tdb1|              Client|          ClientRead|            00:00:02|         0
|   46|                         |      tdb1|              LWLock|      buffer_content|            00:00:01|         0
|   47|       180077086776069052|      tdb1|              Client|          ClientRead|            00:00:01|         0
|   48|                         |      tdb1|                  IO|       DataFileWrite|            00:00:01|         0
|   49|        28942442626229688|      tdb1|                 IPC| MessageQueueReceive|            00:00:01|         0
|   50|      2369289265278398647|      tdb1|              Client|          ClientRead|            00:00:01|         0
|   51|      9150846928388977274|      tdb1|                  IO|       DataFileWrite|            00:00:01|         0
+-----+-------------------------+----------+--------------------+--------------------+--------------------+----------
|
| CLIENT SQL STATICTICS
|
+------------------------------------------------------------------------------------
| CLIENT SQL ordered by Elapsed Time
+--------------------+----------+----------+----------+----------+----------+--------------------
|        elapsed time|     calls|  % dbtime|     % CPU|      % IO|    dbname|             queryid
+--------------------+----------+----------+----------+----------+----------+--------------------
|            06:43:19|        36|      10.2|     85.09|     17.38|      tdb1|  389015618226997618
|            02:06:53|       715|      3.21|      0.85|      0.06|      tdb1| 1237430309438971376
|            01:52:07|       720|      2.84|      1.19|      0.08|      tdb1| 4710212362688288619
|            00:39:03|       357|      0.99|      1.02|      0.33|      tdb1|   28942442626229688
|            00:22:00|         8|      0.56|      0.96|         0|      tdb1| 2649515222348904837
+--------------------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL ordered by CPU Time
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            cpu time|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            16:14:38|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618
|            00:13:38|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619
|            00:11:39|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688
|            00:10:58|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837
|            00:09:44|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL ordered by User I/O Wait Time
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|        io_wait time|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            05:47:09|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618
|            00:02:15|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688
|            00:00:24|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376
|            00:00:19|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619
|            00:00:00|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL ordered by Shared Buffers Reads
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|       buffers reads|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|          2562353244|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618
|            11041689|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688
|             3303551|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376
|             3242892|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619
|                   0|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL ordered by Disk Reads Time
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|           read time|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            03:37:46|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618
|            00:04:07|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688
|            00:00:59|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619
|            00:00:42|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376
|            00:00:00|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| CLIENT SQL ordered by Executions
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|               calls|      rows|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|                 720|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619
|                 715|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376
|                 357|         0|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688
|                  36|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618
|                   8|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| Complete List of SQL Text
----------------------------------------------
…

ΠŸΡ€ΠΎΠ΄ΠΎΠ»ΠΆΠ΅Π½ΠΈΠ΅ – слСдуСт. На ΠΎΡ‡Π΅Ρ€Π΅Π΄ΠΈ Ρ„ΠΎΡ€ΠΌΠΈΡ€ΠΎΠ²Π°Π½ΠΈΠ΅ истории Π±Π»ΠΎΠΊΠΈΡ€ΠΎΠ²ΠΎΠΊ (pg_stat_locks), Π±ΠΎΠ»Π΅Π΅ ΠΏΠΎΠ΄Ρ€ΠΎΠ±Π½ΠΎΠ΅ описаниС процСсса заполнСния Ρ‚Π°Π±Π»ΠΈΡ†.

Π˜ΡΡ‚ΠΎΡ‡Π½ΠΈΠΊ: habr.com

Π”ΠΎΠ±Π°Π²ΠΈΡ‚ΡŒ ΠΊΠΎΠΌΠΌΠ΅Π½Ρ‚Π°Ρ€ΠΈΠΉ