Mendapatkan dan menganalisis rencana penjelasan AlloyDB

Anda dapat mengidentifikasi hambatan performa dan mengoptimalkan operasi database AlloyDB untuk PostgreSQL dengan mendapatkan dan menganalisis rencana eksekusi. Rencana eksekusi atau rencana EXPLAIN adalah representasi mendetail tentang cara mesin database AlloyDB akan menjalankan kueri SQL. Paket eksekusi terdiri dari pohon node yang menguraikan urutan operasi, seperti pemindaian tabel, penggabungan, pengurutan, dan penggabungan, yang dilakukan database AlloyDB untuk mengambil data yang diminta. Setiap langkah dalam rencana ini disebut sebagai node.

Rencana eksekusi diperoleh dengan menggunakan perintah EXPLAIN, yang menampilkan rencana yang dihasilkan oleh perencana kueri AlloyDB untuk pernyataan SQL tertentu. Perencana kueri, yang juga dikenal sebagai pengoptimal, menentukan cara paling efisien untuk mengeksekusi kueri SQL tertentu.

Rencana eksekusi mencakup komponen berikut:

  • Node rencana: node ini merepresentasikan berbagai langkah dalam eksekusi kueri, seperti operasi pemindaian, penggabungan, atau pengurutan.
  • Waktu eksekusi: rencana EXPLAIN mencakup perkiraan atau waktu eksekusi aktual untuk setiap langkah, yang membantu Anda mengidentifikasi hambatan database.
  • Penggunaan buffer: menunjukkan jumlah data yang dibaca dari disk versus cache, yang membantu mengidentifikasi masalah pembacaan disk.
  • Setelan parameter: rencana menampilkan setelan parameter yang berlaku selama eksekusi kueri.

PostgreSQL, dan secara tidak langsung AlloyDB, mendukung rencana eksekusi untuk pernyataan berikut:

  • SELECT
  • INSERT
  • UPDATE
  • DECLARE CURSOR
  • CREATE AS
  • CREATE MATERIALIZED VIEW
  • REFRESH MATERIALIZED VIEW
  • EXECUTE

Sebelum memulai

Anda harus memiliki cluster dan instance AlloyDB. Untuk mengetahui informasi selengkapnya, lihat Membuat cluster dan instance utamanya.

Buat rencana eksekusi

Anda membuat rencana eksekusi dari aplikasi klien seperti psql, pgAdmin, atau DBeaver. AlloyDB mendukung pembuatan rencana eksekusi dalam format teks atau JSON.

Untuk membuat rencana eksekusi, ikuti langkah-langkah berikut:

  1. Hubungkan klien psql ke instance.

  2. Untuk membuat rencana eksekusi dalam format teks, jalankan perintah berikut:

    SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

  3. Untuk membuat rencana eksekusi dalam format JSON, jalankan perintah berikut:

    SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)

    Perintah EXPLAIN mencakup semua opsi yang tersedia—analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, dan summary—untuk membuat rencana eksekusi mendetail untuk kueri tertentu dalam format teks atau JSON. Opsi analyze berarti kueri dieksekusi untuk memberikan statistik runtime yang sebenarnya serta perkiraan perencana kueri.

Melihat dan menganalisis data rencana EXPLAIN

Setelah mendapatkan rencana eksekusi, Anda dapat melihat dan menganalisis hasilnya.

Secara default, output EXPLAIN menampilkan aktivitas kueri sisi server. Untuk mengukur waktu perjalanan pulang pergi end-to-end, gunakan opsi /timing di psql dan buang hasilnya ke /dev/null.

Untuk melihat rencana eksekusi yang Anda buat, gunakan perintah EXPLAIN sebelum kueri SQL Anda.

  • EXPLAIN SELECT...: menampilkan rencana yang akan dipilih pengoptimal tanpa menjalankan kueri.
  • EXPLAIN ANALYZE SELECT...: menjalankan kueri dan menampilkan prediksi rencana dan statistik eksekusi sebenarnya, termasuk waktu proses yang sebenarnya dan jumlah baris.

JELASKAN tanpa ANALISIS

Untuk menampilkan perkiraan biaya kueri perencana kueri, jalankan pernyataan EXPLAIN tanpa opsi ANALYZE .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

Output rencana mencakup data berikut:

  • cost = 0.00..1735481.00: angka pertama menunjukkan biaya untuk mengambil baris pertama. Angka kedua menunjukkan biaya untuk mengambil baris terakhir.
  • rows = 100000000: ini adalah perkiraan jumlah baris yang ditampilkan kueri.
  • width = 27: ini adalah perkiraan lebar baris yang ditampilkan, yang membantu Anda memahami blok yang diakses.

Opsi ANALISIS

Untuk menampilkan statistik eksekusi sebenarnya serta perkiraan eksekusi, tambahkan opsi ANALYZE 

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)

Output rencana mencakup data berikut:

  • waktu sebenarnya (dalam ms) = 0,165..9342,424: ini menunjukkan waktu sebenarnya untuk menampilkan baris pertama dan total waktu untuk menampilkan semua baris.
  • rows = 100000001: ini adalah jumlah baris yang sebenarnya ditampilkan.
  • loops = 1: nilai ini penting untuk node loop bertingkat. Waktu rata-rata per loop ditampilkan jika loops lebih besar dari 1.
  • Waktu Perencanaan: 0,025 md: ini menunjukkan waktu yang diperlukan perencana untuk menentukan jalur eksekusi.
  • Waktu Eksekusi: 13674,794 md: ini menunjukkan waktu eksekusi yang diperlukan setelah perencana menentukan jalur.
  • Total Waktu Eksekusi: jumlah Planning Time dan Execution Time. (0,025 + 13674,794 = 13674,819)

Opsi VERBOSE

Untuk menambahkan informasi selengkapnya ke rencana eksekusi, gunakan opsi VERBOSE. Dalam contoh berikut, penggunaan VERBOSE menambahkan kualifikasi skema ke nama tabel dan menampilkan ID kueri internal yang dapat Anda gunakan untuk mengorelasikan kueri dengan alat pemantauan lainnya.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.164..6568.938 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10875.894 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Output rencana mencakup data berikut:

  • Output Node: mencantumkan kolom yang disertakan dalam kueri. Perencana terkadang menyertakan lebih banyak kolom daripada yang diminta jika menentukan bahwa tidak menyertakannya akan lebih mahal.
  • ID Kueri: ID PostgreSQL yang dipetakan ke pg_stat_statements.
  • ID kueri AlloyDB: ID kueri AlloyDB yang dapat digunakan untuk mengorelasikan informasi insight kueri

Opsi COLUMNAR ENGINE

Untuk menampilkan informasi tentang mesin berbasis kolom AlloyDB, tambahkan opsi COLUMNAR_ENGINE.

Jika tabel tidak ada di mesin kolom, lihat kolom Pemeriksaan Kolom dalam rencana penjelasan berikut untuk mengetahui statusnya.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.009..6328.154 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10673.310 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Jika tabel ada di mesin berbasis kolom dan berbasis kolom digunakan, Pemindaian Kustom akan ditunjukkan bersama dengan statistik tentang cara penggunaan mesin berbasis kolom.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test where product_id = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Append  (cost=20.00..27438.78 rows=1166668 width=27) (actual time=0.066..377.029 rows=1000290 loops=1)
   ->  Custom Scan (columnar scan) on public.index_advisor_test  (cost=20.00..27437.66 rows=1166667 width=27) (actual time=0.065..296.904 rows=1000290 loops=1)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
         Rows Removed by Columnar Filter: 98999711
         Bytes fetched from storage cache: 774835915
         Columnar cache search mode: native
         Swap-in Time: 92.708 ms
   ->  Seq Scan on public.index_advisor_test  (cost=0.00..1.11 rows=1 width=27) (never executed)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
 Query Identifier: -4660018746142248761
 Planning Time: 0.217 ms
 Execution Time: 421.114 ms
 AlloyDB query id: 13855683355620344431
 AlloyDB plan id: 2126918133221480510

Output rencana mencakup data berikut:

  • Filter Kueri (predikat): ini menampilkan filter yang diterapkan, jika Anda menggunakannya.
  • Baris yang Dihapus oleh Filter Kolom: ini menunjukkan jumlah baris yang dihapus oleh filter kolom.
  • Byte yang diambil dari cache penyimpanan: ini menunjukkan jumlah byte yang diambil dari cache penyimpanan.
  • Waktu Penukaran: ini adalah waktu yang diperlukan untuk menukar data dari cache tumpahan kolom (SSD) jika relasi tidak sesuai dengan memori.

Opsi SETELAN

Untuk menampilkan setelan sesi, database, atau global non-default yang digunakan perencana, tambahkan opsi SETTINGS.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS) select * from public.index_advisor_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.007..6366.249 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10727.068 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

Opsi BUFFER

Untuk menampilkan informasi tentang sumber data, gunakan kata kunci BUFFERS. Jumlah BUFFERS terakumulasi dari semua langkah, bukan hanya dari langkah tertentu dalam rencana.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.111..10007.193 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2588.597
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning:
   Buffers: shared hit=58 read=2, ultra fast cache hit=2
   I/O Timings: shared read=0.215
 Planning Time: 0.410 ms
 Execution Time: 14825.271 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

Informasi buffer mencakup hal berikut:

  • hit bersama: jumlah blok data 8 kB yang ditemukan di cache buffer bersama utama PostgreSQL.
  • shared read: jumlah blok data 8 kB yang dibaca dari sistem operasi. Hal ini sering menunjukkan I/O disk.
  • kotor: jumlah blok yang sebelumnya tidak diubah oleh kueri (perubahan peta visibilitas).
  • ditulis: jumlah blok yang sebelumnya kotor dan dikeluarkan dari cache oleh backend ini selama pemrosesan kueri, biasanya karena perubahan bit petunjuk atau peta visibilitas dan di-flush ke disk.
  • hit cache ultra cepat: jumlah blok yang diambil dari cache ultra cepat.
  • Waktu I/O: durasi I/O disk atau SSD dalam milidetik.
  • Perencanaan: aktivitas buffer selama fase perencanaan, seperti membaca metadata atau statistik dari tabel katalog.
  • Waktu yang dihabiskan untuk I/O selama perencanaan: ini menunjukkan waktu I/O jika ada metadata yang memerlukan pembacaan dari disk.

Opsi WAL

Untuk memberikan informasi tentang aktivitas Write-Ahead Logging (WAL), gunakan opsi WAL .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS, WAL) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.010..10147.314 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2590.410
   WAL: records=18 bytes=5178
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning Time: 0.030 ms
 Execution Time: 15033.004 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

Informasi WAL mencakup hal berikut:

  • Catatan WAL: jumlah catatan WAL yang dibaca untuk mempertahankan konsistensi.
  • Byte WAL: jumlah byte yang dibaca dari WAL untuk menjaga konsistensi.
  • Data yang telah diubah tetapi belum dicek poinnya: ini menunjukkan data yang diubah tetapi belum dicek poinnya.

Langkah berikutnya