የችግሩ ቀመር
የPostgreSQL መጠይቆችን ለማመቻቸት የእንቅስቃሴ ታሪክን በተለይም መጠበቅን፣ መቆለፍን እና የጠረጴዛ ስታቲስቲክስን የመተንተን ችሎታ በጣም ያስፈልጋል።
የሚገኙ እድሎች
በጣም ደስ የሚል መፍትሔ፣ ግን የpg_stat_activity እና pg_locks ታሪክ የለም።
:
«ሁሉም የተከማቸ መረጃ በ RAM ውስጥ ብቻ ነው የሚቀመጠው፣ እና የሚፈጀው የማህደረ ትውስታ መጠን በመጨረሻው የተከማቹ መዝገቦች ቁጥር ቁጥጥር ይደረግበታል።
የጥያቄ መስክ ታክሏል - ከpg_stat_statements ቅጥያ (ቅድመ-መጫን ያስፈልጋል) ተመሳሳይ ጥያቄ።«
ይህ በእርግጥ በጣም ይረዳል, ነገር ግን በጣም አስቸጋሪው ነገር የመጀመሪያው ነጥብ ነው.ሁሉም የተከማቹ መረጃዎች የሚቀመጡት በ RAM ውስጥ ብቻ ነው። ”፣ ማለትም እ.ኤ.አ. በዒላማው መሠረት ላይ ተጽእኖ አለ. በተጨማሪም, ምንም የመቆለፊያ ታሪክ እና የሠንጠረዥ ስታቲስቲክስ የለም. እነዚያ። በአጠቃላይ መፍትሄው ያልተሟላ ነው፡ "ለመጫን እስካሁን የተዘጋጀ ጥቅል የለም። ምንጮቹን ለማውረድ እና ቤተመፃህፍቱን እራስዎ ለመሰብሰብ ይመከራል. በመጀመሪያ ለአገልጋይዎ የ"devel" ጥቅል መጫን እና በPATH ተለዋዋጭ ውስጥ ወደ pg_config መንገዱን ማዘጋጀት ያስፈልግዎታል።".
በአጠቃላይ, ብዙ ግርግር አለ, እና በከባድ የምርት ዳታቤዝ ውስጥ, ከአገልጋዩ ጋር ምንም ነገር ማድረግ አይቻልም. እንደገና የራሳችን የሆነ ነገር ማምጣት አለብን።
ማስጠንቀቂያ
በጣም ትልቅ በሆነ መጠን እና ባልተጠናቀቀ የፈተና ጊዜ ምክንያት ጽሑፉ በዋናነት የመረጃ ተፈጥሮ ነው ፣ ይልቁንም እንደ የትርምስ ስብስብ እና መካከለኛ ውጤቶች።
የበለጠ ዝርዝር ቁሳቁስ በኋላ ፣በክፍል ይዘጋጃል።
ለመፍትሔው ረቂቅ መስፈርቶች
ለማከማቸት የሚያስችል መሳሪያ ማዘጋጀት አስፈላጊ ነው-
pg_stat_እንቅስቃሴ እይታ ታሪክ
የpg_locks እይታን በመጠቀም የክፍለ-ጊዜ ቆልፍ ታሪክ
የመፍትሄ ፍላጎት- በታለመው የውሂብ ጎታ ላይ ያለውን ተጽእኖ ይቀንሱ.
አጠቃላይ ሀሳብ- የመረጃ መሰብሰቢያ ወኪሉ የተጀመረው በታለመው የውሂብ ጎታ ውስጥ ሳይሆን በክትትል ዳታቤዝ ውስጥ እንደ ስርዓት አገልግሎት ነው። አዎ, አንዳንድ የውሂብ መጥፋት ይቻላል, ነገር ግን ይህ ሪፖርት ለማድረግ ወሳኝ አይደለም, ነገር ግን በማስታወሻ እና በዲስክ ቦታ ላይ በታለመው የውሂብ ጎታ ላይ ምንም ተጽእኖ የለም. እና የግንኙነት ገንዳ በሚጠቀሙበት ጊዜ በተጠቃሚዎች ሂደቶች ላይ ያለው ተጽእኖ አነስተኛ ነው.
የአተገባበር ደረጃዎች
1.የአገልግሎት ሠንጠረዦች
ጥቅም ላይ የዋሉትን ዋና ጠረጴዛዎች ትንተና እንዳያወሳስብ የተለየ ንድፍ ጠረጴዛዎችን ለማከማቸት ጥቅም ላይ ይውላል.
DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;
ጠቃሚ፡ መርሃግብሩ በዒላማ ዳታቤዝ ውስጥ አልተፈጠረም፣ ነገር ግን በክትትል ዳታቤዝ ውስጥ።
pg_stat_እንቅስቃሴ እይታ ታሪክ
ሠንጠረዥ የአሁኑን የpg_stat_activity እይታ ቅጽበተ-ፎቶዎችን ለማከማቸት ጥቅም ላይ ይውላል
እንቅስቃሴ_ሂስት.ታሪክ_pg_ስታቲስቲክስ_እንቅስቃሴ፡
--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
);ማስገባትን ለማፋጠን - ምንም ኢንዴክሶች ወይም ገደቦች የሉም።
ታሪኩን በራሱ ለማከማቸት, የተከፋፈለ ሰንጠረዥ ጥቅም ላይ ይውላል:
እንቅስቃሴ_hist.archive_pg_stat_እንቅስቃሴ፡
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);በዚህ ሁኔታ ውስጥ ለማስገባት ፍጥነት ምንም መስፈርቶች ስለሌለ ሪፖርቶችን ለመፍጠር አንዳንድ ኢንዴክሶች ተፈጥረዋል.
የክፍለ-ጊዜ እገዳ ታሪክ
ሠንጠረዥ የአሁኑን የክፍለ ጊዜ መቆለፊያዎች ቅጽበተ-ፎቶዎችን ለማከማቸት ጥቅም ላይ ይውላል፡-
እንቅስቃሴ_ሂስት.ታሪክ_መቆለፍ፡
--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
);እንዲሁም ማስገባትን ለማፋጠን ምንም ኢንዴክሶች ወይም ገደቦች የሉም።
ታሪኩን በራሱ ለማከማቸት, የተከፋፈለ ሰንጠረዥ ጥቅም ላይ ይውላል:
እንቅስቃሴ_hist.የማህደር_መቆለፊያ፡
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);በዚህ ሁኔታ ውስጥ ለማስገባት ፍጥነት ምንም መስፈርቶች ስለሌለ ሪፖርቶችን ለመፍጠር አንዳንድ ኢንዴክሶች ተፈጥረዋል.
2. የአሁኑን ታሪክ መሙላት
የእይታ ቅጽበተ-ፎቶዎችን በቀጥታ ለመሰብሰብ የplpgsql ተግባርን የሚያሄድ የ bash ስክሪፕት ስራ ላይ ይውላል።
የአሁኑን_እንቅስቃሴ_አግኝ.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 የ dblink ተግባር በታለመው ዳታቤዝ ውስጥ እይታዎችን ያገኛል እና ረድፎችን በክትትል ዳታቤዝ ውስጥ በአገልግሎት ሰንጠረዦች ውስጥ ያስገባል።
የአሁን_እንቅስቃሴን አግኝ.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;የእይታ ቅጽበተ-ፎቶዎችን ለመሰብሰብ የስርዓት አገልግሎት እና ሁለት ስክሪፕቶች ጥቅም ላይ ይውላሉ።
pg_current_activity.አገልግሎት
# /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.targetለስክሪፕቶች መብቶችን መድብ፡
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.አገልግሎት
አገልግሎቱን እንጀምር፡-
# systemctl daemon-reload
# systemctl pg_current_activity.አገልግሎት ጀምር
ስለዚህ, የእይታዎች ታሪክ የሚሰበሰበው በሁለተኛው-በ-ሰከንድ ቅጽበተ-ፎቶዎች መልክ ነው. እርግጥ ነው, ሁሉም ነገር እንዳለ ከተተወ, ጠረጴዛዎቹ በፍጥነት መጠናቸው እየጨመረ ይሄዳል እና ብዙ ወይም ያነሰ ውጤታማ ስራ የማይቻል ይሆናል.
የውሂብ መዝገብን ማደራጀት አስፈላጊ ነው.
3. ታሪክን በማህደር ማስቀመጥ
በማህደር ለማስቀመጥ፣ የተከፋፈሉ የሰንጠረዦች መዝገብ * ጥቅም ላይ ይውላል።
በየሰዓቱ አዲስ ክፍልፋዮች ይፈጠራሉ፣ አሮጌ መረጃዎች ከታሪክ * ሰንጠረዦች ይወገዳሉ፣ ስለዚህ የታሪክ * ሰንጠረዦች መጠን ብዙም አይለወጥም እና የማስገቢያ ፍጥነት በጊዜ ሂደት አይቀንስም።
የአዳዲስ ክፍሎች መፈጠር የሚከናወነው በplpgsql ተግባር እንቅስቃሴ_hist.archive_current_activity ነው። የሥራው ስልተ ቀመር በጣም ቀላል ነው (የክፍሉን ምሳሌ ለ archive_pg_stat_activity ሠንጠረዥ በመጠቀም)።
አዲስ ክፍል ይፍጠሩ እና ይሙሉ
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
);ኢንዴክሶችን መፍጠር
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 )'
);የድሮ ውሂብን ከታሪክ_pg_stat_እንቅስቃሴ ሰንጠረዥ በማስወገድ ላይ
DELETE
FROM activity_hist.history_pg_stat_activity
WHERE timepoint < partition_max_range;እርግጥ ነው, ከጊዜ ወደ ጊዜ, የቆዩ ክፍሎች እንደ አላስፈላጊ ይሰረዛሉ.
መሰረታዊ ሪፖርቶች
በእውነቱ ይህ ሁሉ የሚደረገው ለምንድነው? የOracle AWRን የሚያስታውሱ በጣም ግልጽ ያልሆኑ ሪፖርቶችን ለማግኘት።
ሪፖርቶችን ለመቀበል በpg_stat_activity እና pg_stat_statements እይታዎች መካከል ግንኙነት መፍጠር እንዳለቦት ማከል አስፈላጊ ነው። ሠንጠረዦቹ የተገናኙት 'Queryid' አምድ ወደ 'history_pg_stat_activity'፣ 'archive_pg_stat_activity' ሰንጠረዦች ላይ በማከል ነው። የአምድ እሴት የማከል ዘዴ ከዚህ ጽሑፍ ወሰን በላይ ነው እና እዚህ ተብራርቷል - .
ጠቅላላ የሲፒዩ ጊዜ ለጥያቄዎች
ጥያቄ፡
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ለምሳሌ:
-------------------------------------------------------------------
| 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
+----+----------------------------------------+--------------------
ጠቅላላ የጥበቃ ጊዜ ለጥያቄዎች
ጥያቄ፡
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 አንድ ምሳሌ
-------------------------------------------------------------------
| 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
+----+----------------------------------------+--------------------
መጠይቆችን በመጠበቅ ላይ
ጥያቄዎች፡-
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ለምሳሌ:
------------------------------------------------
| 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
+-----------------------------------------------
የተቆለፉ ሂደቶች ታሪክ
ጥያቄ፡-
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ለምሳሌ:
---------------------------------- ---------------------------------- --------------------------------- | የተቆለፉ ሂደቶች ታሪክ +------------- ---------------------------- +------------------ | #| ፒድ | ጀመረ| ቆይታ| ማገድ_pids| ግንኙነት| ሁነታ| የመቆለፊያ አይነት +-------------------------- -------------------------------- ------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| ይድረስShareLock| ግንኙነት | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| ይድረስShareLock| ግንኙነት | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| ይድረስShareLock| ግንኙነት | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| ይድረስShareLock| ግንኙነት | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| ይድረስShareLock| ግንኙነት | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| ይድረስShareLock| ግንኙነት | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| ይድረስShareLock| ግንኙነት | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| ይድረስShareLock| ግንኙነት | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| ይድረስShareLock| ግንኙነት | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| ይድረስShareLock| ግንኙነት | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| ግንኙነት | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| ይድረስShareLock| ግንኙነት | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| ግንኙነት | | | | | 28376| |
የማገድ ሂደቶች ታሪክ
ጥያቄዎች፡-
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ለምሳሌ:
---------------------------------- ---------------------------------- ---------------------------------- --------የማገድ ሂደቶች ታሪክ +--- ---+---------------- --------------- ----------------------------------------- | #| ፒድ | የተጠቃሚ ስም| መተግበሪያ_ስም| ዳታ ስም| ጀመረ| ቆይታ| ግዛት| ጥያቄ -------------------- ----------------- ------------------------------------ ----------------- | 1| 26211| ቱዘር| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| ስራ ፈት | | 2| 26211| ቱዘር| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| በግብይት ላይ ስራ ፈት | ጀምር; | 3| 26211| ቱዘር| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| በግብይት ላይ ስራ ፈት | የመቆለፊያ ሰንጠረዥ wafer_data; | 4| 26211| ቱዘር| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| ስራ ፈት | መፈጸም; | 5| 26211| ቱዘር| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| በግብይት ላይ ስራ ፈት | ጀምር; | 6| 26211| ቱዘር| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| በግብይት ላይ ስራ ፈት | የመቆለፊያ ሰንጠረዥ wafer_data; | 7| 26211| ቱዘር| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| ስራ ፈት | መፈጸም; | 8| 26211| ቱዘር| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| ንቁ| test_del () ይምረጡ;
ልማት.
የታዩት መሰረታዊ ጥያቄዎች እና የተገኙት ሪፖርቶች የአፈጻጸም ክስተቶችን ሲተነትኑ ህይወትን በጣም ቀላል ያደርጉታል።
በመሰረታዊ ጥያቄዎች ላይ በመመስረት፣ ከOracle's AWR ጋር የሚመሳሰል ሪፖርት ማግኘት ይችላሉ።
የማጠቃለያ ዘገባ ምሳሌ
------------------------------------------------ ---------------------------------- | የተጠናከረ የእንቅስቃሴ እና የመጠባበቅ ሪፖርት።
ይቀጥላል. የሚቀጥለው መስመር የመቆለፊያ ታሪክ መፍጠር ነው (pg_stat_locks)፣ ጠረጴዛዎችን የመሙላት ሂደት የበለጠ ዝርዝር መግለጫ።
ምንጭ: hab.com