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 menyebabkannya dieksekusi secara serial. Seiring meningkatnya konkurensi, thread mengantri di belakang kunci tersebut, sehingga throughput menurun. Statement queue mengatasi masalah ini dengan mengarahkan pernyataan yang saling bertentangan ke dalam bucket yang sama menggunakan algoritma hash, sehingga pernyataan tersebut menunggu dalam antrian alih-alih bersaing memperebutkan kunci. Pendekatan ini mengurangi beban dari siklus berulang akuisisi 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.

Fitur ini kurang efektif untuk beban kerja di mana pernyataan menargetkan baris yang berbeda, karena hampir tidak ada kontensi kunci yang perlu dikurangi.

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

Prasyarat

Sebelum memulai, pastikan Anda telah 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 lebih baru
- PolarDB for MySQL 5.7, versi mesin minor 5.7.1.0.6 atau lebih baru
- PolarDB for MySQL 5.6, versi mesin minor 20200601 atau lebih baru
Sebelum menggunakan fitur Statement Queue, atur parameter loose_rds_ic_reduce_hint_enable ke ON. Nilai default-nya adalah OFF. Perubahan berlaku langsung tanpa perlu melakukan restart kluster.

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.

Antrian berdasarkan nilai

Tetapkan pernyataan ke suatu 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;

Antrian berdasarkan bidang klausa WHERE

Tetapkan pernyataan ke suatu bucket berdasarkan nilai runtime 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.

Antrian 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('xpchild') */ t SET c=c+1 WHERE name = 'xpchild';

Antrian 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 nilainya sesuai dengan kebutuhan konkurensi Anda.

Variabel	Deskripsi	Nilai valid	Default
`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

Pada PolarDB for MySQL 5.7 dan 8.0, Anda dapat menggabungkan statement queue dengan Statement outline untuk menyisipkan 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 menyisipkan 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 disisipkan:

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

Pada 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 selanjutnya

Statement outline — sisipkan petunjuk pengoptimal tanpa mengubah kode aplikasi