Apa itu Kontrol Konkurensi - PolarDB

Alibaba Cloud menyediakan aturan Concurrency Control (CCL) berbasis Pernyataan SQL dan paket DBMS_CCL untuk memastikan stabilitas kluster PolarDB dengan menangani lonjakan traffic database, pernyataan SQL yang mengonsumsi banyak resource, serta perubahan pada model akses SQL.

Prasyarat

Kluster PolarDB Anda harus menggunakan salah satu versi berikut:

PolarDB for MySQL 8.0.
PolarDB for MySQL 5.7 dengan versi mesin minor 5.7.1.0.6 atau lebih baru.
Catatan
Jika kluster Anda adalah PolarDB for MySQL 5.7 dengan versi mesin minor 5.7.1.0.27 atau lebih baru, CCL kompatibel dengan Thread Pool.
PolarDB for MySQL 5.6.

Catatan

Anda hanya dapat memodifikasi aturan CCL pada node primary. Sistem secara otomatis menyinkronkan modifikasi tersebut ke node lainnya.

Desain fitur

Dimensions

CCL mencocokkan pernyataan SQL dengan aturan berdasarkan lima dimensi berikut:

Dimensi	Deskripsi
TIPE	Jenis pernyataan SQL, seperti SELECT, UPDATE, INSERT, DELETE, atau DDL.
SKEMA	Nama database tempat operasi SQL dilakukan.
TABEL	Nama tabel atau view tempat operasi SQL dilakukan.
KATA KUNCI	Kata kunci dalam pernyataan SQL. Anda dapat mengonfigurasi beberapa kata kunci dalam satu aturan CCL. Pisahkan beberapa kata kunci dengan tanda titik koma (;).
DIGEST	String hash yang dihasilkan dari pernyataan SQL. Untuk informasi selengkapnya, lihat STATEMENT_DIGEST().

How SQL statements are matched with CCL rules

Jika nilai DIGEST dalam aturan CCL kosong, metode pencocokan berikut digunakan:
- Jika nilai DIGEST kosong dan nilai TYPE, SCHEMA, serta TABLE ditentukan, aturan hanya berlaku jika TYPE, SCHEMA, dan TABLE dari pernyataan SQL sesuai dengan nilai yang sesuai dalam aturan.
- Jika nilai DIGEST kosong, nilai SCHEMA dan TABLE kosong, dan nilai TYPE ditentukan, aturan hanya berlaku jika TYPE pernyataan SQL sesuai dengan TYPE dalam aturan.
Catatan
Jika KEYWORD dalam aturan CCL tidak kosong, kata kunci tersebut juga diperiksa:
- Jika satu KEYWORD ditentukan dalam aturan CCL, terjadi kecocokan jika pernyataan SQL mengandung kata kunci tersebut.
- Jika beberapa KEYWORD ditentukan dalam aturan CCL, terjadi kecocokan hanya jika pernyataan SQL mengandung semua kata kunci yang ditentukan. Urutan kata kunci dalam pernyataan SQL tidak memengaruhi kecocokan.
Jika nilai DIGEST dalam aturan CCL tidak kosong, aturan hanya berlaku jika SCHEMA dan nilai DIGEST dari pernyataan SQL sesuai dengan nilai yang sesuai dalam aturan.
Jika SCHEMA dalam aturan CCL kosong, aturan berlaku jika nilai DIGEST pernyataan SQL sesuai dengan nilai DIGEST dalam aturan.

Matching Order of SQL Statements and CCL Rules

Satu pernyataan SQL hanya dapat mencocokkan satu aturan CCL. Jika suatu pernyataan cocok dengan beberapa aturan, aturan dengan prioritas tertinggi yang digunakan. Prioritas ditentukan dalam urutan berikut. Jika beberapa aturan memiliki tingkat prioritas yang sama, aturan dengan ID lebih rendah yang dicocokkan terlebih dahulu:

Cocokkan berdasarkan nilai DIGEST.
Cocokkan berdasarkan TYPE, SCHEMA, dan TABLE.
Cocokkan berdasarkan TYPE saja.

Deskripsi parameter

Anda dapat memodifikasi parameter berikut di Konsol PolarDB. Untuk informasi selengkapnya, lihat Setel parameter kluster dan node.

Parameter

Deskripsi

loose_ccl_mode

Perilaku pernyataan SQL ketika batas konkurensi terlampaui. Nilai yang valid:

WAIT (default): Pernyataan masuk antrian dan menunggu. Pernyataan dieksekusi setelah pernyataan SQL lain selesai.
REFUSE: Dilaporkan error.

Catatan

Parameter ini hanya didukung untuk PolarDB for MySQL 8.0. Untuk versi 5.6 dan 5.7, pernyataan selalu masuk antrian dan menunggu.

loose_ccl_max_waiting_count

Ketika loose_ccl_mode diatur ke WAIT, parameter ini menentukan jumlah maksimum pernyataan SQL yang dapat dimasukkan ke dalam antrian untuk satu aturan CCL. Jika jumlah ini terlampaui, dilaporkan error.

Rentang nilai: 0 hingga 65536. Nilai default: 0.

Catatan

Parameter ini hanya didukung untuk PolarDB for MySQL 5.7 dan 8.0.

Tabel aturan CCL

PolarDB menggunakan tabel sistem concurrency_control untuk menyimpan aturan CCL. Tabel ini dibuat secara otomatis saat sistem dimulai. Pernyataan `CREATE TABLE` untuk tabel sistem ini adalah sebagai berikut:

CREATE TABLE concurrency_control (
  Id bigint AUTO_INCREMENT NOT NULL,
  Type varchar(64),
  Schema_name varchar(64),
  Table_name varchar(64),
  Concurrency_count bigint NOT NULL,
  Keywords text,
  State enum('N','Y') COLLATE utf8_general_ci DEFAULT 'Y' NOT NULL,
  Ordered enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
  Digest varchar(64),
  Digest_text longtext,
  Extra mediumtext,
  PRIMARY KEY Rule_id(id)
) Engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8 COLLATE utf8_bin
  COMMENT='Concurrency control' TABLESPACE=mysql;

Tabel berikut menjelaskan parameter.

Parameter	Deskripsi
Id	ID aturan CCL.
Type	Jenis pernyataan SQL, seperti SELECT, UPDATE, INSERT, DELETE, atau DDL.
Schema_name	Nama database.
Table_name	Nama tabel dalam database.
Concurrency_count	Jumlah konkurensi. Catatan Anda dapat mengatur `Concurrency_count` ke 0 untuk menerapkan blacklist SQL. Hal ini melarang jenis kueri ini dieksekusi.
Keywords	Kata kunci. Pisahkan beberapa kata kunci dengan tanda titik koma (;).
State	Menentukan apakah aturan diaktifkan. Nilai yang valid: Y (default): Aturan diaktifkan. N: Aturan dinonaktifkan.
Ordered	Menentukan apakah kata kunci harus dicocokkan secara berurutan ketika beberapa kata kunci dikonfigurasi di bidang Keywords. Nilai yang valid: N (default): Kata kunci di bidang Keywords tidak perlu dicocokkan secara berurutan. Y: Kata kunci di bidang Keywords harus dicocokkan secara berurutan.
Digest	String hash 64-byte yang dihasilkan dari `Digest_text`. Untuk informasi selengkapnya, lihat STATEMENT_DIGEST().
Digest_text	Fitur pernyataan SQL.
Extra	Informasi lainnya.

Kelola aturan CCL

Untuk membantu Anda mengelola aturan CCL, PolarDB menyediakan enam prosedur tersimpan lokal berikut dalam paket DBMS_CCL:

add_ccl_rule: Menambahkan aturan CCL yang dicocokkan berdasarkan TYPE, SCHEMA, TABLE, dan KEYWORD.
Syntax
```
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'<Keywords>');
```
Examples
- Tambahkan aturan CCL untuk tipe SELECT. Jika jumlah konkurensi mencapai 10, pernyataan berikutnya akan masuk antrian untuk menunggu atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
```
- Tambahkan aturan CCL untuk pernyataan SELECT yang mengandung kata kunci `key1`. Jika jumlah konkurensi mencapai 20, pernyataan berikutnya akan masuk antrian untuk menunggu atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
```
- Tambahkan aturan CCL untuk pernyataan SELECT yang mengandung kata kunci `key1`, `key2`, dan `key3`. Jika jumlah konkurensi mencapai 20, pernyataan berikutnya akan masuk antrian untuk menunggu atau dilaporkan error. Urutan kata kunci tidak menjadi masalah.
```
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1;key2;key3');
```
- Tambahkan aturan CCL untuk pernyataan SELECT dengan SCHEMA diatur ke test dan TABLE diatur ke t. Jika jumlah konkurensi untuk pernyataan SELECT ini mencapai 10, pernyataan berikutnya akan masuk antrian untuk menunggu atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 10, '');
```
add_ccl_digest_rule: Menambahkan aturan CCL yang dicocokkan berdasarkan nilai DIGEST.
Catatan
Prosedur tersimpan add_ccl_digest_rule didukung pada versi engine database berikut:
- PolarDB for MySQL 8.0.1 dengan versi mesin minor 8.0.1.1.31 atau lebih baru.
- PolarDB for MySQL 8.0.2 dengan versi mesin minor 8.0.2.2.12 atau lebih baru.
Syntax
```
dbms_ccl.add_ccl_digest_rule('<Schema_name>', '<Query>', <Concurrency_count>);
```
Examples
- Tambahkan aturan CCL yang mencocokkan pernyataan SQL SELECT * FROM t1. Jika jumlah konkurensi mencapai 10, pernyataan akan masuk antrian atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1", 10);
```
- Tambahkan aturan CCL untuk skema test yang mencocokkan pernyataan SQL SELECT * FROM t1. Jika jumlah konkurensi mencapai 10, pernyataan akan masuk antrian atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_digest_rule("test", "SELECT * FROM t1", 10);
```
- Tambahkan aturan CCL yang mencocokkan pernyataan SQL SELECT * FROM t1 WHERE col1 = 1. Jika jumlah konkurensi mencapai 10, pernyataan akan masuk antrian atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1 WHERE col1 = 1", 10);
```
  Catatan
  Jika pernyataan SQL mengandung konstanta, aturan tetap mencocokkan pernyataan tersebut meskipun nilai konstantanya berbeda. Sebagai contoh, aturan CCL di atas juga mencocokkan pernyataan SQL SELECT * FROM t1 WHERE col1 = 2.
add_ccl_digest_rule_by_hash: Menambahkan aturan Concurrency Control (CCL) yang mencocokkan nilai DIGEST. Prosedur ini menggunakan nilai DIGEST yang telah dihitung sebelumnya alih-alih pernyataan SQL.
Catatan
Prosedur tersimpan add_ccl_digest_rule_by_hash hanya didukung pada PolarDB for MySQL 8.0.1 dengan versi mesin minor 8.0.1.1.31 atau lebih baru.
Syntax
```
dbms_ccl.add_ccl_digest_rule_by_hash('<Schema_name>', '<Digest>', <Concurrency_count>);
```
Examples
- Tambahkan aturan CCL yang mencocokkan nilai DIGEST 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. Jika jumlah konkurensi mencapai 10, pernyataan akan masuk antrian untuk menunggu atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_digest_rule_by_hash('', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);
```
  Nilai 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a adalah nilai DIGEST yang dihitung untuk SELECT * FROM t1. Anda dapat menghitung nilai ini dengan menjalankan perintah SELECT statement_digest("SELECT * FROM t1") atau mengambilnya dari modul lain.
- Tambahkan aturan CCL untuk skema test yang mencocokkan nilai DIGEST 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a. Jika jumlah konkurensi mencapai 10, pernyataan akan masuk antrian untuk menunggu atau dilaporkan error.
```
CALL dbms_ccl.add_ccl_digest_rule_by_hash('test', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);
```

del_ccl_rule: Menghapus aturan CCL tertentu.

Syntax

dbms_ccl.del_ccl_rule(<Id>);

Example

Hapus aturan CC dengan ID 15.

CALL dbms_ccl.del_ccl_rule(15);

Jika aturan yang ditentukan tidak ada, sistem melaporkan peringatan. Anda dapat menjalankan perintah SHOW WARNINGS; untuk melihat peringatan tersebut. Contohnya:

Hapus aturan CC dengan ID 100.

CALL dbms_ccl.del_ccl_rule(100);

Hasil berikut dikembalikan:

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Jalankan perintah berikut untuk melihat peringatan.

SHOW WARNINGS;

Berikut ini hasil yang dikembalikan:

+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 7517 | Aturan kontrol konkurensi 100 tidak ditemukan di tabel |
| Warning | 7517 | Aturan kontrol konkurensi 100 tidak ditemukan di cache |
+---------+------+----------------------------------------------------+

Catatan

Pada contoh di atas, PolarDB for MySQL 8.0 memiliki Code 7517, PolarDB for MySQL 5.7 memiliki Code 3267, dan PolarDB for MySQL 5.6 memiliki Code 3045.

show_ccl_rule: Menampilkan aturan CCL yang diaktifkan di memori.

Syntax

dbms_ccl.show_ccl_rule();

Example

CALL dbms_ccl.show_ccl_rule();

Hasil berikut dikembalikan:

+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING  | 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 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

Catatan

Tabel berikut menjelaskan parameter MATCHED, RUNNING, dan WAITING.

MATCHED: Jumlah kali aturan dicocokkan.
RUNNING: Jumlah thread yang berjalan secara konkuren untuk aturan ini.
WAITING: Jumlah thread yang menunggu untuk dijalankan untuk aturan ini.

Anda dapat menggunakan pernyataan UPDATE untuk mengubah ID aturan CCL guna menyesuaikan prioritasnya.

Syntax

UPDATE mysql.concurrency_control SET ID = xx WHERE ID = xx;

Example

Jalankan perintah berikut untuk melihat aturan CCL yang diaktifkan di memori.

CALL dbms_ccl.show_ccl_rule();

Hasil berikut dikembalikan:

+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING  | 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 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

Jalankan perintah berikut untuk menyesuaikan prioritas aturan CCL dengan ID 17 dengan mengubah ID-nya menjadi 20.
```
UPDATE mysql.concurrency_control SET ID = 20 WHERE ID = 17;
```

Jalankan perintah berikut untuk melihat daftar terbaru aturan CCL yang diaktifkan.

CALL dbms_ccl.show_ccl_rule();

Hasil berikut dikembalikan:

+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING  | KEYWORDS |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
|   16 | SELECT |        |       | Y     | N     |                20 |       0 |       0 |        0 | key1     |
|   18 | SELECT |        |       | Y     | N     |                10 |       0 |       0 |        0 |          |
|   20 | SELECT | test   | t     | Y     | N     |                30 |       0 |       0 |        0 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

flush_ccl_rule: Jika Anda memodifikasi aturan CCL dengan langsung mengubah tabel concurrency_control, Anda harus menjalankan perintah berikut untuk menerapkan perubahan tersebut.
Syntax
```
dbms_ccl.flush_ccl_rule();
```
Example
Anda dapat menyesuaikan prioritas aturan tujuan menggunakan pernyataan UPDATE untuk memodifikasi ID aturan CC-nya.
```
UPDATE mysql.concurrency_control SET CONCURRENCY_COUNT = 15 WHERE Id = 18;
```
Output berikut dikembalikan:
```
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
Jalankan perintah berikut untuk menerapkan pengaturan tersebut.
```
CALL dbms_ccl.flush_ccl_rule();
```
Output berikut dikembalikan:
```
Query OK, 0 rows affected (0.00 sec)
```

Pengujian fungsional

Buat aturan CCL berdasarkan tiga dimensi berbeda:

CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');  // Gunakan pernyataan SELECT untuk mengoperasikan tabel sbtest1 di database test. Jumlah konkurensi adalah 3.
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');       // Pernyataan SELECT mengandung kata kunci sbtest2. Jumlah konkurensi adalah 2.
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');            // Pernyataan SELECT. Jumlah konkurensi adalah 2.

Uji menggunakan Sysbench dalam skenario berikut:
- 64 thread
- 4 tabel
- select.lua

Lihat jumlah konkurensi untuk aturan sebagai berikut:

CALL dbms_ccl.show_ccl_rule();

Output berikut dikembalikan:

+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+
| ID   | TYPE   | SCHEMA | TABLE   | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING  | 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)

Periksa kolom RUNNING untuk memverifikasi bahwa jumlah eksekusi konkuren sesuai dengan yang diharapkan.