PostgreSQL sorğularını optimallaşdırmaq üçün fəaliyyət tarixini, xüsusən gözləmələr, kilidlər və cədvəl statistikasını təhlil etmək bacarığı çox tələb olunur.
pgsentinel uzantısı :
«Bütün yığılmış məlumatlar yalnız RAM-da saxlanılır və istehlak olunan yaddaşın miqdarı son saxlanılan qeydlərin sayı ilə tənzimlənir.
Queryid sahəsi əlavə edildi - pg_stat_statements genişləndirməsindən eyni sorğuid (əvvəlcədən quraşdırma tələb olunur).«
Bu, əlbəttə ki, çox kömək edərdi, lakin ən çətin şey birinci nöqtədir”.Bütün yığılmış məlumatlar yalnız RAM-da saxlanılır ”, yəni. hədəf bazasına təsiri var. Bundan əlavə, heç bir kilid tarixçəsi və cədvəl statistikası yoxdur. Bunlar. həll ümumiyyətlə natamamdır: "Quraşdırma üçün hələ hazır paket yoxdur. Mənbələri yükləmək və kitabxananı özünüz toplamaq tövsiyə olunur. Əvvəlcə serveriniz üçün “devel” paketini quraşdırmalı və PATH dəyişənində pg_config yolunu təyin etməlisiniz.".
Ümumiyyətlə, bir çox təlaş var və ciddi istehsal verilənlər bazası vəziyyətində, serverlə heç bir şey etmək mümkün olmaya bilər. Yenidən özümüzə aid bir şey ortaya qoymalıyıq.
Uyarı
Kifayət qədər böyük həcmə və natamam sınaq müddətinə görə məqalə tezislər və ara nəticələr toplusu kimi, əsasən məlumat məqsədləri üçün hazırlanmışdır.
Daha ətraflı material daha sonra hissə-hissə hazırlanacaq
Həll üçün tələblər layihəsi
Saxlamağa imkan verən bir vasitə hazırlamaq lazımdır:
pg_stat_activity baxış tarixçəsi pg_locks görünüşündən istifadə edərək sessiya kilidi tarixçəsi
Həll tələbi– hədəf verilənlər bazasına təsiri minimuma endirmək.
Ümumi fikir– məlumat toplama agenti hədəf verilənlər bazasında deyil, monitorinq bazasında sistem xidməti kimi işə salınır. Bəli, bəzi məlumat itkisi mümkündür, lakin bu hesabat üçün kritik deyil, lakin yaddaş və disk sahəsi baxımından hədəf verilənlər bazasına heç bir təsiri yoxdur. Bir əlaqə hovuzundan istifadə edildiyi təqdirdə, istifadəçi proseslərinə təsir minimaldır.
İcra mərhələləri
1.Xidmət masaları
İstifadə olunan əsas cədvəllərin təhlilini çətinləşdirməmək üçün cədvəlləri saxlamaq üçün ayrıca bir sxem istifadə olunur.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
Əhəmiyyətli: Sxema hədəf verilənlər bazasında deyil, monitorinq verilənlər bazasında yaradılıb.
pg_stat_activity baxış tarixçəsi
Cədvəl pg_stat_activity görünüşünün cari şəkillərini saxlamaq üçün istifadə olunur
fəaliyyət_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
);
Daxil etməyi sürətləndirmək üçün - indekslər və ya məhdudiyyətlər yoxdur.
Tarixin özünü saxlamaq üçün bölünmüş cədvəldən istifadə olunur:
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);
Bu halda daxiletmə sürəti üçün tələblər olmadığı üçün hesabatların yaradılmasını sürətləndirmək üçün bəzi indekslər yaradılmışdır.
Sessiyanın bloklanması tarixçəsi
Cədvəl sessiya kilidlərinin cari görüntülərini saxlamaq üçün istifadə olunur:
fəaliyyət_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
);
Həmçinin, daxil etməyi sürətləndirmək üçün heç bir indeks və ya məhdudiyyət yoxdur.
Tarixin özünü saxlamaq üçün bölünmüş cədvəldən istifadə olunur:
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);
Bu halda daxiletmə sürəti üçün tələblər olmadığı üçün hesabatların yaradılmasını sürətləndirmək üçün bəzi indekslər yaradılmışdır.
2. Cari tarixçənin doldurulması
Görünüş anlarını birbaşa toplamaq üçün plpgsql funksiyasını işlədən bir bash skripti istifadə olunur.
plpgsql dblink funksiyası hədəf verilənlər bazasında baxışlara daxil olur və monitorinq verilənlər bazasındakı xidmət cədvəllərinə sətirlər əlavə edir.
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;
Görünüş görüntülərini toplamaq üçün systemd xidməti və iki skript istifadə olunur:
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
Xidmətə başlayaq:
# systemctl daemon-reload
# systemctl pg_current_activity.service işə salın
Beləliklə, baxışların tarixi saniyə-saniyə snapshotlar şəklində toplanır. Əlbəttə ki, hər şey olduğu kimi qalsa, masalar çox tez ölçüdə artacaq və daha çox və ya daha az məhsuldar iş qeyri-mümkün olacaq.
Məlumatların arxivləşdirilməsini təşkil etmək lazımdır.
3. Tarixi arxivləşdirmə
Arxivləşdirmə üçün bölünmüş cədvəllər arxivi* istifadə olunur.
Hər saat yeni arakəsmələr yaradılır, köhnə məlumatlar tarix* cədvəllərindən silinir, ona görə də tarix* cədvəllərinin ölçüsü çox dəyişmir və zamanla daxiletmə sürəti aşağı düşmür.
Yeni bölmələrin yaradılması plpgsql activity_hist.archive_current_activity funksiyası ilə həyata keçirilir. İşin alqoritmi çox sadədir (arşiv_pg_stat_activity cədvəli üçün bölmə nümunəsindən istifadə etməklə).
Yeni bölmə yaradın və doldurun
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
);
İndekslərin yaradılması
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 )'
);
History_pg_stat_activity cədvəlindən köhnə məlumatların silinməsi
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;
Təbii ki, vaxtaşırı köhnə bölmələr lazımsız olaraq silinir.
Əsas hesabatlar
Əslində bütün bunlar niyə edilir? Oracle-ın AWR-ni çox qeyri-müəyyən xatırladan hesabatları əldə etmək.
Əlavə etmək vacibdir ki, hesabatları qəbul etmək üçün pg_stat_activity və pg_stat_statements baxışları arasında əlaqə qurmalısınız. Cədvəllər 'history_pg_stat_activity', 'archive_pg_stat_activity' cədvəllərinə 'queryid' sütunu əlavə edilməklə əlaqələndirilir. Sütun dəyərinin əlavə edilməsi üsulu bu məqalənin əhatə dairəsi xaricindədir və burada təsvir edilmişdir - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .
SORULAR ÜÇÜN ÜMUMİ CPU VAXTI
Sorğu:
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
Göstərilən əsas sorğular və nəticədə əldə edilən hesabatlar performans insidentlərini təhlil edərkən həyatı çox asanlaşdırır.
Əsas sorğulara əsaslanaraq, Oracle-ın AWR-ni qeyri-müəyyən şəkildə xatırladan hesabat əldə edə bilərsiniz. Xülasə hesabat nümunəsi
+------------------------------------------------ ---------------------------------- | FƏALİYYƏT VƏ GÖZLƏMƏLƏR ÜÇÜN KONSOLİDE HESABAT.
Ardı var. Növbəti sırada, cədvəllərin doldurulması prosesinin daha ətraflı təsviri olan kilid tarixçəsinin (pg_stat_locks) yaradılmasıdır.