Transkrip laporan Alexei Lesovsky tahun 2015 "Menyelami statistik internal PostgreSQL"
Penafian dari penulis laporan: Saya perhatikan bahwa laporan ini tertanggal November 2015 - lebih dari 4 tahun telah berlalu dan banyak waktu telah berlalu. Versi 9.4 yang dibahas dalam laporan tidak lagi didukung. Selama 4 tahun terakhir, telah dirilis 5 rilis baru yang didalamnya terdapat banyak inovasi, perbaikan dan perubahan terkait statistik, dan beberapa materi sudah ketinggalan zaman dan tidak relevan. Saat saya mengulas, saya mencoba menandai tempat-tempat ini agar tidak menyesatkan pembaca. Saya tidak menulis ulang bagian-bagian ini, ada banyak dan hasilnya akan menjadi laporan yang sangat berbeda.
DBMS PostgreSQL adalah mekanisme yang sangat besar, dan mekanisme ini terdiri dari banyak subsistem, yang operasi terkoordinasinya secara langsung mempengaruhi kinerja DBMS. Selama pengoperasian, statistik dan informasi tentang pengoperasian komponen dikumpulkan, yang memungkinkan Anda mengevaluasi efektivitas PostgreSQL dan mengambil tindakan untuk meningkatkan kinerja. Namun, informasi ini banyak dan disajikan dalam bentuk yang agak disederhanakan. Memproses informasi ini dan menafsirkannya terkadang merupakan tugas yang tidak sepele, dan “kebun binatang” alat dan utilitas dapat dengan mudah membingungkan bahkan seorang DBA tingkat lanjut.
Selamat siang Nama saya Aleksey. Seperti yang Ilya katakan, saya akan berbicara tentang statistik PostgreSQL.
Statistik aktivitas PostgreSQL. PostgreSQL memiliki dua statistik. Statistik aktivitas yang akan dibahas. Dan statistik penjadwal tentang distribusi data. Saya akan berbicara secara khusus tentang statistik aktivitas PostgreSQL, yang memungkinkan kita menilai kinerja dan meningkatkannya.
Saya akan memberi tahu Anda cara menggunakan statistik secara efektif untuk menyelesaikan berbagai masalah yang Anda miliki atau mungkin miliki.
Apa yang tidak ada dalam laporan? Dalam laporan saya tidak akan menyentuh statistik penjadwal, karena... Ini adalah topik terpisah untuk laporan terpisah tentang bagaimana data disimpan dalam database dan bagaimana perencana kueri mendapatkan gambaran tentang karakteristik kualitatif dan kuantitatif data ini.
Dan tidak akan ada review alat, saya tidak akan membandingkan produk satu dengan produk lainnya. Tidak akan ada iklan. Mari kita kesampingkan hal itu.
Saya ingin menunjukkan kepada Anda bahwa menggunakan statistik itu bermanfaat. Itu perlu. Aman untuk digunakan. Yang kita butuhkan hanyalah SQL reguler dan pengetahuan dasar tentang SQL.
Dan mari kita bicara tentang statistik mana yang harus dipilih untuk memecahkan masalah.
Jika kita melihat PostgreSQL dan menjalankan perintah pada sistem operasi untuk melihat proses, kita akan melihat "kotak hitam". Kita akan melihat beberapa proses yang melakukan sesuatu, dan dari namanya kita dapat membayangkan secara kasar apa yang mereka lakukan di sana, apa yang mereka lakukan. Namun, pada dasarnya, ini adalah kotak hitam; kita tidak bisa melihat ke dalamnya.
Kita dapat melihat beban CPU masuk top, kita dapat melihat penggunaan memori oleh beberapa utilitas sistem, namun kita tidak dapat melihat ke dalam PostgreSQL. Untuk ini kita memerlukan alat lain.
Dan selanjutnya, saya akan memberi tahu Anda di mana waktu dihabiskan. Jika kita membayangkan PostgreSQL dalam bentuk diagram seperti itu, maka kita bisa menjawab di mana waktu yang dihabiskan. Ini adalah dua hal: memproses permintaan klien dari aplikasi dan tugas latar belakang yang dilakukan PostgreSQL agar tetap berjalan.
Jika kita mulai melihat dari pojok kiri atas, kita dapat melihat bagaimana permintaan klien diproses. Permintaan datang dari aplikasi dan sesi klien dibuka untuk pekerjaan lebih lanjut. Permintaan dikirim ke penjadwal. Penjadwal membuat rencana kueri. Mengirimkannya lebih jauh untuk dieksekusi. Ada semacam input/output data blok yang terkait dengan tabel dan indeks. Data yang diperlukan dibaca dari disk ke dalam memori ke dalam area khusus "buffer bersama". Hasil permintaan, jika diperbarui, dihapus, dicatat dalam log transaksi di WAL. Beberapa informasi statistik berakhir di log atau pengumpul statistik. Dan hasil permintaan tersebut dikirim kembali ke klien. Setelah itu klien dapat mengulangi semuanya lagi dengan permintaan baru.
Bagaimana dengan tugas latar belakang dan proses latar belakang? Kami memiliki beberapa proses yang menjaga database tetap aktif dan berjalan dalam mode operasi normal. Proses-proses ini juga akan dibahas dalam laporan: autovacuum, checkpointer, proses terkait replikasi, penulis latar belakang. Saya akan membahas masing-masingnya saat saya melaporkannya.
Masalah apa saja yang ada pada statistik?
- Ada banyak informasi. PostgreSQL 9.4 menyediakan 109 metrik untuk melihat data statistik. Namun, jika database menyimpan banyak tabel, skema, database, maka semua metrik ini harus dikalikan dengan jumlah tabel, database yang sesuai. Artinya, ada lebih banyak informasi. Dan sangat mudah untuk tenggelam di dalamnya.
- Masalah selanjutnya adalah statistik direpresentasikan dengan counter. Jika kita melihat statistik ini, kita akan melihat penghitung yang terus meningkat. Dan jika banyak waktu telah berlalu sejak statistik disetel ulang, kita akan melihat nilainya dalam miliaran. Dan mereka tidak memberi tahu kami apa pun.
- Tidak ada cerita. Jika Anda mengalami kegagalan, sesuatu jatuh 15-30 menit yang lalu, Anda tidak akan dapat menggunakan statistik dan melihat apa yang terjadi 15-30 menit yang lalu. Ini masalah.
- Kurangnya alat yang dibangun ke dalam PostgreSQL adalah sebuah masalah. Pengembang kernel tidak menyediakan utilitas apa pun. Mereka tidak memiliki hal seperti itu. Mereka hanya menyediakan statistik dalam database. Gunakan, buat permintaan, lakukan apa pun yang Anda inginkan.
- Karena tidak ada alat bawaan PostgreSQL, hal ini menyebabkan masalah lain. Banyak alat pihak ketiga. Setiap perusahaan yang mempunyai tangan langsung mencoba menulis programnya sendiri. Hasilnya, komunitas memiliki banyak alat yang dapat digunakan untuk bekerja dengan statistik. Dan beberapa alat memiliki kemampuan tertentu, alat lainnya tidak memiliki kemampuan lain, atau ada beberapa kemampuan baru. Dan muncul situasi di mana Anda perlu menggunakan dua, tiga atau empat alat yang saling tumpang tindih dan memiliki fungsi berbeda. Ini sangat tidak menyenangkan.
Apa yang berikut ini? Penting untuk dapat mengambil statistik secara langsung, agar tidak bergantung pada program, atau memperbaiki sendiri program ini: tambahkan beberapa fungsi untuk mendapatkan keuntungan Anda sendiri.
Dan Anda memerlukan pengetahuan dasar tentang SQL. Untuk mendapatkan beberapa data dari statistik, Anda perlu membuat kueri SQL, yaitu Anda perlu mengetahui bagaimana pilih dan gabung dikompilasi.
Statistik memberi tahu kita beberapa hal. Mereka dapat dibagi ke dalam beberapa kategori.
- Kategori pertama adalah peristiwa yang terjadi di database. Ini adalah ketika beberapa peristiwa terjadi di database: permintaan, akses ke tabel, autovacuum, penerapan, maka ini semua adalah peristiwa. Penghitung yang terkait dengan peristiwa ini bertambah. Dan kami dapat melacak peristiwa ini.
- Kategori kedua adalah properti objek seperti tabel dan database. Mereka memiliki properti. Ini adalah ukuran tabelnya. Kita dapat melacak pertumbuhan tabel dan pertumbuhan indeks. Kita bisa melihat perubahannya secara dinamis.
- Dan kategori ketiga adalah waktu yang dihabiskan untuk acara tersebut. Permintaan adalah sebuah peristiwa. Ia memiliki ukuran durasi spesifiknya sendiri. Dimulai di sini, berakhir di sini. Kita bisa melacaknya. Entah waktu yang diperlukan untuk membaca blok dari disk atau menulisnya. Hal-hal seperti itu juga diawasi.
Sumber statistik disajikan sebagai berikut:
- Dalam memori bersama (shared buffer) terdapat segmen untuk menyimpan data statis, ada juga counter yang terus bertambah ketika terjadi peristiwa tertentu, atau timbul momen tertentu dalam pengoperasian database.
- Semua penghitung ini tidak dapat diakses oleh pengguna dan bahkan tidak dapat diakses oleh administrator. Ini adalah hal-hal tingkat rendah. Untuk mengaksesnya, PostgreSQL menyediakan antarmuka berupa fungsi SQL. Kita dapat memilih lemparan menggunakan fungsi ini dan mendapatkan semacam metrik (atau kumpulan metrik).
- Namun, menggunakan fungsi-fungsi ini tidak selalu mudah, jadi fungsi adalah dasar untuk tampilan (VIEW). Ini adalah tabel virtual yang menyediakan statistik pada subsistem tertentu, atau serangkaian peristiwa tertentu dalam database.
- Tampilan tertanam (VIEW) ini adalah antarmuka pengguna utama untuk bekerja dengan statistik. Mereka tersedia secara default tanpa pengaturan tambahan apa pun, Anda dapat langsung menggunakannya, melihatnya, dan mengambil informasi darinya. Dan kemudian ada kontribusi. Kontribusi bersifat resmi. Anda dapat menginstal paket postgresql-contrib (misalnya, postgresql94-contrib), memuat modul yang diperlukan dalam konfigurasi, menentukan parameternya, memulai ulang PostgreSQL dan Anda dapat menggunakannya. (Catatan. Tergantung pada distribusinya, dalam versi terbaru paket contrib adalah bagian dari paket utama).
- Dan ada kontribusi tidak resmi. Mereka tidak termasuk dalam distribusi standar PostgreSQL. Mereka harus dikompilasi atau diinstal sebagai perpustakaan. Opsinya bisa sangat berbeda, bergantung pada apa yang dibuat oleh pengembang kontribusi tidak resmi ini.
Slide ini menyajikan semua VIEW dan beberapa fungsi yang tersedia di PostgreSQL 9.4. Seperti yang bisa kita lihat, ada banyak sekali. Dan cukup mudah untuk menjadi bingung jika Anda baru pertama kali menemukannya.
Namun jika kita mengambil gambar sebelumnya Как тратится время на PostgreSQL dan kompatibel dengan daftar ini, kami mendapatkan gambar ini. Kita dapat menggunakan setiap tampilan (VIEW) atau setiap fungsi untuk satu tujuan atau lainnya untuk mendapatkan statistik terkait saat PostgreSQL berjalan. Dan kita sudah dapat memperoleh beberapa informasi tentang pengoperasian subsistem tersebut.
Hal pertama yang akan kita lihat adalah pg_stat_database. Seperti yang bisa kita lihat, ini adalah pertunjukan. Ada banyak informasi di dalamnya. Informasi paling bervariasi. Dan ini memberikan pengetahuan yang sangat berguna tentang apa yang terjadi di database kami.
Hal bermanfaat apa yang bisa kita ambil dari sana? Mari kita mulai dengan hal yang paling sederhana.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Hal pertama yang bisa kita lihat adalah persentase cache hit. Tingkat cache hit adalah metrik yang berguna. Ini memungkinkan Anda memperkirakan berapa banyak data yang diambil dari cache buffer bersama dan berapa banyak yang dibaca dari disk.
Sudah jelas itu semakin banyak cache yang kita miliki, semakin baik. Kami mengukur metrik ini sebagai persentase. Dan, misalnya, jika persentase cache hit ini lebih dari 90%, maka ini bagus. Jika turun di bawah 90%, itu berarti kita tidak memiliki cukup memori untuk menyimpan data panas di memori. Dan untuk menggunakan data ini, PostgreSQL terpaksa mengakses disk dan ini lebih lambat dibandingkan jika data dibaca dari memori. Dan Anda perlu memikirkan untuk menambah memori: menambah buffer bersama, atau menambah memori perangkat keras (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Apa lagi yang bisa Anda ambil dari pertunjukan ini? Anda dapat melihat anomali yang terjadi di database. Apa yang ditampilkan di sini? Ada penerapan, pengembalian, pembuatan file sementara, ukurannya, kebuntuan, dan konflik.
Kita bisa menggunakan permintaan ini. SQL ini cukup sederhana. Dan kita bisa melihat datanya di sini.
Dan berikut adalah nilai ambang batasnya. Kami melihat rasio komitmen dan pengembalian. Komit adalah konfirmasi sukses suatu transaksi. Rollback adalah rollback, yaitu suatu transaksi melakukan beberapa pekerjaan, membebani database, menghitung sesuatu, dan kemudian terjadi kegagalan dan hasil transaksi dibuang. Itu adalah jumlah rollback yang terus meningkat itu buruk. Dan Anda harus menghindarinya, dan mengedit kodenya agar hal ini tidak terjadi.
Konflik terkait dengan replikasi. Dan hal tersebut juga harus dihindari. Jika Anda memiliki beberapa pertanyaan yang dijalankan pada replika dan konflik muncul, Anda perlu menyelesaikan konflik ini dan melihat apa yang terjadi. Detailnya dapat ditemukan di log. Dan hilangkan situasi konflik sehingga permintaan aplikasi berfungsi tanpa kesalahan.
Kebuntuan juga merupakan situasi yang buruk. Ketika permintaan berebut sumber daya, satu permintaan mengakses satu sumber daya dan mengambil kunci, permintaan kedua mengakses sumber daya kedua dan juga mengambil kunci, lalu kedua permintaan mengakses sumber daya satu sama lain dan memblokir sambil menunggu tetangga melepaskan kunci. Ini juga merupakan situasi yang problematis. Masalah ini perlu diatasi pada tingkat penulisan ulang aplikasi dan pembuatan serial akses ke sumber daya. Dan jika Anda melihat kebuntuan Anda terus meningkat, Anda perlu melihat detail di log, menganalisis situasi yang muncul, dan melihat apa masalahnya.
File sementara (temp_files) juga buruk. Ketika permintaan pengguna tidak memiliki cukup memori untuk menampung data operasional dan sementara, maka permintaan tersebut akan membuat file pada disk. Dan semua operasi yang dapat dilakukan di buffer sementara di memori mulai dilakukan di disk. Ini lambat. Ini meningkatkan waktu eksekusi kueri. Dan klien yang mengirim permintaan ke PostgreSQL akan menerima tanggapan beberapa saat kemudian. Jika semua operasi ini dilakukan di memori, Postgres akan merespons lebih cepat dan klien akan menunggu lebih sedikit.
Pg_stat_bgwriter - Tampilan ini menjelaskan pengoperasian dua subsistem latar belakang PostgreSQL: ini checkpointer и background writer.
Pertama, mari kita lihat titik kontrol, yang disebut. checkpoints. Apa yang dimaksud dengan titik kontrol? Pos pemeriksaan adalah posisi dalam log transaksi yang menunjukkan bahwa semua perubahan data yang dicatat dalam log telah berhasil disinkronkan dengan data pada disk. Prosesnya, bergantung pada beban kerja dan pengaturan, bisa memakan waktu lama dan sebagian besar terdiri dari sinkronisasi halaman kotor di buffer bersama dengan file data di disk. Untuk apa? Jika PostgreSQL terus-menerus mengakses disk dan mengambil data dari sana, dan menulis data pada setiap akses, itu akan menjadi lambat. Oleh karena itu, PostgreSQL memiliki segmen memori yang ukurannya bergantung pada pengaturan dalam konfigurasi. Postgres menyimpan data langsung dalam memori ini untuk diproses atau dibuat kuerinya nanti. Jika ada permintaan untuk mengubah data, itu akan diubah. Dan kami mendapatkan dua versi data. Yang satu ada di memori kita, yang lain ada di disk. Dan secara berkala Anda perlu menyinkronkan data ini. Kita perlu menyinkronkan apa yang diubah di memori ke disk. Untuk ini, Anda memerlukan pos pemeriksaan.
Pos pemeriksaan melewati buffer bersama, menandai halaman kotor yang diperlukan untuk pos pemeriksaan. Kemudian ia meluncurkan lintasan kedua melalui buffer bersama. Dan halaman-halaman yang ditandai untuk pos pemeriksaan, sudah disinkronkan. Dengan cara ini data disinkronkan dengan disk.
Ada dua jenis pos pemeriksaan. Satu pos pemeriksaan dijalankan dengan batas waktu. Pos pemeriksaan ini berguna dan bagus – checkpoint_timed. Dan ada pos pemeriksaan sesuai permintaan - checkpoint required. Pos pemeriksaan ini terjadi ketika kita memiliki catatan data yang sangat besar. Kami mencatat banyak log transaksi. Dan PostgreSQL percaya bahwa mereka perlu menyinkronkan semua ini secepat mungkin, membuat pos pemeriksaan, dan melanjutkan.
Dan jika Anda melihat statistiknya pg_stat_bgwriter dan melihat apa yang kamu punya checkpoint_req jauh lebih besar dari checkpoint_timed, maka ini buruk. Mengapa buruk? Ini berarti PostgreSQL terus-menerus mengalami tekanan saat perlu menulis data ke disk. Pos pemeriksaan batas waktu tidak terlalu menegangkan dan dilakukan sesuai dengan jadwal internal dan tersebar dari waktu ke waktu. PostgreSQL memiliki kemampuan untuk menjeda pekerjaan dan tidak membebani subsistem disk. Ini berguna untuk PostgreSQL. Dan kueri yang dijalankan selama pos pemeriksaan tidak akan mengalami tekanan karena subsistem disk sedang sibuk.
Dan untuk mengatur checkpoint ada tiga parameter:
сheckpoint_segments.сheckpoint_timeout.сheckpoint_competion_target.
Mereka memungkinkan Anda untuk mengatur pengoperasian titik kontrol. Tapi saya tidak akan membahasnya secara mendalam. Pengaruh mereka adalah topik tersendiri.
peringatan: Versi 9.4 yang dibahas dalam laporan tidak lagi relevan. Dalam versi modern PostgreSQL parameternya checkpoint_segments digantikan oleh parameter min_wal_size и max_wal_size.
Subsistem berikutnya adalah penulis latar belakang - background writer. Apa yang dia lakukan? Ini berjalan terus-menerus dalam lingkaran tanpa akhir. Memindai halaman di buffer bersama dan membuang halaman kotor yang ditemukan ke disk. Oleh karena itu, ini membantu checkpointer melakukan lebih sedikit pekerjaan selama eksekusi checkpoint.
Untuk apa lagi itu diperlukan? Ini menyediakan kebutuhan akan halaman kosong di buffer bersama jika diperlukan secara tiba-tiba (dalam jumlah besar dan segera) untuk menampung data. Misalkan sebuah situasi muncul ketika halaman kosong diperlukan untuk menyelesaikan permintaan dan halaman tersebut sudah berada di buffer bersama. Pascagresif backend dia hanya mengambilnya dan menggunakannya, dia tidak perlu membersihkan apa pun sendiri. Tetapi jika tiba-tiba tidak ada halaman seperti itu, backend berhenti bekerja dan mulai mencari halaman untuk membuangnya ke disk dan mengambilnya untuk kebutuhannya sendiri - yang berdampak negatif pada waktu eksekusi permintaan saat ini. Jika Anda melihat bahwa Anda memiliki parameter maxwritten_clean besar, ini berarti penulis latar belakang tidak melakukan tugasnya dan Anda perlu meningkatkan parameternya bgwriter_lru_maxpages, sehingga dia dapat melakukan lebih banyak pekerjaan dalam satu siklus, menyelesaikan lebih banyak halaman.
Dan indikator lain yang sangat berguna adalah buffers_backend_fsync. Backend tidak disinkronkan karena lambat. Mereka meneruskan fsync ke pos pemeriksaan tumpukan IO. Checkpointer memiliki antriannya sendiri, secara berkala memproses fsync dan menyinkronkan halaman di memori dengan file di disk. Jika antrian di checkpointer besar dan penuh, maka backend terpaksa melakukan fsync sendiri dan ini memperlambat kerja backend, yaitu klien akan menerima respons lebih lambat dari yang seharusnya. Jika Anda melihat nilai Anda lebih besar dari nol, maka ini sudah menjadi masalah dan Anda perlu memperhatikan pengaturan penulis latar belakang dan juga mengevaluasi kinerja subsistem disk.
peringatan: _Teks berikut menjelaskan pandangan statistik yang terkait dengan replikasi. Sebagian besar nama tampilan dan fungsi diganti namanya di Postgres 10. Inti dari penggantian nama adalah penggantian xlog pada wal и location pada lsn dalam nama fungsi/tampilan, dll. Contoh khusus, fungsi pg_xlog_location_diff() diubah namanya menjadi pg_wal_lsn_diff()._
Kami juga punya banyak hal di sini. Tapi kita hanya membutuhkan item yang berhubungan dengan lokasi saja.
Jika kita melihat bahwa semua nilainya sama, maka ini adalah pilihan ideal dan replikanya tidak ketinggalan dari masternya.
Posisi heksadesimal disini adalah posisi pada log transaksi. Itu terus meningkat jika ada aktivitas apa pun di database: menyisipkan, menghapus, dll.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Jika hal-hal ini berbeda, maka ada semacam kelambatan. Lag adalah jeda antara replika dan master, yaitu data berbeda antar server.
Ada tiga alasan kelambatan ini:
- Subsistem disk ini tidak dapat mengatasi sinkronisasi file rekaman.
- Ini adalah kemungkinan kesalahan jaringan, atau kelebihan jaringan, ketika data tidak punya waktu untuk mencapai replika dan tidak dapat mereproduksinya.
- Dan prosesornya. Prosesor adalah kasus yang sangat jarang terjadi. Dan saya melihatnya dua atau tiga kali, tapi ini juga bisa terjadi.
Dan berikut tiga pertanyaan yang memungkinkan kita menggunakan statistik. Kita bisa memperkirakan berapa banyak yang telah kita catat di log transaksi. Ada fungsi seperti itu pg_xlog_location_diff dan kami dapat memperkirakan jeda replikasi dalam byte dan detik. Kami juga menggunakan nilai dari tampilan ini (VIEWs) untuk ini.
Catatan: _Alih-alih pg_xlog_locationFungsi diff() dapat menggunakan operator pengurangan dan mengurangi satu lokasi dari lokasi lainnya. Nyaman.
Ada satu titik lag, yaitu dalam hitungan detik. Jika tidak ada aktivitas pada master, transaksi sudah ada sekitar 15 menit yang lalu dan tidak ada aktivitas, dan jika kita melihat lag pada replika ini, kita akan melihat jeda 15 menit. Ini patut diingat. Dan ini bisa membingungkan ketika Anda melihat kelambatan ini.
Pg_stat_all_tables adalah tampilan berguna lainnya. Ini menunjukkan statistik pada tabel. Ketika kita memiliki tabel dalam database, ada beberapa aktivitas dengannya, beberapa tindakan, kita bisa mendapatkan informasi ini dari tampilan ini.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;Hal pertama yang bisa kita lihat adalah pemindaian berurutan di seluruh tabel. Angka setelah melewati ini belum tentu buruk dan bukan merupakan indikator bahwa kita perlu melakukan sesuatu.
Namun, ada metrik kedua - seq_tup_read. Ini adalah jumlah baris yang dihasilkan dari pemindaian berurutan. Jika jumlah rata-rata melebihi 1, 000, 10, 000, maka ini sudah menjadi indikator bahwa mungkin Anda perlu membuat indeks di suatu tempat sehingga kueri didasarkan pada indeks, atau dimungkinkan untuk mengoptimalkan kueri yang menggunakan pemindaian berurutan seperti itu bahwa hal ini tidak terjadi adalah.
Contoh sederhana - katakanlah permintaan dengan biaya OFFSET dan LIMIT yang besar. Misalnya, 100 baris dalam sebuah tabel dipindai dan setelah itu 000 baris yang diperlukan diambil, dan baris yang dipindai sebelumnya dibuang. Ini juga merupakan kasus yang buruk. Dan pertanyaan seperti itu perlu dioptimalkan. Dan berikut adalah kueri SQL sederhana di mana Anda dapat melihatnya dan mengevaluasi angka yang dihasilkan.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Ukuran tabel juga dapat diperoleh dengan menggunakan tabel ini dan menggunakan fungsi tambahan pg_total_relation_size(), pg_relation_size().
Secara umum, ada metacommand dt и di, yang dapat digunakan di PSQL dan juga melihat ukuran tabel dan indeks.
Namun, penggunaan fungsi membantu kita melihat ukuran tabel, bahkan dengan memperhitungkan indeks, atau tanpa memperhitungkan indeks, dan sudah membuat beberapa perkiraan berdasarkan pertumbuhan database, yaitu bagaimana pertumbuhannya, dengan intensitas apa, dan menarik beberapa kesimpulan tentang optimasi ukuran.
Merekam aktivitas. Apa itu rekaman? Mari kita lihat operasinya UPDATE – operasi memperbarui baris dalam tabel. Faktanya, pembaruan adalah dua operasi (atau bahkan lebih). Ini menyisipkan baris versi baru dan menandai baris versi lama sebagai usang. Selanjutnya, autovacuum akan datang dan membersihkan versi jalur yang sudah ketinggalan zaman ini, menandai tempat ini sebagai tersedia untuk digunakan kembali.
Selain itu, update tidak hanya tentang memperbarui tabel. Ini juga merupakan pembaruan indeks. Jika Anda memiliki banyak indeks di tabel, maka selama pembaruan, semua indeks yang menyertakan bidang yang diperbarui dalam kueri juga perlu diperbarui. Indeks ini juga akan memiliki versi baris basi yang perlu dibersihkan.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;Dan karena desainnya yang baru, UPDATE adalah operasi kelas berat. Tapi hal itu bisa dibuat lebih mudah. Makan hot updates. Mereka muncul di PostgreSQL versi 8.3. Dan apa ini? Ini adalah pembaruan ringan yang tidak menyebabkan indeks dibangun kembali. Artinya, kami memperbarui catatan, tetapi hanya catatan di halaman (yang termasuk dalam tabel) yang diperbarui, dan indeks masih menunjuk ke catatan yang sama di halaman. Ada sedikit logika pengoperasian yang menarik: ketika ruang hampa muncul, rantai ini akan tercipta hot dibangun kembali dan semuanya terus berfungsi tanpa memperbarui indeks, dan semuanya terjadi dengan lebih sedikit sumber daya yang terbuang.
Dan kapan kamu melakukannya n_tup_hot_upd besar, maka itu sangat bagus. Ini berarti pembaruan ringan mendominasi dan ini lebih murah bagi kami dalam hal sumber daya dan semuanya baik-baik saja.
ALTER TABLE table_name SET (fillfactor = 70);Cara menambah volume hot updateov? Kita bisa gunakan fillfactor. Ini menentukan ukuran ruang kosong yang dipesan saat mengisi halaman dalam tabel menggunakan INSERT. Saat sisipan ditambahkan ke tabel, sisipan tersebut memenuhi halaman sepenuhnya dan tidak meninggalkan ruang kosong. Kemudian halaman baru disorot. Data diisi kembali. Dan ini adalah perilaku default, faktor pengisian = 100%.
Kita dapat membuat faktor pengisian menjadi 70%. Artinya, selama penyisipan, halaman baru disorot, tetapi hanya 70% halaman yang terisi. Dan kami memiliki 30% tersisa sebagai cadangan. Saat Anda perlu melakukan pembaruan, kemungkinan besar itu akan terjadi di halaman yang sama, dan versi baru dari baris tersebut akan muat di halaman yang sama. Dan hot_update akan selesai. Hal ini memudahkan penulisan pada tabel.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Antrian autovakum. Autovacuum adalah subsistem yang statistiknya sangat sedikit di PostgreSQL. Kami hanya dapat melihat pada tabel di pg_stat_activity berapa banyak penyedot debu yang kami miliki saat ini. Namun, sangat sulit untuk langsung memahami berapa banyak tabel yang ada dalam antrian.
Catatan: _Dimulai dengan Postgres 10, situasi dengan pelacakan Vatovac telah meningkat pesat - tampilan pg_stat_progress telah munculvakum, yang secara signifikan menyederhanakan masalah pemantauan vakum mobil.
Kita dapat menggunakan kueri yang disederhanakan ini. Dan kita bisa melihat kapan vakum harus dilakukan. Namun bagaimana dan kapan penyedotan debu harus dimulai? Ini adalah versi lama dari baris-baris yang saya bicarakan sebelumnya. Pembaruan terjadi, versi baris baru telah dimasukkan. Versi string yang ketinggalan jaman telah muncul. Di meja pg_stat_user_tables ada parameter seperti itu n_dead_tup. Ini menunjukkan jumlah garis "mati". Dan segera setelah jumlah baris mati menjadi lebih besar dari ambang batas tertentu, autovacuum akan muncul.
Dan bagaimana ambang batas ini dihitung? Ini adalah persentase yang sangat spesifik dari jumlah total baris dalam tabel. Ada parameternya autovacuum_vacuum_scale_factor. Ini menentukan persentasenya. Katakanlah 10% + ada tambahan ambang dasar 50 baris. Dan apa yang terjadi? Jika kita memiliki lebih banyak baris mati daripada “10% + 50” dari semua baris dalam tabel, maka kita meletakkan tabel tersebut pada autovacuum.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Namun, ada satu hal. Ambang batas dasar untuk parameter av_base_thresh и av_scale_factor dapat ditugaskan secara individual. Oleh karena itu, ambang batasnya tidak bersifat global, tetapi bersifat individual untuk tabel tersebut. Oleh karena itu, untuk menghitungnya perlu menggunakan trik dan trik. Dan jika Anda tertarik, Anda bisa melihat pengalaman rekan-rekan kami di Avito (link di slide tidak valid dan sudah diperbarui di teks).
Mereka menulis untuk , yang mempertimbangkan hal-hal ini. Ada alas kaki dua lembar di sana. Tapi ini menghitung dengan benar dan cukup efektif memungkinkan kita menilai di mana kita membutuhkan banyak ruang hampa untuk tabel yang hanya ada sedikit.
Apa boleh buat? Jika kita mempunyai antrian yang besar dan autovacuum tidak dapat mengatasinya, maka kita dapat menambah jumlah pekerja penyedot debu, atau sekadar membuat penyedot debu lebih agresif., agar terpicu lebih awal, proses tabel menjadi potongan-potongan kecil. Dan dengan demikian antrian akan berkurang. — Hal utama di sini adalah memantau beban pada disk, karena... vakum bukanlah hal yang gratis, meskipun dengan munculnya perangkat SSD/NVMe, masalahnya menjadi kurang terlihat.
Pg_stat_all_indexes adalah statistik indeks. Dia tidak besar. Dan kita dapat menggunakannya untuk memperoleh informasi tentang penggunaan indeks. Dan misalnya, kita dapat menentukan indeks mana yang kita punya tambahan.
Seperti yang sudah saya katakan, update tidak hanya update tabel, tapi juga update indeks. Oleh karena itu, jika kita memiliki banyak indeks pada tabel, maka ketika memperbarui baris dalam tabel, indeks dari bidang yang diindeks juga perlu diperbarui, dan jika kita memiliki indeks yang tidak digunakan dan tidak ada pemindaian indeks, maka indeks tersebut akan hang sebagai pemberat. Dan kita harus menyingkirkan mereka. Untuk ini kita membutuhkan lapangan idx_scan. Kami hanya melihat jumlah pemindaian indeks. Jika indeks tidak memiliki pemindaian dalam jangka waktu penyimpanan statistik yang relatif lama (setidaknya 2-3 minggu), kemungkinan besar ini adalah indeks yang buruk, kita harus membuangnya.
Catatan: Saat mencari indeks yang tidak digunakan dalam kasus cluster replikasi streaming, Anda perlu memeriksa semua node cluster, karena statistik tidak bersifat global, dan jika indeks tidak digunakan pada master, maka dapat digunakan pada replika (jika ada beban di sana).
Dua tautan:
Ini adalah contoh kueri tingkat lanjut tentang cara mencari indeks yang tidak digunakan.
Tautan kedua adalah permintaan yang cukup menarik. Ada logika yang sangat tidak sepele di sana. Saya merekomendasikannya untuk referensi.
Apa lagi yang layak diringkas menggunakan indeks?
Indeks yang tidak digunakan buruk.
Mereka memakan tempat.
Memperlambat operasi pembaruan.
Pekerjaan ekstra untuk penyedot debu.
Jika kami menghapus indeks yang tidak digunakan, kami hanya akan membuat database menjadi lebih baik.
Presentasi selanjutnya adalah pg_stat_activity. Ini adalah analog dari utilitas ps, hanya di PostgreSQL. Jika ps'om, kalau begitu, lihat proses di sistem operasi pg_stat_activity Ini akan menunjukkan aktivitas di dalam PostgreSQL.
Hal bermanfaat apa yang bisa kita ambil dari sana?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Kita bisa melihat aktivitas umum, apa yang terjadi di database. Kita bisa membuat penerapan baru. Semuanya di sini meledak, koneksi baru tidak diterima, kesalahan mengalir ke dalam aplikasi.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Kita dapat menjalankan query seperti ini dan melihat persentase total koneksi relatif terhadap batas koneksi maksimum dan melihat siapa yang memiliki koneksi paling banyak. Dan dalam kasus ini kita melihat pengguna itu cron_role membuka 508 koneksi. Dan sesuatu terjadi padanya di sana. Kita perlu menghadapinya dan melihatnya. Dan sangat mungkin bahwa ini adalah jumlah koneksi yang tidak wajar.
Jika kita memiliki beban kerja OLTP, kuerinya harus cepat, sangat cepat, dan tidak boleh ada kueri yang panjang. Namun, jika muncul pertanyaan panjang, maka dalam jangka pendek tidak ada yang perlu dikhawatirkan, tapi Dalam jangka panjang, kueri yang panjang membahayakan database; kueri tersebut meningkatkan efek penggembungan tabel ketika terjadi fragmentasi tabel. Anda harus menghilangkan pertanyaan yang membengkak dan panjang.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Harap dicatat: dengan permintaan ini kami dapat mengidentifikasi pertanyaan dan transaksi yang panjang. Kami menggunakan fungsinya clock_timestamp() untuk menentukan waktu pengoperasian. Pertanyaan panjang yang kami temukan, kami dapat mengingatnya, memenuhinya explain, lihat rencananya dan optimalkan. Kami menolak permintaan lama saat ini dan melanjutkan hidup kami.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Transaksi buruk adalah transaksi dalam keadaan menganggur dalam transaksi dan menganggur dalam keadaan transaksi (dibatalkan).
Apa artinya? Transaksi memiliki banyak negara bagian. Dan salah satu dari keadaan ini dapat diasumsikan kapan saja. Ada bidang untuk menentukan negara bagian state dalam presentasi ini. Dan kami menggunakannya untuk menentukan keadaan.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Dan, seperti yang saya katakan di atas, kedua negara bagian ini menganggur dalam transaksi dan menganggur dalam transaksi (dibatalkan) itu buruk. Apa itu? Ini adalah saat aplikasi membuka transaksi, melakukan beberapa tindakan dan menjalankan bisnisnya. Transaksi tetap terbuka. Itu hang, tidak ada yang terjadi di dalamnya, itu memakan koneksi, mengunci baris yang diubah dan berpotensi meningkatkan kembungnya tabel lain, karena arsitektur mesin transaksi Postrges. Dan transaksi semacam itu juga harus dihentikan, karena pada umumnya transaksi tersebut merugikan.
Jika Anda melihat bahwa Anda memiliki lebih dari 5-10-20 di database Anda, maka Anda perlu khawatir dan mulai melakukan sesuatu dengan mereka.
Disini kami juga menggunakannya untuk perhitungan waktu clock_timestamp(). Kami merekam transaksi dan mengoptimalkan aplikasi.
Seperti yang saya katakan di atas, pemblokiran adalah ketika dua atau lebih transaksi memperebutkan satu atau sekelompok sumber daya. Untuk ini kami memiliki lapangan waiting dengan nilai boolean true или false.
Benar – ini berarti prosesnya tertunda, ada sesuatu yang perlu dilakukan. Ketika suatu proses sedang menunggu, berarti klien yang memulai proses ini juga sedang menunggu. Klien duduk di browser dan juga menunggu.
peringatan: _Mulai dari bidang Postgres versi 9.6 waiting dihapus dan dua bidang informatif ditambahkan sebagai gantinya wait_event_type и wait_event._
Apa yang harus dilakukan? Jika Anda melihat kebenaran untuk waktu yang lama, itu berarti Anda harus membuang permintaan tersebut. Kami hanya menolak transaksi tersebut. Kami menulis kepada pengembang bahwa mereka perlu mengoptimalkan sehingga tidak ada perlombaan untuk mendapatkan sumber daya. Kemudian pihak pengembang mengoptimalkan aplikasinya agar hal tersebut tidak terjadi.
Dan kasus yang ekstrim namun berpotensi tidak fatal adalah terjadinya kebuntuan. Dua transaksi memperbarui dua sumber daya, lalu mengaksesnya lagi, kali ini ke sumber daya yang berlawanan. Dalam hal ini, PostgreSQL menghentikan transaksi itu sendiri sehingga transaksi lain dapat terus bekerja. Ini adalah situasi buntu dan dia tidak bisa mengatasinya sendiri. Oleh karena itu, PostgreSQL terpaksa mengambil tindakan ekstrim.
Dan berikut adalah dua pertanyaan yang memungkinkan Anda melacak pemblokiran. Kami menggunakan tampilan pg_locks, yang memungkinkan Anda melacak kunci berat.
Dan tautan pertama adalah teks permintaan itu sendiri. Ini cukup panjang.
Dan link kedua adalah artikel tentang kunci. Sangat bermanfaat untuk dibaca, sangat menarik.
Jadi apa yang kita lihat? Kami melihat dua permintaan. Transaksi dengan ALTER TABLE adalah transaksi pemblokiran. Itu dimulai, tetapi tidak selesai, dan aplikasi yang mencatat transaksi ini melakukan hal lain di suatu tempat. Dan permintaan kedua adalah pembaruan. Dia menunggu meja alter berakhir sebelum dia dapat melanjutkan pekerjaannya.
Dengan begitu kita bisa mengetahui siapa yang mengunci siapa, menahan siapa, dan kita bisa mengatasinya lebih lanjut.
Modul selanjutnya adalah pg_stat_statements. Seperti yang saya katakan, ini adalah sebuah modul. Untuk menggunakannya, Anda perlu memuat perpustakaannya di konfigurasi, restart PostgreSQL, instal modul (dengan satu perintah) dan kemudian kita akan memiliki tampilan baru.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Apa yang bisa kita ambil dari sana? Jika kita berbicara tentang hal-hal sederhana, kita dapat mengambil waktu rata-rata eksekusi query. Waktu semakin berkembang, yang berarti PostgreSQL merespons dengan lambat dan kita perlu melakukan sesuatu.
Kita dapat melihat transaksi penulisan paling aktif di database yang mengubah data di buffer bersama. Lihat siapa yang memperbarui atau menghapus data di sana.
Dan kita dapat dengan mudah melihat statistik yang berbeda untuk permintaan ini.
Kita pg_stat_statements Kami menggunakannya untuk membuat laporan. Kami mengatur ulang statistik sekali sehari. Mari kita kumpulkan. Sebelum menyetel ulang statistik di lain waktu, mari buat laporan. Berikut ini tautan ke laporan tersebut. Anda bisa menontonnya.
Apa yang kita lakukan? Kami menghitung statistik umum untuk semua permintaan. Kemudian, untuk setiap permintaan, kami menghitung kontribusi masing-masing terhadap keseluruhan statistik tersebut.
Dan apa yang bisa kita tonton? Kita dapat melihat total waktu eksekusi semua permintaan jenis tertentu dengan latar belakang semua permintaan lainnya. Kita dapat melihat penggunaan sumber daya CPU dan I/O relatif terhadap gambaran keseluruhan. Dan sudah mengoptimalkan kueri ini. Kami sedang membuat kueri teratas berdasarkan laporan ini dan sudah memikirkan apa yang harus dioptimalkan.
Apa yang kita tinggalkan di balik layar? Masih ada beberapa kiriman tersisa yang tidak saya pertimbangkan karena waktu terbatas.
Ada pgstattuple juga merupakan modul tambahan dari paket contribs standar. Ini memungkinkan Anda untuk mengevaluasi bloat tabel, yang disebut fragmentasi tabel. Dan jika ada banyak fragmentasi, Anda perlu menghapusnya dan menggunakan alat yang berbeda. Dan fungsi pgstattuple bekerja untuk waktu yang lama. Dan semakin banyak tabel, semakin lama waktu kerjanya.
Kontribusi selanjutnya adalah pg_buffercache. Hal ini memungkinkan Anda untuk memeriksa buffer bersama: seberapa intensif dan untuk halaman buffer tabel mana yang digunakan. Dan itu hanya memungkinkan Anda untuk melihat buffer bersama dan mengevaluasi apa yang terjadi di sana.
Modul selanjutnya adalah pgfincore. Ini memungkinkan operasi tabel tingkat rendah melalui panggilan sistem mincore(), yaitu memungkinkan Anda memuat tabel ke dalam buffer bersama, atau membongkarnya. Dan, antara lain, ini memungkinkan Anda untuk memeriksa cache halaman sistem operasi, yaitu, berapa banyak ruang yang ditempati tabel di cache halaman, di buffer bersama, dan memungkinkan Anda mengevaluasi beban kerja tabel.
Modul selanjutnya – pg_stat_kcache. Itu juga menggunakan panggilan sistem getrusage(). Dan itu mengeksekusinya sebelum dan sesudah permintaan dijalankan. Dan dalam statistik yang dihasilkan, ini memungkinkan kami memperkirakan berapa banyak permintaan kami yang dihabiskan pada I/O disk, yaitu operasi dengan sistem file dan melihat penggunaan prosesor. Namun modulnya masih muda (uhuk uhuk) dan untuk pengoperasiannya memerlukan PostgreSQL 9.4 dan pg_stat_statements, yang saya sebutkan sebelumnya.
Mengetahui cara menggunakan statistik berguna. Anda tidak memerlukan program pihak ketiga. Anda bisa masuk, melihat, melakukan sesuatu, mencapai sesuatu.
Menggunakan statistik tidaklah sulit, hanya SQL biasa. Anda mengumpulkan permintaan, menyusunnya, mengirimkannya, melihatnya.
Statistik membantu menjawab pertanyaan. Jika Anda memiliki pertanyaan, Anda beralih ke statistik - lihat, buat kesimpulan, analisis hasilnya.
Dan bereksperimen. Permintaannya banyak, datanya banyak. Anda selalu dapat mengoptimalkan kueri yang ada. Anda dapat membuat versi permintaan Anda sendiri yang lebih sesuai untuk Anda daripada aslinya dan menggunakannya.
referensi
Tautan yang sesuai yang ditemukan di artikel, berdasarkan materi, ada di laporan.
Penulis menulis lebih banyak
(Bahasa Inggris)
Kolektor Statistik
Fungsi Administrasi Sistem
Modul kontribusi
Utilitas SQL dan contoh kode sql
Terima kasih atas perhatian Anda!
Sumber: www.habr.com
