Gunakan aturan Kontrol Konkurensi Pernyataan (CCL) - ApsaraDB RDS

Alibaba Cloud menyediakan fitur Concurrency Control (CCL) dan paket DBMS_CCL untuk menjaga stabilitas instans ApsaraDB RDS for MySQL menghadapi lonjakan lalu lintas tak terduga, pernyataan yang memakan banyak sumber daya, serta perubahan pola akses SQL. Fitur CCL dibangun berdasarkan aturan sintaksis. Anda dapat menggunakan paket DBMS_CCL untuk membatasi jumlah maksimum pernyataan SQL yang dieksekusi secara konkuren untuk tipe tertentu. Jika jumlah pernyataan SQL konkuren mencapai batas atas, pernyataan tambahan akan masuk ke status menunggu hingga jumlah tersebut kembali di bawah batas.

Prasyarat

Instans RDS menjalankan salah satu versi MySQL berikut:

MySQL 8.4
MySQL 8.0 dengan versi mesin minor 20190816 atau lebih baru
MySQL 5.7 dengan versi mesin minor 20200630 atau lebih baru

Parameter

Parameter	Deskripsi
ccl_max_waiting_count	Jumlah maksimum pernyataan SQL yang sesuai dengan aturan CCL yang sama dan dapat berada dalam status `Concurrency control waiting`. Jika batas ini dilampaui, pernyataan tersebut gagal dan mengembalikan error `ERROR 7534 (HY000): Concurrency control waiting count exceed max waiting count`. Nilai default-nya adalah 0, yang berarti tidak ada batasan.
ccl_wait_timeout	Waktu maksimum yang dapat ditunggu oleh suatu pernyataan SQL dalam status `Concurrency control waiting`. Jika batas ini dilampaui, pernyataan SQL tersebut tetap dieksekusi dan tidak lagi dikendalikan alirannya oleh CCL. Nilai default-nya adalah 86400 detik.

Catatan penggunaan

Operasi CCL hanya berlaku pada instans RDS saat ini karena tidak menghasilkan log. Sebagai contoh, operasi CCL yang dilakukan pada instans RDS primary tidak disinkronkan ke instans RDS secondary, instans RDS read-only, atau instans RDS disaster recovery.
CCL menyediakan mekanisme timeout untuk mengatasi deadlock transaksi yang disebabkan oleh pernyataan DML. Thread yang tertunda juga merespons timeout transaksi dan terminasi thread guna mencegah deadlock.

Deskripsi fitur

CCL menyediakan fitur berdasarkan dimensi berikut:

Perintah SQL: Jenis perintah SQL, seperti SELECT, UPDATE, INSERT, atau DELETE.
Objek: Objek tempat perintah SQL dijalankan, seperti TABLE atau VIEW.
Kata kunci: Kata kunci yang terdapat dalam perintah SQL.
Templat: Templat perintah SQL.

Buat tabel CCL

AliSQL menggunakan tabel sistem bernama concurrency_control untuk menyimpan aturan CCL. Tabel ini dibuat secara otomatis oleh sistem saat startup. Anda dapat menjalankan pernyataan berikut untuk membuat tabel tersebut:

CREATE TABLE `concurrency_control` (
  `Id` bigint NOT NULL AUTO_INCREMENT,
  `Type` enum('SELECT','UPDATE','INSERT','DELETE','TEMPLATE') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'SELECT',
  `Schema_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Table_name` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `Concurrency_count` bigint NOT NULL,
  `Keywords` text COLLATE utf8mb3_bin,
  `State` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'Y',
  `Ordered` enum('N','Y') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'N',
  `Digest` varchar(64) COLLATE utf8mb3_bin DEFAULT NULL,
  `SQL_template` longtext COLLATE utf8mb3_bin,
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin STATS_PERSISTENT=0 COMMENT='Concurrency control'

Parameter	Deskripsi
Id	ID aturan CCL.
Type	Jenis pernyataan SQL.
Schema_name	Nama database.
Table_name	Nama tabel dalam database.
Concurrency_count	Jumlah konkurensi. Jika parameter ini diatur ke 0, semua pernyataan SQL yang sesuai dengan aturan tersebut ditolak dan mengembalikan error `ERROR 7535 (HY000): Concurrency control refuse to execute query`.
Keywords	Kata kunci. Pisahkan beberapa kata kunci dengan titik koma (;).
State	Menentukan apakah aturan diaktifkan atau tidak.
Ordered	Menentukan apakah beberapa kata kunci harus dicocokkan secara berurutan.
Digest	String hash sepanjang 64 byte yang diperoleh dari perhitungan hash berdasarkan SQL_template.
SQL_template	Templat pernyataan SQL.

Manage aturan CCL

AliSQL menyediakan empat antarmuka manajemen dalam paket DBMS_CCL. Daftar berikut menjelaskan antarmuka tersebut:

add_ccl_rule: Tambahkan aturan

Sintaks

dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'<Keywords>');

Contoh
- Atur jumlah konkurensi untuk semua pernyataan SELECT menjadi 10.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Atur jumlah konkurensi menjadi 20 untuk pernyataan SELECT yang mengandung kata kunci key1.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Atur jumlah konkurensi menjadi 20 untuk pernyataan SELECT yang dijalankan pada tabel test.t dan mengandung kata kunci key2.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');
```

add_ccl_template_rule: Tambahkan aturan templat

Prasyarat
Untuk menambahkan aturan templat, instans Anda harus menjalankan salah satu versi berikut:
- MySQL 8.4
- MySQL 8.0 dengan versi mesin minor 20230630 atau lebih baru
- MySQL 5.7 dengan versi mesin minor 20241231 atau lebih baru

Sintaks
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'', 'Template_sql');
```
Catatan
- Anda tidak perlu menentukan parameter Table_name. Cukup tentukan satu pernyataan SQL untuk parameter Template_sql.
- Anda dapat menggunakan fungsi bawaan STATEMENT_DIGEST_TEXT untuk melihat templat SQL yang sesuai dengan pernyataan SQL tersebut.

Contoh
Atur jumlah konkurensi menjadi 30 untuk pernyataan SQL dalam database test yang sesuai dengan templat 'SELECT c FROM t1 WHERE id=?'.
```
call dbms_ccl.add_ccl_rule('TEMPLATE', 'test', '', 30, '', 'SELECT c FROM t1 WHERE id=4');
```
Catatan
- Setiap pernyataan SQL hanya dapat mencocokkan satu aturan.
- Aturan dari berbagai jenis diurutkan dari yang paling spesifik ke paling umum: aturan templat, aturan lengkap, aturan kata kunci, aturan pernyataan SQL. Setiap set aturan diperiksa secara berurutan untuk menentukan apakah suatu aturan terkena (hit).

del_ccl_rule: Hapus aturan

Sintaks
```
dbms_ccl.del_ccl_rule(<Id>);
```

Contoh

Hapus aturan CCL dengan ID 15.

mysql> call dbms_ccl.del_ccl_rule(15);

Catatan

Jika aturan yang ingin Anda hapus tidak ada, sistem akan mengeluarkan warning. Anda dapat menjalankan perintah show warnings; untuk melihat detail warning tersebut.

mysql> call dbms_ccl.del_ccl_rule(100);
  Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 7514 | Concurrency control rule 100 is not found in table |
| Warning | 7514 | Concurrency control rule 100 is not found in cache |
+---------+------+----------------------------------------------------+

show_ccl_rule: Lihat aturan yang diaktifkan

Sintaks
```
dbms_ccl.show_ccl_rule();
```

Contoh

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
|   17 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
|   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
|   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

Tabel berikut menjelaskan MATCHED, RUNNING, dan WAITTING.

Parameter	Deskripsi
MATCHED	Jumlah kali aturan tersebut dicocokkan.
RUNNING	Jumlah thread yang sedang berjalan secara konkuren berdasarkan aturan tersebut.
WAITTING	Jumlah thread yang tertunda berdasarkan aturan tersebut.

flush_ccl_rule: Terapkan aturan

Sintaks
Jika Anda langsung memodifikasi aturan dalam tabel concurrency_control, perubahan tersebut tidak langsung berlaku. Anda harus menjalankan perintah berikut untuk menerapkan aturan:
```
dbms_ccl.flush_ccl_rule();
```

Contoh

mysql> update mysql.concurrency_control set CONCURRENCY_COUNT = 15 where Id = 18;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> call dbms_ccl.flush_ccl_rule();
Query OK, 0 rows affected (0.00 sec)

Pengujian fitur

Skrip pengujian

Jalankan pernyataan berikut untuk membuat aturan berdasarkan tiga dimensi.

-- Buat aturan dengan jumlah konkurensi pernyataan SELECT yang mengandung kata kunci sbtest1 sebanyak 3.
call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

-- Buat aturan dengan jumlah konkurensi pernyataan SELECT yang mengandung kata kunci sbtest2 sebanyak 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');  

-- Buat aturan dengan jumlah konkurensi pernyataan SELECT sebanyak 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');

Skenario pengujian
Gunakan sysbench untuk pengujian dalam skenario berikut:
- 64 thread
- 4 tabel
- select.lua

Hasil pengujian

Jalankan pernyataan berikut untuk menanyakan jumlah thread konkuren berdasarkan aturan:

mysql> call dbms_ccl.show_ccl_rule();
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE   | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
|   20 | SELECT | test   | sbtest1 | Y     | N     |                 3 |     389 |       3 |        9 |          |
|   21 | SELECT |        |         | Y     | N     |                 2 |     375 |       2 |       14 | sbtest2  |
|   22 | SELECT |        |         | Y     | N     |                 2 |     519 |       2 |       34 |          |
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
3 rows in set (0.00 sec)

Angka yang ditampilkan pada kolom RUNNING sama dengan nilai yang ditentukan saat membuat aturan.