Gunakan Statement Queue untuk mengantrikan pernyataan dalam bucket - PolarDB

Ketika banyak pernyataan Data Manipulation Language (DML) menargetkan baris yang sama secara bersamaan, kunci tingkat baris InnoDB membuat eksekusinya berjalan secara serial. Seiring meningkatnya konkurensi, thread mengantri di belakang kunci tersebut dan throughput menurun. Statement queue mengatasi masalah ini dengan mengarahkan pernyataan yang saling bertentangan ke dalam bucket bersama menggunakan algoritma hash, sehingga pernyataan tersebut menunggu dalam antrian alih-alih bersaing memperoleh kunci. Pendekatan ini mengurangi beban dari siklus berulang pemerolehan dan pelepasan kunci.

Dalam pengujian benchmark yang menjalankan 128 pernyataan UPDATE secara konkuren pada satu baris, PolarDB dengan statement queue mencapai throughput sekitar empat kali lipat dibandingkan MySQL standar.

Kapan menggunakan statement queue

Statement queue paling efektif ketika volume tinggi pernyataan DML menargetkan baris yang sama—misalnya, pencacah leaderboard, pembaruan inventaris, atau sistem reservasi tiket di mana banyak operasi tulis konkuren mengenai primary key yang sama.

Pendekatan ini kurang efektif untuk beban kerja yang pernyataannya menargetkan baris berbeda, karena kontensi kunci yang terjadi sangat minim sehingga tidak banyak yang dapat dikurangi.

Jika Anda telah mengaktifkan optimasi hot row dengan mengatur variabel hotspot ke ON, statement queue akan dilewati secara otomatis. Gunakan salah satu saja, bukan keduanya.

Prasyarat

Sebelum memulai, pastikan Anda memiliki:

Kluster PolarDB for MySQL yang menjalankan salah satu versi mesin minor berikut:
- PolarDB for MySQL 8.0, versi mesin minor 8.0.1.1.10 atau yang lebih baru
- PolarDB for MySQL 5.7, versi mesin minor 5.7.1.0.6 atau yang lebih baru
- PolarDB for MySQL 5.6, versi mesin minor 20200601 atau yang lebih baru

Sintaksis

PolarDB for MySQL 5.6

Letakkan kata kunci POLARDB_STATEMENT_CONCURRENT_QUEUE tepat setelah kata kunci DML (SELECT, UPDATE, INSERT, atau DELETE). Kata kunci ini berlaku untuk keempat jenis pernyataan DML tersebut.

Queue berdasarkan nilai

Tetapkan pernyataan ke bucket berdasarkan nilai integer atau string eksplisit. Pernyataan yang memiliki nilai yang sama akan masuk ke bucket yang sama.

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;

Antrikan berdasarkan bidang klausa WHERE

Tetapkan pernyataan ke bucket berdasarkan nilai waktu proses suatu kolom dalam klausa WHERE. Pernyataan dengan nilai bidang yang sama akan masuk ke bucket yang sama.

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';

Klausa WHERE harus menggunakan operasi biner hanya pada kolom asli. Sisi kanan setiap operasi biner harus berupa angka literal atau string. Fungsi dan perhitungan pada kolom asli tidak didukung.

PolarDB for MySQL 5.7 dan 8.0

Gunakan sintaksis petunjuk /*+ ... */ yang ditempatkan tepat setelah kata kunci DML (SELECT, UPDATE, INSERT, atau DELETE). Petunjuk ini berlaku untuk keempat jenis pernyataan DML tersebut.

Value-based queue

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';

Queue berdasarkan bidang klausa WHERE

Sintaksis:

/*+ CCL_QUEUE_FIELD(STRING) */

Contoh:

UPDATE /*+ ccl_queue_field("id") */ t SET c=c+1 WHERE id = 1 AND name = 'xpchild';

Klausa WHERE harus menggunakan operasi biner hanya pada kolom asli. Sisi kanan setiap operasi biner harus berupa angka literal atau string. Fungsi dan perhitungan pada kolom asli tidak didukung.

Konfigurasikan ukuran antrian

Dua variabel mengontrol kapasitas antrian. Atur berdasarkan kebutuhan konkurensi Anda.

Variabel	Deskripsi	Nilai valid	Bawaan
`ccl_queue_bucket_count`	Jumlah bucket	1–64	4
`ccl_queue_bucket_size`	Maksimum pernyataan konkuren per bucket	1–4096	64

Monitor status antrian

Gunakan prosedur tersimpan berikut untuk memeriksa dan mengatur ulang status antrian.

Periksa status saat ini

CALL dbms_ccl.show_ccl_queue();

Contoh output:

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

Bidang output:

Bidang	Deskripsi
`CONCURRENCY_COUNT`	Maksimum pernyataan konkuren yang diizinkan dalam bucket ini
`MATCHED`	Total pernyataan yang sesuai dengan aturan bucket ini
`RUNNING`	Pernyataan yang sedang dieksekusi dalam bucket ini
`WAITTING`	Pernyataan yang sedang menunggu dalam antrian

Atur ulang data antrian

dbms_ccl.flush_ccl_queue() menghapus statistik antrian dalam memori dan mengembalikan status saat ini.

CALL dbms_ccl.flush_ccl_queue();
CALL dbms_ccl.show_ccl_queue();

Contoh output setelah flush:

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

Terapkan petunjuk antrian tanpa mengubah kode aplikasi

Di PolarDB for MySQL 5.7 dan 8.0, Anda dapat menggabungkan statement queue dengan Statement outline untuk menyuntikkan petunjuk antrian ke dalam pernyataan SQL yang sudah ada di tingkat server—tanpa perlu mengubah kode aplikasi.

Integrasi ini tidak tersedia untuk PolarDB for MySQL 5.6, yang menggunakan sintaksis berbeda.

Contoh berikut menggunakan Sysbench dengan skrip update_non_index.lua untuk mendemonstrasikan alur kerja ini.

Skema pengujian:

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 target:

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

Prosedur:

Buat statement outline yang menyuntikkan petunjuk antrian secara online.

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

Konfirmasi bahwa outline telah dibuat.

CALL dbms_outln.show_outline();

Output yang diharapkan:

+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| 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` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+

Verifikasi bahwa petunjuk aktif dengan menjalankan EXPLAIN pada pernyataan target.

EXPLAIN UPDATE sbtest1 SET c='xpchild' WHERE id=0;
SHOW WARNINGS;

Output SHOW WARNINGS seharusnya menampilkan petunjuk yang disuntikkan:

+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ CCL_QUEUE_FIELD('id') */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xpchild' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+

Periksa bahwa antrian tidak menunjukkan aktivitas sebelum pengujian dimulai.
```
CALL dbms_ccl.show_ccl_queue();
```

Jalankan pengujian Sysbench.

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

Periksa status antrian selama pengujian untuk memverifikasi bahwa pernyataan diarahkan dengan benar.

CALL dbms_ccl.show_ccl_queue();

Output yang diharapkan selama pengujian:

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

Lakukan cross-check terhadap jumlah hit outline:

CALL dbms_outln.show_outline();

Dalam pengujian ini, 115.795 pernyataan sesuai dengan aturan outline, 10.996 di antaranya masuk ke antrian, 63 dieksekusi secara konkuren, dan 4 sedang menunggu.

Langkah berikutnya

Statement outline — suntikkan petunjuk pengoptimal tanpa mengubah kode aplikasi