Er mwyn gwneud y gorau o ymholiadau PostgreSQL, mae angen y gallu i ddadansoddi hanes gweithgaredd, yn benodol, amseroedd aros, cloeon ac ystadegau bwrdd.
estyniad pgsentinel :
«Mae'r holl wybodaeth gronedig yn cael ei storio mewn RAM yn unig, ac mae faint o gof a ddefnyddir yn cael ei reoleiddio gan nifer y cofnodion storio diwethaf.
Ychwanegir y maes queryid - yr un queryid o'r estyniad pg_stat_statements (angen gosod ymlaen llaw).«
Byddai hyn, wrth gwrs, yn helpu llawer, ond y peth mwyaf trafferthus yw’r pwynt cyntaf.”Mae'r holl wybodaeth gronedig yn cael ei storio mewn RAM yn unig ”, h.y. mae effaith ar y sylfaen darged. Yn ogystal, nid oes hanes clo ac ystadegau tabl. Y rhai. mae'r ateb yn gyffredinol yn anghyflawn: “Nid oes pecyn parod i'w osod eto. Awgrymir lawrlwytho'r ffynonellau a rhoi'r llyfrgell at ei gilydd eich hun. Yn gyntaf mae angen i chi osod y pecyn “datblygu” ar gyfer eich gweinydd a gosod y llwybr i pg_config yn y newidyn PATH.".
Yn gyffredinol, mae llawer o ffwdan, ac yn achos cronfeydd data cynhyrchu difrifol, efallai na fydd yn bosibl gwneud unrhyw beth gyda'r gweinydd. Mae angen i ni feddwl am rywbeth ein hunain eto.
Rhybudd.
Oherwydd y cyfaint eithaf mawr ac oherwydd y cyfnod profi anghyflawn, mae'r erthygl at ddibenion gwybodaeth yn bennaf, yn hytrach fel set o draethodau ymchwil a chanlyniadau canolradd.
Bydd deunydd manylach yn cael ei baratoi yn ddiweddarach, mewn rhannau
Gofynion drafft ar gyfer y datrysiad
Mae angen datblygu offeryn sy'n eich galluogi i storio:
pg_stat_gweithgaredd hanes gweld Hanes clo sesiwn gan ddefnyddio'r olwg pg_locks
Gofyniad datrysiad–lleihau'r effaith ar y gronfa ddata darged.
Syniad cyffredinol– caiff yr asiant casglu data ei lansio nid yn y gronfa ddata darged, ond yn y gronfa ddata fonitro fel gwasanaeth system. Ydy, mae rhywfaint o golli data yn bosibl, ond nid yw hyn yn hanfodol ar gyfer adrodd, ond nid oes unrhyw effaith ar y gronfa ddata darged o ran cof a gofod disg. Ac yn achos defnyddio cronfa gysylltu, mae'r effaith ar brosesau defnyddwyr yn fach iawn.
Camau gweithredu
Tablau 1.Service
Defnyddir sgema ar wahân i storio tablau, er mwyn peidio â chymhlethu'r dadansoddiad o'r prif dablau a ddefnyddir.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
Pwysig: Nid yn y gronfa ddata darged y caiff y sgema ei greu, ond yn y gronfa ddata monitro.
pg_stat_gweithgaredd hanes gweld
Defnyddir y tabl pg_stat_activity i storio cipluniau cyfredol o'r olygfa
gweithgaredd_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
);
Er mwyn cyflymu'r gosodiad - dim mynegeion na chyfyngiadau.
I storio'r hanes ei hun, defnyddir tabl rhanedig:
gweithgaredd_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);
Gan nad oes unrhyw ofynion ar gyfer cyflymder mewnosod yn yr achos hwn, mae rhai mynegeion wedi'u creu i gyflymu'r broses o greu adroddiadau.
Hanes blocio sesiynau
Defnyddir tabl i storio cipluniau cyfredol o gloeon sesiwn:
gweithgaredd_hist.history_cloi :
--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
);
Hefyd, er mwyn cyflymu'r gosodiad, nid oes unrhyw fynegeion na chyfyngiadau.
I storio'r hanes ei hun, defnyddir tabl rhanedig:
gweithgaredd_hist.archif_cloi:
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);
Gan nad oes unrhyw ofynion ar gyfer cyflymder mewnosod yn yr achos hwn, mae rhai mynegeion wedi'u creu i gyflymu'r broses o greu adroddiadau.
2.Llenwi'r hanes cyfredol
I gasglu cipluniau gweld yn uniongyrchol, defnyddir sgript bash sy'n rhedeg y swyddogaeth plpgsql.
plpgsql Mae'r swyddogaeth dblink yn cyrchu golygfeydd yn y gronfa ddata darged ac yn mewnosod rhesi mewn tablau gwasanaeth yn y gronfa ddata monitro.
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;
I gasglu cipluniau gweld, defnyddir y gwasanaeth systemd a dwy sgript:
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
Felly, cesglir hanes golygfeydd ar ffurf cipluniau eiliad-wrth-eiliad. Wrth gwrs, os bydd popeth ar ôl fel y mae, bydd y tablau'n cynyddu'n gyflym iawn mewn maint a bydd mwy neu lai o waith cynhyrchiol yn dod yn amhosibl.
Mae angen trefnu archifo data.
3. Hanes archifo
Ar gyfer archifo, defnyddir archif tablau rhanedig*.
Mae rhaniadau newydd yn cael eu creu bob awr, tra bod hen ddata yn cael ei dynnu o'r tablau hanes*, felly nid yw maint y tablau hanes* yn newid llawer ac nid yw'r cyflymder mewnosod yn diraddio dros amser.
Perfformir creu adrannau newydd gan y swyddogaeth plpgsql activity_hist.archive_current_activity. Mae'r algorithm gwaith yn syml iawn (gan ddefnyddio enghraifft yr adran ar gyfer y tabl gweithgaredd archive_pg_stat_activity).
Creu a llenwi adran newydd
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
);
Creu mynegeion
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 )'
);
Tynnu hen ddata o'r tabl gweithgaredd history_pg_stat_activity
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
Wrth gwrs, o bryd i'w gilydd, caiff hen adrannau eu dileu fel rhai diangen.
Adroddiadau sylfaenol
A dweud y gwir, pam mae hyn i gyd yn cael ei wneud? I gael adroddiadau yn annelwig iawn atgoffa rhywun o Oracle's AWR.
Mae'n bwysig ychwanegu bod angen i chi adeiladu cysylltiad rhwng y golygfeydd pg_stat_activity a pg_stat_statements er mwyn derbyn adroddiadau. Cysylltir y tablau drwy ychwanegu colofn 'queryid' at y tablau 'history_pg_stat_activity', 'archive_pg_stat_activity'. Mae'r dull o ychwanegu gwerth colofn y tu hwnt i gwmpas yr erthygl hon ac fe'i disgrifir yma − pg_stat_statements +pg_stat_activity +loq_query = pg_ash? .
CYFANSWM AMSER CPU AR GYFER YMHOLIADAU
Cais:
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
Mae'r ymholiadau sylfaenol a ddangoswyd a'r adroddiadau dilynol eisoes yn gwneud bywyd yn llawer haws wrth ddadansoddi digwyddiadau perfformiad.
Yn seiliedig ar ymholiadau sylfaenol, gallwch gael adroddiad sy'n ymdebygu'n fras i Oracle's AWR. Enghraifft o adroddiad cryno
+-------------------------------------------- --------------------------------- | ADRODDIAD CYFUNOL AR GYFER GWEITHGAREDD AC AROS.
I'w barhau. Nesaf yn y llinell mae creu hanes clo (pg_stat_locks), disgrifiad manylach o'r broses o lenwi tablau.