Cara menggunakan fitur pemeliharaan partisi online PolarDB - PolarDB

Tabel time-series—seperti log, pesanan, dan catatan historis—bertambah di satu ujung dan kadaluarsa di ujung lainnya. Tabel-tabel ini memerlukan operasi roll-in dan roll-out partisi secara berkala. Di MySQL standar, setiap operasi DDL partisi akan memblokir seluruh trafik DML hingga operasi tersebut selesai atau dibatalkan, sehingga Anda terpaksa menjadwalkan maintenance pada jam sepi dan menerima periode tanpa throughput sama sekali.

Pemeliharaan partisi online mengatasi masalah ini dengan mengganti kunci metadata (MDL) tingkat tabel menjadi MDL tingkat partisi. Saat operasi DDL menargetkan partisi tertentu, PolarDB for MySQL hanya mengambil MDL tingkat partisi pada partisi tersebut. Partisi lain tetap dapat diakses oleh operasi bahasa manipulasi data (DML) secara konkuren. Pendekatan ini menghilangkan jendela blackout DML dan memungkinkan Anda menjalankan maintenance partisi kapan saja.

Gambar berikut menunjukkan bagaimana fitur ini mengurangi kontensi kunci selama operasi bahasa definisi data (DDL) dan DML yang berjalan bersamaan.

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

Kluster PolarDB for MySQL 8.0 dengan versi revisi 8.0.2.2.0 atau lebih baru. Untuk memeriksa versi Anda, lihat Kueri versi engine.
Parameter partition_level_mdl_enabled diatur ke ON. Untuk instruksinya, lihat Tentukan parameter kluster dan node.
Parameter transaction_isolation diatur ke READ-COMMITTED pada tingkat global. Untuk instruksinya, lihat Tentukan parameter kluster dan node.

Aktifkan MDL tingkat partisi

Atur partition_level_mdl_enabled ke ON untuk mengaktifkan MDL tingkat partisi. Parameter ini mengontrol granularitas kunci: alih-alih mengunci seluruh tabel selama operasi DDL, PolarDB for MySQL hanya mengunci partisi yang terpengaruh.

Parameter	Tingkat	Deskripsi
`partition_level_mdl_enabled`	Global	Mengaktifkan MDL tingkat partisi. Nilai yang valid: `ON` (diaktifkan), `OFF` (dinonaktifkan).

Catatan

Mulai ulang kluster setelah mengubah parameter ini agar perubahan berlaku.

Operasi yang didukung

Pemeliharaan partisi online berlaku untuk operasi DDL berikut. Operasi ADD PARTITION didukung untuk tabel partisi RANGE dan LIST. Dukungan untuk operasi DDL tambahan dan jenis partisi lainnya akan tersedia dalam rilis mendatang.

Operasi	Deskripsi
`ADD PARTITION`	Menambahkan partisi baru (hanya untuk partisi RANGE dan LIST)
`DROP PARTITION`	Menghapus partisi yang ada
`EXCHANGE PARTITION`	Menukar data antara partisi dan tabel non-partisi
`REBUILD PARTITION`	Membangun ulang partisi di tempatnya
`REORGANIZE PARTITION`	Memisahkan atau menggabungkan partisi yang ada

Batasan

Jika tingkat isolasi diatur ke REPEATABLE-READ atau lebih tinggi dan operasi DDL dijalankan secara konkuren, error berikut mungkin muncul:

ERROR HY000: Table definition has changed, please retry transaction

Ini merupakan perilaku yang diharapkan. Error terjadi karena pernyataan DML mengakses partisi yang baru saja dibuat oleh operasi DDL konkuren. Ulangi transaksi untuk mengatasinya.

Catatan

Tingkat isolasi juga dapat diatur pada tingkat sesi, bukan hanya secara global.

Contoh penggunaan

Contoh berikut menggunakan dua client konkuren untuk menunjukkan bagaimana pemeliharaan partisi online memungkinkan operasi DML dan DDL berjalan berdampingan.

-- Client 1: Lihat struktur tabel saat ini
SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

-- Client 1: Mulai transaksi dan kueri data
BEGIN;
Query OK, 0 rows affected (0.01 sec)

SELECT * FROM tr WHERE purchased >= '2010-01-01';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    5 | exercise bike  | 2014-05-09 |
|    7 | espresso maker | 2011-11-22 |
+------+----------------+------------+
2 rows in set (0.01 sec)

-- Client 2: Saat transaksi client 1 masih terbuka, tambahkan partisi baru dan masukkan data
ALTER TABLE tr ADD PARTITION (PARTITION p6 VALUES LESS THAN (2020));
INSERT INTO tr VALUES (11, 'hope', '2017-11-04'), (12, 'carmen', '2018-06-08');

-- Client 1: Kueri lagi dalam transaksi yang sama — data partisi baru terlihat
SELECT * FROM tr WHERE purchased >= '2010-01-01';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    5 | exercise bike  | 2014-05-09 |
|    7 | espresso maker | 2011-11-22 |
|   11 | hope           | 2017-11-04 |
|   12 | carmen         | 2018-06-08 |
+------+----------------+------------+
4 rows in set (0.00 sec)

-- Client 2: Hapus partisi lama saat transaksi client 1 masih terbuka
ALTER TABLE tr DROP PARTITION p0;

-- Client 1: Konfirmasi struktur tabel mencerminkan kedua perubahan — p6 ditambahkan, p0 dihapus
SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDB) */
1 row in set (0.00 sec)

-- Client 1: Commit transaksi
COMMIT;

Perbandingan performa

Dua skenario berikut membandingkan throughput DML dengan dan tanpa pemeliharaan partisi online.

Skenario 1: DDL diblokir oleh transaksi jangka panjang

Di MySQL standar, ketika operasi DDL tidak dapat dilanjutkan karena transaksi terbuka memegang kunci, operasi DDL tersebut akan memblokir semua operasi DML berikutnya—menyebabkan throughput turun ke nol hingga DDL dibatalkan atau transaksi di-commit.

Dengan pemeliharaan partisi online diaktifkan:

Throughput normal identik dengan saat fitur dinonaktifkan—mengaktifkannya tidak menimbulkan overhead.
Transaksi jangka panjang tidak lagi memblokir operasi DDL partisi. Trafik DML tetap stabil sepanjang waktu.

Blocked DDL operations by long-running transactions

Skenario 2: Operasi DDL yang memakan waktu

Ketika operasi DDL lambat (misalnya, membangun ulang partisi besar), operasi tersebut dapat menyebabkan jitter parah pada throughput DML meskipun tidak ada transaksi yang memblokir.

Dengan pemeliharaan partisi online diaktifkan, operasi DDL yang memakan waktu memiliki dampak minimal terhadap throughput DML.

Lihat status MDL

Saat operasi DDL sedang berjalan, kueri performance_schema.metadata_locks untuk melihat status kunci tingkat partisi.

Contoh berikut menunjukkan tabel kunci saat client 1 memegang kunci baca pada partisi p5, dan client 2 kemudian mencoba menghapus partisi tersebut.

-- Client 1: Mulai transaksi dan kueri partisi p5
BEGIN;
SELECT * FROM tr WHERE purchased >= '2010-01-01';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|    5 | exercise bike  | 2014-05-09 |
|    7 | espresso maker | 2011-11-22 |
+------+----------------+------------+
2 rows in set (0.01 sec)

-- Client 1: Periksa tabel kunci
SELECT * FROM performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | test               | tr             | NULL        |       140734887898944 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6759 |              67 |             17 |
| PARTITION   | test               | tr             | p5          |       140734887896704 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6502 |              67 |             17 |
| TABLE       | performance_schema | metadata_locks | NULL        |       140734879511488 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6759 |              68 |              4 |
| SCHEMA      | performance_schema | NULL           | NULL        |       140734879511648 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | dd_schema.cc:108  |              68 |              4 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
4 rows in set (0.02 sec)

Output menunjukkan dua kunci yang dipegang oleh thread 67 (client 1): kunci SHARED_READ pada tingkat tabel untuk tr, dan kunci SHARED_READ pada tingkat partisi untuk p5 (setelah Pemangkasan partisi). Kolom OWNER_THREAD_ID mengidentifikasi thread mana yang memegang kunci tersebut.

-- Client 2: Mencoba menghapus partisi p5 — ini masuk ke status menunggu
--   karena client 1 masih memegang kunci SHARED_READ pada p5
ALTER TABLE tr DROP PARTITION p5;

-- Konfirmasi DDL sedang menunggu kunci metadata tingkat partisi
SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
| Id | User            | Host      | db   | Command | Time | State                               | Info                             |
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 1550 | Waiting on empty queue              | NULL                             |
|  8 | root            | localhost | test | Sleep   |  426 |                                     | NULL                             |
|  9 | root            | localhost | NULL | Query   |    0 | starting                            | SHOW PROCESSLIST                 |
| 10 | root            | localhost | test | Query   |   10 | Waiting for partition metadata lock | ALTER TABLE tr DROP PARTITION p5 |
+----+-----------------+-----------+------+---------+------+-------------------------------------+----------------------------------+
4 rows in set (0.00 sec)

Kolom State menunjukkan Waiting for partition metadata lock saat DDL sedang dalam antrian di belakang kunci tingkat partisi. Untuk membuka blokir DDL tersebut, commit atau rollback transaksi pada sesi yang memblokir. Identifikasi sesi yang memblokir menggunakan OWNER_THREAD_ID dari kueri metadata_locks, lalu commit atau rollback transaksi sesi tersebut.

-- Operasi DROP PARTITION pada client 2 berjalan otomatis setelah client 1 melakukan commit

Lacak operasi pemeliharaan partisi online

Gunakan variabel status Online_altered_partition untuk melihat berapa banyak operasi pemeliharaan partisi online yang telah dijalankan.

SHOW STATUS LIKE 'Online_altered_partition';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Online_altered_partition | 2565  |
+--------------------------+-------+
1 row in set (0.00 sec)

Video operasi

Demo — Penguncian metadata (MDL) tingkat partisi PolarDB for MySQL