ProHoster > Blog > Stjórnsýsla > Tilraun til að búa til ASH hliðstæðu fyrir PostgreSQL

Tilraun til að búa til ASH hliðstæðu fyrir PostgreSQL

Samsetning vandans

Til að hámarka PostgreSQL fyrirspurnir, er hæfileikinn til að greina virknisögu, sérstaklega bið, læsingar og töflutölfræði, mjög nauðsynleg.

Laus tækifæri

Sögulegt vinnuálagsgreiningartól eða „AWR fyrir Postgres“: mjög áhugaverð lausn, en það er engin saga um pg_stat_activity og pg_locks.

pgsentinel eftirnafn :
«Allar uppsafnaðar upplýsingar eru aðeins geymdar í vinnsluminni og neytt magn af minni er stjórnað af fjölda síðustu vistuðu gagna.

Fyrirspurnarauðkennisreitnum er bætt við - sama fyrirspurnarauðkenni frá pg_stat_statements viðbótinni (foruppsetning krafist).«

Þetta myndi auðvitað hjálpa mikið, en það erfiðasta er fyrsti punkturinn.“Allar uppsafnaðar upplýsingar eru aðeins geymdar í vinnsluminni “, þ.e. það hefur áhrif á markgrunninn. Að auki er engin læsisaga og töflutölfræði. Þeir. lausnin er almennt talað ófullnægjandi: “Það er enginn tilbúinn pakki til uppsetningar ennþá. Mælt er með því að hlaða niður heimildunum og setja saman safnið sjálfur. Þú þarft fyrst að setja upp „devel“ pakkann fyrir netþjóninn þinn og stilla slóðina á pg_config í PATH breytunni.".

Almennt séð er mikið vesen og ef um alvarlega framleiðslugagnagrunna er að ræða er ekki víst að hægt sé að gera neitt við netþjóninn. Við þurfum að koma með eitthvað okkar eigin aftur.

Viðvörun

Vegna frekar mikið magns og vegna ófullkomins prófunartímabils er greinin aðallega til upplýsinga, frekar sem safn ritgerða og milliniðurstöður.
Nánara efni verður unnið síðar, í köflum

Drög að kröfum um lausnina

Það er nauðsynlegt að þróa tól sem gerir þér kleift að geyma:

pg_stat_activity skoða sögu
Saga lotulás með pg_locks skjánum

Lausnaþörf– lágmarka áhrifin á markgagnagrunninn.

Almenn hugmynd– gagnasöfnunaraðilinn er ekki opnaður í markgagnagrunninum heldur í vöktunargagnagrunninum sem kerfisþjónusta. Já, eitthvað gagnatap er mögulegt, en þetta er ekki mikilvægt fyrir skýrslugerð, en það hefur engin áhrif á markgagnagrunninn hvað varðar minni og diskpláss. Og ef um er að ræða tengingarlaug eru áhrifin á notendaferla í lágmarki.

Stig framkvæmdar

1.Þjónustutöflur

Sérstakt skema er notað til að geyma töflur, til að flækja ekki greininguna á helstu töflunum sem notaðar eru.

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

Mikilvægt: Skemanið er ekki búið til í markgagnagrunninum, heldur í vöktunargagnagrunninum.

pg_stat_activity skoða sögu

Tafla er notuð til að geyma núverandi skyndimyndir af pg_stat_activity yfirlitinu

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

Til að flýta fyrir innsetningu - engar vísitölur eða takmarkanir.

Til að geyma söguna sjálfa er skipt í tafla:

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

Þar sem í þessu tilfelli eru engar kröfur um innsetningarhraða, hafa nokkrar vísitölur verið búnar til til að flýta fyrir gerð skýrslna.

Saga lokunar á lotu

Tafla er notuð til að geyma núverandi skyndimyndir af lotulásum:

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

Einnig, til að flýta fyrir innsetningu, eru engar vísitölur eða takmarkanir.

Til að geyma söguna sjálfa er skipt í tafla:

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

Þar sem í þessu tilfelli eru engar kröfur um innsetningarhraða, hafa nokkrar vísitölur verið búnar til til að flýta fyrir gerð skýrslna.

2.Að fylla út núverandi sögu

Til að safna skyndimyndum beint er bash forskrift notað sem keyrir plpgsql aðgerðina.

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 aðgerðin opnar skoðanir í markgagnagrunninum og setur línur inn í þjónustutöflur í vöktunargagnagrunninum.

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;

Til að safna skyndimyndum er systemd þjónustan og tvö forskrift notuð:

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

Úthluta rétti til handrita:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Við skulum hefja þjónustuna:
# systemctl daemon-reload
# systemctl byrja pg_current_activity.service

Þannig er sögu skoðana safnað saman í formi skyndimynda sekúndu fyrir sekúndu. Auðvitað, ef allt er látið óbreytt, munu borðin stækka mjög fljótt og meira eða minna afkastamikil vinna verður ómöguleg.

Nauðsynlegt er að skipuleggja gagnageymslu.

3. Skjalavistunarsaga

Til skjalavistunar er skipt í töflusafn* notað.

Ný skipting eru búin til á klukkutíma fresti á meðan gömul gögn eru fjarlægð úr sögu* töflunum, þannig að stærð sögu* töflunnar breytist ekki mikið og innsetningarhraðinn minnkar ekki með tímanum.

Stofnun nýrra hluta er framkvæmd af plpgsql aðgerðinni activity_hist.archive_current_activity. Reiknirit vinnunnar er mjög einfalt (með því að nota dæmið um hlutann fyrir töfluna archive_pg_stat_activity).

Búðu til og fylltu út nýjan hluta

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

Að búa til vísitölur

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

Fjarlægir gömul gögn úr history_pg_stat_activity töflunni

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Auðvitað er gömlum köflum af og til eytt sem óþarfi.

Grunnskýrslur

Reyndar, hvers vegna er allt þetta gert? Til að fá skýrslur sem minna mjög óljóst á AWR Oracle.

Það er mikilvægt að bæta því við að til að fá skýrslur þarftu að byggja upp tengingu á milli pg_stat_activity og pg_stat_statements útsýnisins. Töflurnar eru tengdar með því að bæta 'queryid' dálki við 'history_pg_stat_activity', 'archive_pg_stat_activity' töflurnar. Aðferðin við að bæta við dálkgildi er utan gildissviðs þessarar greinar og er lýst hér - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

HEILDAR CPU TÍMI FYRIR FYRIRTÆKUR

Beiðni:

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

Dæmi:

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

HEILDAR BÍÐTÍMI FYRIR FYRIRFRÆÐUR

Beiðni:

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

Dæmi:

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

BÍÐUR EFTIR FYRIRFRÆÐUR

Beiðnir:

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

Dæmi:

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

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

LÆST FERLI SAGA

Beiðni:

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

Dæmi:

-------------------------------------------------- -------------------------------------------------- ---------------------------------- | LÆST FERLI SAGA +-------------------+-------- --+------ ---------------------------+----------------------------- +------------------------ | #| pid| byrjaði| lengd | blocking_pids| samband| háttur| locktype +----------+-------+------------------- --------------+------------+-----------------------+----- -------------- | 1| 26224| 2019 09:02:19| 32:16:00| {01}| 45| AccessShareLock| samband | 26211| 16541| 2 26390:2019:09| 02:19:34| {03}| 00| AccessShareLock| samband | 00| 53| 26211 16541:3:26391| 2019:09:02| {19}| 34| AccessShareLock| samband | 03| 00| 00 53:26211:16541| 4:26531:2019| {09}| 02| AccessShareLock| samband | 19| 35| 27/00/00 12:26211:16541| 5:27284:2019| {09}| 02| AccessShareLock| samband | 19| 44| 02/00/00 19:27276:16541| 6:27283:2019| {09}| 02| AccessShareLock| samband | 19| 44| 02/00/00 19:27276:16541| 7:27286:2019| {09}| 02| AccessShareLock| samband | 19| 44| 02 00:00:19| 27276:16541:8| {27423}| 2019| AccessShareLock| samband | 09| 02| 19 45:24:00| 00:12:27394| {16541}| 9| AccessShareLock| samband | 27648| 2019| 09 02:19:48| 06:00:00| {20}| 27647| AccessShareLock| samband | 16541| 10| 27650 2019:09:02| 19:48:06| {00}| 00| AccessExclusiveLock| samband | 20| 27647| 16541 11:27735:2019| 09:02:19| {49}| 08| AccessShareLock| samband | 00| 00| 06 27650:16541:12| 28380:2019:09| 02| 19| AccessExclusiveLock| samband | | | | | 56| |

SAGA LOKKUNARFERLA

Beiðnir:

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

Dæmi:

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- SAGA LOKKUNARFERLA +-----+------------ ---+---------------------+-----------+------------------------ ------+--------------------+----------------------------- -------+----------------------------------------------------- | #| pid| notendanafn| umsókn_nafn| nafnnafn| byrjaði| lengd | ástand| fyrirspurn +-----------+------------------+ ---------- --+--------------------+------------------------ --+------ --------------------------+---------------- ------- ------------------ | 1| 26211| notandi| psql| tdb1| 2019/09/02 19:31:54| 00:00:04| aðgerðalaus | | 2| 26211| notandi| psql| tdb1| 2019 09:02:19| 31:58:00| aðgerðalaus í viðskiptum| byrja; | 00| 06| notandi| psql| tdb3| 26211 1:2019:09| 02:19:32| aðgerðalaus í viðskiptum| læsa borð wafer_data; | 16| 00| notandi| psql| tdb01| 45 4:26211:1| 2019:09:02| aðgerðalaus | skuldbinda sig; | 19| 35| notandi| psql| tdb54| 00 01:23:5| 26211:1:2019| aðgerðalaus í viðskiptum| byrja; | 09| 02| notandi| psql| tdb19| 38 46:00:00| 02:6:26211| aðgerðalaus í viðskiptum| læsa borð wafer_data; | 1| 2019| notandi| psql| tdb09| 02 19:38:54| 00:00:08| aðgerðalaus | skuldbinda sig; | 7| 26211| notandi| psql| tdb1| 2019 09:02:19| 39:08:00| virkur| veldu test_del();

Þróun.

Grunnfyrirspurnirnar sem sýndar eru og skýrslurnar sem afleiddar eru gera lífið nú þegar miklu auðveldara þegar frammistöðuatvik eru greind.
Byggt á grunnfyrirspurnum geturðu fengið skýrslu sem líkist óljóst AWR Oracle.
Dæmi um samantektarskýrslu

+-------------------------------------------------------- ---------------------------------- | SAMSTÆÐI SKÝRSLA FYRIR STARFSEMI OG BÍÐIR.

Framhald. Næst í röðinni er að búa til læsingasögu (pg_stat_locks), nánari lýsingu á því ferli að fylla töflur.

Heimild: www.habr.com

Bæta við athugasemd