pgsentinel fa'aopoopoga :
«O faʻamatalaga faʻaputu uma e teuina i totonu o le RAM, ma o le aofaʻi o le manatua e faʻatonutonuina e le numera o faʻamaumauga mulimuli na teuina.
O lo'o fa'aopoopoina le fa'ailoga fesili - o le queryid tutusa mai le fa'aopoopoga pg_stat_statements (mua'i fa'apipi'i mana'omia).«
O le mea moni, o le a fesoasoani tele, ae o le mea sili ona faigata o le mea muamua.O faʻamatalaga faʻaputu uma e teuina naʻo le RAM ”, o lona uiga. o loʻo i ai se aʻafiaga i luga o le faʻavae autu. E le gata i lea, e leai se loka tala fa'asolopito ma fuainumera laulau. O na. o le fofo e masani lava ona le atoatoa: "E leai se afifi ua saunia mo le faʻapipiʻiina. E fautuaina e sii mai punaoa ma faʻapipiʻi oe lava le faletusi. E te manaʻomia muamua faʻapipiʻi le pusa "devel" mo lau 'auʻaunaga ma seti le ala i le pg_config i le PATH fesuiaiga.".
I se tulaga lautele, e tele le vevesi, ma i le tulaga o faʻamaumauga tuʻufaʻatasiga ogaoga, atonu e le mafai ona faia se mea i le server. E tatau ona tatou toe faia se mea mo i tatou lava.
Lapataiga
Ona o le tele o le voluma ma ona o le le atoatoa o le taimi o suʻega, o le tusiga e masani lava o se natura faʻamatalaga, ae o se seti o suʻesuʻega ma faʻaiʻuga vavalalata.
O mea e sili atu auiliiliga o le a saunia mulimuli ane, i vaega
Fa'ata'ita'i mana'oga mo le fofo
E manaʻomia le atinaʻeina o se meafaigaluega e mafai ai ona e teuina:
pg_stat_activity va'ai tala'aga Tala'aga loka o sauniga e fa'aaoga ai le pg_locks va'aiga
Mana'oga fofo– fa'aiti'itia le a'afiaga o fa'amaumauga autu.
Manatu lautele- o le sui o le aoina o faʻamatalaga e faʻalauiloaina e le o totonu o le faʻamaumauga autu, ae i totonu o le faʻamaumauga mataʻituina o se auaunaga systemd. Ioe, o nisi faʻamatalaga leiloa e mafai, ae e le taua tele mo le lipotia, ae leai se aʻafiaga i luga o faʻamaumauga autu i tulaga o le manatua ma le avanoa o le disk. Ma i le tulaga o le faʻaaogaina o se vaitaele fesoʻotaʻiga, o le aʻafiaga i faiga faʻaoga e laʻititi.
Laasaga o le faatinoga
1. Lau'aunaga laulau
O lo'o fa'aogaina se fa'ailoga ese'ese e teu ai laulau, ina ia aua ne'i fa'alavelaveina le au'ili'iliga o laulau autu na fa'aaogaina.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
Taua: E le o faia le fuafuaga i totonu o le faʻamaumauga autu, ae i totonu o le faʻamaumauga mataʻituina.
pg_stat_activity va'ai tala'aga
E fa'aoga le laulau e teu ai ata o iai nei o le pg_stat_activity view
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
);
Ina ia faʻavave le faʻaofiina - leai ni faʻailoga poʻo ni faʻatapulaʻaina.
Ina ia teuina le talafaasolopito lava ia, e faʻaaogaina se laulau vaeluaga:
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);
Talu ai i lenei tulaga e leai ni mea e manaʻomia mo le faʻaofiina o le saoasaoa, o nisi faʻamatalaga ua faia e faʻavaveina ai le fausiaina o lipoti.
Tala'aga poloka poloka
E fa'aoga se laulau e teu ai ata o lo'o iai nei o loka o sauniga:
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
);
E le gata i lea, ina ia faʻavaveina le faʻaofiina, e leai ni faʻamatalaga poʻo ni tapulaʻa.
Ina ia teuina le talafaasolopito lava ia, e faʻaaogaina se laulau vaeluaga:
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);
Talu ai i lenei tulaga e leai ni mea e manaʻomia mo le faʻaofiina o le saoasaoa, o nisi faʻamatalaga ua faia e faʻavaveina ai le fausiaina o lipoti.
2. Faatumu le tala faasolopito o iai nei
Ina ia aoina saʻo ata vaʻaia, o loʻo faʻaaogaina se tusitusiga bash e faʻatino ai le galuega plpgsql.
plpgsql O le galuega dblink e maua ai vaaiga i totonu o faʻamaumauga autu ma faʻapipiʻi laina i laulau tautua i totonu o le database mataʻituina.
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;
Ina ia aoina ata vaʻaia, o le systemd service ma lua tusitusiga e faʻaaogaina:
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
Tatou amata le auaunaga:
# systemctl daemon-reload
# systemctl amata pg_current_activity.service
O le mea lea, o le tala faasolopito o manatu e aoina i le tulaga o ata lona lua ma le lua. O le mea moni, afai e tuʻu mea uma, o le a vave ona faʻateleina laulau ma sili atu pe itiiti foi galuega aoga o le a le mafai.
E mana'omia le fa'atulagaina o fa'amaumauga fa'amaumauga.
3. Fa'amaumauga fa'amaumauga
Mo le fa'amaumauga, o laulau vaeluaga fa'amaumauga * e fa'aaogaina.
O vaega fou e faia i itula uma, a'o fa'amaumauga tuai e aveese mai le tala fa'asolopito * laulau, o le tele o le tala fa'asolopito * laulau e le suia tele ma le fa'aofiina o le saoasaoa e le fa'aleagaina i le taimi.
O le fausiaina o vaega fou e faia e le plpgsql function activity_hist.archive_current_activity. O le algorithm o le galuega e matua faigofie lava (faʻaaogaina le faʻataʻitaʻiga o le vaega mo le archive_pg_stat_activity table).
Fausia ma faatumu se vaega fou
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
);
Fausia fa'asino igoa
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 )'
);
Ave'ese fa'amaumauga tuai mai le laulau history_pg_stat_activity table
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
Ioe, mai lea taimi i lea taimi, o vaega tuai e tapeina e le manaʻomia.
Lipoti autu
O le mea moni, aisea ua faia ai nei mea uma? Ina ia maua lipoti e matua le manino e manatua ai le Oracle's AWR.
E taua le faʻaopoopoina ina ia mafai ona maua lipoti, e tatau ona e fausia se fesoʻotaʻiga i le va o le pg_stat_activity ma le pg_stat_statements view. E feso'ota'i laulau e ala i le fa'aopoopoina o se koluma 'queryid' ile 'history_pg_stat_activity', 'archive_pg_stat_activity' laulau. Ole auala ole fa'aopoopoina o se koluma e sili atu i le lautele o lenei tusiga ma o lo'o fa'amatalaina iinei − pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .
AOFAIGA TAIMI PPU MO FESILI
Talosaga:
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
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
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
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
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
O fesili autu o loʻo faʻaalia ma lipoti o loʻo iʻu mai ua faʻafaigofieina le olaga pe a suʻesuʻeina faʻalavelave faʻatinoga.
Faʻavae i luga o faʻamatalaga autu, e mafai ona e mauaina se lipoti e foliga tutusa ma le Oracle's AWR. Aotelega fa'ata'ita'iga lipoti
+------------------------------------------------ ----------------------------------- | LIPOTI FAAVAE MO GAOIOIGA MA FA'ATAALI.
Ia toe faauau. Soso'o i le laina o le fatuina o se tala fa'asolopito loka (pg_stat_locks), o se faʻamatalaga auiliili o le faagasologa o le faʻatumu o laulau.