Cara menggunakan fitur DDL read-ahead untuk memperpendek waktu eksekusi operasi DDL - PolarDB

DDL read-ahead mempercepat operasi DDL pada tabel besar dengan melakukan prefetch halaman data sebelum pemindaian pembuatan indeks mencapainya, sehingga mengurangi waktu tunggu I/O selama operasi.

Saat menjalankan pernyataan ALTER TABLE pada tabel besar, InnoDB membaca setiap halaman data secara berurutan untuk membangun ulang indeks atau menyusun ulang baris. DDL read-ahead melakukan prefetch halaman di depan pemindaian sehingga halaman tersebut sudah tersedia saat dibutuhkan.

Prasyarat

Sebelum memulai, pastikan kluster PolarDB Anda memenuhi salah satu persyaratan versi berikut:

PolarDB for MySQL 8.0, versi revisi 8.0.1.1.28 atau lebih baru
PolarDB for MySQL 5.7, versi revisi 5.7.1.0.22 atau lebih baru
PolarDB for MySQL 5.6, versi revisi 5.6.1.0.34 atau lebih baru

Untuk memeriksa versi revisi kluster Anda, lihat Query the engine version.

Dampak I/O dan penjadwalan

DDL read-ahead menggunakan thread tambahan khusus untuk prefetch halaman, yang meningkatkan konsumsi I/O selama operasi DDL berlangsung. Beban I/O tambahan ini dapat memengaruhi beban kerja SQL konkuren. Jalankan DDL read-ahead selama jam sepi untuk meminimalkan dampak terhadap traffic produksi.

Parameter

Gunakan dua parameter global berikut untuk mengaktifkan DDL read-ahead dan menyesuaikan volume prefetch-nya.

`loose_innodb_polar_ddl_build_index_readahead`

Parameter	Level	Default	Valid values	Description
`loose_innodb_polar_ddl_build_index_readahead`	Global	OFF	ON, OFF	Mengaktifkan atau menonaktifkan DDL read-ahead.

`loose_innodb_polar_ddl_build_index_readahead_page_num`

Parameter	Level	Default	Valid values	Description
`loose_innodb_polar_ddl_build_index_readahead_page_num`	Global	64	32–256	Jumlah halaman yang diprefetch per permintaan read-ahead. Setiap halaman berukuran 16 KB. Nilai yang lebih tinggi akan memprefetch lebih banyak data per permintaan, yang menguntungkan pemindaian sekuensial besar tetapi mengonsumsi lebih banyak bandwidth I/O.

Mulailah dengan nilai default 64 halaman (1 MB per prefetch). Tingkatkan hingga 256 halaman (4 MB per prefetch) untuk tabel yang sangat besar di mana pemindaian DDL merupakan konsumen I/O dominan dan kluster memiliki ruang kapasitas I/O yang tersedia.

Tes kinerja

Benchmark berikut membandingkan waktu eksekusi DDL dengan dan tanpa read-ahead diaktifkan.

Lingkungan pengujian

PolarDB for MySQL 8.0, 8 core CPU, memori 32 GB
Kapasitas penyimpanan: 50 TB

Skema

CREATE TABLE `table_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) DEFAULT NULL,
  `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  `update_time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Data uji

Masukkan 100.000.000 baris menggunakan prosedur tersimpan berikut:

delimiter ||
CREATE PROCEDURE populate_0(IN NUM INT)
BEGIN
  DECLARE sid INT;
  DECLARE suffix_name INT;
  DECLARE i INT;
  SET sid=1000;
  SET suffix_name=10;
  SET i=1;
  START TRANSACTION;
  WHILE i <= NUM
  DO
    INSERT INTO table_1(seller_id,seller_name,gmt_create,update_time) VALUES(sid,CONCAT('sellername',suffix_name),NOW(),NOW());
    SET suffix_name=suffix_name+1;
    SET sid=sid+1;
    SET i=i+1;
  END WHILE;
  COMMIT;
END ||
delimiter ;
CALL populate_0(100000000);

Mulai ulang proses PolarDB sebelum menjalankan setiap pengujian untuk membersihkan cache kolam buffer. Hal ini memastikan hasil mencerminkan kinerja I/O disk, bukan data yang telah di-cache.

Hasil

Semua pengujian menggunakan loose_innodb_polar_ddl_build_index_readahead_page_num=256 saat read-ahead diaktifkan.

ALTER TABLE table_1 ADD INDEX name_index (seller_name):

DDL read-ahead	Waktu (detik)
Nonaktif	485
Aktif	412

ALTER TABLE table_1 ADD COLUMN c1 varchar(100) after id:

DDL read-ahead	Waktu (detik)
Nonaktif	264
Aktif	159

Hubungi kami

Jika Anda memiliki pertanyaan mengenai operasi DDL, silakan menghubungi dukungan teknis.

PolarDB:DDL read-ahead