Antrian Pernyataan - PolarDB

Fitur Antrian Pernyataan dari PolarDB memungkinkan pengantrean pernyataan yang mungkin saling bertentangan dalam bucket yang sama, mengurangi overhead akibat potensi konflik.

Informasi Latar Belakang

Selama eksekusi pernyataan konkuren, server MySQL dan mesin dapat mengalami konflik pada operasi serial tertentu. Sebagai contoh, kunci transaksi sering ditemui dalam pernyataan Data Manipulation Language (DML). Di InnoDB, baris merupakan unit terkecil yang dapat dikunci, dengan granularitas kunci minimum berupa kunci tingkat baris dalam kunci transaksi. Jika pernyataan dijalankan secara bersamaan pada baris yang sama, konflik dapat terjadi, menyebabkan penurunan throughput sistem seiring meningkatnya konkurensi. Fitur Antrian Pernyataan dari PolarDB mengurangi overhead dari konflik ini dan meningkatkan performa basis data Anda.

Batasan

Salah satu kluster PolarDB berikut harus digunakan:
- Kluster PolarDB for MySQL 8.0 dengan versi mesin minor 8.0.1.1.10 atau lebih baru.
- Kluster PolarDB for MySQL 5.7 dengan versi mesin minor 5.7.1.0.6 atau lebih baru.
- Kluster PolarDB for MySQL 5.6 dengan versi mesin minor 20200601 atau lebih baru.
PolarDB for MySQL 5.6 dan PolarDB for MySQL 5.7 serta 8.0 menggunakan sintaksis berbeda untuk mengimplementasikan fitur Antrian Pernyataan. Jika Anda menggunakan kluster PolarDB for MySQL 5.7 dan 8.0, Antrian Pernyataan dapat bekerja dengan Outline Pernyataan untuk mendukung manajemen online jumlah pernyataan SQL konkuren.
Setelah Anda mengaktifkan fitur optimasi baris panas dengan mengatur hotspot ke ON, mekanisme Antrian Pernyataan tidak valid.

Hasil

Dalam pengujian skenario di mana pernyataan UPDATE konkuren dieksekusi pada satu baris, performa PolarDB mencapai sekitar empat kali lipat dibandingkan dengan MySQL asli.

Sintaksis

Di PolarDB for MySQL 5.6, Anda dapat mengantrekan pernyataan dalam bucket dengan algoritma hash menggunakan salah satu metode berikut.
Catatan
Anda dapat menggunakan POLARDB_STATEMENT_CONCURRENT_QUEUE dalam pernyataan SELECT, UPDATE, INSERT, dan DELETE.
- Mengantrekan pernyataan dalam bucket berdasarkan nilai INT atau STRING.
  - Sintaksis:
```
POLARDB_STATEMENT_CONCURRENT_QUEUE [int | string]
```
  - Contoh:
```
insert POLARDB_STATEMENT_CONCURRENT_QUEUE 1 into t values(1, 1, 'xpchild');
update POLARDB_STATEMENT_CONCURRENT_QUEUE 1 t set c=c+1 where id = 1;
update POLARDB_STATEMENT_CONCURRENT_QUEUE "xpchild" t set col1 = col1+1 where id =1;
```
- Mengantrekan pernyataan dalam bucket berdasarkan nilai bidang dalam klausa WHERE.
  - Sintaksis:
```
POLARDB_STATEMENT_CONCURRENT_QUEUE [field]
```
  - Contoh:
```
select POLARDB_STATEMENT_CONCURRENT_QUEUE id * from t where 3 = id;
update POLARDB_STATEMENT_CONCURRENT_QUEUE id t set c=c+1 where id = 1 and name = 'xpchild';
```
    Catatan
    Dalam sintaksis POLARDB_STATEMENT_CONCURRENT_QUEUE [field], hanya operasi biner pada kolom asli yang didukung dalam klausa WHERE. Nilai di sisi kanan operasi biner harus berupa angka atau string. Fungsi atau perhitungan tidak dapat digunakan pada kolom asli ini.
Di PolarDB for MySQL 5.7 dan 8.0, Anda dapat mengantrekan pernyataan dalam bucket menggunakan salah satu metode berikut dengan sintaksis hint.
Catatan
Anda dapat menggunakan ccl_queue_value dalam pernyataan SELECT, UPDATE, INSERT, dan DELETE.
- Mengantrekan pernyataan dalam bucket berdasarkan nilai INT atau STRING.
  - Sintaksis:
```
/*+ CCL_QUEUE_VALUE([INT | STRING)] */
```
  - Contoh:
```
update /*+ ccl_queue_value(1) */ t set c=c+1 where id = 1;

update /*+ ccl_queue_value('xpchild') */ t set c=c+1 
                                           where name = 'xpchild';
```
- Mengantrekan pernyataan dalam bucket berdasarkan nilai bidang dalam klausa WHERE.
  - Sintaksis:
```
/*+ CCL_QUEUE_FIELD(STRING) */
```
  - Contoh:
```
 update /*+ ccl_queue_field("id") */ t set c=c+1
                                      where id = 1 and name = 'xpchild';
```
    Catatan
    Dalam sintaksis /*+ CCL_QUEUE_FIELD(STRING) */, hanya operasi biner pada kolom asli yang didukung dalam klausa WHERE. Nilai di sisi kanan operasi biner harus berupa angka atau string. Fungsi atau perhitungan tidak dapat digunakan pada kolom asli ini.

Variabel

PolarDB menyediakan variabel berikut untuk mendefinisikan jumlah bucket dan ukuran antrian pernyataan.

Variabel	Deskripsi
ccl_queue_bucket_count	Jumlah bucket. Nilai valid: 1 hingga 64. Nilai default: 4.
ccl_queue_bucket_size	Jumlah pernyataan konkuren yang didukung oleh sebuah bucket. Nilai valid: 1 hingga 4096. Nilai default: 64.

Operasi

PolarDB menyediakan fungsi berikut untuk menanyakan status Antrian Pernyataan:

dbms_ccl.show_ccl_queue()

Fungsi ini digunakan untuk menanyakan status Antrian Pernyataan saat ini.

mysql> call dbms_ccl.show_ccl_queue();   
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       1 |       0 |        0 |
|    2 | QUEUE |                64 |   40744 |      65 |        6 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.01 sec)

Catatan

Tabel berikut mencantumkan parameter CONCURRENCY_COUNT, MATCHED, RUNNING, dan WAITTING:

CONCURRENCY_COUNT: jumlah maksimum pernyataan konkuren.
MATCHED: jumlah total pernyataan yang sesuai dengan aturan.
RUNNING: jumlah pernyataan konkuren yang sedang berjalan.
WAITTING: jumlah pernyataan yang sedang menunggu dalam antrian.

dbms_ccl.flush_ccl_queue()

Fungsi ini digunakan untuk menghapus data tentang Antrian Pernyataan dari memori dan menanyakan status Antrian Pernyataan.

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

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       0 |       0 |        0 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)

Bekerja dengan outline pernyataan untuk mendukung pembaruan online

Di PolarDB for MySQL 5.7 dan 8.0, Antrian Pernyataan dapat bekerja dengan Outline Pernyataan untuk mendukung manajemen online jumlah pernyataan SQL konkuren, meminimalkan perubahan pada kode aplikasi Anda. Dalam contoh berikut, Sysbench digunakan untuk menjalankan skrip update_non_index.lua.

Catatan

Di PolarDB for MySQL 5.6, Antrian Pernyataan tidak dapat bekerja dengan Outline Pernyataan karena PolarDB for MySQL 5.6 menggunakan sintaksis yang berbeda dari PolarDB for MySQL 8.0.

Lingkungan Uji

Skema

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 MAX_ROWS=1000000;

Pernyataan

UPDATE sbtest1 SET c='xpchild' WHERE id=0;

Skrip

./sysbench 
--mysql-host= {$ip}\
--mysql-port= {$port}\
--mysql-db=test\ 
--test=./sysbench/share/sysbench/update_non_index.lua\ 
--oltp-tables-count=1\ 
--oltp_table_size=1\ 
--num-threads=128\
--mysql-user=u0\

Prosedur

Buat Outline Pernyataan dalam mode online.

mysql> CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, 
                                             ' /*+ ccl_queue_field("id") */ ',
                         "UPDATE sbtest1 SET c='xpchild' WHERE id=0");
Query OK, 0 rows affected (0.01 sec)

Lihat Outline Pernyataan yang telah Anda buat.

mysql> call dbms_outln.show_outline();
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                           | HIT  | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
|    1 | test   | 7b945614749e541e0600753367884acff5df7e7ee2f5fb0af5ea58897910f023 | OPTIMIZER |       |    1 |  /*+ ccl_queue_field("id") */  |    0 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
1 row in set (0.00 sec)

Verifikasi bahwa Outline Pernyataan telah berlaku.

mysql> explain UPDATE sbtest1 SET c='xpchild' WHERE id=0;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | sbtest1 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ CCL_QUEUE_FIELD('id') */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xpchild' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Tanyakan status Antrian Pernyataan yang digunakan.

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       0 |       0 |        0 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)

Mulai pengujian.

sysbench 
--mysql-host= {$ip}\
--mysql-port= {$port}\
--mysql-db=test\ 
--test=./sysbench/share/sysbench/update_non_index.lua\ 
--oltp-tables-count=1\ 
--oltp_table_size=1\ 
--num-threads=128\
--mysql-user=u0\

Verifikasi hasil pengujian.

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |   10996 |      63 |        4 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.03 sec)

mysql> call dbms_outln.show_outline();
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST    | TYPE      | SCOPE | POS  | HINT                           | HIT    | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
|    1 | test   | xxxxxxxxx | OPTIMIZER |       |    1 |  /*+ ccl_queue_field("id") */  | 115795 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
1 row in set (0.00 sec)

Catatan

Berdasarkan hasil query, total 115.795 pernyataan memenuhi aturan untuk Outline Pernyataan, total 10.996 pernyataan memenuhi aturan untuk Antrian Pernyataan, total 63 pernyataan sedang dijalankan secara konkuren, dan empat pernyataan sedang menunggu dalam antrian.