Pada beban kerja MySQL dengan konkurensi tinggi, titik serialisasi di lapisan layanan dan lapisan engine—seperti kontensi lock transaksi—dapat menurunkan kinerja. AliSQL menyediakan fitur Antrian Pernyataan yang menggunakan mekanisme antrian berbasis bucket untuk mengurangi overhead kontensi dan meningkatkan kinerja instans. Fitur ini menetapkan pernyataan yang kemungkinan besar bersaing—misalnya, pernyataan yang beroperasi pada baris yang sama—ke dalam bucket yang sama guna mengoptimalkan eksekusi konkuren.
Informasi latar belakang
Di lapisan layanan dan lapisan engine MySQL, eksekusi konkuren pernyataan melibatkan beberapa titik serialisasi yang rentan menyebabkan kontensi. Sebagai contoh, kontensi lock transaksi umum terjadi saat eksekusi pernyataan DML. Di engine InnoDB, granularitas terkecil dari lock transaksi adalah lock tingkat baris. Ketika beberapa pernyataan secara konkuren beroperasi pada baris yang sama, terjadi kontensi parah yang secara drastis mengurangi throughput sistem seiring peningkatan konkurensi. Untuk mengatasi masalah ini, AliSQL menyediakan fitur Antrian Pernyataan guna meningkatkan kinerja instans dengan mengurangi overhead kontensi.
Prasyarat
Fitur ini tersedia untuk instans ApsaraDB RDS for MySQL dengan versi berikut:
MySQL 8.4 pada Edisi Dasar atau Edisi Ketersediaan Tinggi
MySQL 8.0 pada Edisi Dasar atau Edisi Ketersediaan Tinggi (versi mesin minor 20191115 atau lebih baru)
MySQL 5.7 pada Edisi Dasar atau Edisi Ketersediaan Tinggi (versi mesin minor 20200630 atau lebih baru)
Variabel Konfigurasi
AliSQL menyediakan dua variabel untuk menentukan jumlah dan ukuran bucket dalam Antrian Pernyataan. Anda dapat mengubah variabel ini di Konsol ApsaraDB RDS.
ccl_queue_bucket_count: Menentukan jumlah bucket.
Nilai valid: 1 hingga 64
Nilai default: 4
ccl_queue_bucket_size: Menentukan jumlah pernyataan konkuren yang diizinkan per bucket.
Nilai valid: 1 hingga 4.096
Nilai default: 64
Sintaksis
AliSQL mendukung dua jenis hint:
ccl_queue_value: Melakukan hash terhadap nilai yang ditentukan untuk menetapkan pernyataan ke suatu bucket.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: Melakukan hash terhadap nilai bidang yang ditentukan dari klausa WHERE untuk menetapkan bucket.
Sintaksis:
/*+ ccl_queue_field(string) */Contoh:
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';CatatanKedua hint bersifat sensitif terhadap posisi dan harus ditempatkan tepat setelah kata kunci UPDATE.
Hint
ccl_queue_fieldhanya dapat menentukan satu bidang dalam satu waktu. Format/*+ ccl_queue_field(id name) */merupakan kesalahan sintaksis dan mencegah Antrian Pernyataan berfungsi. Jika Anda menggunakan hint duplikat seperti/*+ ccl_queue_field(id) ccl_queue_field(name) */, hanya hint pertama yang digunakan.Bidang yang ditentukan dalam hint
ccl_queue_fieldharus muncul dalam klausa WHERE.Untuk hint
ccl_queue_field, klausa WHERE hanya mendukung operasi biner pada bidang mentah (tanpa fungsi atau perhitungan yang diterapkan pada bidang tersebut). Nilai di sisi kanan operasi biner harus berupa angka atau string.
API
AliSQL menyediakan dua fungsi untuk memantau Antrian Pernyataan:
dbms_ccl.show_ccl_queue(): Menanyakan status saat ini dari Antrian Pernyataan.call dbms_ccl.show_ccl_queue();Output berikut dikembalikan:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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)Tabel berikut menjelaskan parameter-parameter tersebut.
Parameter
Deskripsi
CONCURRENCY_COUNT
Jumlah maksimum pernyataan konkuren yang diizinkan.
MATCHED
Jumlah total pernyataan yang sesuai dengan aturan.
RUNNING
Jumlah pernyataan yang sedang berjalan.
WAITING
Jumlah pernyataan yang sedang menunggu.
dbms_ccl.flush_ccl_queue(): Menghapus data dalam memori.call dbms_ccl.flush_ccl_queue(); call dbms_ccl.show_ccl_queue();Output berikut dikembalikan:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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 fitur
Untuk menghindari perubahan panjang pada kode aplikasi Anda, Anda dapat menggunakan Antrian Pernyataan bersamaan dengan statement outline guna memodifikasi aplikasi Anda secara online dengan cepat dan mudah. Contoh berikut menggunakan kasus uji update_non_index dari SysBench untuk menunjukkan proses ini.
Lingkungan pengujian
Skema tabel
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
Tambahkan statement outline secara online.
CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, ' /*+ ccl_queue_field(id) */ ', "UPDATE sbtest1 SET c='xyz' WHERE id=0");Output berikut dikembalikan:
Query OK, 0 rows affected (0.01 sec)Lihat statement outline.
call dbms_outln.show_outline();Output berikut dikembalikan:
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+ | 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 statement outline telah berlaku.
explain UPDATE sbtest1 SET c='xyz' WHERE id=0;Output berikut dikembalikan:
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 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;Output berikut dikembalikan:
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+ | 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)Periksa status Antrian Pernyataan.
call dbms_ccl.show_ccl_queue();Output berikut dikembalikan:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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)Jalankan 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=u0Verifikasi hasil pengujian.
call dbms_ccl.show_ccl_queue();Output berikut dikembalikan:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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();Output berikut dikembalikan:
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+ | 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)CatatanHasil menunjukkan bahwa statement outline sesuai dengan aturan sebanyak 115.795 kali. Status Antrian Pernyataan menunjukkan 10.996 kecocokan, dengan 63 pernyataan sedang berjalan dan 4 sedang menunggu.
Pengujian kinerja
Lingkungan pengujian
Server aplikasi: Instance ECS
Spesifikasi instans RDS: 8 vCPU, memori 16 GB, dan ESSD
Edisi instans: Edisi Ketersediaan Tinggi, yang menggunakan replikasi asinkron.
Kasus uji
Kasus uji SysBench berikut melakukan pembaruan konkuren pada record dengan id=1:
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) endHasil pengujian
Mengaktifkan fitur Antrian Pernyataan secara signifikan meningkatkan QPS pada beban kerja dengan konkurensi tinggi. Semakin tinggi konkurensinya, semakin besar peningkatannya.
CatatanTanpa fitur Antrian Pernyataan, pengujian stres dengan 4.096 thread menyebabkan alih bencana primary/secondary, menghasilkan QPS sebesar 0.