He ngana ki te hanga i tetahi tairitenga o ASH mo PostgreSQL
Kaupapa raru
Hei arotau i nga patai a PostgreSQL, ko te kaha ki te tātari i nga hitori o nga mahi, ina koa, te tatari, te raka, me nga tatauranga ripanga, me tino hiahiatia.
toronga pgsentinel :
«Ko nga korero katoa kua kohia ka rongoa ki roto i te RAM anake, a ko te nui o te mahara ka whakatauhia e te maha o nga rekoata penapena whakamutunga.
Kua tāpirihia te āpure pātai - he ōrite te id pātai mai i te toronga pg_stat_statements (me hiahia i mua i te whakaurunga).«
Ko te tikanga, ka awhina nui tenei, engari ko te mea tino raru ko te kaupapa tuatahi.Ko nga korero whakaemi katoa ka rongoa ki te RAM anake ”, i.e. he paanga ki runga i te turanga whaainga. I tua atu, kaore he hitori raka me nga tatauranga tepu. Ko era. ko te otinga ko te korero kaore i oti: "Karekau he kete kua rite mo te whakaurunga. Ko te whakaaro me tango ake nga puna me te whakahiato i te whare pukapuka. Me whakauru tuatahi koe i te kete "devel" mo to kaimau ka tautuhi i te ara ki te pg_config i roto i te taurangi PATH.".
I te nuinga o te waa, he nui te ngangau, a, mo te take o nga papaa korero whakaputa nui, kaore pea e taea te mahi i tetahi mea me te tūmau. Me whai whakaaro ano tatou ki a tatou ano.
Whakatupato.
Na te nui o te pukapuka me te kore o te waa whakamatautau, ko te tuhinga te nuinga o te ahua korero, engari he huinga tuhinga me nga hua takawaenga.
Ko etahi atu korero taipitopito ka whakaritea i muri mai, hei waahanga
Nga whakaritenga tauira mo te otinga
He mea tika ki te whakawhanake i tetahi taputapu ka taea e koe te rokiroki:
pg_stat_activity view history Hītori maukati wātū mā te tiro pg_locks
Te whakaritenga otinga–whakaitihia te awenga ki runga i te putunga raraunga kua tohua.
Whakaaro whānui– ka whakarewahia te kaihoko kohikohi raraunga ehara i te papaaarangi kua tohua, engari i roto i te paataka aroturuki hei ratonga systemd. Ae, ka taea etahi ngaronga raraunga, engari ehara tenei i te mea nui mo te ripoata, engari kaore he paanga ki te papaaarangi kua whakaritea mo te mahara me te mokowā kōpae. A, mo te whakamahi i te puna hononga, he iti noa te paanga ki nga tukanga kaiwhakamahi.
Nga wahanga whakatinana
1.Tepu ratonga
Ka whakamahia he aronuinga motuhake hei penapena i nga ripanga, kia kore ai e whakararu i te tātaritanga o nga ripanga matua i whakamahia.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
He mea nui: Karekau te aronuinga i hangaia i roto i te papaaarangi kua whakaritea, engari i roto i te paataka aroturuki.
pg_stat_activity view history
Ka whakamahia he ripanga hei rokiroki i nga whakaahua o naianei o te tirohanga 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
);
Kia tere ake te whakauru - kaore he tohu, he here ranei.
Hei rokiroki i te hitori ake, ka whakamahia he ripanga wehewehe:
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);
Mai i tenei keehi kaore he whakaritenga mo te tere whakauru, kua hangaia etahi tohu hei tere ake i te hanganga o nga purongo.
Hītori ārai wātū
Ka whakamahia he ripanga hei rokiroki i nga whakaahua o naianei o nga raka o te waahi:
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
);
Ano, kia tere ake te whakauru, kaore he tohu tohu, he here ranei.
Hei rokiroki i te hitori ake, ka whakamahia he ripanga wehewehe:
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);
Mai i tenei keehi kaore he whakaritenga mo te tere whakauru, kua hangaia etahi tohu hei tere ake i te hanganga o nga purongo.
2.Whakakī i te hītori o nāianei
Hei kohikohi tika i nga whakaahua tirohanga, ka whakamahia he tuhinga bash e whakahaere ana i te mahi plpgsql.
plpgsql Ka uru atu te mahi dblink ki nga tirohanga i roto i te papaaarangi kua tohua me te whakauru i nga rarangi ki nga ripanga ratonga i roto i te papaa raraunga aroturuki.
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;
Hei kohikohi i nga whakaahua tirohanga, ka whakamahia te ratonga systemd me nga tuhinga e rua:
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
Tautapa motika ki nga tuhinga:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service
Me timata te mahi:
# systemctl daemon-reload
# systemctl timata pg_current_activity.service
No reira, ka kohia te hitori o nga tirohanga ki te ahua o nga whakaahua tuarua-a-rua. Ko te tikanga, ki te waiho tonu nga mea katoa, ka tere te piki o nga teepu ki te rahi, ka nui ake ranei nga mahi whai hua ka kore e taea.
He mea tika ki te whakarite i te whakapuranga raraunga.
3. Puranga hitori
Mo te whakapuranga, ka whakamahia te puranga ripanga wehewehea*.
Ka hangaia nga wehewehenga hou ia haora, ka tangohia nga raraunga tawhito mai i nga ripanga hitori*, no reira karekau e rerekee te rahi o nga ripanga hitori* ka kore e heke te tere whakauru i roto i te waa.
Ko te hanganga o nga waahanga hou ka mahia e te mahi plpgsql activity_hist.archive_current_activity. He tino ngawari te algorithm o te mahi (ma te whakamahi i te tauira o te waahanga mo te ripanga archive_pg_stat_activity).
Waihangahia ka whakakiia he waahanga hou
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
);
Te hanga taupū
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 )'
);
Te tango i nga raraunga tawhito mai i te ripanga history_pg_stat_activity
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
Ko te tikanga, mai i tera wa ki tera wa, ka whakakorehia nga waahanga tawhito hei mea kore.
Nga purongo taketake
Inaa, he aha enei mea katoa i mahia ai? Ki te whiwhi i nga purongo e tino whakamaumahara ana ki te AWR o Oracle.
He mea nui ki te taapiri kia whiwhi purongo, me hanga hononga i waenga i nga tirohanga pg_stat_activity me pg_stat_statements. Ka honoa nga ripanga ma te taapiri i te tīwae 'queryid' ki te ripanga 'history_pg_stat_activity', 'archive_pg_stat_activity'. Ko te tikanga mo te taapiri uara pou kei tua atu i te whanuitanga o tenei tuhinga, ka whakaahuatia ki konei − pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .
TAPEKE TE WATA PTM MO NGA Uiui
Tono :
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
Ko nga patai matua kua whakaatuhia me nga purongo kua puta kua tino maamaa ake te oranga i te wa e tarai ana i nga maiki mahi.
I runga i nga patai taketake, ka taea e koe te tiki purongo e rite ana ki te AWR o Oracle. He tauira purongo whakarāpopoto
+------------------------------------------------ ----------------------------------- | RIPOATA WHAKATAHI MO NGA MAHI ME NGA WANANGA.
Ka haere tonu. I muri mai ko te hanga i te hitori raka (pg_stat_locks), he whakamaarama taipitopito mo te tukanga o te whakakii i nga ripanga.