ProHoster > Bloc > Administració > Un intent de crear un anàleg ASH per a PostgreSQL

Un intent de crear un anàleg ASH per a PostgreSQL

Declaració de problemes

Per optimitzar les consultes de PostgreSQL, és molt necessària la capacitat d'analitzar l'historial d'activitats, en particular, les esperes, els bloquejos i les estadístiques de la taula.

Oportunitats disponibles

Eina d'anàlisi de càrrega de treball històrica o "AWR per a Postgres": una solució molt interessant, però no hi ha cap historial de pg_stat_activity i pg_locks.

extensió pgsentinel :
«Tota la informació acumulada només s'emmagatzema a la memòria RAM i la quantitat de memòria consumida es regula pel nombre dels últims registres emmagatzemats.

S'afegeix el camp queryid: el mateix queryid de l'extensió pg_stat_statements (necessita instal·lació prèvia).«

Això, per descomptat, ajudaria molt, però el més problemàtic és el primer punt".Tota la informació acumulada només s'emmagatzema a la memòria RAM ", és a dir. hi ha un impacte en la base objectiu. A més, no hi ha cap historial de bloqueig ni estadístiques de taula. Aquells. la solució és, en general, incompleta: "Encara no hi ha cap paquet preparat per a la instal·lació. Es recomana descarregar les fonts i muntar la biblioteca vosaltres mateixos. Primer heu d'instal·lar el paquet "devel" per al vostre servidor i establir el camí a pg_config a la variable PATH.".

En general, hi ha molt d'enrenou i, en el cas de bases de dades de producció serioses, és possible que no es pugui fer res amb el servidor. Hem de tornar a inventar alguna cosa pròpia.

Avís

A causa del volum força gran i a causa del període de proves incomplet, l'article és principalment de caràcter informatiu, més aviat com un conjunt de tesis i resultats intermedis.
Més endavant es prepararà material més detallat, per parts

Esborrany de requisits per a la solució

Cal desenvolupar una eina que us permeti emmagatzemar:

pg_stat_activity visualitza l'historial
Historial de bloqueig de sessions mitjançant la vista pg_locks

Requisit de la solució–minimitzar l'impacte en la base de dades objectiu.

Idea general– l'agent de recollida de dades no s'inicia a la base de dades de destinació, sinó a la base de dades de supervisió com a servei de sistema. Sí, és possible la pèrdua de dades, però això no és crític per als informes, però no hi ha cap impacte a la base de dades de destinació en termes de memòria i espai de disc. I en el cas d'utilitzar un grup de connexions, l'impacte en els processos dels usuaris és mínim.

Fases d'implantació

1.Taules de servei

S'utilitza un esquema separat per emmagatzemar taules, per no complicar l'anàlisi de les taules principals utilitzades.

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

Important: l'esquema no es crea a la base de dades de destinació, sinó a la base de dades de supervisió.

pg_stat_activity visualitza l'historial

S'utilitza una taula per emmagatzemar les instantànies actuals de la vista 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
);

Per accelerar la inserció, sense índexs ni restriccions.

Per emmagatzemar l'historial en si, s'utilitza una taula particionada:

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

Com que en aquest cas no hi ha requisits de velocitat d'inserció, s'han creat alguns índexs per agilitzar la creació d'informes.

Historial de bloqueig de sessions

S'utilitza una taula per emmagatzemar les instantànies actuals dels bloquejos de sessió:

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

A més, per accelerar la inserció, no hi ha índexs ni restriccions.

Per emmagatzemar l'historial en si, s'utilitza una taula particionada:

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

Com que en aquest cas no hi ha requisits de velocitat d'inserció, s'han creat alguns índexs per agilitzar la creació d'informes.

2.Omplint la història actual

Per recollir directament les instantànies de visualització, s'utilitza un script bash que executa la funció 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 La funció dblink accedeix a les vistes de la base de dades de destinació i insereix files a les taules de servei de la base de dades de supervisió.

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;

Per recopilar instantànies de visualització, s'utilitzen el servei systemd i dos scripts:

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

Assigna drets als scripts:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Comencem el servei:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

Així, l'historial de les vistes es recull en forma d'instantànies segon a segon. Per descomptat, si tot es deixa com està, les taules augmentaran molt ràpidament de mida i es farà impossible un treball més o menys productiu.

Cal organitzar l'arxiu de dades.

3. Arxiu de la història

Per arxivar, s'utilitzen l'arxiu* de taules particionades.

Es creen particions noves cada hora, mentre que les dades antigues s'eliminen de les taules d'historial*, de manera que la mida de les taules de l'historial* no canvia gaire i la velocitat d'inserció no es degrada amb el temps.

La creació de noves seccions la realitza la funció plpgsql activity_hist.archive_current_activity. L'algorisme de treball és molt senzill (utilitzant l'exemple de la secció per a la taula archive_pg_stat_activity).

Crea i omple una nova secció

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

Creació d'índexs

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

S'estan eliminant dades antigues de la taula history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Per descomptat, de tant en tant, s'eliminen seccions antigues com a innecessàries.

Informes bàsics

De fet, per què es fa tot això? Per obtenir informes que recordin molt vagament l'AWR d'Oracle.

És important afegir que per rebre informes, cal establir una connexió entre les vistes pg_stat_activity i pg_stat_statements. Les taules s'enllaçen afegint una columna "queryid" a les taules "history_pg_stat_activity", "archive_pg_stat_activity". El mètode per afegir un valor de columna està fora de l'abast d'aquest article i es descriu aquí − pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TEMPS TOTAL DE LA CPU PER A CONSULTES

Sol·licitud:

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

Exemple:

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

TEMPS TOTAL D'ESPERA PER A CONSULTES

Sol·licitud:

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

Exemple:

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

ESPERA DE CONSULTES

Sol·licituds:

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

Exemple:

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

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

HISTÒRIA DE PROCESSOS BLOCATS

Sol·licitud:

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

Exemple:

-------------------------------------------------- -------------------------------------------------- ---------------------------------- | HISTÒRIA DE PROCESSOS BLOCATS +-----+----------+-----+-------- --+------------ --------+--------------------+----- ---------------- +------------------- | #| pid| començat| durada| bloqueig_pids| relació| mode| tipus de bloqueig +----------+----------+-----+---------- +--------- -----------+-----------+------- -------------+----- -------------- | 1| 26224| 2019/09/02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| relació | 2| 26390| 2019/09/02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| relació | 3| 26391| 2019/09/02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| relació | 4| 26531| 2019/09/02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| relació | 5| 27284| 2019/09/02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| relació | 6| 27283| 2019/09/02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| relació | 7| 27286| 2019/09/02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| relació | 8| 27423| 2019/09/02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| relació | 9| 27648| 2019/09/02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| relació | 10| 27650| 2019/09/02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| relació | 11| 27735| 2019/09/02 19:49:08| 00:00:06| {27650}| 16541| Accés ExclusiveLock| relació | 12| 28380| 2019/09/02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| relació | 13| 28379| 2019/09/02 19:56:03| 00:00:01| 28377| 16541| Accés ExclusiveLock| relació | | | | | 28376| |

BLOC DE PROCESSOS HISTÒRIA

Sol·licituds:

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

Exemple:

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- HISTÒRIA DE PROCESSOS DE BLOCKING +----+-----------+------- ---+--------------------+----------+--------------- ------+--------------------+---------------------- -------+--------------------------------------- | #| pid| nom d'ús| nom_aplicació| nom de dades| començat| durada| estat| consulta +----------+----------+----------+-----+ --------- -+--------------------+------------------ --+------ --------------------------+---------------- ------- ----------------- | 1| 26211| tuser| psql| tdb1| 2019/09/02 19:31:54| 00:00:04| inactiu| | 2| 26211| tuser| psql| tdb1| 2019/09/02 19:31:58| 00:00:06| inactiu en transacció| començar; | 3| 26211| tuser| psql| tdb1| 2019/09/02 19:32:16| 00:01:45| inactiu en transacció| bloqueig de la taula wafer_data; | 4| 26211| tuser| psql| tdb1| 2019/09/02 19:35:54| 00:01:23| inactiu| comprometre; | 5| 26211| tuser| psql| tdb1| 2019/09/02 19:38:46| 00:00:02| inactiu en transacció| començar; | 6| 26211| tuser| psql| tdb1| 2019/09/02 19:38:54| 00:00:08| inactiu en transacció| bloqueig de dades de la taula; | 7| 26211| tuser| psql| tdb1| 2019/09/02 19:39:08| 00:42:42| inactiu| comprometre; | 8| 26211| tuser| psql| tdb1| 2019/09/03 07:12:07| 00:00:52| actiu| seleccioneu prova_del();

Desenvolupament.

Les consultes bàsiques que es mostren i els informes resultants ja faciliten molt la vida a l'hora d'analitzar incidents de rendiment.
A partir de consultes bàsiques, podeu obtenir un informe que s'assembla vagament a l'AWR d'Oracle.
Exemple d'informe resum

+------------------------------------------------- ------------------------------------ | INFORME CONSOLIDAT D'ACTIVITAT I ESPERE.

Continuarà. El següent a la línia és la creació d'un historial de bloqueig (pg_stat_locks), una descripció més detallada del procés d'ompliment de taules.

Font: www.habr.com

Afegeix comentari