ProHoster > Blog > башкаруу > PostgreSQL үчүн ASH аналогун түзүү аракети

PostgreSQL үчүн ASH аналогун түзүү аракети

Тапшырманын коюлушу

PostgreSQL сурамдарын оптималдаштыруу үчүн, иш-аракеттердин тарыхын талдоо мүмкүнчүлүгү, атап айтканда, күтүү, кулпулар жана таблица статистикасы абдан талап кылынат.

Жеткиликтүү мүмкүнчүлүктөр

Тарыхый жүктөмдү талдоо куралы же "Postgres үчүн AWR": абдан кызыктуу чечим, бирок pg_stat_activity жана pg_locks тарыхы жок.

pgsentinel кеңейтүүсү :
«Бардык топтолгон маалымат RAMда гана сакталат, ал эми эстутумдун сарпталган көлөмү акыркы сакталган жазуулардын саны менен жөнгө салынат.

Queryid талаасы кошулду - pg_stat_statements кеңейтүүсүнөн ошол эле queryid (алдын ала орнотуу талап кылынат).«

Бул, албетте, чоң жардам бермек, бирок эң кыйыны – биринчи пункт».Бардык топтолгон маалымат RAMда гана сакталат ”, б.а. максаттуу базага таасири бар. Мындан тышкары, кулпу тарыхы жана стол статистикасы жок. Ошол. чечим жалпысынан толук эмес: "Азырынча орнотуу үчүн даяр пакет жок. Булактарды жүктөп алып, китепкананы өзүңүз чогултуу сунушталат. Сиз адегенде сервериңиз үчүн “devel” пакетин орнотуп, PATH өзгөрмөсүндө pg_config жолун коюшуңуз керек.«.

Жалпысынан алганда, ызы-чуу көп, жана олуттуу өндүрүштүк маалымат базалары учурда, ал сервер менен эч нерсе кылуу мүмкүн эмес болушу мүмкүн. Биз дагы өзүбүзгө тиешелүү бир нерсе ойлоп табышыбыз керек.

Эскертүү.

Өтө чоң көлөмдө жана тестирлөө мөөнөтү бүтпөгөндүктөн, макала тезистердин жана аралык натыйжалардын жыйындысы катары, негизинен, маалыматтык мүнөзгө ээ.
Кененирээк материал кийинчерээк, бөлүктөр менен даярдалат

Чечимге коюлган талаптардын долбоору

Бул сактоого мүмкүндүк берүүчү куралды иштеп чыгуу зарыл:

pg_stat_activity көрүү таржымалы
pg_locks көрүнүшүн колдонуу менен сессияны бөгөттөө таржымалы

Чечимдин талабы– максаттуу маалымат базасына таасирин азайтуу.

Жалпы идея– маалымат чогултуу агенти максаттуу маалымат базасында эмес, системалык кызмат катары мониторингдин маалымат базасында ишке киргизилет. Ооба, кээ бир маалыматтарды жоготуу мүмкүн, бирок бул отчеттуулук үчүн маанилүү эмес, бирок эс жана диск мейкиндиги жагынан максаттуу маалымат базасына эч кандай таасири жок. Ал эми туташуу пулун колдонгон учурда, колдонуучу процесстерине таасири минималдуу.

Ишке ашыруу этаптары

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.Учурдагы тарыхты толтуруу

Көз ирмемдик сүрөттөрдү түздөн-түз чогултуу үчүн plpgsql функциясын иштеткен bash скрипти колдонулат.

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 кызматы жана эки скрипт колдонулат:

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-кайра жүктөө
# systemctl pg_current_activity.service баштоо

Ошентип, көз караштардын тарыхы секунданын секундасына сүрөт түрүндө жыйналат. Албетте, эгерде баары ошол бойдон калса, үстөлдөрдүн көлөмү абдан тез өсөт жана аздыр-көптүр жемиштүү иштөө мүмкүн эмес болуп калат.

Маалыматтарды архивдөө ишин уюштуруу зарыл.

3. Архивдөө тарыхы

Архивдөө үчүн бөлүнгөн таблицалар архив* колдонулат.

Саат сайын жаңы бөлүмдөр түзүлөт, ошол эле учурда тарых* таблицаларынан эски маалыматтар алынып салынат, андыктан тарых* таблицаларынын көлөмү көп деле өзгөрбөйт жана убакыттын өтүшү менен киргизүү ылдамдыгы начарлабайт.

Жаңы бөлүмдөрдү түзүү plpgsql function_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;

Албетте, мезгил-мезгили менен эски бөлүмдөр керексиз деп өчүрүлөт.

Негизги отчеттор

Чындыгында мунун баары эмне үчүн жасалып жатат? Oracle's AWRди абдан бүдөмүк эске салган отчетторду алуу үчүн.

Отчетторду алуу үчүн 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? .

СУРООЛОР ҮЧҮН ЖАЛПЫ CPU УБАКЫТ

Сураныч:

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

СУРООЛОРДУ КҮТҮҮ УБАКЫТЫ

Сураныч:

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

СУРООЛОРДУ КҮТӨТ

Суроолор:

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

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

БУЛПАЛГАН ПРОЦЕССТЕР ТАРЫХЫ

Сурам:

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

мисалы:

------------------------------------------------- ------------------------------------------------- --------------------------------- | БУЛПАЛГАН ПРОЦЕССТЕР ТАРЫХЫ +-----+----------+-----+-------- --+------------ --------+--------------------+----- --------------- +------------------- | #| pid| баштады| узактыгы| blocking_pids| мамилеси| режим| locktype +----------+----------+-----+---------- +--------- -----------+-----------+------- -------------+----- ------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| мамиле | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| мамиле | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| мамиле | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| мамиле | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| мамиле | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| мамиле | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| мамиле | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| мамиле | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| мамиле | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| мамиле | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| мамиле | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| мамиле | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| мамиле | | | | | 28376| |

ПРОЦЕССТЕРДИН ТАРЫХЫН БӨЛӨӨ

Суроолор:

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

мисалы:

------------------------------------------------- ------------------------------------------------- ------------------------------------------------- ----------------------- БЛОГОЛОО ПРОЦЕССТЕРИНИН ТАРЫХЫ +----+----------+------- ---+-------------------+----------+-------------- -----------------------------------+--------------------- - -------+--------------------------------------- | #| pid| usename| application_name| datname| баштады| узактыгы| мамлекеттик| суроо +----------+----------+----------+-----+ --------- -+-------------------+------------------ --+------ ----------------------------------+---------------- ------- ----------------- | 1| 26211| тусер| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| бош| | 2| 26211| тусер| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| транзакцияда бош | баштоо; | 3| 26211| тусер| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| транзакцияда бош | lock table wafer_data; | 4| 26211| тусер| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| бош| жасоо; | 5| 26211| тусер| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| транзакцияда бош | баштоо; | 6| 26211| тусер| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| транзакцияда бош | lock table wafer_data; | 7| 26211| тусер| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| бош| жасоо; | 8| 26211| тусер| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| активдүү| тандоо test_del();

Өнүгүү.

Көрсөтүлгөн негизги суроо-талаптар жана натыйжадагы отчеттор аткаруу инциденттерин талдоодо жашоону бир топ жеңилдетет.
Негизги суроо-талаптардын негизинде, сиз Oracle's AWRге такыр окшошпогон отчетту ала аласыз.
Кыскача отчеттун мисалы

+------------------------------------------------ ---------------------------------- | ИШМЕРДҮҮЛҮК ЖАНА КҮТҮҮЛӨР ҮЧҮН КОНСОЛИДАДРДУУ ОТЧЕТ.

Уландысы бар. Кийинки кезекте кулпу таржымалын түзүү (pg_stat_locks), таблицаларды толтуруу процессинин кеңири сүрөттөлүшү.

Source: www.habr.com

Комментарий кошуу