ProHoster > Blog > Rianachd > Oidhirp ri analogue de ASH a chruthachadh airson PostgreSQL
Oidhirp ri analogue de ASH a chruthachadh airson PostgreSQL
Aithris dhuilgheadas
Gus ceistean PostgreSQL a bharrachadh, tha feum mòr air a’ chomas air eachdraidh gnìomhachd a sgrùdadh, gu sònraichte, feitheamh, glasan, agus staitistig bùird.
leudachadh pgsentinel :
«Chan eil a h-uile fiosrachadh cruinnichte air a stòradh ach ann an RAM, agus tha an ìre de chuimhne a thèid a chaitheamh air a riaghladh leis an àireamh de na clàran mu dheireadh a chaidh a stòradh.
Tha an raon queryid air a chur ris - an aon queryid bhon leudachadh pg_stat_statements (feumar ro-stàladh).«
Bhiodh seo, gu dearbh, na chuideachadh mòr, ach is e an rud as duilghe a’ chiad phuing. ”Chan eil a h-uile fiosrachadh cruinnichte air a stòradh ach ann an RAM ”, i.e. tha buaidh air a’ bhunait targaid. A bharrachd air an sin, chan eil eachdraidh glasaidh agus staitistig bùird ann. An fheadhainn sin. tha am fuasgladh sa chumantas neo-iomlan: “Chan eil pasgan deiseil ann airson a stàladh fhathast. Thathas a’ moladh na stòran a luchdachadh sìos agus an leabharlann a chruinneachadh thu fhèin. Feumaidh tu an-toiseach am pasgan “devel” a stàladh airson an fhrithealaiche agad agus an t-slighe gu pg_config a shuidheachadh anns an caochladair PATH.".
San fharsaingeachd, tha tòrr fealla-dhà ann, agus a thaobh droch stòran-dàta toraidh, is dòcha nach bi e comasach dad a dhèanamh leis an fhrithealaiche. Feumaidh sinn rudeigin a dhèanamh dhuinn fhìn a-rithist.
Rabhadh
Mar thoradh air an ìre caran mòr agus mar thoradh air an ùine deuchainn neo-choileanta, tha an artaigil gu ìre mhòr de nàdar fiosrachaidh, seach mar sheata de thràchdas agus toraidhean eadar-mheadhanach.
Thèid stuth nas mionaidiche ullachadh nas fhaide air adhart, ann am pàirtean
Dreach riatanasan airson fuasgladh
Tha e riatanach inneal a leasachadh a leigeas leat stòradh:
pg_stat_activity eachdraidh sealladh Eachdraidh glasaidh seisean a’ cleachdadh an t-sealladh pg_locks
Feum air fuasgladh- lùghdachadh a’ bhuaidh air an stòr-dàta targaidte.
Beachd coitcheann- tha an t-àidseant cruinneachadh dàta air a chuir air bhog chan ann san stòr-dàta targaid, ach san stòr-dàta sgrùdaidh mar sheirbheis siostaim. Tha, tha cuid de chall dàta comasach, ach chan eil seo deatamach airson aithris, ach chan eil buaidh sam bith air an stòr-dàta targaid a thaobh cuimhne agus àite diosc. Agus a thaobh cleachdadh amar ceangail, chan eil a’ bhuaidh air pròiseasan luchd-cleachdaidh ach glè bheag.
Ìrean buileachaidh
1.Seirbheis bùird
Bithear a’ cleachdadh sgeama air leth airson clàran a stòradh, gus nach dèan e iom-fhillte air mion-sgrùdadh nam prìomh chlàran a thathar a’ cleachdadh.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
Cudromach: Chan eil an sgeama air a chruthachadh anns an stòr-dàta targaid, ach anns an stòr-dàta sgrùdaidh.
pg_stat_activity eachdraidh sealladh
Tha clàr air a chleachdadh gus dealbhan gnàthaichte den t-sealladh pg_stat_activity a stòradh
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
);
Gus cuir a-steach a luathachadh - gun chlàran-amais no cuingealachaidhean.
Gus an eachdraidh fhèin a stòradh, thathas a’ cleachdadh clàr air a sgaradh:
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);
Leis nach eil riatanasan sam bith ann airson astar cuir a-steach sa chùis seo, chaidh cuid de chlàran-amais a chruthachadh gus cruthachadh aithisgean a luathachadh.
Eachdraidh casg seisean
Tha clàr air a chleachdadh gus dealbhan gnàthach de ghlasan seisean a stòradh:
gnìomhachd_hist.history_glasadh :
--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
);
Cuideachd, gus cuir a-steach a luathachadh, chan eil clàran-amais no cuingealachaidhean ann.
Gus an eachdraidh fhèin a stòradh, thathas a’ cleachdadh clàr air a sgaradh:
gnìomhachd_hist.archive_glasadh:
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);
Leis nach eil riatanasan sam bith ann airson astar cuir a-steach sa chùis seo, chaidh cuid de chlàran-amais a chruthachadh gus cruthachadh aithisgean a luathachadh.
2.Filling a-mach an-dràsta eachdraidh
Gus dealbhan seallaidh a chruinneachadh gu dìreach, thèid sgriobt bash a chleachdadh a ruitheas gnìomh plpgsql.
plpgsql Bidh an gnìomh dblink a’ faighinn cothrom air seallaidhean anns an stòr-dàta targaid agus a’ cuir a-steach sreathan ann an clàran seirbheis san stòr-dàta sgrùdaidh.
faigh_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;
Gus dealbhan seallaidh a chruinneachadh, thathas a’ cleachdadh an t-seirbheis systemd agus dà sgriobt:
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
Feuch an tòisich sinn air an t-seirbheis:
# systemctl daemon-reload
# systemctl tòisich pg_current_activity.service
Mar sin, tha eachdraidh bheachdan air a chruinneachadh ann an cruth dealbhan dàrna-às-diog. Gu dearbh, ma tha a h-uile càil air fhàgail mar a tha, bidh na clàran a 'meudachadh gu luath ann am meud agus bidh obair nas cinneasaiche no nas lugha do-dhèanta.
Tha e riatanach tasglann dàta a chuir air dòigh.
3. Eachdraidh tasglann
Airson tasglann, thathas a’ cleachdadh tasglann bùird air an sgaradh *.
Bidh sgaraidhean ùra air an cruthachadh gach uair a thìde, fhad ‘s a thèid seann dàta a thoirt air falbh bho na clàran eachdraidh *, agus mar sin chan eil meud nan clàran eachdraidh * ag atharrachadh mòran agus chan eil an astar cuir a-steach a’ dol sìos thar ùine.
Bithear a’ cruthachadh earrannan ùra leis a’ ghnìomh plpgsql activity_hist.archive_current_activity. Tha an algairim obrach gu math sìmplidh (a’ cleachdadh eisimpleir na h-earrainn airson a’ chlàr archive_pg_stat_activity).
Cruthaich agus lìon earrann ùr
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
);
Cruthachadh clàran-amais
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 )'
);
A' toirt air falbh seann dàta on chlàr history_pg_stat_activity
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
Gu dearbh, bho àm gu àm, thèid seann earrannan a dhubhadh às mar rud nach eil riatanach.
Aithisgean bunaiteach
Gu fìrinneach, carson a tha seo uile ga dhèanamh? Gus aithisgean fhaighinn a tha gu math neo-shoilleir a 'cuimhneachadh air AWR Oracle.
Tha e cudromach a chur ris gus am faigh thu aithisgean, feumaidh tu ceangal a thogail eadar na seallaidhean pg_stat_activity agus pg_stat_statements. Tha na clàran ceangailte le bhith a' cur colbh 'queryid' ris na clàran 'history_pg_stat_activity', 'archive_pg_stat_activity'. Tha an dòigh air luach colbh a chur ris nas fhaide na raon an artaigil seo agus tha e air a mhìneachadh an seo - pg_stat_statements +pg_stat_activity +loq_query = pg_ash? .
Àm CPU IOMLAN AIRSON CEISTEANAN
Iarrtas:
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
Tha na ceistean bunaiteach a chaidh a nochdadh agus na h-aithisgean a thàinig às a dhèidh mar-thà a’ dèanamh beatha fada nas fhasa nuair a thathar a’ dèanamh anailis air tachartasan coileanaidh.
Stèidhichte air ceistean bunaiteach, gheibh thu aithisg a tha gu math coltach ri Oracle's AWR. Eisimpleir geàrr-chunntas
+---------------------------------------------- ---------------------------------
| AITHISG DHAONTAICHTE AIRSON GNÌOMHACHADH AGUS FREAGRADH.
Ri leantainn. An ath loidhne tha cruthachadh eachdraidh glasaidh (pg_stat_locks), tuairisgeul nas mionaidiche air a’ phròiseas lìonadh chlàran.