Jurutera - diterjemahkan dari bahasa Latin - diilhamkan.
Seorang jurutera boleh melakukan apa sahaja. (c) R. Diesel.
Epigraf.
Atau cerita tentang sebab pentadbir pangkalan data perlu mengingati masa lalu pengaturcaraannya.
Perutusan
Semua nama telah ditukar. Kebetulan adalah rawak. Bahan tersebut mewakili pendapat peribadi pengarang semata-mata.
Penafian jaminan: Siri rencana yang dirancang tidak akan mengandungi penerangan terperinci dan tepat tentang jadual dan skrip yang digunakan. Bahan-bahan tidak boleh digunakan dengan segera "SEBAGAIMANA ADANYA".
Pertama, disebabkan jumlah bahan yang besar,
kedua, kerana hubungan rapat dengan pangkalan pengeluaran pelanggan sebenar.
Oleh itu, artikel hanya akan mengandungi idea dan penerangan dalam bentuk yang paling umum.
Mungkin pada masa hadapan sistem akan berkembang ke tahap yang disiarkan di GitHub, atau mungkin tidak. Masa akan menentukan.
Permulaan cerita - "'.
Apa yang berlaku akibatnya, dalam istilah yang paling umum - "Β»
Kenapa saya perlukan semua ini?
Baiklah, pertama, supaya tidak lupa, mengingati hari-hari kegemilangan dalam persaraan.
Kedua, untuk sistematik apa yang ditulis. Kerana kadang-kadang saya mula keliru dan lupa bahagian tertentu.
Nah, dan perkara yang paling penting ialah ia mungkin berguna untuk seseorang dan membantu mereka mengelakkan mencipta semula roda dan tidak mengumpul garu. Dengan kata lain, tingkatkan karma anda (bukan Khabrov). Kerana perkara yang paling berharga di dunia ini adalah idea. Perkara utama ialah mencari idea. Tetapi mengubah idea menjadi realiti adalah persoalan teknikal semata-mata.
Jadi, mari kita mulakan, sedikit demi sedikit...
Perumusan masalah.
Tersedia:
Pangkalan data PostgreSQL(10.5), jenis beban bercampur (OLTP+DSS), beban sederhana ringan, terletak di awan AWS.
Tiada pemantauan pangkalan data; pemantauan infrastruktur disediakan dalam bentuk alat AWS standard dalam konfigurasi minimum.
Ia diperlukan:
Pantau prestasi dan status pangkalan data, cari dan dapatkan maklumat awal untuk mengoptimumkan pertanyaan pangkalan data yang berat.
Pengenalan ringkas atau analisis pilihan penyelesaian
Sebagai permulaan, mari kita cuba menganalisis pilihan untuk menyelesaikan masalah dari sudut pandangan analisis perbandingan faedah dan keburukan untuk jurutera, dan biarkan mereka yang berhak mengikut jadual kakitangan berurusan dengan faedah dan kerugian pengurusan.
Pilihan 1 - "Bekerja atas permintaan"
Kita biarkan segalanya seperti sedia ada. Jika pelanggan tidak berpuas hati dengan sesuatu dalam fungsi, prestasi pangkalan data atau aplikasi, dia akan memberitahu jurutera DBA melalui e-mel atau dengan mencipta insiden dalam dulang tiket.
Jurutera, setelah menerima pemberitahuan, akan memahami masalah itu, menawarkan penyelesaian atau meletakkan masalah pada pembakar belakang, berharap semuanya akan diselesaikan dengan sendirinya, dan bagaimanapun, semuanya akan segera dilupakan.
Roti halia dan donat, lebam dan benjolanRoti halia dan donat:
1. Tidak perlu melakukan apa-apa tambahan.
2. Selalu ada peluang untuk berdalih dan kacau.
3. Banyak masa yang boleh anda luangkan mengikut budi bicara anda sendiri.
Lebam dan benjolan:
1. Lambat laun, pelanggan akan berfikir tentang intipati kewujudan dan keadilan sejagat di dunia ini dan sekali lagi bertanya pada dirinya sendiri - mengapa saya membayar wang saya kepada mereka? Akibatnya sentiasa sama - satu-satunya persoalan ialah apabila pelanggan akan bosan dan melambai selamat tinggal. Dan penyuap akan kosong. memang sedih.
2. Pembangunan jurutera - sifar.
3. Kesukaran dalam merancang kerja dan memuatkan
Pilihan 2- "Menari dengan rebana, mengukus dan memakai kasut"
Perenggan 1-Mengapa kita memerlukan sistem pemantauan, kita akan menerima segala-galanya dengan permintaan. Kami menjalankan sekumpulan semua jenis pertanyaan ke kamus data dan paparan dinamik, menghidupkan semua jenis pembilang, meletakkan segala-galanya ke dalam jadual dan menganalisis senarai dan jadual secara berkala. Akibatnya, kami mempunyai graf, jadual, laporan yang cantik atau tidak begitu cantik. Perkara utama adalah untuk mempunyai lebih banyak lagi.
Perenggan 2-Kami menjana aktiviti dan melancarkan analisis semua ini.
Perenggan 3-Kami sedang menyediakan dokumen tertentu, kami memanggil dokumen ini secara ringkas - "bagaimana kita harus menyediakan pangkalan data."
Perenggan 4-Pelanggan, melihat semua kemegahan graf dan nombor ini, berada dalam keyakinan kebudak-budakan, naif - kini semuanya akan berfungsi untuk kami, tidak lama lagi. Dan, dia dengan mudah dan tanpa rasa sakit berpisah dengan sumber kewangannya. Pihak pengurusan juga yakin bahawa jurutera kami bekerja dengan baik. Memuatkan pada maksimum.
Perenggan 5-Ulang Langkah 1 dengan kerap.
Roti halia dan donat, lebam dan benjolanRoti halia dan donat:
1. Kehidupan pengurus dan jurutera adalah mudah, boleh diramal dan dipenuhi dengan aktiviti. Semuanya berdengung, semua sibuk.
2. Kehidupan pelanggan juga tidak buruk - dia sentiasa yakin bahawa dia hanya perlu bersabar sedikit dan semuanya akan berjalan lancar. Ia tidak menjadi lebih baik, baik, dunia ini tidak adil, dalam kehidupan seterusnya anda akan bertuah.
Lebam dan benjolan:
1. Lambat laun, akan ada penyedia perkhidmatan serupa yang lebih pantas yang akan melakukan perkara yang sama, tetapi sedikit lebih murah. Dan jika hasilnya sama, mengapa perlu membayar lebih. Yang sekali lagi akan membawa kepada kehilangan penyuap.
2. Ia membosankan. Betapa membosankan apa-apa aktiviti yang tidak bermakna.
3. Seperti versi sebelumnya, tiada perkembangan. Tetapi bagi seorang jurutera, kelemahannya ialah, tidak seperti pilihan pertama, anda perlu sentiasa menjana IBD. Dan ini memerlukan masa. Yang boleh anda belanjakan untuk manfaat orang tersayang. Kerana anda tidak boleh menjaga diri anda, tiada siapa yang peduli tentang anda.
Pilihan 3 - Anda tidak perlu mencipta basikal, anda hanya perlu membelinya dan menunggangnya.
Bukan tanpa alasan bahawa jurutera dari syarikat lain makan piza dengan bir (oh, zaman kegemilangan St. Petersburg pada tahun 90-an). Mari gunakan sistem pemantauan yang dibuat, dinyahpepijat dan berfungsi, dan secara amnya memberi manfaat (sekurang-kurangnya kepada pencipta mereka).
Roti halia dan donat, lebam dan benjolanRoti halia dan donat:
1. Tidak perlu membuang masa untuk memikirkan sesuatu yang telah dicipta. Ambil dan gunakannya.
2. Sistem pemantauan tidak ditulis oleh orang bodoh dan ia, sudah tentu, berguna.
3. Sistem pemantauan yang berfungsi biasanya menyediakan maklumat yang ditapis yang berguna.
Lebam dan benjolan:
1. Jurutera dalam kes ini bukan jurutera, tetapi hanya pengguna produk orang lain. Atau pengguna.
2. Pelanggan mesti yakin tentang keperluan untuk membeli sesuatu yang, secara amnya, dia tidak mahu memahami, dan tidak sepatutnya, dan secara umum bajet untuk tahun itu telah diluluskan dan tidak akan berubah. Kemudian anda perlu memperuntukkan sumber yang berasingan dan mengkonfigurasinya untuk sistem tertentu. Itu. pertama anda perlu bayar, bayar dan bayar semula. Dan pelanggan itu kedekut. Inilah norma kehidupan ini.
Apa yang perlu dilakukan - Chernyshevsky? Soalan anda sangat relevan. (Dengan)
Dalam kes khusus ini dan situasi semasa, anda boleh melakukannya dengan sedikit berbeza - jom buat sistem pemantauan sendiri.
Sudah tentu, bukan sistem, dalam erti kata penuh, yang terlalu kuat dan sombong, tetapi sekurang-kurangnya entah bagaimana memudahkan tugas anda dan mengumpul lebih banyak maklumat untuk menyelesaikan insiden prestasi. Agar tidak mendapati diri anda dalam situasi - "pergi ke sana, saya tidak tahu di mana, cari sesuatu, saya tidak tahu apa."
Apakah kebaikan dan keburukan pilihan ini:
Kelebihan:
1. Ini menarik. Nah, sekurang-kurangnya ia lebih menarik daripada "fail data mengecil, mengubah ruang jadual, dll."
2. Ini adalah kemahiran baru dan perkembangan baru. Yang, lambat laun, akan memberi anda roti halia dan donat yang sangat berfaedah.
Cons:
1. Anda perlu bekerja. Berusaha keras.
2. Anda perlu kerap menerangkan maksud dan prospek semua aktiviti.
3. Sesuatu perlu dikorbankan, kerana satu-satunya sumber yang tersedia untuk seorang jurutera - masa - adalah terhad oleh Alam Semesta.
4. Perkara yang paling teruk dan tidak menyenangkan - hasilnya mungkin omong kosong seperti "Bukan tikus, bukan katak, tetapi haiwan yang tidak dikenali."
Mereka yang tidak mengambil risiko tidak minum champagne.
Jadi - keseronokan bermula.
Idea umum - secara skematik
(Ilustrasi diambil dari artikel «»)
Penjelasan:
- Sambungan PostgreSQL standard "pg_stat_statements" dipasang dalam pangkalan data sasaran.
- Dalam pangkalan data pemantauan, kami mencipta satu set jadual perkhidmatan untuk menyimpan sejarah pg_stat_statements pada peringkat awal dan untuk menyediakan metrik dan pemantauan pada masa hadapan
- Pada hos pemantauan, kami mencipta satu set skrip bash, termasuk skrip untuk menjana insiden dalam sistem tiket.
Meja perkhidmatan
Pertama, ERD yang dipermudahkan skema, apa yang berlaku pada akhirnya:
Penerangan ringkas tentang jadualtitik akhir β hos, titik sambungan kepada contoh
pangkalan data - parameter pangkalan data
pg_stat_sejarah - jadual sejarah untuk menyimpan petikan sementara paparan pg_stat_statements pangkalan data sasaran
metric_glossary - kamus metrik prestasi
metric_config β konfigurasi metrik individu
metrik β metrik khusus untuk permintaan yang sedang dipantau
metric_alert_history - sejarah amaran prestasi
log_query β jadual perkhidmatan untuk menyimpan rekod yang dihuraikan daripada fail log PostgreSQL yang dimuat turun daripada AWS
asas β parameter tempoh masa yang digunakan sebagai asas
pemeriksaan β konfigurasi metrik untuk menyemak status pangkalan data
checkpoint_alert_history β sejarah amaran metrik pemeriksaan kesihatan pangkalan data
pg_stat_db_queries β jadual perkhidmatan permintaan aktif
log aktiviti β jadual perkhidmatan log aktiviti
perangkap_oid β jadual perkhidmatan konfigurasi perangkap
Peringkat 1 - kumpulkan maklumat statistik tentang prestasi dan terima laporan
Jadual digunakan untuk menyimpan maklumat statistik pg_stat_sejarah
pg_stat_history struktur jadual
Jadual Lajur "public.pg_stat_history" | Taip | Pengubah suai---------------------+------------------------- - ------------------------------------------ id | integer | bukan null lalai nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | cap waktu tanpa zon waktu | id_pangkalan data | integer | dbid | oid | userid | oid | id pertanyaan | bigint | pertanyaan | teks | panggilan | bigint | jumlah_masa | ketepatan berganda | min_time | ketepatan berganda | max_time | ketepatan berganda | mean_time | ketepatan berganda | stddev_time | ketepatan berganda | baris | 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 | ketepatan berganda | blk_write_time | ketepatan berganda | baseline_id | integer | Indeks: "pg_stat_history_pkey" KUNCI UTAMA, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Kekangan kunci asing: (Yfdatabase_idx RECES) ) PADA DELETE CASCADE
Seperti yang anda lihat, jadual hanyalah data paparan terkumpul pg_stat_statements dalam pangkalan data sasaran.
Menggunakan jadual ini sangat mudah
pg_stat_sejarah akan mewakili statistik terkumpul pelaksanaan pertanyaan untuk setiap jam. Pada permulaan setiap jam, selepas mengisi jadual, statistik pg_stat_statements set semula dengan pg_stat_statements_reset().
Nota: Statistik dikumpul untuk pertanyaan dengan tempoh pelaksanaan lebih daripada 1 saat.
Mengisi jadual pg_stat_history
--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
endpoint_rec record ;
database_rec record ;
pg_stat_snapshot record ;
current_snapshot_timestamp timestamp without time zone;
BEGIN
current_snapshot_timestamp = date_trunc('minute',now());
FOR endpoint_rec IN SELECT * FROM endpoint
LOOP
FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
LOOP
RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
--Connect to the target DB
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
SELECT
*
INTO
pg_stat_snapshot
FROM dblink('LINK1',
'SELECT
dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) ,
SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() )
GROUP BY dbid
'
)
AS t
( dbid oid , calls bigint ,
total_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
);
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid , calls ,total_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
FOR pg_stat_snapshot IN
--All queries with max_time greater than 1000 ms
SELECT
*
FROM dblink('LINK1',
'SELECT
dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,
local_blks_hit , local_blks_read , local_blks_dirtied ,
local_blks_written , temp_blks_read , temp_blks_written , blk_read_time ,
blk_write_time
FROM pg_stat_statements
WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 )
'
)
AS t
( 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
)
LOOP
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid ,userid , queryid , query , calls ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
END LOOP;
PERFORM dblink_disconnect('LINK1');
END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;Akibatnya, selepas beberapa tempoh masa dalam jadual pg_stat_sejarah kami akan mempunyai satu set petikan kandungan jadual pg_stat_statements pangkalan data sasaran.
Sebenarnya melaporkan
Menggunakan pertanyaan mudah, anda boleh mendapatkan laporan yang agak berguna dan menarik.
Data agregat untuk tempoh masa tertentu
Permintaan
SELECT
database_id ,
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
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;Masa DB
to_char(selang '1 milisaat' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
Masa I/O
to_char(selang '1 milisaat' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL mengikut jumlah_masa
Permintaan
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(total_time) AS total_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10------------------------------------------------- ----------------------------------- | TOP10 SQL MENGIKUT JUMLAH MASA PELAKSANAAN | #| id pertanyaan| panggilan| panggilan %| jumlah_masa (ms) | dbtime % +----------+-----------+-----------+-----------+ ------ --------------------------+---------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 ms.)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215 ms.)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057 ms.)| 3.44 | 4| 655729273| 1| .00000|00:02:01.869( 121869.981 ms.)| 3.25 | 5| 2460318461| 1| .00000|00:01:33.113( 93113.835 ms.)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345| 1| .00000|00:00:06.156( 6156.352 ms.)| .16 | 8| 3644780286| 1| .00000|00:00:01.063( 1063.830 ms.)| .03
TOP10 SQL mengikut jumlah masa I/O
Permintaan
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(blk_read_time + blk_write_time) AS io_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10------------------------------------------------- ---------------------------------------------------- | SQL TOP10 MENGIKUT JUMLAH MASA I/O | #| id pertanyaan| panggilan| panggilan %| Masa I/O (ms)|db masa I/O % +----+-----------+-----------+------ -------------------------+--------------------------------+----- ------ -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 ms.)| 18.35 | 4| 2460318461| 1| .00000|00:04:05.981( 245981.117 ms.)| 14.93 | 5| 1484454471| 4| .00001|00:00:39.144( 39144.221 ms.)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 ms.)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 ms.)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 ms.)| .03
TOP10 SQL mengikut masa maksimum pelaksanaan
Permintaan
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
max_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC
LIMIT 10------------------------------------------------- ---------------------------------------------------- | SQL TOP10 MENGIKUT MASA PELAKSANAAN MAX | #| syot kilat| snapshotID| id pertanyaan| max_time (ms) +----+------------------+-----------+--------- ------------------------------------------ | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019 ms.) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00 ms.) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390 ms.) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00(01 ms.) | 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452(93452.150 ms.) | 7| 04.04.2019/17/00 4150:2460318461| 00| 01| 33.113:93113.835:8( 04.04.2019 ms.) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16(00 ms.) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 ms.) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)
TOP10 SQL by SHARED buffer baca/tulis
Permintaan
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
shared_blks_read ,
shared_blks_written
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC , 5 DESC
LIMIT 10------------------------------------------------- ------------------------------------------ | TOP10 SQL OLEH PENAPI KONGSI BACA/TULIS | #| syot kilat| snapshotID| id pertanyaan| blok kongsi dibaca| tulis blok kongsi +----+-----+-----------+---------- ---------------------------------- | 1| 04.04.2019/17/00 4153:821760255| 797308| 0| 2| 04.04.2019 | 16| 00/4146/821760255 797308:0| 3| 05.04.2019| 01| 03 | 4169| 655729273/797158/0 4:04.04.2019| 16| 00| 4144| 4152624390 | 756514| 0/5/04.04.2019 17:00| 4151| 4152624390| 756514| 0 | 6| 04.04.2019/17/00 4150:2460318461| 734117| 0| 7| 04.04.2019 | 17| 00/4155/3644780286 52973:0| 8| 05.04.2019| 01| 03 | 4168| 1053044345/52818/0 9:04.04.2019| 15| 00| 4141| 2194493487 | 52813| 0/10/04.04.2019 16:00| 4147| 2194493487| 52813| 0 | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX ------------------------------------------------- ------------------------------------------
Histogram pengedaran permintaan mengikut masa pelaksanaan maksimum
permintaan
SELECT
MIN(max_time) AS hist_min ,
MAX(max_time) AS hist_max ,
(( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;
SELECT
SUM(calls) AS calls
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id =DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( max_time >= hist_current_min AND max_time < hist_current_max ) ;
|------------------------------------------------ - ------------------------------------------ | MAX_TIME HISTOGRAM | JUMLAH PANGGILAN: 33851920 | MASA MIN: 00:00:01.063 | MASA MAKSIMA: 00:02:01.869 ------------------------------------------ ---------------------------------------------------- | tempoh min| tempoh maksimum| panggilan +---------------------------------+-------------------------- ----------------------+---------- | 00:00:01.063( 1063.830 ms.) | 00:00:13.144( 13144.445 ms.) | 9 | 00:00:13.144( 13144.445 ms.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 00:00:37.305( 37305.675 ms.) | 0 | 00:00:37.305( 37305.675 ms.) | 00:00:49.386( 49386.290 ms.) | 0 | 00:00:49.386( 49386.290 ms.) | 00:01:01.466( 61466.906 ms.) | 0 | 00:01:01.466( 61466.906 ms.) | 00:01:13.547( 73547.521 ms.) | 0 | 00:01:13.547( 73547.521 ms.) | 00:01:25.628( 85628.136 ms.) | 0 | 00:01:25.628( 85628.136 ms.) | 00:01:37.708( 97708.751 ms.) | 4 | 00:01:37.708( 97708.751 ms.) | 00:01:49.789( 109789.366 ms.) | 2 | 00:01:49.789( 109789.366 ms.) | 00:02:01.869( 121869.981 ms.) | 0
TOP10 Syot Kilat mengikut Pertanyaan sesaat
permintaan
--pg_qps.sql
--Calculate Query Per Second
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
pg_stat_history_rec record ;
prev_pg_stat_history_id integer ;
prev_pg_stat_history_rec record;
total_seconds double precision ;
result double precision;
BEGIN
result = 0 ;
SELECT *
INTO pg_stat_history_rec
FROM
pg_stat_history
WHERE id = pg_stat_history_id ;
IF pg_stat_history_rec.snapshot_timestamp IS NULL
THEN
RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
END IF ;
--RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id ,
pg_stat_history_rec.snapshot_timestamp ;
SELECT
MAX(id)
INTO
prev_pg_stat_history_id
FROM
pg_stat_history
WHERE
database_id = pg_stat_history_rec.database_id AND
queryid IS NULL AND
id < pg_stat_history_rec.id ;
IF prev_pg_stat_history_id IS NULL
THEN
RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
RETURN NULL ;
END IF;
SELECT *
INTO prev_pg_stat_history_rec
FROM
pg_stat_history
WHERE id = prev_pg_stat_history_id ;
--RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;
total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
--RAISE NOTICE 'total_seconds = % ', total_seconds ;
--RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;
IF total_seconds > 0
THEN
result = pg_stat_history_rec.calls / total_seconds ;
ELSE
result = 0 ;
END IF;
RETURN result ;
END
$$ LANGUAGE plpgsql;
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( select pg_qps( id )) IS NOT NULL
ORDER BY 5 DESC
LIMIT 10
|------------------------------------------------ - ------------------------------------------ | Gambar TOP10 yang dipesan mengikut nombor QueryPerSeconds -------------------------------------------- ------------------------------------------------- ------------------------------------------------- | #| syot kilat| snapshotID| panggilan| jumlah dbtime| QPS| Masa I/O| Masa I/O % +-----+---------------------------------+------- ----+----------------------------------+---------- --------------------------+----------- | 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00 ms.)| 00| 01.470:1470.110:376( 2 ms.)| .04.04.2019 | 17| 00/4149/3529197 00:11| 48.830| 708830.618| 980.332:00:12( 47.834 ms.)| 767834.052| 108.324:3:04.04.2019( 16 ms.)| 00 | 4143| 3525360/00/10 13.492:613492.351| 979.267| 00| 08:41.396:521396.555( 84.988 ms.)| 4| 04.04.2019:21:03(4163 ms.)| 2781536 | 00| 03/06.470/186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019 ms.)| 19| 03:4159:2890362( 00 ms.)| .03 | 16.784| 196784.755 776.979:00| 00| 01.441| 1441.386:732:6( 04.04.2019 ms.)| 14| 00:4137:2397326( 00 ms.)| .04 | 43.033| 283033.854/665.924/00 00:00.024| 24.505| 009| 7:04.04.2019:15( 00 ms.)| 4139| 2394416:00:04(51.435 ms.)| .291435.010 | 665.116| 00/00/12.025 12025.895:4.126| 8| 04.04.2019| 13:00:4135( 2373043 ms.)| 00| 04:26.791:266791.988( 659.179 ms.)| 00 | 00| 00.064 64.261:024| 9| 05.04.2019| 01:03:4167( 4387191 ms.)| 00| 06:51.380:411380.293( 609.332 ms.)| .00 | 05| 18.847/318847.407/77.507 10:04.04.2019| 18| 01| 4157:1145596:00( 01 ms.)| 19.217| 79217.372:313.004:00( 00 ms.)| 01.319 | 1319.676| 1.666/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX
Sejarah Pelaksanaan Setiap Jam dengan QueryPerSeconds dan Masa I/O
Permintaan
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2
|----------------------------------------------------------------------------------------------- | HOURLY EXECUTION HISTORY WITH QueryPerSeconds and I/O Time ----------------------------------------------------------------------------------------------------------------------------------------------- | QUERY PER SECOND HISTORY | #| snapshot| snapshotID| calls| total dbtime| QPS| I/O time| I/O time % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 1| 04.04.2019 11:00| 4131| 3747| 00:00:00.835( 835.374 ms.)| 1.041| 00:00:00.000( .000 ms.)| .000 | 2| 04.04.2019 12:00| 4133| 1002722| 00:01:52.419( 112419.376 ms.)| 278.534| 00:00:00.149( 149.105 ms.)| .133 | 3| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 4| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 5| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 6| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396( 521396.555 ms.)| 84.988 | 7| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 8| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666 | 9| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 10| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 11| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 12| 04.04.2019 23:03| 4165| 1443155| 00:01:34.467( 94467.539 ms.)| 200.438| 00:00:00.015( 15.287 ms.)| .016 | 13| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 14| 05.04.2019 02:03| 4171| 189852| 00:00:10.989( 10989.899 ms.)| 52.737| 00:00:00.539( 539.110 ms.)| 4.906 | 15| 05.04.2019 03:01| 4173| 3627| 00:00:00.103( 103.000 ms.)| 1.042| 00:00:00.004( 4.131 ms.)| 4.010 | 16| 05.04.2019 04:00| 4175| 3627| 00:00:00.085( 85.235 ms.)| 1.025| 00:00:00.003( 3.811 ms.)| 4.471 | 17| 05.04.2019 05:00| 4177| 3747| 00:00:00.849( 849.454 ms.)| 1.041| 00:00:00.006( 6.124 ms.)| .721 | 18| 05.04.2019 06:00| 4179| 3747| 00:00:00.849( 849.561 ms.)| 1.041| 00:00:00.000( .051 ms.)| .006 | 19| 05.04.2019 07:00| 4181| 3747| 00:00:00.839( 839.416 ms.)| 1.041| 00:00:00.000( .062 ms.)| .007 | 20| 05.04.2019 08:00| 4183| 3747| 00:00:00.846( 846.382 ms.)| 1.041| 00:00:00.000( .007 ms.)| .001 | 21| 05.04.2019 09:00| 4185| 3747| 00:00:00.855( 855.426 ms.)| 1.041| 00:00:00.000( .065 ms.)| .008 | 22| 05.04.2019 10:00| 4187| 3797| 00:01:40.150( 100150.165 ms.)| 1.055| 00:00:21.845( 21845.217 ms.)| 21.812
Teks semua pilihan SQL
Permintaan
SELECT
queryid ,
query
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query
Jumlah
Seperti yang anda lihat, menggunakan cara yang agak mudah, anda boleh mendapatkan banyak maklumat berguna tentang beban kerja dan keadaan pangkalan data.
Catatan:Jika kami merekodkan id pertanyaan dalam pertanyaan, kami akan mendapat sejarah untuk pertanyaan berasingan (untuk menjimatkan ruang, laporan untuk pertanyaan berasingan akan ditinggalkan).
Jadi, data statistik tentang prestasi pertanyaan tersedia dan dikumpul.
Tahap pertama "pengumpulan data statistik" selesai.
Anda boleh meneruskan ke peringkat kedua - "menyediakan metrik prestasi".
Tetapi itu cerita lain.
Perlu diteruskan ...
Sumber: www.habr.com