MySQL - Mencegah Pembengkakan Ibdata

Submitted by w41lf0x on Thu, 10/10/2013 - 03:04

Ada yang pakai #InnoDB untuk databasenya? Pernah merasa kalau berkas ibdata* membengkak seiring dengan pertumbuhan data? Meskipun kemudian database dihapus, berkas ibdata* masih berukuran raksasa? Hal ini sering terjadi utamanya jika kita memiliki banyak #database yang menggunakan InnoDB, apalagi jika data yang tersimpan sudah mencapai jutaan atau lebih. Kondisi ini tidak akan menjadi masalah apabila satu atau lebih database dengan InnoDB kita hapus dan berkas ibdata* ikut menyesuaikan diri alias menyusut. Tetapi kenyataannya tidaklah demikian. Ibdata tidak didesain untuk menyusut meskipun seluruh database dengan format InnoDB kita hapus dari daftar. Artinya kita tetap menyimpan 'sampah' di dalam direktori database kita meskipun berkas databasenya sudah tidak ada. Menyebalkan bukan?

Apa sih #ibdata itu? Menurut dokumentasi dari #MySQL[1][3], ibdata adalah sekumpulan berkas biasanya bernama ibdata1, ibdata2, dan seterusnya yang menyusun ruangan tabel sistem InnoDB. Berkas-berkas ini berisi metadata dari tabel-tabel InnoDB, kamus data, ruangan untuk log UNDO, buffer perubahan, dan buffer doublewrite. Berkas ini juga dapat memuat beberapa atau seluruh data dalam tabel (tergantung apakah opsi file-per-table dalam keadaan aktif pada saat tabel dibuat atau tidak). Jika opsi innodb_file_per_table diaktifkan, maka data dan indeks dari tabel yang akan dibuat (tabel baru) akan disimpan ke dalam berkas .ibd yang terpisah dan bukan disimpan di dalam ruangan tabel sistem.

Nah sesuai dengan dokumentasi tersebut, sampeyan bisa pakai opsi innodb_file_per_table di my.cnf, lalu restart database sampeyan. Salah satu keuntungan menggunakan opsi ini adalah, tiap kali sampeyan menghapus sebuah atau beberapa database, anda bisa memperoleh kembali ruang harddisk yang tadinya dipakai oleh database.2

Jika database berformat InnoDB sampeyan sudah kadung tidak menggunakan opsi innodb_file_per_table, sampeyan tetap bisa mengubah strukturnya dengan cara seperti ini4:

1. Dump seluruh database, misalnya dengan:

shell> mysqldump -u root -p --opt -A > semua_db.sql

2. Login ke mysql, lalu drop seluruh database kecuali milik mysql

mysql> drop database DATABASEKU;

3. Hentikan servis mysql

4. Edit file my.cnf, masukkan opsi innodb_file_per_table

5. Hapus berkas ibdata* dan ib_log*

6. Jalankan servis mysql. Sampeyan bisa lihat di log mysql bahwa mysql membuat ulang berkas ibdata* dan ib_log* sesuai kebutuhan

7. Restore kembali database sampeyan

shell> mysql -u root -p < semua_db.sql

Sampeyan bisa cek kembali ukuran ibdata* setelah restore dan setelah transaksi. Perubahannya akan lebih sedikit daripada tanpa opsi innodb_file_per_table.

Selamat mencoba

Referensi:

  1. https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_ibdata_file
  2. https://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
  3. http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-…
  4. http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-…