pgsentinel itẹsiwaju :
«Gbogbo alaye ti a kojọpọ ti wa ni ipamọ nikan ni Ramu, ati iye iranti ti o jẹ ni ofin nipasẹ nọmba awọn igbasilẹ ti o fipamọ kẹhin.
Aaye queryid ti wa ni afikun - ibeere kanna lati pg_stat_statements itẹsiwaju (fifi sori ẹrọ nilo).«
Eyi, nitorinaa, yoo ṣe iranlọwọ pupọ, ṣugbọn ohun ti o ni wahala julọ ni aaye akọkọ. ”Gbogbo alaye akojo ti wa ni ipamọ nikan ni Ramu ”, i.e. ipa kan wa lori ipilẹ ibi-afẹde. Ni afikun, ko si itan titiipa ati awọn iṣiro tabili. Awon. Ojutu ni gbogbogbo n sọrọ pe: “Ko si package ti o ṣetan fun fifi sori ẹrọ sibẹsibẹ. O daba lati ṣe igbasilẹ awọn orisun ati pejọ ile-ikawe funrararẹ. O nilo akọkọ lati fi idii “idagbasoke” sori olupin rẹ ki o ṣeto ọna si pg_config ni oniyipada PATH.".
Ni gbogbogbo, ariwo pupọ wa, ati ninu ọran ti awọn apoti isura infomesonu iṣelọpọ pataki, o le ma ṣee ṣe lati ṣe ohunkohun pẹlu olupin naa. A nilo lati tun wa pẹlu nkan ti ara wa lẹẹkansi.
Ikilo
Nitori iwọn didun ti o tobi pupọ ati nitori akoko idanwo ti ko pe, nkan naa jẹ nipataki ti iseda alaye, dipo bi eto awọn iwe-ọrọ ati awọn abajade agbedemeji.
Awọn ohun elo alaye diẹ sii yoo pese sile nigbamii, ni awọn apakan
Akọpamọ awọn ibeere fun ojutu
O jẹ dandan lati ṣe agbekalẹ ohun elo kan ti o fun ọ laaye lati fipamọ:
itan iwo pg_stat_activity Itan titiipa igba nipa lilo wiwo pg_locks
Ibeere ojutu–dinku ipa lori ibi ipamọ data ibi-afẹde.
Gbogbogbo agutan- Aṣoju ikojọpọ data ti ṣe ifilọlẹ kii ṣe ni ibi ipamọ data ibi-afẹde, ṣugbọn ninu aaye data ibojuwo bi iṣẹ eto kan. Bẹẹni, diẹ ninu awọn ipadanu data ṣee ṣe, ṣugbọn eyi kii ṣe pataki fun ijabọ, ṣugbọn ko si ipa lori ibi ipamọ data ibi-afẹde ni awọn ofin ti iranti ati aaye disk. Ati ninu ọran ti lilo adagun asopọ kan, ipa lori awọn ilana olumulo jẹ iwonba.
Awọn ipele imuse
1.Service tabili
Ilana ti o yatọ ni a lo lati tọju awọn tabili, ki o má ba ṣe idiju iṣiro ti awọn tabili akọkọ ti a lo.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
Pàtàkì: Ilana naa ko ṣẹda ni ibi ipamọ data ibi-afẹde, ṣugbọn ninu aaye data ibojuwo.
itan iwo pg_stat_activity
Tabili kan ni a lo lati tọju awọn aworan iwoyi lọwọlọwọ ti wiwo pg_stat_activity
aṣayan iṣẹ-ṣiṣe.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
);
Lati titẹ sii titẹ sii - ko si awọn atọka tabi awọn ihamọ.
Lati tọju itan funrararẹ, tabili ti o pin ni a lo:
aṣayan iṣẹ-ṣiṣe_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);
Niwon ninu ọran yii ko si awọn ibeere fun iyara titẹ sii, diẹ ninu awọn atọka ti ṣẹda lati mu ki awọn ẹda ti awọn iroyin pọ si.
Itan idinamọ igba
Tabili kan ni a lo lati tọju awọn aworan iwoyi lọwọlọwọ ti awọn titiipa igba:
aṣayan iṣẹ-ṣiṣe.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
);
Pẹlupẹlu, lati mu titẹ sii, ko si awọn atọka tabi awọn ihamọ.
Lati tọju itan funrararẹ, tabili ti o pin ni a lo:
function_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);
Niwon ninu ọran yii ko si awọn ibeere fun iyara titẹ sii, diẹ ninu awọn atọka ti ṣẹda lati mu ki awọn ẹda ti awọn iroyin pọ si.
2.Filling jade ti isiyi itan
Lati gba awọn aworan iwoye taara, a lo iwe afọwọkọ bash ti o nṣiṣẹ iṣẹ plpgsql.
plpgsql Iṣẹ dblink n wọle si awọn iwo ni ibi ipamọ data ibi-afẹde ati fi awọn ori ila sinu awọn tabili iṣẹ ni ibi ipamọ data ibojuwo.
gba_akitiyan_current.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;
Lati gba awọn aworan iwoye, iṣẹ eto ati awọn iwe afọwọkọ meji ni a lo:
pg_current_activity.iṣẹ
# /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
Fi awọn ẹtọ si awọn iwe afọwọkọ:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service
Jẹ ki a bẹrẹ iṣẹ naa:
# systemctl daemon-reload
# systemctl bẹrẹ pg_current_activity.service
Nitorinaa, itan-akọọlẹ ti awọn iwo ni a gba ni irisi awọn aworan iwoye keji-si-keji. Nitoribẹẹ, ti ohun gbogbo ba fi silẹ bi o ti jẹ, awọn tabili yoo yarayara pọ si ni iwọn ati diẹ sii tabi kere si iṣẹ iṣelọpọ yoo di eyiti ko ṣeeṣe.
O jẹ dandan lati ṣeto fifipamọ data.
3. archiving itan
Fun fifipamọ, ibi ipamọ awọn tabili ti a pin * ni a lo.
Awọn ipin tuntun ni a ṣẹda ni gbogbo wakati, lakoko ti a ti yọ data atijọ kuro ninu awọn tabili itan *, nitorinaa iwọn awọn tabili itan * ko yipada pupọ ati iyara fifi sii ko dinku ni akoko pupọ.
Ṣiṣẹda ti awọn apakan titun jẹ ṣiṣe nipasẹ iṣẹ iṣẹ plpgsql iṣẹ_hist.archive_current_activity. Algoridimu ti iṣẹ rọrun pupọ (lilo apẹẹrẹ ti apakan fun tabili pamosi_pg_stat_activity).
Ṣẹda ati ki o fọwọsi jade titun kan apakan
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
);
Ṣiṣẹda awọn atọka
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 )'
);
Yiyọ data atijọ kuro lati itan_pg_stat_activity tabili
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
Nitoribẹẹ, lati igba de igba, awọn apakan atijọ ti paarẹ bi ko ṣe pataki.
Awọn iroyin ipilẹ
Na nugbo tọn, naegbọn ehe do to yinyin wiwà? Lati gba awọn ijabọ aiduro pupọ ti Oracle's AWR.
O ṣe pataki lati ṣafikun pe lati le gba awọn ijabọ, o nilo lati kọ asopọ laarin pg_stat_activity ati awọn iwo pg_stat_statements. Awọn tabili naa jẹ asopọ nipasẹ fifi iwe 'queryid' kun si awọn tabili 'history_pg_stat_activity', 'archive_pg_stat_activity'. Ọna ti fifi iye iwe kun kọja ipari ti nkan yii ati pe a ṣe apejuwe rẹ nibi - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .
Lapapọ Sipiyu TIME FUN ibeere
Ibere:
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
Awọn ibeere ipilẹ ti o han ati awọn ijabọ abajade tẹlẹ jẹ ki igbesi aye rọrun pupọ nigbati o ṣe itupalẹ awọn iṣẹlẹ iṣẹ.
Da lori awọn ibeere ipilẹ, o le gba ijabọ kan ti o jọra AWR Oracle. Apeere Iroyin Lakotan
------------------------------------------------ ---------------------------------- | Ijabọ Iṣọkan fun Iṣe ati awọn idaduro.
A tun ma a se ni ojo iwaju. Nigbamii ni ila ni ṣiṣẹda itan titiipa (pg_stat_locks), apejuwe alaye diẹ sii ti ilana ti awọn tabili kikun.