DELETE selesai, jumlah row turun drastis. Tapi waktu dicek pakai df -h, sisa disk tidak berubah, dan file ibdata1 di direktori data MySQL ukurannya persis sama seperti sebelumnya. Asumsi bahwa menghapus data otomatis mengembalikan ruang ke disk itu wajar, tapi di InnoDB hampir selalu salah.
Pertanyaan ini sering muncul di tiket support, biasanya dengan nada curiga: datanya sudah dihapus, kenapa kuota disk masih penuh? Jawabannya ada di cara InnoDB mengelola file tablespace-nya.
Apa yang sebenarnya terjadi saat DELETE di InnoDB
DELETE, row tidak langsung dibuang dari disk. Row itu ditandai terhapus dulu, karena transaksi lain mungkin masih perlu melihat versi lama data tersebut (bagian dari MVCC, mekanisme yang membuat pembaca tidak memblokir penulis). Setelah tidak ada transaksi yang membutuhkannya, purge thread di latar belakang membersihkan row itu dan page-nya menjadi ruang kosong.
Ruang kosong itu tetap berada di dalam file tablespace. InnoDB menyimpannya untuk dipakai ulang oleh INSERT berikutnya, bukan mengembalikannya ke sistem operasi. Dari sudut pandang OS, file-nya tetap sebesar sebelumnya. Dari sudut pandang MySQL, di dalam file itu sekarang ada banyak ruang bebas.
Perilaku ini disengaja. Memperkecil file secara fisik setiap ada penghapusan berarti memindahkan page dan menulis ulang data, dan itu mahal. Untuk tabel yang terus diisi dan dihapus, misalnya tabel session atau queue, ruang bebas yang dipakai ulang justru lebih efisien.
Artinya, DELETE tetap berguna. Pertumbuhan file berhenti untuk sementara karena data baru mengisi ruang yang tadi dikosongkan. Hanya saja disk di level OS tidak langsung lega.
ibdata1 dan file .ibd punya nasib yang berbeda
innodb_file_per_table jadi penentu. Variabel ini mengatur di mana data tabel disimpan.
Kalau nilainya ON, setiap tabel InnoDB punya file sendiri berekstensi .ibd di folder database-nya. Ruang bebas di dalam file .ibd itu masih bisa diambil kembali dengan me-rebuild tabelnya, karena rebuild membuat file baru yang padat lalu menghapus file lama.
Kalau nilainya OFF, data tabel disimpan di system tablespace, yaitu file ibdata1. File ini dipakai bersama oleh semua tabel tersebut, ditambah struktur internal InnoDB lain. Tergantung versi, isinya bisa mencakup change buffer, doublewrite buffer, undo log, dan data dictionary di versi lama. ibdata1 bisa membesar otomatis, tapi tidak pernah menyusut. Rebuild tabel pun tidak membantu. Ruang yang dibebaskan hanya bisa dipakai ulang oleh InnoDB di dalam file yang sama.
Di MySQL modern, innodb_file_per_table default-nya ON. Masalahnya, banyak server membawa riwayat panjang. Database yang dibuat di versi lama, atau dimigrasikan apa adanya dari server lama, bisa saja tabel-tabelnya masih tinggal di ibdata1. Mengubah variabel ke ON hari ini tidak memindahkan tabel yang sudah ada. Tabel lama tetap di ibdata1 sampai di-rebuild.
Ada satu penyebab lain yang sering terlewat: ibdata1 bisa membengkak walau semua tabel sudah punya file .ibd sendiri. Penyebabnya undo log yang masih disimpan di system tablespace, yang biasa terjadi di setup lama. Transaksi yang berjalan sangat lama, atau koneksi yang membuka transaksi lalu tidak pernah commit, membuat purge tertahan dan undo log terus menumpuk. Indikatornya bisa dilihat di output SHOW ENGINE INNODB STATUS, bagian TRANSACTIONS, pada baris History list length. Kalau angkanya terus naik dan tidak pernah turun, ada transaksi yang menahan purge.
Cek dulu sebelum mengambil tindakan
SQL
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'datadir';
SELECT table_schema, table_name,
ROUND(data_length / 1024 / 1024) AS data_mb,
ROUND(index_length / 1024 / 1024) AS index_mb,
ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.TABLES
WHERE engine = 'InnoDB'
ORDER BY data_free DESC
LIMIT 20;data_free menunjukkan perkiraan ruang bebas di tablespace tempat tabel itu berada. Untuk tabel yang punya file .ibd sendiri, angka itu adalah ruang yang bisa diambil kembali lewat rebuild. Untuk tabel yang masih di ibdata1, angkanya mencerminkan ruang bebas di tablespace bersama, jadi beberapa tabel bisa menampilkan angka yang sama. Itu salah satu tanda tabel tersebut belum punya file sendiri.
Lalu lihat dari sisi file system. Sesuaikan path dengan hasil datadir di atas, karena /var/lib/mysql hanya default yang umum:
BASH
ls -lh /var/lib/mysql/ibdata1
sudo du -sh /var/lib/mysql/*/ | sort -h | tail -n 20ibdata1 atau folder database tertentu. Kadang ternyata keduanya bukan masalah utama, dan yang memakan disk justru binary log. Kasus itu dibahas terpisah di artikel kenapa MySQL crash saat binary log memenuhi disk. Log di luar MySQL juga patut dicek dengan du di level direktori yang lebih tinggi.
OPTIMIZE TABLE mengecilkan file .ibd, bukan ibdata1
.ibd sendiri, cara standarnya adalah me-rebuild tabel tersebut:
SQL
OPTIMIZE TABLE nama_database.nama_tabel;
-- Untuk InnoDB, efeknya setara dengan rebuild berikut:
ALTER TABLE nama_database.nama_tabel ENGINE=InnoDB;.ibd turun dan ruangnya kembali ke OS.
Ada beberapa hal yang perlu diperhatikan sebelum menjalankannya di production.
Pertama, rebuild butuh ruang disk kosong kira-kira sebesar tabel yang sedang di-rebuild, karena file lama dan baru sempat ada bersamaan. Kalau disk sudah benar-benar penuh, rebuild tabel besar bisa gagal di tengah jalan. Lebih aman mulai dari tabel yang lebih kecil dulu, atau kosongkan ruang di tempat lain.
Kedua, rebuild membaca dan menulis seluruh tabel, jadi I/O akan naik. Versi MySQL modern umumnya menjalankannya sebagai online DDL sehingga tulis-baca tetap bisa berjalan, tapi ada pengecualian, misalnya tabel dengan FULLTEXT index, dan perilaku pastinya bergantung versi. Untuk tabel besar, jadwalkan di jam sepi.
Ketiga, kalau tabel itu ternyata masih tinggal di ibdata1 dan innodb_file_per_table sekarang sudah ON, perintah ALTER TABLE ... ENGINE=InnoDB akan memindahkannya ke file .ibd sendiri. Ini berguna untuk ke depannya, tapi ibdata1 tetap tidak mengecil. Ruang yang ditinggalkan tabel itu hanya menjadi ruang bebas di dalam ibdata1.
Satu catatan soal kebiasaan: menjalankan OPTIMIZE TABLE terjadwal untuk semua tabel jarang perlu. Kalau tabelnya rutin diisi lagi, ruang bebasnya akan dipakai ulang dengan sendirinya. Rebuild paling masuk akal setelah penghapusan besar yang tidak akan terisi lagi dalam waktu dekat.
Kalau yang bengkak ibdata1, jalannya dump dan restore
ibdata1. Satu-satunya cara adalah membuat ulang system tablespace dari nol. Itu berarti mengekspor semua data, menginisialisasi ulang instance MySQL, lalu mengimpor kembali dengan innodb_file_per_table=ON supaya kejadiannya tidak terulang.
Peringatan penting: prosedur ini destruktif dan menyentuh seluruh instance, bukan satu database saja. Menghapus ibdata1 tanpa reinisialisasi yang benar akan membuat MySQL tidak bisa start, dan data yang belum ter-dump akan hilang. Di MySQL 8.0, data dictionary disimpan di file terpisah, jadi pendekatan lama "hapus ibdata1 dan ib_logfile lalu restart" tidak lagi berlaku. Yang dibutuhkan adalah inisialisasi ulang seluruh datadir. Detailnya bergantung pada versi MySQL atau MariaDB dan control panel yang dipakai, jadi uji dulu di staging.
Garis besarnya seperti ini:
- Ambil dump logis semua database aplikasi, misalnya dengan
mysqldumpmemakai--single-transaction,--routines,--triggers, dan--events. Simpan juga user dan grant secara terpisah. - Verifikasi dump bisa dibaca dan ukurannya masuk akal. Kalau memungkinkan, coba restore ke server lain dulu.
- Ambil juga backup fisik datadir saat MySQL dalam keadaan berhenti, sebagai jalan pulang kalau ada yang gagal.
- Hentikan aplikasi yang menulis ke database, lalu hentikan MySQL.
- Pindahkan datadir lama ke lokasi lain (jangan langsung dihapus), pastikan
innodb_file_per_table=ONdi konfigurasi, lalu inisialisasi datadir baru sesuai prosedur versi yang dipakai. - Start MySQL, restore user, grant, dan dump database, lalu cek aplikasi.
- Hapus datadir lama hanya setelah semuanya terbukti berjalan normal.
ibdata1 hanya beberapa GB dan disk masih cukup, sering kali keputusan paling masuk akal adalah membiarkannya. Ruang bebas di dalamnya akan dipakai ulang, dan risikonya jauh lebih kecil daripada reinisialisasi instance. Ini trade-off, bukan aturan. Hitung dulu berapa yang benar-benar didapat dibanding risiko dan downtime-nya.
Yang perlu dibawa pulang
OPTIMIZE semua tabel. Cek dulu innodb_file_per_table, lihat data_free per tabel, dan bandingkan dengan ukuran file di disk. Kalau ruang bebasnya ada di file .ibd, rebuild tabel yang relevan saat jam sepi dan pastikan sisa disk cukup untuk salinannya. Kalau yang besar ibdata1, cari tahu dulu apakah penyebabnya tabel lama atau undo log yang tertahan transaksi panjang. Setelah itu putuskan dengan tenang apakah dump dan restore memang sepadan dengan downtime-nya. Untuk server baru, pastikan innodb_file_per_table aktif sejak awal supaya pertanyaan ini tidak muncul lagi.