Cara menggunakan aturan CCL kontrol konkurensi - ApsaraDB RDS

Alibaba Cloud menyediakan fitur pengendalian konkurensi (CCL) dan paket DBMS_CCL untuk memastikan stabilitas instance ApsaraDB RDS for MySQL dalam menghadapi lalu lintas permintaan yang tidak terduga, pernyataan yang mengonsumsi banyak sumber daya, serta perubahan model akses SQL. Fitur CCL dikembangkan berdasarkan aturan sintaksis. Anda dapat menggunakan paket DBMS_CCL untuk membatasi jumlah maksimum pernyataan SQL dengan jenis tertentu yang dieksekusi secara bersamaan. Jika jumlah pernyataan SQL yang dieksekusi secara bersamaan mencapai batas atas, pernyataan SQL tambahan akan masuk ke keadaan menunggu hingga jumlahnya kurang dari batas atas.

Prasyarat

Versi instance yang didukung adalah sebagai berikut:

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 dapat berada dalam status `Concurrency control waiting` untuk aturan CCL yang sama. Jika batas ini dilampaui, pernyataan SQL gagal dan mengembalikan kode kesalahan `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 waktu ini dilampaui, pernyataan SQL tetap dieksekusi dan tidak lagi dibatasi oleh aturan CCL. Nilai default-nya adalah 86.400 detik.

Catatan Penggunaan

Operasi CCL hanya memengaruhi instance RDS saat ini karena tidak ada log yang dihasilkan. Sebagai contoh, operasi CCL yang dilakukan pada instance RDS utama tidak disinkronkan ke instance RDS sekunder, instance RDS baca-saja, atau instance RDS pemulihan bencana.
CCL menyediakan mekanisme timeout untuk menyelesaikan deadlock transaksi yang disebabkan oleh pernyataan DML. Thread yang tertunda juga merespons timeout transaksi dan penghentian thread untuk mencegah deadlock.

Deskripsi Fitur

CCL menyediakan fitur berdasarkan dimensi berikut:

SQL command: Jenis perintah SQL, seperti SELECT, UPDATE, INSERT, atau DELETE.
Object: Objek yang dioperasikan oleh perintah SQL, seperti TABLE atau VIEW.
Keywords: Kata kunci dalam perintah SQL.
Template: Templat perintah SQL.

Buat Tabel CCL

AliSQL menggunakan tabel sistem bernama concurrency_control untuk menyimpan aturan CCL. Sistem secara otomatis membuat tabel tersebut ketika sistem dijalankan. Anda dapat mengeksekusi pernyataan berikut untuk membuat tabel:

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='Pengendalian konkurensi'

Parameter	Deskripsi
Id	ID aturan CCL.
Type	Tipe pernyataan SQL.
Schema_name	Nama database.
Table_name	Nama tabel dalam database.
Concurrency_count	Jumlah konkurensi. Jika diatur ke 0, semua pernyataan SQL yang sesuai dengan aturan ini ditolak, dan kode kesalahan `ERROR 7535 (HY000): Concurrency control refuse to execute query` dikembalikan.
Keywords	Kata kunci. Pisahkan beberapa kata kunci dengan titik koma (;).
State	Menentukan apakah aturan diaktifkan.
Ordered	Menentukan apakah akan mencocokkan beberapa kata kunci secara berurutan.
Digest	String hash 64-byte yang diperoleh dengan melakukan perhitungan hash berdasarkan SQL_template.
SQL_template	Template pernyataan SQL.

Kelola Aturan CCL

Untuk menyederhanakan pengelolaan aturan CCL, AliSQL menyediakan empat prosedur penyimpanan lokal berikut dalam DBMS_CCL:

add_ccl_rule: Tambahkan aturan

Templat perintah

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

Contoh
- Tambahkan aturan berdasarkan tipe perintah SQL: Atur konkurensi untuk semua pernyataan SELECT menjadi 10.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Tambahkan aturan berdasarkan kata kunci: Atur konkurensi untuk pernyataan SELECT yang mengandung kata kunci `key1` menjadi 20.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Tambahkan aturan lengkap: Atur konkurensi untuk pernyataan SELECT pada tabel `test.t` yang mengandung kata kunci `key2` menjadi 20.
```
mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 20, 'key2');
```

add_ccl_template_rule: Tambahkan aturan berbasis templat

Prasyarat
Untuk menambahkan aturan berbasis templat, instance Anda harus memenuhi salah satu persyaratan versi berikut:
- MySQL 8.0 dengan versi mesin minor 20230630 atau lebih baru
- MySQL 5.7 dengan versi mesin minor 20241231 atau lebih baru

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

Contoh
Tambahkan aturan berbasis templat untuk mengatur konkurensi menjadi 30 bagi 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 cocok dengan satu aturan.
- Aturan dari tipe berbeda terdaftar dalam urutan menurun: aturan template, aturan lengkap, aturan kata kunci, aturan pernyataan SQL. Setiap set aturan diperiksa bergantian untuk menentukan apakah suatu aturan tercapai.

del_ccl_rule: Hapus aturan

Templat perintah
```
dbms_ccl.del_ccl_rule(<Id>);
```

Contoh

Menghapus 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 peringatan. Anda dapat menjalankan perintah show warnings; untuk melihat pesan peringatan 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 dalam memori

Templat perintah
```
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 kolom MATCHED, RUNNING, dan WAITING.

Parameter	Deskripsi
MATCHED	Jumlah kali aturan dicocokkan.
RUNNING	Jumlah thread yang sedang berjalan secara bersamaan di bawah aturan.
WAITTING	Jumlah thread yang tertunda di bawah aturan.

flush_ccl_rule: Terapkan perubahan aturan

Templat perintah
Jika Anda langsung memodifikasi aturan dalam tabel concurrency_control, perubahan tersebut tidak langsung berlaku. Anda harus menjalankan perintah berikut untuk menerapkan perubahan tersebut:
```
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

Aturan uji

Tiga aturan berikut masing-masing sesuai dengan tiga dimensi:

-- Buat aturan di mana jumlah pernyataan SELECT bersamaan dengan kata kunci sbtest1 adalah 3.
call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

-- Buat aturan di mana jumlah pernyataan SELECT bersamaan dengan kata kunci sbtest2 adalah 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');  

-- Buat aturan di mana jumlah pernyataan SELECT bersamaan adalah 2.
call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');

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

Hasil Uji

Eksekusi pernyataan berikut untuk menanyakan jumlah thread bersamaan di bawah 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 dalam kolom RUNNING sama dengan yang ditentukan saat Anda membuat aturan.