PolarDB:Outline Pernyataan

Informasi Latar Belakang

Dalam lingkungan database produksi, pernyataan SQL lambat merupakan tantangan umum bagi tim operasi dan pemeliharaan (O&M) serta pengembang. Pernyataan lambat ini dapat disebabkan oleh berbagai faktor, seperti distribusi data yang tidak merata atau informasi statistik yang tidak akurat.

Menggunakan petunjuk (hints) untuk memengaruhi perilaku pengoptimal (optimizer) adalah metode umum dan efektif guna menghasilkan rencana eksekusi yang lebih baik. Namun, menambahkan hints secara langsung ke pernyataan SQL bisnis menghadirkan beberapa tantangan:

• Kompleksitas penambahan hints: Karena banyak aplikasi menggunakan middleware untuk menghasilkan SQL secara otomatis, penambahan hints secara manual menjadi tidak praktis dan melelahkan.

• Kompleksitas proses rilis: Menambahkan hints secara manual dalam aplikasi memerlukan rilis versi aplikasi baru untuk setiap penyesuaian hint. Proses ini memakan waktu, tenaga, dan dapat memperkenalkan risiko tambahan.

• Kompleksitas manajemen hints: Seiring bertambahnya jumlah hints yang ditambahkan ke aplikasi, hints tersebut menjadi sulit dikelola. Administrator basis data kesulitan melacak semua hints yang digunakan dalam sistem.

Untuk mengatasi tantangan tersebut, PolarDB for PostgreSQL memperkenalkan kemampuan untuk secara otomatis menambahkan hints ke kelas pernyataan SQL tertentu. Inilah fitur statement outline, atau OUTLINE. Fitur ini memungkinkan Anda mengontrol rencana eksekusi SQL secara tepat tanpa memengaruhi aplikasi bisnis.

Pengenalan Fungsi

Fitur OUTLINE memungkinkan Anda membekukan (freeze) rencana eksekusi untuk pernyataan SQL tertentu secara manual serta menambahkan atau memodifikasi hints untuk kelas SQL tersebut. OUTLINE memberikan keunggulan berikut:

• Independen dari aplikasi: Anda dapat mengonfigurasi OUTLINE di lapisan database tanpa mengubah kode aplikasi atau melakukan redeploy aplikasi.

• Respons dan penyesuaian cepat: Anda dapat dengan cepat menyesuaikan rencana eksekusi untuk secara efektif mengatasi masalah pernyataan SQL lambat. Hal ini meningkatkan stabilitas dan performa sistem.

• Kontrol dan manajemen granular tinggi: Anda dapat memberikan deskripsi yang sesuai untuk pernyataan SQL yang berbeda. Ini memperjelas semua deskripsi dalam sistem beserta statusnya, seperti jumlah hit dan apakah deskripsi tersebut diaktifkan.

Tanpa memengaruhi aplikasi bisnis, Anda dapat menggunakan fitur OUTLINE untuk mengontrol rencana eksekusi SQL secara tepat dan menyelesaikan masalah pernyataan SQL lambat secara efisien.

Istilah

• HINT: Petunjuk pengoptimal (optimizer hint). Ini memengaruhi pengoptimal untuk menghasilkan rencana eksekusi yang berbeda dengan disematkan dalam komentar SQL.

• OUTLINE: Definisi yang menentukan hint yang akan ditambahkan ke kelas pernyataan SQL.

• OUTLINE DDL: Modifikasi terhadap OUTLINE, termasuk pembuatan, penghapusan, dan perubahan statusnya.

• SQL_ID: Pengidentifikasi pernyataan SQL. Dihasilkan secara otomatis oleh sistem database untuk membedakan pernyataan SQL yang berbeda. OUTLINE menggunakan SQL_ID untuk mengidentifikasi dan mencocokkan pernyataan SQL target.

Applicability

• Versi PolarDB for PostgreSQL yang didukung adalah sebagai berikut:

  • PostgreSQL 16 (versi mesin minor 2.0.16.9.9.0 atau lebih baru).

  • PostgreSQL 14 (versi mesin minor 2.0.14.13.28.0 atau lebih baru).

  Catatan

  Anda dapat melihat nomor versi mesin minor di console atau dengan menjalankan pernyataan SHOW polardb_version;. Jika versi mesin minor Anda tidak didukung, upgrade the minor engine version.

• Ekstensi pg_hint_plan harus diinstal, dan versinya harus 1.4.1 atau lebih baru.

Applicability

• Fitur ini didukung pada dengan dan versi mesin minor 2.0.14.13.28.0 atau lebih baru.

  Catatan

  Anda dapat melihat nomor versi mesin minor di console atau dengan menjalankan pernyataan SHOW polardb_version;. Jika versi mesin minor Anda tidak didukung, upgrade the minor engine version.

• Ekstensi harus diinstal, dan versinya harus 1.4.1 atau lebih baru.

Perhatian

• Kriteria pencocokan SQL_ID: Kernel database menormalisasi kelas pernyataan SQL dan mengabaikan faktor-faktor berikut saat mencocokkan OUTLINE.

  • Spasi, jeda baris, dan komentar.

  • Parameter dalam SQL, termasuk konstanta dan variabel. Misalnya, a = 1, a = $1, dan a = 2 dinormalisasi.

  • Huruf besar/kecil kata kunci. Misalnya, SELECT a, select a, dan Select a dinormalisasi.

  • Untuk daftar dengan beberapa parameter, jumlah parameter diabaikan. Misalnya, a IN (1,2,3) dan a IN (1,2) dinormalisasi. Namun, a IN (1,2,3) dan a IN (1) tidak dinormalisasi karena IN (1) tidak mengandung beberapa parameter.

• Anda dapat membuat beberapa OUTLINE untuk satu kelas SQL, yang ditumpuk bersama, sehingga memengaruhi pernyataan SQL dengan beberapa HINT.

• Prioritas OUTLINE: Outline memiliki prioritas lebih tinggi daripada hints yang disematkan dalam pernyataan SQL. Setelah Anda membuat OUTLINE untuk suatu pernyataan SQL, hints asli dalam pernyataan tersebut diabaikan. Pernyataan hanya dipengaruhi oleh hints dalam OUTLINE.

• Eksklusivitas timbal balik dengan hint_table: Fitur OUTLINE dan fitur `hint_table` pg_hint_plan saling eksklusif. Fitur `hint_table` mirip dengan OUTLINE. Saat Anda mengaktifkan fitur OUTLINE, fitur `hint_table` dinonaktifkan secara default.

Dampak kinerja

Fitur OUTLINE dirancang dengan modul cache berkonkurensi tinggi untuk meminimalkan dampak performa. Pengujian stres Sysbench standar menunjukkan bahwa setelah Anda mengaktifkan fitur ini dan menambahkan OUTLINE, Transactions Per Second (TPS) dan Queries Per Second (QPS) hanya menurun sekitar 1% hingga 2%.

Aktifkan fitur OUTLINE

1. Di database tempat Anda ingin menggunakan fitur OUTLINE, pastikan ekstensi pg_hint_plan telah diinstal dan versinya 1.4.1 atau lebih baru.

   SELECT extname, extversion >= '1.4.1' AS outline_version_ok FROM pg_extension WHERE extname = 'pg_hint_plan';

   Jika hasilnya sebagai berikut, ekstensi telah berhasil diinstal dan memenuhi persyaratan versi untuk fitur OUTLINE.

      extname    | outline_version_ok
   --------------+--------------------
    pg_hint_plan | t
   (1 row)

   Jika hasilnya berbeda, salah satu masalah berikut mungkin terjadi. Atasi sebagai berikut:

   Catatan

   Jalankan pernyataan berikut di database target menggunakan akun dengan izin yang sesuai.

   • Ekstensi tidak ada:

     CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

   • Ekstensi telah diinstal, tetapi versinya tidak memenuhi persyaratan:

     ALTER EXTENSION pg_hint_plan UPDATE;

2. Aktifkan fitur OUTLINE. Atur parameter pg_hint_plan.polar_enable_outline ke `on`. Memodifikasi parameter ini tidak menyebabkan kluster restart. Untuk informasi selengkapnya tentang cara mengatur parameter kluster di console, lihat Set cluster parameters.

Gunakan fitur OUTLINE

Buat OUTLINE

PolarDB menyediakan fungsi hint_plan.create_outline untuk membuat OUTLINE. Fungsi ini secara otomatis mengekstrak Hints dari pernyataan SQL yang ingin Anda stabilkan dan menambahkannya ke OUTLINE.

Example

CALL hint_plan.create_outline($$ SELECT /*+ Set(enable_bitmapscan off) */ * FROM t WHERE a = 1 $$);

Lihat OUTLINE

PolarDB for PostgreSQL menyediakan tampilan hint_plan.outlines_status untuk melihat OUTLINE.

Anda dapat mengkueri OUTLINE di database menggunakan pernyataan berikut:

SELECT * FROM hint_plan.outlines_status;

Bidang yang dikembalikan dijelaskan sebagai berikut:

Aktifkan atau nonaktifkan OUTLINE target

Setelah membuat OUTLINE, Anda dapat menggunakan fungsi berikut untuk mengaktifkan atau menonaktifkannya.

• hint_plan.enable_outline: Mengaktifkan OUTLINE. Tentukan id OUTLINE yang sesuai.

• hint_plan.disable_outline: Menonaktifkan OUTLINE dengan id yang ditentukan.

Example

--- Query the OUTLINE id in the database
SELECT * FROM hint_plan.outlines_status;

--- Enable the OUTLINE
CALL hint_plan.enable_outline(1);

--- Disable the OUTLINE
CALL hint_plan.disable_outline(1);

Hapus OUTLINE

Anda dapat menggunakan fungsi hint_plan.del_outline untuk menghapus OUTLINE yang tidak digunakan dengan menyediakan ID-nya.

Example

--- Query the outline IDs in the database.
SELECT * FROM hint_plan.outlines_status;

--- Delete the outline.
CALL hint_plan.del_outline(1);

Contoh lengkap

1. Aktifkan fitur OUTLINE. Untuk informasi lebih lanjut, lihat Aktifkan fitur OUTLINE.

2. Persiapkan data uji dasar.

   CREATE TABLE t(a int,b int,PRIMARY KEY(a));
   CREATE INDEX ON t(b);
   INSERT INTO t SELECT i,i FROM generate_series(1,100000)i;
   ANALYZE t;

3. Pengoptimal menganggap biaya indeks kunci primer sebanding dengan biaya indeks pada kolom b. Oleh karena itu, pilihan rencana eksekusi oleh pengoptimal dapat bervariasi.

   EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

   Hasil berikut dikembalikan:

             QUERY PLAN
   -------------------------------
    Index Scan using t_b_idx on t
      Index Cond: (b = 1)
      Filter: (a = 1)
   (3 rows)

4. Gunakan hint untuk memengaruhi pengoptimal agar memilih indeks kunci primer. Kemudian, buat OUTLINE untuk memastikan rencana tetap stabil.

   1. Gunakan hint untuk memengaruhi pengoptimal agar memilih indeks kunci primer.

      EXPLAIN (costs off) /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;

      Hasil berikut dikembalikan:

                QUERY PLAN
      ------------------------------
       Index Scan using t_pkey on t
         Index Cond: (a = 1)
         Filter: (b = 1)
      (3 rows)

   2. Buat OUTLINE untuk pernyataan SQL ini. Perhatikan bahwa teks SQL harus konsisten dengan templat aplikasi. Parameter, konstanta, hints, spasi, dan komentar tidak memengaruhi pencocokan. Namun, faktor seperti :: tambahan untuk casting tipe, menentukan skema untuk tabel, atau mengubah huruf besar/kecil nama tabel atau kolom dapat mencegah pencocokan. Untuk informasi selengkapnya tentang aturan pencocokan, lihat Kriteria pencocokan SQL_ID.

      CALL hint_plan.create_outline($$/*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1;$$);

      Catatan

      Jika terjadi error serupa ERROR: invalid transaction termination saat Anda menggunakan DMS untuk menghubungkan kluster dan mengeksekusi pernyataan di atas, gunakan client lain, seperti psql. Untuk informasi selengkapnya, lihat Connect to a database cluster.

      Verifikasi kembali pernyataan SQL target. Indeks kunci primer yang diharapkan digunakan:

      EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

      Berikut ini hasilnya:

                QUERY PLAN
      ------------------------------
       Index Scan using t_pkey on t
         Index Cond: (a = 1)
         Filter: (b = 1)
      (3 rows)

      Pencocokan OUTLINE tidak terpengaruh oleh parameter tambahan, spasi, atau komentar dalam pernyataan SQL yang cocok dengan templat. Indeks kunci primer tetap digunakan.

      EXPLAIN (costs off) SELECT * -- comment
      FROM t
      WHERE b = 2 AND a = 4;

      Berikut ini hasilnya:

                QUERY PLAN
      ------------------------------
       Index Scan using t_pkey on t
         Index Cond: (a = 4)
         Filter: (b = 2)
      (3 rows)

5. Anda dapat melihat semua status OUTLINE di tampilan hint_plan.outlines_status.

   SELECT * FROM hint_plan.outlines_status;

   Berikut ini hasilnya:

    id |        sql_id        |        hints        | state | depends_rels |                           query_string                           | create_user |        create_time         |     total_hints     | calls
   ----+----------------------+---------------------+-------+--------------+------------------------------------------------------------------+-------------+----------------------------+---------------------+-------
     1 | -3220256307655713529 | IndexScan(t t_pkey) | Y     | {public.t}   | /*+IndexScan(t t_pkey) */ SELECT * FROM t WHERE b = 1 AND a = 1; | postgres    | 2024-11-11 11:24:44.063143 | IndexScan(t t_pkey) |     2
   (1 row)

   Anda dapat menonaktifkan atau menghapus OUTLINE yang tidak digunakan.

   • Nonaktifkan OUTLINE dengan id 1.

     CALL hint_plan.disable_outline(1);

   • Hapus OUTLINE dengan id 1.

     CALL hint_plan.del_outline(1);

   Jika Anda menonaktifkan atau menghapus OUTLINE, rencana kembali ke kondisi semula.

   EXPLAIN (costs off) SELECT * FROM t WHERE b = 1 AND a = 1;

   Berikut ini hasilnya:

             QUERY PLAN
   -------------------------------
    Index Scan using t_b_idx on t
      Index Cond: (b = 1)
      Filter: (a = 1)
   (3 rows)