ProHoster > Dienoraštis > Administravimas > Bandymas sukurti ASH analogą PostgreSQL

Bandymas sukurti ASH analogą PostgreSQL

Problemos teiginys

Norint optimizuoti PostgreSQL užklausas, labai reikalinga galimybė analizuoti veiklos istoriją, ypač laukimo, užrakinimo ir lentelės statistiką.

Galimos galimybės

Istorinio darbo krūvio analizės įrankis arba „AWR, skirta Postgres“: labai įdomus sprendimas, tačiau nėra pg_stat_activity ir pg_locks istorijos.

pgsentinel plėtinys :
«Visa sukaupta informacija saugoma tik RAM, o sunaudojamos atminties kiekį reguliuoja paskutinių saugomų įrašų skaičius.

Pridedamas užklausos ID laukas – tas pats užklausos ID iš plėtinio pg_stat_statements (reikia iš anksto įdiegti).«

Tai, žinoma, labai padėtų, bet labiausiai vargina pirmas taškas.Visa sukaupta informacija saugoma tik RAM ", t.y. yra poveikis tikslinei bazei. Be to, nėra užraktų istorijos ir lentelės statistikos. Tie. Apskritai sprendimas yra neišsamus: „Dar nėra paruošto paketo diegimui. Siūloma šaltinius parsisiųsti ir biblioteką surinkti patiems. Pirmiausia turite įdiegti serverio paketą „devel“ ir kintamajame PATH nustatyti kelią į pg_config.".

Apskritai daug šurmulio, o rimtų gamybinių duomenų bazių atveju su serveriu gali ir nepavykti nieko padaryti. Turime vėl sugalvoti ką nors savo.

Įspėjimas.

Dėl gana didelės apimties ir neišsamaus testavimo laikotarpio, straipsnis daugiausia skirtas informaciniams tikslams, veikiau kaip tezių ir tarpinių rezultatų rinkinys.
Išsamesnė medžiaga bus parengta vėliau, dalimis

Sprendimo reikalavimų projektas

Būtina sukurti įrankį, leidžiantį saugoti:

pg_stat_activity peržiūros istorija
Seanso užrakinimo istorija naudojant pg_locks rodinį

Sprendimo reikalavimas– sumažinti poveikį tikslinei duomenų bazei.

Bendra mintis– duomenų rinkimo agentas paleidžiamas ne tikslinėje duomenų bazėje, o stebėjimo duomenų bazėje kaip sisteminė paslauga. Taip, galimas tam tikras duomenų praradimas, tačiau tai nėra labai svarbu teikiant ataskaitas, tačiau tai neturi įtakos tikslinei duomenų bazei atminties ir vietos diske atžvilgiu. O ryšio baseino naudojimo atveju įtaka vartotojo procesams yra minimali.

Įgyvendinimo etapai

1.Aptarnavimo stalai

Lentelėms saugoti naudojama atskira schema, kad nebūtų apsunkinta pagrindinių naudojamų lentelių analizė.

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

Svarbu: schema kuriama ne tikslinėje duomenų bazėje, o stebėjimo duomenų bazėje.

pg_stat_activity peržiūros istorija

Lentelė naudojama dabartinėms pg_stat_activity rodinio momentinėms nuotraukoms saugoti

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
);

Norėdami pagreitinti įterpimą - jokių indeksų ar apribojimų.

Norėdami išsaugoti pačią istoriją, naudojama suskaidyta lentelė:

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);

Kadangi šiuo atveju nėra jokių reikalavimų įterpimo greičiui, buvo sukurti tam tikri indeksai, siekiant pagreitinti ataskaitų kūrimą.

Seanso blokavimo istorija

Lentelė naudojama dabartinėms seansų užrakinimo momentinėms nuotraukoms saugoti:

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
);

Be to, siekiant pagreitinti įterpimą, nėra jokių indeksų ar apribojimų.

Norėdami išsaugoti pačią istoriją, naudojama suskaidyta lentelė:

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);

Kadangi šiuo atveju nėra jokių reikalavimų įterpimo greičiui, buvo sukurti tam tikri indeksai, siekiant pagreitinti ataskaitų kūrimą.

2. Dabartinės istorijos užpildymas

Norint tiesiogiai rinkti peržiūros momentines nuotraukas, naudojamas bash scenarijus, paleidžiantis funkciją 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 Funkcija dblink pasiekia rodinius tikslinėje duomenų bazėje ir įterpia eilutes į stebėjimo duomenų bazės paslaugų lenteles.

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;

Norint rinkti peržiūros momentines nuotraukas, naudojama sistemos paslauga ir du scenarijai:

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

Priskirkite scenarijus teises:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Pradėkime paslaugą:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

Taigi, peržiūrų istorija renkama sekundės momentinių kadrų pavidalu. Žinoma, jei viskas bus palikta kaip yra, stalai labai greitai padidės ir daugiau ar mažiau produktyvus darbas taps neįmanomas.

Būtina organizuoti duomenų archyvavimą.

3. Istorijos archyvavimas

Archyvavimui naudojamas suskirstytų lentelių archyvas*.

Nauji skirsniai kuriami kas valandą, o seni duomenys pašalinami iš istorijos* lentelių, todėl istorijos* lentelių dydis nesikeičia ir įterpimo greitis laikui bėgant nemažėja.

Naujų skyrių kūrimą atlieka plpgsql funkcija activity_hist.archive_current_activity. Darbo algoritmas labai paprastas (naudojant archive_pg_stat_activity lentelės skyriaus pavyzdį).

Sukurkite ir užpildykite naują skyrių

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 		
);

Indeksų kūrimas

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 )' 
);

Senų duomenų pašalinimas iš lentelės history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Žinoma, karts nuo karto senos skiltys ištrinamos kaip nereikalingos.

Pagrindinės ataskaitos

Tiesą sakant, kodėl visa tai daroma? Gauti ataskaitas, labai neaiškiai primenančias „Oracle“ AWR.

Svarbu pridurti, kad norint gauti ataskaitas, reikia sukurti ryšį tarp pg_stat_activity ir pg_stat_statements rodinių. Lentelės susietos pridedant stulpelį „queryid“ prie lentelių „history_pg_stat_activity“, „archive_pg_stat_activity“. Stulpelio vertės pridėjimo būdas nepatenka į šio straipsnio taikymo sritį ir yra aprašytas čia − pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

BENDRAS CPU LAIKAS UŽKLAUSIMAI

Prašymas:

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

Pavyzdys:

-------------------------------------------------------------------
| 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
+----+----------------------------------------+--------------------

BENDRAS UŽKLAUSIMŲ LAUKIMO LAIKAS

Prašymas:

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

Pavyzdys:

-------------------------------------------------------------------
| 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
+----+----------------------------------------+--------------------

LAUKIA UŽKLAUSIMŲ

Prašymai:

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

Pavyzdys:

------------------------------------------------
| 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

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

UŽRAKINTŲ PROCESŲ ISTORIJA

Prašymas:

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

Pavyzdys:

-------------------------------------------------- -------------------------------------------------- ---------------------------------- | UŽRAKINTŲ PROCESŲ ISTORIJA +-----+----------+------+-------- --+------------- --------+--------------------+----- ---------------- +-------------------- | #| pid| prasidėjo| trukmė| blocking_pids| santykis| režimas| spynos tipas +----------+-----------+------------------ +---------- -----------+------------+------- --------------+------ --------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| santykis | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| santykis | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| santykis | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| santykis | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| santykis | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| santykis | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| santykis | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| santykis | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| santykis | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| santykis | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| santykis | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| santykis | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| santykis | | | | | 28376| |

BLOKAVIMO PROCESŲ ISTORIJA

Prašymai:

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

Pavyzdys:

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------ BLOKAVIMO PROCESŲ ISTORIJA +----+-----------+-------- ---+--------------------+-----------+--------------- ------+--------------------+--------------------- -------+---------------------------------------- | #| pid| naudoti vardas| programos_pavadinimas| datname| prasidėjo| trukmė| valstybė| užklausa +----------+-----------+----------+-----+ ---------- --+--------------------+------------------ --+------- --------------------------+---------------- ------- ------------------ | 1| 26211| naudotojas| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| tuščiąja eiga| | 2| 26211| naudotojas| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| neaktyvus sandoryje| pradėti; | 3| 26211| naudotojas| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| neaktyvus sandoryje| užrakinti lentelę wafer_data; | 4| 26211| naudotojas| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| tuščiąja eiga| įsipareigoti; | 5| 26211| naudotojas| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| neaktyvus sandoryje| pradėti; | 6| 26211| naudotojas| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| neaktyvus sandoryje| užrakinti lentelę wafer_data; | 7| 26211| naudotojas| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| tuščiąja eiga| įsipareigoti; | 8| 26211| naudotojas| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| aktyvus| pasirinkite test_del();

Plėtra.

Rodomos pagrindinės užklausos ir gautos ataskaitos labai palengvina gyvenimą analizuojant našumo incidentus.
Remdamiesi pagrindinėmis užklausomis, galite gauti ataskaitą, kuri neaiškiai primena „Oracle“ AWR.
Suvestinės ataskaitos pavyzdys

+-------------------------------------------------- ------------------------------------ | KONSOLIDUOTAS VEIKLOS IR LAUKUČIŲ ATASKAITA.

Tęsinys. Kitas eilėje yra užrakto istorijos (pg_stat_locks) sukūrimas, išsamesnis lentelių pildymo proceso aprašymas.

Šaltinis: www.habr.com

Добавить комментарий