Dasar-dasar fitur AutoPlanCache - PolarDB

Gunakan fitur Auto Plan Cache dari PolarDB for MySQL untuk menyimpan cache rencana eksekusi Pernyataan SQL, mempersingkat waktu optimasi kueri, dan meningkatkan kinerja kueri. Topik ini menjelaskan fitur cache rencana otomatis.

Informasi latar belakang

Pemilihan rencana eksekusi bergantung pada berbagai faktor seperti statistik, urutan join, dan transformasi kueri. Waktu optimasi bervariasi untuk setiap pernyataan kueri. Beberapa Pernyataan SQL memiliki waktu optimasi yang sangat lama dibandingkan dengan waktu eksekusi keseluruhan. Jika Pernyataan SQL tersebut dieksekusi berulang kali, beban sistem akan meningkat karena waktu optimasi yang panjang. Menyimpan dan menggunakan kembali rencana eksekusi dapat mengurangi waktu optimasi saat Pernyataan SQL dijalankan, meningkatkan kinerja kueri, mengurangi beban database, dan meningkatkan throughput.

Sebaliknya, beberapa pernyataan kueri memiliki waktu optimasi yang sangat singkat. Waktu eksekusi mereka sangat bergantung pada rencana eksekusi, yang dipengaruhi oleh nilai parameter dalam Pernyataan SQL. Dalam beberapa skenario, MySQL mengoptimalkan data aktual yang diperoleh dari mesin berdasarkan nilai parameter.

Jika pernyataan kueri sebelumnya menggunakan rencana eksekusi tetap, waktu respons kueri dan overhead beban tidak dioptimalkan secara signifikan. Kinerja kueri bahkan bisa menurun.

Untuk meningkatkan kinerja kueri Pernyataan SQL dengan waktu optimasi sangat lama, mengurangi beban sistem, dan menghindari penurunan kinerja akibat rencana eksekusi tetap, PolarDB for MySQL meluncurkan fitur cache rencana otomatis. Fitur ini mendukung tiga mode: AUTO, DEMAND, dan ENFORCE. Anda dapat memilih salah satu dengan mengatur parameter loose_plan_cache_type untuk menyimpan cache rencana eksekusi Pernyataan SQL dalam cache rencana, mengurangi waktu optimasi, dan meningkatkan kinerja kueri. Saat statistik tabel yang dirujuk dalam rencana eksekusi berubah atau operasi DDL dilakukan pada tabel yang dirujuk, rencana eksekusi yang disimpan secara otomatis menjadi tidak valid.

Prasyarat

Kluster PolarDB Anda harus menggunakan salah satu versi berikut:

Kluster PolarDB for MySQL 8.0.1 dengan versi revisi 8.0.1.1.33 atau lebih baru.
Kluster PolarDB for MySQL 8.0.2 dengan versi revisi 8.0.2.2.12 atau lebih baru.

Parameter

Anda dapat mengatur parameter berikut di PolarDB console. Untuk informasi lebih lanjut, lihat Konfigurasikan parameter kluster dan node.

Parameter	Deskripsi
loose_plan_cache_type	Mode cache rencana otomatis. Nilai yang valid: OFF (default): Menonaktifkan fitur cache rencana otomatis. AUTO: Secara otomatis men-cache rencana eksekusi Pernyataan SQL yang memenuhi kondisi cache. Catatan Kondisi cache: Jika waktu eksekusi keseluruhan dari Pernyataan SQL lebih besar dari atau sama dengan nilai `loose_auto_plan_cache_time_threshold` dan rasio waktu optimasi Pernyataan SQL terhadap waktu eksekusi keseluruhan lebih besar dari atau sama dengan nilai `loose_auto_plan_cache_pct_threshold`, rencana eksekusi dari Pernyataan SQL disimpan dalam cache. DEMAND: menyimpan cache rencana eksekusi dari Pernyataan SQL tertentu. ENFORCE: secara paksa menyimpan cache rencana eksekusi dari semua Pernyataan SQL.
loose_plan_cache_expire_time	Periode waktu sebelum cache rencana diklaim kembali saat tidak ada rencana eksekusi yang cocok. Satuan: detik. Nilai yang valid: 0 hingga 4294967295. Nilai default: 1800.
loose_auto_plan_cache_pct_threshold	Rasio waktu optimasi Pernyataan SQL terhadap waktu eksekusi keseluruhan yang ditentukan. Nilai yang valid: 0 hingga 100. Nilai default: 20.
loose_auto_plan_cache_time_threshold	Waktu eksekusi keseluruhan Pernyataan SQL yang ditentukan. Satuan: mikrodetik. Nilai yang valid: 0 hingga 18446744073709551615. Nilai default: 400.
loose_auto_plan_cache_count_threshold	Jumlah operasi yang diizinkan untuk menyimpan cache rencana eksekusi dari Pernyataan SQL yang memenuhi kondisi cache saat parameter `loose_plan_cache_type` diatur ke AUTO. Nilai yang valid: 0 hingga 18446744073709551615. Nilai default: 512. Catatan Cache rencana eksekusi hanya berlaku jika jumlah operasi yang diizinkan untuk menyimpan cache rencana lebih besar dari atau sama dengan nilai `loose_auto_plan_cache_count_threshold`.

Prosedur tersimpan

dbms_sql.add_plan_cache(skema, kueri): menyimpan cache rencana eksekusi Pernyataan SQL tertentu dalam cache rencana.
Jika parameter loose_plan_cache_type diatur ke DEMAND, Anda dapat menggunakan prosedur tersimpan ini untuk menyimpan cache rencana eksekusi Pernyataan SQL tertentu dalam cache rencana. Contoh:
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```
Setelah pernyataan di atas dieksekusi, ketika Pernyataan SQL yang Anda jalankan sesuai dengan template SELECT * FROM t_for_plan WHERE c1 > ? AND c1 < ?, rencana eksekusi Pernyataan SQL disimpan dalam cache rencana.
dbms_sql.display_plan_cache_table(): menampilkan informasi tentang tabel yang dirujuk dalam cache rencana. Contoh:
```
CALL dbms_sql.display_plan_cache_table()\G
```
Hasil contoh:
```
*************************** 1. row ***************************
 SCHEMA_NAME: test
  TABLE_NAME: t_for_plan
   REF_COUNT: 1
     VERSION: 0
VERSION_TIME: 2023-03-10 17:21:35.605264
```
Parameter dalam prosedur tersimpan:
- SCHEMA_NAME: nama skema tempat tabel yang dirujuk berada.
- TABLE_NAME: nama tabel yang dirujuk.
- REF_COUNT: jumlah referensi tabel dalam cache rencana.
- VERSION: versi tabel yang dirujuk dalam cache rencana.
- VERSION_TIME: waktu ketika tabel versi saat ini dirujuk.
dbms_sql.delete_sharing_by_rowid(row_id): menghapus rencana eksekusi Pernyataan SQL tertentu.
row_id: nilai ID baris untuk rencana eksekusi yang disimpan dalam tabel mysql.sql_sharing.
Examples
1. Eksekusi pernyataan SQL berikut untuk menanyakan rencana eksekusi yang disimpan dalam cache rencana:
```
SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\G
```
  Hasil contoh:
```
*************************** 1. row ***************************
         Id: 1
Schema_name: test
       Type: PLAN_CACHE
Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
```
  Hasil menunjukkan bahwa nilai row_id adalah 1.
2. Hapus rencana eksekusi yang diperoleh dalam kueri sebelumnya.
```
CALL dbms_sql.delete_sharing_by_rowid(1);
```

Menanyakan informasi cache rencana

Rencana eksekusi Pernyataan SQL disimpan dalam modul SQL Sharing. Anda dapat mengeksekusi pernyataan SQL berikut untuk menanyakan informasi cache rencana dari tabel INFORMATION_SCHEMA.SQL_SHARING.

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

Examples

Siapkan data.

CREATE TABLE t_for_plan AS WITH RECURSIVE t(c1, c2, c3) AS (SELECT 1, 1, 1 UNION ALL SELECT c1+1, c1 % 50, c1 %200 FROM t WHERE c1 < 1000) SELECT c1, c2, c3 FROM t;
CREATE INDEX i_c1_c2 on t_for_plan(c1, c2);

Atur mode cache rencana otomatis ke DEMAND.
Anda dapat menggunakan salah satu metode berikut untuk mengatur mode cache rencana otomatis:
- Di halaman Parameters dari PolarDB console, atur parameter loose_plan_cache_type ke DEMAND. Putuskan sambungan dan kemudian sambungkan kembali ke database.
- Pertahankan koneksi database saat ini dan eksekusi pernyataan berikut untuk mengatur parameter plan_cache_type dalam Sesi saat ini ke DEMAND.
```
SET plan_cache_type=demand;
```
Eksekusi pernyataan berikut untuk menyimpan cache rencana eksekusi Pernyataan SQL tertentu dalam cache rencana:
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```

Eksekusi pernyataan kueri.

SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10;

Tanyakan informasi cache rencana.

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA FROM INFORMATION_SCHEMA.SQL_SHARING WHERE json_contains(REF_BY, '"PLAN_CACHE"') or json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

Hasil contoh:

*************************** 1. row ***************************
       TYPE: SQL
     REF_BY: ["PLAN_CACHE(DEMAND)"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
    PLAN_ID: NULL
       PLAN: NULL
 PLAN_EXTRA: NULL
      EXTRA: {"TRACE_ROW_ID":1}
*************************** 2. row ***************************
       TYPE: PLAN
     REF_BY: ["PLAN_CACHE"]
     SQL_ID: 9jrvksr3wjux6
SCHEMA_NAME: test
DIGEST_TEXT: NULL
    PLAN_ID: 08xftakma6pm6
       PLAN: /*+ INDEX(`t_for_plan`@`select#1` `i_c1_c2`) */
 PLAN_EXTRA: {"access_type":["`t_for_plan`:range"]}
      EXTRA: {"PLAN_CACHE_INFO":{"tables":[`test`.`t_for_plan`], "versions":[0], "hits": 0}}

Item PLAN_CACHE_INFO dari bidang EXTRA menampilkan tabel yang dirujuk, versi tabel yang dirujuk, dan jumlah hit dari rencana eksekusi.

Data kinerja

Uji stres dilakukan untuk kluster yang menggunakan 8 core dan 32 GB memori. 25 tabel dibuat dalam database, masing-masing menyimpan 4 juta baris data. Pernyataan SQL yang digunakan dalam pengujian adalah SELECT id FROM sbtestN WHERE k IN(...). Panjang IN LIST adalah 20. Kinerja diuji saat parameter loose_plan_cache_type diatur ke OFF, AUTO, dan ENFORCE untuk kedua protokol PS dan non-PS. Hasil pengujian:

Gambar berikut menunjukkan hasil pengujian kinerja untuk protokol PS.
Gambar berikut menunjukkan hasil pengujian kinerja untuk protokol non-PS.

Hasil pengujian menunjukkan bahwa fitur cache rencana otomatis dapat meningkatkan kinerja lebih dari 50% untuk kedua protokol PS dan non-PS.