Informasi dasar mengenai fitur auto plan cache - PolarDB

Fitur PolarDB for MySQL ini memungkinkan Anda menyimpan rencana eksekusi pernyataan SQL di dalam cache, sehingga memperpendek waktu optimasi kueri dan meningkatkan performa kueri. Topik ini menjelaskan informasi latar belakang, prasyarat, parameter, serta antarmuka untuk fitur auto plan cache.

Informasi latar belakang

Pemilihan rencana eksekusi melibatkan banyak faktor, seperti statistik, urutan join yang berbeda, dan transformasi kueri. Waktu optimasi bervariasi tergantung pada pernyataan kueri yang digunakan. Untuk beberapa pernyataan SQL, waktu optimasi kueri dapat menjadi bagian besar dari total waktu eksekusi. Jika pernyataan-pernyataan tersebut dijalankan secara berkala, waktu optimasi yang panjang akan meningkatkan beban sistem. Dengan menyimpan dan menggunakan kembali rencana eksekusi, waktu optimasi setiap kali kueri dijalankan dapat dikurangi, sehingga meningkatkan performa kueri, menurunkan beban database, dan meningkatkan kapasitas throughput.

Namun, untuk banyak kueri lainnya, waktu optimasi relatif kecil, sedangkan waktu eksekusi sangat dipengaruhi oleh rencana eksekusi. Nilai parameter yang berbeda dalam suatu pernyataan SQL dapat menghasilkan rencana eksekusi optimal yang berbeda pula. Dalam beberapa skenario, MySQL mengambil data aktual dari engine berdasarkan nilai parameter untuk melakukan optimasi lebih lanjut.

Menggunakan rencana eksekusi tetap untuk kueri-kueri tersebut mungkin tidak meningkatkan waktu respons kueri atau mengurangi beban sistem. Bahkan, hal ini dapat menyebabkan penurunan performa (performance regression).

PolarDB for MySQL menyediakan fitur auto plan cache untuk meningkatkan performa kueri dari pernyataan SQL dengan waktu optimasi yang panjang, mengurangi beban sistem, dan menghindari penurunan performa akibat penggunaan rencana eksekusi tetap. Fitur auto plan cache memiliki tiga mode: AUTO, DEMAND, dan ENFORCE. Anda dapat mengatur parameter loose_plan_cache_type ke salah satu mode tersebut agar rencana eksekusi disimpan di dalam plan cache, sehingga mengurangi waktu optimasi dan meningkatkan performa kueri. Rencana eksekusi yang tersimpan di cache akan secara otomatis dibatalkan jika statistik tabel yang direferensikan berubah atau jika operasi Data Definition Language (DDL) dilakukan pada tabel yang direferensikan.

Prasyarat

Kluster PolarDB Anda harus memenuhi salah satu persyaratan versi berikut:

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

Parameter

Anda dapat mengatur parameter dalam tabel berikut di PolarDB console. Untuk informasi selengkapnya, lihat Set cluster and node parameters.

Parameter	Deskripsi
loose_plan_cache_type	Mode untuk auto plan cache. Nilai yang valid: OFF (Default): Menonaktifkan fitur auto plan cache. AUTO: Secara otomatis menyimpan rencana eksekusi pernyataan SQL yang memenuhi kondisi penyimpanan di cache. Catatan Kondisi penyimpanan: Rencana eksekusi untuk suatu pernyataan SQL disimpan di cache jika total waktu eksekusinya lebih besar dari atau sama dengan nilai parameter `loose_auto_plan_cache_time_threshold`, dan persentase waktu optimasi terhadap total waktu eksekusi lebih besar dari atau sama dengan nilai parameter `loose_auto_plan_cache_pct_threshold`. DEMAND: Menyimpan rencana eksekusi pernyataan SQL tertentu. ENFORCE: Memaksa penyimpanan rencana eksekusi semua pernyataan SQL.
loose_plan_cache_expire_time	Jika rencana eksekusi di dalam plan cache tidak digunakan (hit) dalam rentang waktu ini, memorinya akan dikembalikan. Satuan: detik. Rentang nilai: 0 hingga 4294967295. Nilai default: 1800.
loose_auto_plan_cache_pct_threshold	Ambang batas persentase waktu optimasi terhadap total waktu eksekusi suatu pernyataan. Rentang nilai: 0 hingga 100. Nilai default: 20.
loose_auto_plan_cache_time_threshold	Ambang batas total waktu eksekusi suatu pernyataan SQL. Satuan: mikrodetik. Rentang nilai: 0 hingga 18446744073709551615. Nilai default: 400.
loose_auto_plan_cache_count_threshold	Saat parameter `loose_plan_cache_type` diatur ke AUTO, ini adalah ambang batas jumlah kali rencana eksekusi pernyataan SQL yang memenuhi syarat disimpan di cache. Rentang nilai: 0 hingga 18446744073709551615. Nilai default: 512. Catatan Rencana eksekusi yang tersimpan di cache hanya berlaku jika jumlah kali penyimpanannya lebih besar dari atau sama dengan nilai parameter `loose_auto_plan_cache_count_threshold`.

Deskripsi antarmuka

dbms_sql.add_plan_cache(schema, query): Menyimpan rencana eksekusi pernyataan SQL tertentu di dalam plan cache.
Saat parameter loose_plan_cache_type diatur ke DEMAND, Anda dapat menggunakan prosedur tersimpan bawaan ini untuk menyimpan rencana eksekusi pernyataan SQL tertentu. Contoh penggunaannya sebagai berikut:
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```
Setelah pernyataan ini dieksekusi, rencana eksekusi akan disimpan di cache untuk semua pernyataan SQL yang sesuai dengan templat SELECT * FROM t_for_plan WHERE c1 > ? AND c1 < ?.
dbms_sql.display_plan_cache_table(): Menampilkan informasi tentang tabel yang direferensikan dalam plan cache saat ini. Contoh penggunaannya sebagai berikut:
```
CALL dbms_sql.display_plan_cache_table()\G
```
Hasil berikut dikembalikan:
```
*************************** 1. row ***************************
 SCHEMA_NAME: test
  TABLE_NAME: t_for_plan
   REF_COUNT: 1
     VERSION: 0
VERSION_TIME: 2023-03-10 17:21:35.605264
```
Tabel berikut menjelaskan parameter-parameter tersebut.
- SCHEMA_NAME: Nama skema tempat tabel yang direferensikan berada.
- TABLE_NAME: Nama tabel yang direferensikan.
- REF_COUNT: Jumlah kali tabel direferensikan dalam plan cache.
- VERSION: Versi tabel yang direferensikan dalam plan cache.
- VERSION_TIME: Waktu saat versi tabel saat ini direferensikan.
dbms_sql.delete_sharing_by_rowid(row_id): Menghapus rencana eksekusi pernyataan SQL tertentu.

row_id adalah ID baris dari rencana eksekusi yang disimpan di tabel mysql.sql_sharing.

Example
1. Jalankan perintah berikut untuk melihat informasi rencana eksekusi di dalam cache.
```
SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\G
```
  Hasil berikut dikembalikan:
```
*************************** 1. row ***************************
         Id: 1
Schema_name: test
       Type: PLAN_CACHE
Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
```
  Hasil kueri menunjukkan bahwa nilai row_id adalah 1.
2. Hapus rencana eksekusi dari kueri sebelumnya.
```
CALL dbms_sql.delete_sharing_by_rowid(1);
```

Mendapatkan informasi yang tersimpan di plan cache

Rencana eksekusi pernyataan SQL disimpan di modul SQL Sharing. Anda dapat menjalankan pernyataan SQL berikut untuk mengkueri informasi yang tersimpan di plan cache 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

Example

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 auto plan cache ke DEMAND.
Anda dapat mengatur mode auto plan cache dengan salah satu dari dua cara berikut.
- Di halaman Parameters di PolarDB console, atur parameter loose_plan_cache_type ke DEMAND. Setelah pengaturan selesai, putuskan koneksi lalu sambungkan kembali ke database.
- Dalam koneksi database saat ini, jalankan perintah berikut untuk mengatur parameter plan_cache_type untuk session saat ini ke demand.
```
SET plan_cache_type=demand;
```
Jalankan perintah berikut untuk menyimpan rencana eksekusi pernyataan SQL tertentu di dalam plan cache.
```
CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");
```

Jalankan pernyataan kueri.

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

Kueri informasi yang tersimpan di plan cache.

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 berikut dikembalikan:

*************************** 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}}

Pada bidang EXTRA, PLAN_CACHE_INFO menampilkan tabel yang direferensikan, versi tabel yang direferensikan, dan jumlah kali rencana eksekusi digunakan (hits).

Performa kueri

Uji stres dilakukan pada kluster 8 core, 32 GB. Database berisi 25 tabel, dengan masing-masing tabel menyimpan 4 juta baris data. Uji stres menggunakan pernyataan SQL SELECT id FROM sbtestN WHERE k IN(...), dengan panjang daftar IN sebanyak 20. Performa diuji dengan parameter loose_plan_cache_type diatur ke OFF, AUTO, dan ENFORCE baik dalam protokol Prepared Statement (PS) maupun non-PS. Hasil pengujian sebagai berikut:

Hasil uji performa dalam protokol PS:
Hasil uji performa dalam protokol non-PS:

Hasil pengujian menunjukkan bahwa fitur auto plan cache meningkatkan performa lebih dari 50% baik dalam protokol PS maupun non-PS.