مضمون کا تسلسل "".
مضمون مخصوص سوالات اور مثالوں کا استعمال کرتے ہوئے جانچے گا اور دکھائے گا کہ pg_stat_activity منظر کی تاریخ کا استعمال کرتے ہوئے کیا مفید معلومات حاصل کی جا سکتی ہیں۔
انتباہ
موضوع کی نیاپن اور نامکمل آزمائشی مدت کی وجہ سے، مضمون میں غلطیاں ہوسکتی ہیں۔ تنقید اور تبصرے کا بھرپور خیرمقدم اور توقع کی جاتی ہے۔
ان پٹ ڈیٹا
pg_stat_statements جمع کرانے کی تاریخ
pg_stat_history
CREATE TABLE pg_stat_history (
id SERIAL,
snapshot_timestamp timestamp without time zone,
database_id integer,
dbid oid,
userid oid,
queryid bigint,
query text,
calls bigint,
total_time double precision,
min_time double precision,
max_time double precision,
mean_time double precision,
stddev_time double precision,
rows bigint,
shared_blks_hit bigint,
shared_blks_read bigint,
shared_blks_dirtied bigint,
shared_blks_written bigint,
local_blks_hit bigint,
local_blks_read bigint,
local_blks_dirtied bigint,
local_blks_written bigint,
temp_blks_read bigint,
temp_blks_written bigint,
blk_read_time double precision,
blk_write_time double precision,
baseline_id integer );ٹیبل ہر گھنٹے ٹارگٹ ڈیٹا بیس میں dblink کا استعمال کرتے ہوئے آباد ہوتا ہے۔ ٹیبل میں سب سے زیادہ دلچسپ اور مفید کالم، یقینا queryid.
pg_stat_activity دیکھنے کی سرگزشت
archive_pg_stat_activity
CREATE TABLE 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 without time zone,
xact_start timestamp without time zone,
query_start timestamp without time zone,
state_change timestamp without time zone,
wait_event_type text,
wait_event text,
state text,
backend_xid xid,
backend_xmin xid,
query text,
backend_type text,
queryid bigint
);ٹیبل ایک history_pg_stat_activity ٹیبل ہے جسے گھنٹے کے حساب سے تقسیم کیا گیا ہے (مزید تفصیلات یہاں - اور یہاں -
Выходные данные
کلسٹر CPU ٹائم (سسٹم + کلائنٹس)
درخواست کریں۔
WITH
t AS
(
SELECT
date_trunc('second', timepoint)
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
( aa.wait_event_type IS NULL ) AND
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;مثال کے طور پر
CLUSTER CPU TIME (SYSTEM + CLIENTS ) : 28:37:46کلسٹر انتظار کا وقت
درخواست کریں۔
WITH
t AS
(
SELECT
date_trunc('second', timepoint)
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
( aa.wait_event_type IS NOT NULL ) AND
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;مثال کے طور پر
CLUSTER WAITINGS TIME : 30:12:49pg_stat_statements کی کل اقدار
درخواست کریں۔
--TOTAL pg_stat
SELECT
SUM(calls) AS calls, SUM(total_time) AS total_time, SUM(rows) AS rows ,
SUM(shared_blks_hit) AS shared_blks_hit,SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit , SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied , SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read, SUM(temp_blks_written) temp_blks_written ,
SUM(blk_read_time) AS blk_read_time , SUM(blk_write_time) AS blk_write_time
INTO
pg_total_stat_history_rec
FROM
pg_stat_history
WHERE
snapshot_timestamp BETWEEN pg_stat_history_begin AND pg_stat_history_end AND
queryid IS NULL;SQL DBTIME - کل استفسار پر عمل درآمد کا وقت
درخواست کریں۔
dbtime_total = interval '1 millisecond' * pg_total_stat_history_rec.total_time ;مثال کے طور پر
SQL DBTIME : 136:49:36ایس کیو ایل سی پی یو ٹائم - سوالات کو انجام دینے میں سی پی یو کا وقت گزارا۔
درخواست کریں۔
WITH
t AS
(
SELECT
date_trunc('second', timepoint)
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
( aa.wait_event_type IS NULL ) AND
backend_type = 'client backend' AND
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;مثال کے طور پر
SQL CPU TIME : 27:40:15ایس کیو ایل انتظار کا وقت - سوالات کے لیے کل انتظار کا وقت
درخواست کریں۔
WITH
t AS
(
SELECT
date_trunc('second', timepoint)
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
( aa.wait_event_type IS NOT NULL ) AND
aa.state = 'active' AND
backend_type = 'client backend'
)
SELECT count(*)
INTO waiting_total
FROM t ;مثال کے طور پر
SQL WAITINGS TIME : 30:04:09درج ذیل سوالات معمولی ہیں اور جگہ بچانے کے لیے عمل درآمد کی تفصیلات کو چھوڑ دیا گیا ہے۔
مثال کے طور پر
| SQL IOTIME : 19:44:50
| SQL READ TIME : 19:44:32
| SQL WRITE TIME : 00:00:17
|
| SQL CALLS : 12188248
-------------------------------------------------------------
| SQL SHARED BLOCKS READS : 7997039120
| SQL SHARED BLOCKS HITS : 8868286092
| SQL SHARED BLOCKS HITS/READS % : 110.89
| SQL SHARED BLOCKS DIRTED : 419945
| SQL SHARED BLOCKS WRITTEN : 19857
|
| SQL TEMPORARY BLOCKS READS : 7836169
| SQL TEMPORARY BLOCKS WRITTEN : 10683938
آئیے سب سے دلچسپ سیکشن کی طرف چلتے ہیں۔
انتظار جامد
کلائنٹس کے عمل کے لیے کل انتظار کے وقت کے لحاظ سے سرفہرست 10 انتظار
درخواست کریں۔
SELECT
wait_event_type , wait_event ,
get_system_waiting_duration( wait_event_type , wait_event ,pg_stat_history_begin+(current_hour_diff * interval '1 hour') ,pg_stat_history_end+(current_hour_diff * interval '1 hour') ) 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 wait_event_type IS NOT NULL
GROUP BY
wait_event_type, wait_event
ORDER BY 3 DESC
LIMIT 10مثال کے طور پر
+---------------------------------------------------------------- ----------------------------------- | نظام کے عمل کے لیے کل انتظار کے وقت کے لحاظ سے سرفہرست 10 انتظار +------+------------------ ------------------+---------------------- | #| انتظار_واقعہ_قسم | انتظار_واقعہ | دورانیہ +-------------------------------------------- +------------ -------+------------ | 1| سرگرمی| LogicalLauncherMain| 10:43:28 | 2| سرگرمی| AutoVacuumMain| 10:42:49 | 3| سرگرمی| وال رائٹر مین | 10:28:53 | 4| سرگرمی| چیک پوائنٹر مین | 10:23:50 | 5| سرگرمی| BgWriterMain| 09:11:59 | 6| سرگرمی| BgWriterHibernate| 01:37:46 | 7| IO| بف فائل رائٹ | 00:02:35 | 8| LWLock | buffer_mapping| 00:01:54 | 9| IO| ڈیٹا فائل ریڈ | 00:01:23 | 10| IO| وال رائٹ | 00:00:59 +------------------------------------------------------------ -------------+------------
کلائنٹس کے عمل کے لیے کل انتظار کے وقت کے لحاظ سے سرفہرست 10 انتظار
درخواست کریں۔
SELECT
wait_event_type , wait_event ,
get_clients_waiting_duration( wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) 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 wait_event_type IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY 3 DESC
LIMIT 10مثال کے طور پر
+----------------------------------------------- ------------+------------+--------- | #| انتظار_واقعہ_قسم | انتظار_واقعہ | دورانیہ | %dbtime +------+--------------------------------------------+--------- ----------+---------+------------ | 1| لاک | transactionid| 08:16:47 | 6.05 | 2| IO| ڈیٹا فائل ریڈ | 06:13:41 | 4.55 | 3| ٹائم آؤٹ | PgSleep| 02:53:21 | 2.11 | 4| LWLock | buffer_mapping| 00:40:42 | 0.5 | 5| LWLock | بفر_io| 00:17:17 | 0.21 | 6| IO| بف فائل رائٹ | 00:01:34 | 0.02 | 7| لاک | ٹوپل | 00:01:32 | 0.02 | 8| کلائنٹ | کلائنٹ ریڈ | 00:01:19 | 0.02 | 9| IO| بف فائل ریڈ | 00:00:37 | 0.01 | 10| LWLock | buffer_content| 00:00:08 | 0 +------+-----------------------------------------+------------------------- -------+------------+---------
نظام کے عمل کے لیے کل انتظار کے وقت تک انتظار کی اقسام
درخواست کریں۔
SELECT
wait_event_type ,
get_system_waiting_type_duration( wait_event_type , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) 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 wait_event_type IS NOT NULL
GROUP BY wait_event_type
ORDER BY 2 DESCمثال کے طور پر
+----------------------------------------------- --- -------- | #| انتظار_واقعہ_قسم | دورانیہ +-------------------------------------------- +------------ ------- | 1| سرگرمی| 53:08:45 | 2| IO| 00:06:24 | 3| LWLock | 00:03:02 +-------------------------------------------- -------------
کلائنٹس کے عمل کے لیے کل انتظار کے وقت تک انتظار کی قسمیں
درخواست کریں۔
SELECT
wait_event_type ,
get_clients_waiting_type_duration( wait_event_type , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) 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 wait_event_type IS NOT NULL
GROUP BY wait_event_type
ORDER BY 2 DESCمثال کے طور پر
+----------------------------------------------- ------------+------------ | #| انتظار_واقعہ_قسم | دورانیہ | %dbtime +------+--------------------------------------------+--------- ----------+---------------------- | 1| لاک | 08:18:19 | 6.07 | 2| IO| 06:16:01 | 4.58 | 3| ٹائم آؤٹ | 02:53:21 | 2.11 | 4| LWLock | 00:58:12 | 0.71 | 5| کلائنٹ | 00:01:19 | 0.02 | 6| آئی پی سی | 00:00:04 | 0 +------+-----------------------------------------+------------------------- --------+--------------------------------
سسٹم کے عمل اور انفرادی درخواستوں کے لیے مدت کا انتظار کریں۔
سسٹم کے عمل کا انتظار
درخواست کریں۔
SELECT
backend_type , datname , wait_event_type , wait_event , get_backend_type_waiting_duration( backend_type , wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) 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 wait_event_type IS NOT NULL
GROUP BY backend_type , datname , wait_event_type , wait_event
ORDER BY 5 DESCمثال کے طور پر
+-----+-----------------------------+----------+--------------------+----------------------+-------------------- | #| backend_type| dbname| wait_event_type| wait_event| duration +-----+-----------------------------+----------+--------------------+----------------------+-------------------- | 1| logical replication launcher| | Activity| LogicalLauncherMain| 10:43:28 | 2| autovacuum launcher| | Activity| AutoVacuumMain| 10:42:49 | 3| walwriter| | Activity| WalWriterMain| 10:28:53 | 4| checkpointer| | Activity| CheckpointerMain| 10:23:50 | 5| background writer| | Activity| BgWriterMain| 09:11:59 | 6| background writer| | Activity| BgWriterHibernate| 01:37:46 | 7| parallel worker| tdb1| IO| BufFileWrite| 00:02:35 | 8| parallel worker| tdb1| LWLock| buffer_mapping| 00:01:41 | 9| parallel worker| tdb1| IO| DataFileRead| 00:01:22 | 10| parallel worker| tdb1| IO| BufFileRead| 00:00:59 | 11| walwriter| | IO| WALWrite| 00:00:57 | 12| parallel worker| tdb1| LWLock| buffer_io| 00:00:47 | 13| autovacuum worker| tdb1| LWLock| buffer_mapping| 00:00:13 | 14| background writer| | IO| DataFileWrite| 00:00:12 | 15| checkpointer| | IO| DataFileWrite| 00:00:11 | 16| walwriter| | LWLock| WALWriteLock| 00:00:09 | 17| checkpointer| | LWLock| WALWriteLock| 00:00:06 | 18| background writer| | LWLock| WALWriteLock| 00:00:06 | 19| walwriter| | IO| WALInitWrite| 00:00:02 | 20| autovacuum worker| tdb1| LWLock| WALWriteLock| 00:00:02 | 21| walwriter| | IO| WALInitSync| 00:00:02 | 22| autovacuum worker| tdb1| IO| DataFileRead| 00:00:01 | 23| checkpointer| | IO| ControlFileSyncUpdate| 00:00:01 | 24| background writer| | IO| WALWrite| 00:00:01 | 25| background writer| | IO| DataFileFlush| 00:00:01 | 26| checkpointer| | IO| SLRUFlushSync| 00:00:01 | 27| autovacuum worker| tdb1| IO| WALWrite| 00:00:01 | 28| checkpointer| | IO| DataFileSync| 00:00:01 +-----+-----------------------------+----------+--------------------+----------------------+--------------------
ایس کیو ایل کے لیے انتظار - queryid کے ذریعے انفرادی سوالات کا انتظار کرتا ہے۔
درخواست کریں۔
SELECT
queryid , datname , wait_event_type , wait_event , get_query_waiting_duration( queryid , wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) 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 wait_event_type IS NOT NULL AND queryid IS NOT NULL
GROUP BY queryid , datname , wait_event_type , wait_event
ORDER BY 1 , 5 DESC مثال کے طور پر
+-----+-------------------------+----------+--------------------+--------------------+--------------------+-------------------- | #| queryid| dbname| wait_event_type| wait_event| waitings| total | | | | | | duration| duration +-----+-------------------------+----------+--------------------+--------------------+--------------------+-------------------- | 1| -8247416849404883188| tdb1| Client| ClientRead| 00:00:02| | 2| -6572922443698419129| tdb1| Client| ClientRead| 00:00:05| | 3| -6572922443698419129| tdb1| IO| DataFileRead| 00:00:01| | 4| -5917408132400665328| tdb1| Client| ClientRead| 00:00:04| | 5| -4091009262735781873| tdb1| Client| ClientRead| 00:00:03| | 6| -1473395109729441239| tdb1| Client| ClientRead| 00:00:01| | 7| 28942442626229688| tdb1| IO| BufFileWrite| 00:01:34| 00:46:06 | 8| 28942442626229688| tdb1| LWLock| buffer_mapping| 00:01:05| 00:46:06 | 9| 28942442626229688| tdb1| IO| DataFileRead| 00:00:44| 00:46:06 | 10| 28942442626229688| tdb1| IO| BufFileRead| 00:00:37| 00:46:06 | 11| 28942442626229688| tdb1| LWLock| buffer_io| 00:00:35| 00:46:06 | 12| 28942442626229688| tdb1| Client| ClientRead| 00:00:05| 00:46:06 | 13| 28942442626229688| tdb1| IPC| MessageQueueReceive| 00:00:03| 00:46:06 | 14| 28942442626229688| tdb1| IPC| BgWorkerShutdown| 00:00:01| 00:46:06 | 15| 389015618226997618| tdb1| Lock| transactionid| 03:55:09| 04:14:15 | 16| 389015618226997618| tdb1| IO| DataFileRead| 03:23:09| 04:14:15 | 17| 389015618226997618| tdb1| LWLock| buffer_mapping| 00:12:09| 04:14:15 | 18| 389015618226997618| tdb1| LWLock| buffer_io| 00:10:18| 04:14:15 | 19| 389015618226997618| tdb1| Lock| tuple| 00:00:35| 04:14:15 | 20| 389015618226997618| tdb1| LWLock| WALWriteLock| 00:00:02| 04:14:15 | 21| 389015618226997618| tdb1| IO| DataFileWrite| 00:00:01| 04:14:15 | 22| 389015618226997618| tdb1| LWLock| SyncScanLock| 00:00:01| 04:14:15 | 23| 389015618226997618| tdb1| Client| ClientRead| 00:00:01| 04:14:15 | 24| 734234407411547467| tdb1| Client| ClientRead| 00:00:11| | 25| 734234407411547467| tdb1| LWLock| buffer_mapping| 00:00:05| | 26| 734234407411547467| tdb1| IO| DataFileRead| 00:00:02| | 27| 1237430309438971376| tdb1| LWLock| buffer_mapping| 00:02:18| 02:45:40 | 28| 1237430309438971376| tdb1| IO| DataFileRead| 00:00:27| 02:45:40 | 29| 1237430309438971376| tdb1| Client| ClientRead| 00:00:02| 02:45:40 | 30| 2404820632950544954| tdb1| Client| ClientRead| 00:00:01| | 31| 2515308626622579467| tdb1| Client| ClientRead| 00:00:02| | 32| 4710212362688288619| tdb1| LWLock| buffer_mapping| 00:03:08| 02:18:21 | 33| 4710212362688288619| tdb1| IO| DataFileRead| 00:00:22| 02:18:21 | 34| 4710212362688288619| tdb1| Client| ClientRead| 00:00:06| 02:18:21 | 35| 4710212362688288619| tdb1| LWLock| buffer_io| 00:00:02| 02:18:21 | 36| 9150846928388977274| tdb1| IO| DataFileRead| 00:01:19| | 37| 9150846928388977274| tdb1| LWLock| buffer_mapping| 00:00:34| | 38| 9150846928388977274| tdb1| Client| ClientRead| 00:00:10| | 39| 9150846928388977274| tdb1| LWLock| buffer_io| 00:00:01| +-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
کلائنٹ SQL STATICTICS - سرفہرست سوالات
وصول کرنے کی درخواستیں پھر معمولی ہیں اور جگہ بچانے کے لیے نہیں دی جاتی ہیں۔
مثالیں
+------------------------------------------------------------------------------------ | CLIENT SQL ordered by Elapsed Time +--------------------+----------+----------+----------+----------+----------+-------------------- | elapsed time| calls| % dbtime| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+-------------------- | 04:14:15| 19| 3.1| 10.83| 11.52| tdb1| 389015618226997618 | 02:45:40| 746| 2.02| 4.23| 0.08| tdb1| 1237430309438971376 | 02:18:21| 749| 1.69| 3.39| 0.1| tdb1| 4710212362688288619 | 00:46:06| 375| 0.56| 0.94| 0.41| tdb1| 28942442626229688 +--------------------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by CPU Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | cpu time| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 02:59:49| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 01:10:12| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 00:56:15| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 00:15:35| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by User I/O Wait Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | io_wait time| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 03:23:10| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 00:02:54| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 00:00:27| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 00:00:22| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Shared Buffers Reads +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | buffers reads| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 1056388566| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 11709251| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 3439004| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 3373330| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Disk Reads Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | read time| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 02:16:30| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 00:04:50| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 00:01:10| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 00:00:57| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Executions +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | calls| rows| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 749| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 746| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 375| 0| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 19| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 +--------------------+----------+----------+----------+----------+----------+----------+--------------------
کل
جمع کرائے گئے سوالات اور نتیجے کی رپورٹنگ کا استعمال کرتے ہوئے، آپ انفرادی سوالات اور مجموعی طور پر پورے کلسٹر کے لیے کارکردگی میں کمی کے مسائل کا تجزیہ اور حل کرنے کے لیے مزید مکمل تصویر حاصل کر سکتے ہیں۔
ترقی
اب تک کے ترقیاتی منصوبے درج ذیل ہیں۔
- بلاکنگ ہسٹری کے ساتھ رپورٹنگ کی تکمیل کریں۔ درخواستوں کی جانچ کی جا رہی ہے اور جلد ہی جمع کر دی جائے گی۔
- pg_stat_activity اور pg_locks کی تاریخ کو ذخیرہ کرنے کے لیے TimescaleDB ایکسٹینشن کا استعمال کریں۔
- پیداوار کے اڈوں پر بڑے پیمانے پر تعیناتی کے لیے گیتھب پر ایک پیکیج حل تیار کریں۔
جاری رکھنا ...
ماخذ: www.habr.com