Gunakan OPTIMIZE TABLE untuk mereklaim tablespace MySQL - ApsaraDB RDS

Saat bekerja dengan tabel MySQL berukuran besar, menjalankan pernyataan DELETE tidak serta-merta melepaskan ruang disk. Pernyataan tersebut hanya menandai catatan database atau halaman data sebagai dapat digunakan kembali. Untuk mereklaim tablespace dan mengurangi penggunaan disk, Anda dapat menjalankan perintah OPTIMIZE TABLE.

Prasyarat

Pernyataan OPTIMIZE TABLE hanya didukung oleh mesin penyimpanan InnoDB dan MyISAM.
Ruang disk yang tersedia pada instans harus minimal sebesar ukuran tabel yang ingin Anda optimalkan.

Catatan
Jika instans Anda memiliki ruang disk yang tidak mencukupi, Anda harus terlebih dahulu memperluas ruang disk. Setelah itu, Anda dapat memperkecil kembali ukuran disk, dan sistem akan mengembalikan selisih biaya.

Catatan penggunaan

Hapus sejumlah besar data terlebih dahulu: Menjalankan OPTIMIZE TABLE tidak efektif kecuali Anda terlebih dahulu menggunakan DELETE untuk menghapus sejumlah besar data.
Peningkatan sementara dalam penggunaan ruang disk: Saat menjalankan OPTIMIZE TABLE, MySQL membuat tabel sementara untuk menyimpan data yang telah diatur ulang. Hal ini menyebabkan peningkatan sementara dalam penggunaan ruang disk. Setelah operasi selesai, tabel sementara tersebut dihapus, dan penggunaan ruang disk kembali normal.
Statistik tabel dan indeks mungkin tidak berubah setelah ruang direklaim: Meskipun ruang disk telah direklaim, statistik tabel MySQL mungkin tidak diperbarui secara langsung. Untuk informasi lebih lanjut, lihat Mengapa penggunaan ruang disk instans ApsaraDB RDS for MySQL saya tetap tidak berubah setelah menjalankan OPTIMIZE TABLE?
Dampak performa dan risiko selama jam sibuk: Pada ApsaraDB RDS for MySQL 5.7 dan 8.0, OPTIMIZE TABLE menggunakan online DDL, yang mendukung operasi DML secara konkuren. Namun, menjalankan operasi ini pada tabel besar dapat menyebabkan lonjakan tiba-tiba dalam konsumsi I/O dan resource buffer, sehingga berisiko menyebabkan penguncian tabel atau konflik sumber daya. Selama jam sibuk, hal ini bahkan dapat menyebabkan instans tidak tersedia atau gangguan pada pemantauan. Oleh karena itu, kami menyarankan Anda menjalankan operasi ini selama jam sepi agar tidak memengaruhi bisnis Anda.
Menghentikan secara manual operasi OPTIMIZE TABLE: Menekan Ctrl+C di klien database, seperti tool command-line MySQL atau jendela SQL DMS, hanya memutus sesi klien saat ini. Ini tidak menghentikan operasi OPTIMIZE TABLE di backend. Untuk menghentikan operasi tersebut, Anda harus membuat koneksi database lain, menjalankan SHOW PROCESSLIST; untuk menemukan ID thread dari operasi OPTIMIZE TABLE, lalu menjalankan KILL <thread ID>; untuk menghentikan thread tersebut.

Gunakan command line

Hubungkan ke instans ApsaraDB RDS for MySQL Anda menggunakan klien database.
Gunakan pernyataan DELETE untuk membersihkan data yang tidak diperlukan sesuai kebutuhan bisnis Anda.
Jalankan perintah OPTIMIZE TABLE untuk mereklaim tablespace.
```
OPTIMIZE TABLE <$Database1>.<Table1>,<$Database2>.<Table2>;
```
Catatan
- <$Database1> dan <$Database2> merepresentasikan nama database, sedangkan <Table1> dan <Table2> merepresentasikan nama tabel.
- Saat menjalankan OPTIMIZE TABLE pada tabel InnoDB, perintah tersebut mengembalikan pesan berikut. Ini merupakan perilaku yang diharapkan dan dapat diabaikan. Operasi berhasil jika hasil akhirnya adalah "ok". Untuk informasi lebih lanjut, lihat dokumentasi resmi MySQL untuk Pernyataan OPTIMIZE TABLE.
  Table does not support optimize, doing recreate + analyze instead

Gunakan DMS

Masuk ke instans ApsaraDB RDS for MySQL menggunakan DMS.
Di panel navigasi sebelah kiri, pilih ID instans target, klik ganda database, klik kanan tabel target, lalu pilih Batch operation table.
Pilih tabel yang ingin Anda optimalkan, lalu pilih Table Maintenance > Optimize Table.
Verifikasi informasi dalam kotak dialog dan klik OK.

Dokumen terkait

Reclaim fragmented table space

FAQ

Mengapa ruang disk tidak berubah setelah `OPTIMIZE TABLE`?

Deskripsi masalah

Setelah menghapus sejumlah besar data dan menjalankan OPTIMIZE TABLE sesuai petunjuk dalam dokumentasi resmi ApsaraDB RDS for MySQL, pengguna mungkin melakukan kueri terhadap bidang DATA_FREE di information_schema.tables. Jika nilainya belum diperbarui, mereka mungkin salah mengira bahwa operasi tersebut gagal mereklaim ruang disk.

Penyebab

Ruang tersebut sebenarnya telah direklaim, tetapi statistik tabel tidak diperbarui secara tepat waktu. Ini merupakan isu yang diketahui pada ApsaraDB RDS for MySQL 5.6, 5.7, dan 8.0 (dengan versi mesin minor sebelum 20250531). Pada versi-versi tersebut, menjalankan OPTIMIZE TABLE tidak secara otomatis memperbarui statistik tabel dan indeks. Akibatnya, nilai DATA_FREE di information_schema.tables tetap menampilkan data lama dan tidak mencerminkan penggunaan ruang aktual. Untuk detail lebih lanjut, lihat Bug #117426: optimize table does not update table and index stats.

Solusi

Solusi yang direkomendasikan: Upgrade versi mesin minor ke 20250531 atau lebih baru (untuk MySQL 8.0) untuk memperbaiki isu di mana OPTIMIZE TABLE tidak memperbarui status tabel.

Versi mesin minor 20250531 untuk ApsaraDB RDS for MySQL 8.0 memperbaiki isu ini. Setelah upgrade, OPTIMIZE TABLE secara otomatis memperbarui statistik, dan bidang DATA_FREE mencerminkan penggunaan ruang aktual dengan benar.
- Untuk MySQL 8.0: Kami menyarankan Anda meng-upgrade versi mesin minor ke 20250531 atau lebih baru untuk menyelesaikan isu ini secara permanen.
- Untuk MySQL 5.7 dan 5.6: Kami menyarankan Anda merencanakan terlebih dahulu untuk meng-upgrade versi utama ke MySQL 8.0, lalu meng-upgrade versi mesin minor ke 20250531 atau lebih baru.
Solusi sementara: Perbarui statistik secara manual (untuk lingkungan yang belum dapat di-upgrade segera)
Jika Anda belum dapat segera meng-upgrade versi database, Anda dapat menjalankan perintah ALTER TABLE table_name ENGINE=InnoDB; pada tabel tempat Anda sebelumnya menjalankan OPTIMIZE TABLE. Ini memaksa pembangunan ulang tabel dan memperbarui statistik. Setelah menjalankan perintah ini, bidang DATA_FREE di information_schema.tables akan menampilkan ruang yang telah direklaim secara akurat.

Apa yang harus dilakukan jika ruang tidak direklaim setelah `DELETE`?

Pada ApsaraDB RDS for MySQL, ketika Anda menggunakan pernyataan DELETE untuk menghapus data, perintah tersebut hanya menandai lokasi catatan atau halaman data sebagai dapat digunakan kembali. Ukuran file disk tidak berubah, artinya tablespace tidak langsung direklaim. Perilaku ini menyebabkan fragmentasi tablespace dan mengonsumsi ruang penyimpanan instans.

Anda dapat menggunakan perintah DDL native atau fitur perubahan skema tanpa lock dari DMS. Sebelum menggunakan salah satu solusi tersebut, pastikan instans Anda memiliki ruang disk yang cukup untuk mencegah penguncian instans akibat kehabisan penyimpanan.

Atur ulang ruang yang terfragmentasi menggunakan perintah: Jalankan operasi DDL seperti OPTIMIZE TABLE atau ALTER TABLE <table_name> ENGINE=InnoDB; untuk mengatur ulang struktur data dan indeks tabel, sehingga mereklaim ruang yang terfragmentasi.

Penting
Jalankan perintah DDL native selama jam sepi untuk menghindari kunci metadata. Untuk informasi lebih lanjut, lihat Catatan penggunaan.
Gunakan fitur perubahan skema tanpa lock dari DMS: Untuk menghindari kunci metadata, Anda dapat menggunakan fitur perubahan skema tanpa lock DMS untuk mereklaim ruang tabel yang terfragmentasi.

Apa yang harus dilakukan jika ruang tidak direklaim setelah `TRUNCATE` atau `DROP`?

Pada ApsaraDB RDS for MySQL, jika Anda menemukan bahwa ruang disk tidak direklaim setelah operasi TRUNCATE atau DROP, ikuti langkah-langkah berikut:

Verifikasi logika reklamasi ruang

Setelah menjalankan TRUNCATE atau DROP, periksa apakah ruang telah direklaim dengan memantau penggunaan ruang disk instans. Biasanya, penurunan penggunaan disk harus sesuai dengan ukuran tabel yang dihapus relatif terhadap total ruang instans.
Hindari mengandalkan informasi yang kedaluwarsa

Jika Anda memeriksa ukuran tabel menggunakan information_schema.tables atau konsol ApsaraDB RDS (Autonomy Services > Diagnose > Storage Analysis), informasi tersebut mungkin sudah kedaluwarsa akibat keterlambatan pembaruan data. Oleh karena itu, kami menyarankan Anda mengandalkan metrik penggunaan disk sebagai indikator yang paling andal.
Pahami dampak penghapusan asinkron

Jika instans Anda memiliki fitur penghapusan asinkron yang diaktifkan, seperti Purge Large File Asynchronously dari Alibaba Cloud, ruang tersebut tidak dilepaskan secara langsung. Melainkan, proses latar belakang secara bertahap mereklaimnya. Dalam kasus ini, Anda harus menunggu hingga proses asinkron selesai sebelum ruang disk sepenuhnya direklaim.