Tentattiv biex jinħoloq analogu ta 'ASH għal PostgreSQL
Dikjarazzjoni tal-problema
Biex jiġu ottimizzati l-mistoqsijiet PostgreSQL, il-kapaċità li tanalizza l-istorja tal-attività, b'mod partikolari, stennija, serraturi, u statistika tat-tabella, hija meħtieġa ħafna.
estensjoni pgsentinel :
«L-informazzjoni kollha akkumulata hija maħżuna biss fir-RAM, u l-ammont ta 'memorja kkunsmat huwa rregolat min-numru ta' l-aħħar rekords maħżuna.
Il-qasam queryid huwa miżjud - l-istess queryid mill-estensjoni pg_stat_statements (meħtieġa qabel l-installazzjoni).«
Dan, ovvjament, jgħin ħafna, iżda l-iktar ħaġa li tfixkel hija l-ewwel punt.”L-informazzjoni kollha akkumulata hija maħżuna biss fir-RAM ”, i.e. hemm impatt fuq il-bażi tal-mira. Barra minn hekk, m'hemm l-ebda storja ta 'lock u statistika tat-tabella. Dawk. is-soluzzjoni hija ġeneralment mhux kompluta: "S'issa m'hemm l-ebda pakkett lest għall-installazzjoni. Huwa ssuġġerit li tniżżel is-sorsi u tiġbor il-librerija lilek innifsek. L-ewwel trid tinstalla l-pakkett "devel" għas-server tiegħek u ssettja t-triq għal pg_config fil-varjabbli PATH.".
B'mod ġenerali, hemm ħafna fuss, u fil-każ ta 'databases ta' produzzjoni serji, jista 'ma jkunx possibbli li tagħmel xejn mas-server. Irridu nerġgħu noħorġu b’xi ħaġa tagħna stess.
Twissija
Minħabba l-volum pjuttost kbir u minħabba l-perjodu ta 'ttestjar mhux komplut, l-artikolu huwa prinċipalment ta' natura informattiva, pjuttost bħala sett ta 'teżijiet u riżultati intermedji.
Materjal aktar dettaljat se jitħejja aktar tard, f'partijiet
Abbozz ta' rekwiżiti għas-soluzzjoni
Huwa meħtieġ li tiżviluppa għodda li tippermettilek taħżen:
pg_stat_activity tara l-istorja L-istorja tal-illokkjar tas-sessjoni bl-użu tal-veduta pg_locks
Rekwiżit tas-soluzzjoni– jimminimizza l-impatt fuq id-database fil-mira.
Idea ġenerali– l-aġent tal-ġbir tad-dejta jitnieda mhux fid-database fil-mira, iżda fid-database tal-monitoraġġ bħala servizz systemd. Iva, xi telf ta 'data huwa possibbli, iżda dan mhuwiex kritiku għar-rappurtar, iżda m'hemm l-ebda impatt fuq id-database fil-mira f'termini ta' memorja u spazju tad-diska. U fil-każ tal-użu ta 'pool ta' konnessjoni, l-impatt fuq il-proċessi tal-utent huwa minimu.
Stadji ta' implimentazzjoni
1.Tabelli tas-servizz
Skema separata tintuża biex taħżen it-tabelli, sabiex ma tikkomplikax l-analiżi tat-tabelli prinċipali użati.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
Importanti: L-iskema mhix maħluqa fid-database fil-mira, iżda fid-database tal-monitoraġġ.
pg_stat_activity tara l-istorja
Tabella tintuża biex taħżen snapshots kurrenti tal-veduta 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
);
Biex tħaffef l-inserzjoni - l-ebda indiċi jew restrizzjonijiet.
Biex taħżen l-istorja nnifisha, tintuża tabella maqsuma:
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);
Peress li f'dan il-każ m'hemm l-ebda rekwiżiti għall-veloċità tal-inserzjoni, inħolqu xi indiċi biex jitħaffef il-ħolqien tar-rapporti.
Storja tal-imblukkar tas-sessjoni
Tabella tintuża biex taħżen snapshots kurrenti tas-serraturi tas-sessjoni:
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
);
Ukoll, biex titħaffef l-inserzjoni, m'hemm l-ebda indiċi jew restrizzjonijiet.
Biex taħżen l-istorja nnifisha, tintuża tabella maqsuma:
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);
Peress li f'dan il-każ m'hemm l-ebda rekwiżiti għall-veloċità tal-inserzjoni, inħolqu xi indiċi biex jitħaffef il-ħolqien tar-rapporti.
2.Filling l-istorja kurrenti
Biex tiġbor direttament snapshots tal-vista, jintuża script bash li jmexxi l-funzjoni plpgsql.
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;
Biex jinġabru snapshots tal-vista, jintużaw is-servizz systemd u żewġ skripts:
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
Għalhekk, l-istorja tal-vista tinġabar fil-forma ta 'snapshots sekonda b'sekonda. Naturalment, jekk kollox jitħalla kif inhu, it-tabelli se jiżdiedu malajr ħafna fid-daqs u xogħol xi ftit jew wisq produttiv isir impossibbli.
Huwa meħtieġ li tiġi organizzata l-arkivjar tad-dejta.
3. Arkivjar tal-istorja
Għall-arkivjar, jintużaw arkivji ta' tabelli diviżorji*.
Diviżorji ġodda jinħolqu kull siegħa, filwaqt li dejta l-antika titneħħa mit-tabelli tal-istorja*, għalhekk id-daqs tat-tabelli tal-istorja* ma jinbidilx ħafna u l-veloċità tal-inserzjoni ma tiddegradax maż-żmien.
Il-ħolqien ta' sezzjonijiet ġodda jitwettaq mill-funzjoni plpgsql activity_hist.archive_current_activity. L-algoritmu tax-xogħol huwa sempliċi ħafna (bl-użu tal-eżempju tat-taqsima għat-tabella archive_pg_stat_activity).
Oħloq u imla taqsima ġdida
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
);
Ħolqien ta' indiċi
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 )'
);
Tneħħija ta' data antika mit-tabella history_pg_stat_activity
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
Naturalment, minn żmien għal żmien, sezzjonijiet qodma jitħassru bħala mhux meħtieġa.
Rapporti bażiċi
Fil-fatt, għaliex qed isir dan kollu? Biex tikseb rapporti vagament reminixxenti ta 'AWR Oracle.
Huwa importanti li żżid li sabiex tirċievi rapporti, għandek bżonn tibni konnessjoni bejn il-fehmiet pg_stat_activity u pg_stat_statements. It-tabelli huma marbuta billi tiżdied kolonna 'queryid' mat-tabelli 'history_pg_stat_activity', 'archive_pg_stat_activity'. Il-metodu ta 'żieda ta' valur ta 'kolonna huwa lil hinn mill-ambitu ta' dan l-artikolu u huwa deskritt hawn - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .
ĦIN TOTALI tas-CPU GĦAL MISTOQSIJIET
Talba:
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
Il-mistoqsijiet bażiċi murija u r-rapporti li jirriżultaw diġà jagħmlu l-ħajja ħafna aktar faċli meta jiġu analizzati l-inċidenti tal-prestazzjoni.
Ibbażat fuq mistoqsijiet bażiċi, tista 'tikseb rapport li jixbah b'mod vag l-AWR ta' Oracle. Eżempju ta' rapport sommarju
+------------------------------------------------ ----------------------------------- | RAPPORT KONSOLIDAT GĦALL-ATTIVITÀ U STENNIJIET.
Ser jitkompla aktar il-quddiem. Li jmiss fil-linja hija l-ħolqien ta 'storja serratura (pg_stat_locks), deskrizzjoni aktar dettaljata tal-proċess ta' mili tabelli.