Gunakan Statement Queue untuk meningkatkan performa di bawah konversi tinggi - ApsaraDB RDS

Dalam skenario konkurensi tinggi di MySQL, titik serial pada lapisan layanan dan engine (seperti konflik kunci transaksi) dapat menyebabkan penurunan performa. AliSQL menyediakan fitur Antrian Pernyataan yang mengurangi overhead konflik melalui mekanisme antrian ember, meningkatkan performa instans. Fitur ini menetapkan pernyataan dengan potensi konflik serupa (misalnya, pernyataan yang beroperasi pada baris yang sama) ke dalam antrian di ember yang sama, sehingga mengoptimalkan efisiensi eksekusi konkuren.

Informasi Latar Belakang

Pada lapisan layanan dan engine MySQL, terdapat beberapa titik serialisasi ketika pernyataan dieksekusi secara bersamaan, yang dapat menyebabkan konflik. Sebagai contoh, konflik kunci transaksi sering terjadi selama eksekusi pernyataan DML. Granularitas terbaik dari kunci transaksi di engine InnoDB adalah kunci tingkat baris. Ketika beberapa pernyataan melakukan operasi bersamaan pada baris yang sama, konflik serius terjadi, menyebabkan throughput sistem menurun secara signifikan seiring dengan meningkatnya konkurensi. Untuk mengatasi masalah ini, AliSQL menyediakan fitur Antrian Pernyataan, yang secara efektif meningkatkan performa instans dengan mengurangi overhead konflik.

Prasyarat

Versi instans RDS MySQL harus memenuhi persyaratan berikut:

RDS MySQL 8.0 Edisi Dasar atau Edisi Ketersediaan Tinggi (versi mesin minor 20191115 atau lebih baru)
RDS MySQL 5.7 Edisi Dasar atau Edisi Ketersediaan Tinggi (versi mesin minor 20200630 atau lebih baru)

Variabel Konfigurasi

AliSQL menyediakan dua variabel untuk menentukan jumlah bucket dan ukuran antrian pernyataan. Anda dapat mengubah nilai variabel di Konsol RDS.

ccl_queue_bucket_count: jumlah bucket.
- Nilai valid: 1 hingga 64
- Nilai default: 4
ccl_queue_bucket_size: jumlah pernyataan konkuren yang diizinkan per bucket.
- Nilai valid: 1 hingga 4.096
- Nilai default: 64

Sintaks

AliSQL mendukung dua sintaksis Petunjuk:

ccl_queue_value: menggunakan algoritma hash untuk menentukan ember berdasarkan nilai.

Sintaksis:

/*+ ccl_queue_value([int | string]) */

Contoh:

update /*+ ccl_queue_value(1) */ t set c=c+1 where id = 1;

update /*+ ccl_queue_value('xyz') */ t set c=c+1 where name = 'xyz';

ccl_queue_field: menggunakan algoritma hash untuk menentukan ember 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 = 'xyz';
```
Catatan
- Kedua petunjuk di atas sensitif terhadap posisi dan harus ditempatkan setelah kata kunci update.
- ccl_queue_field hanya dapat menentukan satu bidang pada satu waktu. Sintaksis /*+ ccl_queue_field(id name) */ tidak benar dan antrian CCL tidak akan berlaku. Untuk /*+ ccl_queue_field(id) ccl_queue_field(name) */, yang merupakan petunjuk duplikat, bidang yang ditentukan dalam petunjuk pertama akan digunakan.
- Bidang yang ditentukan dalam ccl_queue_field harus muncul dalam klausa WHERE.
- Dalam sintaksis ccl_queue_field, klausa WHERE hanya mendukung operasi biner pada bidang mentah (tanpa menggunakan fungsi, perhitungan, dll. pada bidang), dan nilai sisi kanan dari operasi biner harus berupa angka atau string.

Fungsi

AliSQL menyediakan dua fungsi untuk membantu Anda menanyakan status Antrian Pernyataan:

dbms_ccl.show_ccl_queue(): menanyakan status Antrian Pernyataan saat ini.

call dbms_ccl.show_ccl_queue();

Mengembalikan hasil berikut:

+------+-------+-------------------+---------+---------+----------+
| 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)

Deskripsi parameter:

Parameter	Deskripsi
CONCURRENCY_COUNT	Konkurensi maksimum.
MATCHED	Jumlah total pukulan aturan.
RUNNING	Jumlah operasi konkuren saat ini.
WAITTING	Jumlah operasi menunggu saat ini.

dbms_ccl.flush_ccl_queue(): membersihkan data dalam memori.

call dbms_ccl.flush_ccl_queue();
call dbms_ccl.show_ccl_queue();

Mengembalikan hasil berikut:

+------+-------+-------------------+---------+---------+----------+
| 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)

Praktik

Pengujian fungsional

Untuk menghindari modifikasi kode aplikasi yang panjang, Antrian Pernyataan dapat digunakan dengan Outline Pernyataan untuk modifikasi bisnis online, yang nyaman dan efisien. Contoh berikut menunjukkan penggunaan kasus uji Sysbench update_non_index.

Lingkungan Pengujian

Skema tabel uji

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 uji
```
UPDATE sbtest1 SET c='xyz' WHERE id=0;
```

Skrip uji

./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 Pengujian

Tambahkan Outline Pernyataan secara online.

CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, 
                                             ' /*+ ccl_queue_field(id) */ ',
                         "UPDATE sbtest1 SET c='xyz' WHERE id=0");

Mengembalikan hasil berikut:

Query OK, 0 rows affected (0.01 sec)

Lihat Outline Pernyataan.

call dbms_outln.show_outline();

Mengembalikan hasil berikut:

+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| 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 efektif.

explain UPDATE sbtest1 SET c='xyz' WHERE id=0;

Mengembalikan hasil berikut:

+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 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)

show warnings;

Mengembalikan hasil berikut:

+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ ccl_queue_field(id) */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xyz' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Tanyakan status Antrian Pernyataan.

call dbms_ccl.show_ccl_queue();

Mengembalikan hasil berikut:

+------+-------+-------------------+---------+---------+----------+
| 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.

call dbms_ccl.show_ccl_queue();

Mengembalikan hasil berikut:

+------+-------+-------------------+---------+---------+----------+
| 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)

call dbms_outln.show_outline();

Mengembalikan hasil berikut:

+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
| 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

Hasil kueri menunjukkan bahwa Outline Pernyataan mencapai 115.795 aturan, status Antrian Pernyataan menunjukkan 10.996 hit antrian, dengan 63 saat ini berjalan secara konkuren dan 4 menunggu dalam antrian.

Pengujian performa

Lingkungan Pengujian
- Server aplikasi: Instance ECS Alibaba Cloud
- Tipe instans RDS: 8 core, 16 GB memori, SSD Perusahaan (ESSD)
- Tipe instans: Edisi Ketersediaan Tinggi (dengan replikasi asinkron)

Kasus Uji

Pembaruan konkuren pada catatan dengan id=1, menggunakan kasus uji Sysbench berikut:

pathtest = string.match(test, "(.*/)")

if pathtest then
 dofile(pathtest .. "oltp_common.lua")
else
 require("oltp_common")
end

function thread_init()
 drv = sysbench.sql.driver()
 con = drv:connect()
end

function event()
 local val_name
 val_name = "'sdnjkmoklvnseajinvijsfdnvkjsnfjvn".. sb_rand_uniform(1, 4096) .. "'"
 query = "UPDATE sbtest1 SET c=" .. val_name .. " WHERE id=1"
 rs = db_query(query)
end

Hasil Pengujian
Dengan Antrian Pernyataan diaktifkan, QPS meningkat secara signifikan dalam skenario konkurensi tinggi. Semakin tinggi konkurensi, semakin jelas peningkatannya.
Catatan
Tanpa Antrian Pernyataan diaktifkan, failover utama/sekunder terjadi selama pengujian stres 4096-thread, menghasilkan QPS 0.