Taarifa ya tatizo
Ili kuboresha hoja za PostgreSQL, uwezo wa kuchanganua historia ya shughuli, haswa, kusubiri, kufuli, na takwimu za jedwali, inahitajika sana.
Fursa zinazopatikana
: suluhisho la kupendeza sana, lakini hakuna historia ya pg_stat_activity na pg_locks.
:
Β«Taarifa zote zilizokusanywa zimehifadhiwa tu kwenye RAM, na kiasi kinachotumiwa cha kumbukumbu kinadhibitiwa na idadi ya rekodi zilizohifadhiwa mwisho.
Sehemu ya queryid imeongezwa - swali sawa kutoka kwa kiendelezi cha pg_stat_statements (usakinishaji wa awali unahitajika).Β«
Hii, bila shaka, ingesaidia sana, lakini jambo gumu zaidi ni hoja ya kwanza.Taarifa zote zilizokusanywa zimehifadhiwa kwenye RAM pekee β, yaani. kuna athari kwenye msingi wa lengo. Kwa kuongeza, hakuna historia ya kufuli na takwimu za meza. Wale. suluhu kwa ujumla ni kusema haijakamilika: "Bado hakuna kifurushi kilichotengenezwa tayari kwa usakinishaji. Inapendekezwa kupakua vyanzo na kukusanya maktaba mwenyewe. Kwanza unahitaji kusakinisha kifurushi cha "kuza" kwa seva yako na uweke njia ya pg_config katika utofauti wa PATH.".
Kwa ujumla, kuna ugomvi mwingi, na katika kesi ya hifadhidata kubwa za uzalishaji, inaweza kuwa haiwezekani kufanya chochote na seva. Tunahitaji kuja na kitu chetu tena.
Onyo
Kwa sababu ya ujazo mkubwa na kwa sababu ya muda wa majaribio kutokamilika, kifungu hicho kimsingi ni cha habari, badala yake kama seti ya nadharia na matokeo ya kati.
Nyenzo za kina zaidi zitatayarishwa baadaye, kwa sehemu
Mahitaji ya rasimu ya suluhisho
Inahitajika kuunda zana ambayo hukuruhusu kuhifadhi:
pg_stat_activity historia ya kutazama
Historia ya kufunga kikao kwa kutumia mwonekano wa pg_locks
Mahitaji ya suluhisho-punguza athari kwenye hifadhidata inayolengwa.
Wazo la jumla- wakala wa kukusanya data amezinduliwa si katika hifadhidata inayolengwa, lakini katika hifadhidata ya ufuatiliaji kama huduma ya mfumo. Ndio, upotezaji fulani wa data inawezekana, lakini hii sio muhimu kwa kuripoti, lakini hakuna athari kwenye hifadhidata inayolengwa kwa suala la kumbukumbu na nafasi ya diski. Na katika kesi ya kutumia bwawa la uunganisho, athari kwa michakato ya mtumiaji ni ndogo.
Hatua za utekelezaji
1.Meza za huduma
Schema tofauti hutumiwa kuhifadhi meza, ili usifanye uchambuzi wa meza kuu zinazotumiwa.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
Muhimu: Ratiba haijaundwa katika hifadhidata inayolengwa, lakini katika hifadhidata ya ufuatiliaji.
pg_stat_activity historia
Jedwali la pg_stat_activity linatumika kuhifadhi vijipicha vya sasa vya mwonekano
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
);Ili kuharakisha uingizaji - hakuna indexes au vikwazo.
Ili kuhifadhi historia yenyewe, meza iliyogawanywa hutumiwa:
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);Kwa kuwa katika kesi hii hakuna mahitaji ya kasi ya kuingizwa, baadhi ya indexes zimeundwa ili kuharakisha kuundwa kwa ripoti.
Historia ya kuzuia kipindi
Jedwali linatumika kuhifadhi vijipicha vya sasa vya kufuli za kipindi:
shughuli_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
);Pia, ili kuharakisha uingizaji, hakuna indexes au vikwazo.
Ili kuhifadhi historia yenyewe, meza iliyogawanywa hutumiwa:
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);Kwa kuwa katika kesi hii hakuna mahitaji ya kasi ya kuingizwa, baadhi ya indexes zimeundwa ili kuharakisha kuundwa kwa ripoti.
2.Kujaza historia ya sasa
Ili kukusanya vijipicha vya kutazama moja kwa moja, hati ya bash inatumiwa inayoendesha kazi ya plpgsql.
pata_shughuli_ya_sasa.sh
#!/bin/bash
#########################################################
#get_current_activity.sh
ERROR_FILE='/home/demon/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S)
host=$1
s_name=$2
s_pass=$3
psql -A -t -q -v ON_ERROR_STOP=1 -c "SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )" >/dev/null 2>$ERROR_FILE
line_count=`cat $ERROR_FILE | wc -l`
if [[ $line_count != '0' ]];
then
rm -f /home/demon/*.err >/dev/null 2>/dev/null
cp $ERROR_FILE $ERROR_FILE'.err' >/dev/null 2>/dev/null
fi
rm $ERROR_FILE >/dev/null 2>/dev/null
exit 0plpgsql Chaguo za kukokotoa za dblink hufikia mionekano katika hifadhidata inayolengwa na kuingiza safu mlalo kwenye majedwali ya huduma katika hifadhidata ya ufuatiliaji.
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;Ili kukusanya vijipicha vya kutazama, huduma ya mfumo na hati mbili hutumiwa:
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.targetpg_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.targetPeana haki kwa hati:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.huduma
Wacha tuanze huduma:
# systemctl daemon-reload upya
# systemctl anza pg_current_activity.service
Kwa hivyo, historia ya maoni inakusanywa kwa namna ya snapshots ya pili kwa pili. Kwa kweli, ikiwa kila kitu kitaachwa kama kilivyo, meza zitaongezeka haraka sana kwa ukubwa na kazi yenye tija zaidi au kidogo haitawezekana.
Inahitajika kuandaa uhifadhi wa data.
3. Kuhifadhi historia
Kwa kuhifadhi, kumbukumbu za meza zilizogawanywa hutumiwa.
Sehemu mpya huundwa kila saa, huku data ya zamani ikiondolewa kwenye jedwali la historia*, kwa hivyo saizi ya majedwali ya historia* haibadiliki sana na kasi ya kuingiza haiharibiki kwa wakati.
Uundaji wa sehemu mpya unafanywa na kazi ya plpgsql shughuli_hist.archive_current_activity. Algorithm ya kazi ni rahisi sana (kwa kutumia mfano wa sehemu ya archive_pg_stat_activity table).
Unda na ujaze sehemu mpya
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
);Kuunda indexes
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 )'
);Inaondoa data ya zamani kutoka kwa jedwali la history_pg_stat_activity
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;Bila shaka, mara kwa mara, sehemu za zamani zinafutwa kama zisizohitajika.
Ripoti za msingi
Kweli, kwa nini haya yote yanafanywa? Ili kupata ripoti zinazokumbusha kwa ufupi sana AWR ya Oracle.
Ni muhimu kuongeza kwamba ili kupokea ripoti, unahitaji kujenga muunganisho kati ya pg_stat_activity na pg_stat_statements views. Majedwali yanaunganishwa kwa kuongeza safu wima ya 'queryid' kwenye majedwali ya 'history_pg_stat_activity', 'archive_pg_stat_activity'. Njia ya kuongeza thamani ya safu ni zaidi ya upeo wa kifungu hiki na imefafanuliwa hapa - .
JUMLA YA MUDA WA CPU KWA MASWALI
Ombi:
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 DESCMfano:
-------------------------------------------------------------------
| TOTAL CPU TIME FOR QUERIES : 07:47:36
+----+----------------------------------------+--------------------
| #| queryid| duration
+----+----------------------------------------+--------------------
| 1| 389015618226997618| 04:28:58
| 2| | 01:07:29
| 3| 1237430309438971376| 00:59:38
| 4| 4710212362688288619| 00:50:48
| 5| 28942442626229688| 00:15:50
| 6| 9150846928388977274| 00:04:46
| 7| -6572922443698419129| 00:00:06
| 8| | 00:00:01
+----+----------------------------------------+--------------------
MUDA KAMILI WA KUSUBIRI KWA MASWALI
Ombi:
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 Mfano:
-------------------------------------------------------------------
| TOTAL WAITINGS TIME FOR QUERIES : 21:55:04
+----+----------------------------------------+--------------------
| #| queryid| duration
+----+----------------------------------------+--------------------
| 1| 389015618226997618| 16:19:05
| 2| | 03:47:04
| 3| 8085340880788646241| 00:40:20
| 4| 4710212362688288619| 00:13:35
| 5| 9150846928388977274| 00:12:25
| 6| 28942442626229688| 00:11:32
| 7| 1237430309438971376| 00:09:45
| 8| 2649515222348904837| 00:09:37
| 9| | 00:03:45
| 10| 3167065002719415275| 00:02:20
| 11| 5731212217001535134| 00:02:13
| 12| 8304755792398128062| 00:01:31
| 13| 2649515222348904837| 00:00:59
| 14| 2649515222348904837| 00:00:22
| 15| | 00:00:12
| 16| 3422818749220588372| 00:00:08
| 17| -5730801771815999400| 00:00:03
| 18| -1473395109729441239| 00:00:02
| 19| 2404820632950544954| 00:00:02
| 20| -6572922443698419129| 00:00:02
| 21| 2369289265278398647| 00:00:01
| 22| 180077086776069052| 00:00:01
+----+----------------------------------------+--------------------
INASUBIRI MASWALI
Maombi:
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 DESCMfano:
------------------------------------------------
| WAITINGS FOR QUERIES
+-----------------------------------------------
| wait_event_type = Client|
| wait_event = ClientRead|
| Total time = 00:46:56|
------------------------------------------------
| #| queryid| duration
+-----+--------------------+--------------------
| 1| 8085340880788646241| 00:40:20
| 2| | 00:03:45
| 3| 5731212217001535134| 00:01:53
| 4| | 00:00:12
| 5| 9150846928388977274| 00:00:09
| 6| 3422818749220588372| 00:00:08
| 7| 1237430309438971376| 00:00:06
| 8| 28942442626229688| 00:00:05
| 9| 4710212362688288619| 00:00:05
| 10|-5730801771815999400| 00:00:03
| 11| 8304755792398128062| 00:00:02
| 12|-6572922443698419129| 00:00:02
| 13|-1473395109729441239| 00:00:02
| 14| 2404820632950544954| 00:00:02
| 15| 180077086776069052| 00:00:01
| 16| 2369289265278398647| 00:00:01
+-----------------------------------------------
| wait_event_type = IO|
| wait_event = BufFileRead|
| Total time = 00:00:38|
------------------------------------------------
| #| queryid| duration
+-----+--------------------+--------------------
| 1| 28942442626229688| 00:00:38
+-----------------------------------------------
HISTORIA YA TARATIBU ILIYOFUNGWA
Ombi:
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 1Mfano:
----------------------------------------------- ----------------------------------------------- -------------------------------- | HISTORIA YA TARATIBU ILIYOFUNGWA +-----+--------------------------------------- --------+------------------+----- ---------------- +------------------- | #| pid| imeanza| muda| blocking_pids| uhusiano| hali| locktype +------------------------------------------------- -----------+------------------- -------------------- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| uhusiano | 2| 26390| 2019-09-02 19:34:03| 00:00:53 | {26211}| 16541| AccessShareLock| uhusiano | 3 | 26391| 2019-09-02 19:34:03| 00:00:53 | {26211}| 16541| AccessShareLock| uhusiano | 4| 26531| 2019-09-02 19:35:27| 00:00:12 | {26211}| 16541| AccessShareLock| uhusiano | 5 | 27284| 2019-09-02 19:44:02| 00:00:19 | {27276}| 16541| AccessShareLock| uhusiano | 6 | 27283| 2019-09-02 19:44:02| 00:00:19 | {27276}| 16541| AccessShareLock| uhusiano | 7| 27286| 2019-09-02 19:44:02| 00:00:19 | {27276}| 16541| AccessShareLock| uhusiano | 8| 27423| 2019-09-02 19:45:24| 00:00:12 | {27394}| 16541| AccessShareLock| uhusiano | 9| 27648| 2019-09-02 19:48:06| 00:00:20 | {27647}| 16541| AccessShareLock| uhusiano | 10 | 27650| 2019-09-02 19:48:06| 00:00:20 | {27647}| 16541| AccessShareLock| uhusiano | 11| 27735| 2019-09-02 19:49:08| 00:00:06 | {27650}| 16541| AccessExclusiveLock| uhusiano | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| uhusiano | 13| 28379| 2019-09-02 19:56:03| 00:00:01 | 28377| 16541| AccessExclusiveLock| uhusiano | | | | | 28376| |
KUZUIA HISTORIA YA TARATIBU
Maombi:
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 , 1Mfano:
----------------------------------------------- ----------------------------------------------- ----------------------------------------------- ----------------------- KUZUIA HISTORIA YA TARATIBU +----+----------+------- ------------------------------------------------- ------+---------------------------------------- - -------+-------------------------------------- | #| pid| jina la matumizi| maombi_jina| jina | imeanza| muda| jimbo| swali +----------+---------+---------+-----+ --------- -+---------------------------------------------- -------------------------+--------------- ------- ----------------- | 1| 26211| mtunzi| psql| tdb1| 2019-09-02 19:31:54| 00:00:04 | bila kazi| | 2| 26211| mtunzi| psql| tdb1| 2019-09-02 19:31:58| 00:00:06 | bila shughuli katika shughuli| kuanza; | 3 | 26211| mtunzi| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| bila shughuli katika shughuli| lock meza kaki data; | 4| 26211| mtunzi| psql| tdb1| 2019-09-02 19:35:54| 00:01:23 | bila kazi| kujitolea; | 5 | 26211| mtunzi| psql| tdb1| 2019-09-02 19:38:46| 00:00:02 | bila shughuli katika shughuli| kuanza; | 6 | 26211| mtunzi| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| bila shughuli katika shughuli| lock meza kaki data; | 7| 26211| mtunzi| psql| tdb1| 2019-09-02 19:39:08| 00:42:42 | bila kazi| kujitolea; | 8| 26211| mtunzi| psql| tdb1| 2019-09-03 07:12:07| 00:00:52 | hai| chagua test_del();
Maendeleo.
Hoja za kimsingi zilizoonyeshwa na ripoti zinazotolewa tayari hurahisisha maisha wakati wa kuchanganua matukio ya utendakazi.
Kulingana na maswali ya kimsingi, unaweza kupata ripoti ambayo inafanana kabisa na AWR ya Oracle.
Mfano wa ripoti ya muhtasari
+----------------------------------------------- ---------------------------------- | RIPOTI MUHIMU YA SHUGHULI NA KUSUBIRI.
Itaendelea. Ifuatayo katika mstari ni uundaji wa historia ya kufuli (pg_stat_locks), maelezo ya kina zaidi ya mchakato wa kujaza meza.
Chanzo: mapenzi.com