Pemeliharaan Database

Slow query log memberi kita daftar query yang perlu diperbaiki, namun pekerjaan administrator tidak berhenti pada menulis ulang query. Seiring pemakaian, statistik optimizer menua, ruang bekas DELETE menganga sebagai lubang internal, dan sesuai keberuntungan, tabel bisa tercatat tidak konsisten. MySQL menyediakan empat perintah pemeliharaan standar — ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, dan REPAIR TABLE — plus alat baris perintah mysqlcheck yang menjalankan semuanya secara batch. Chapter penutup section ini mempraktikkan keempat perintah tersebut pada tabel InnoDB dan MyISAM, menjelaskan kapan masing-masing layak dijadwalkan, lalu meluruskan satu pertanyaan yang sering muncul dari dunia PostgreSQL: apakah MySQL butuh vacuum.

Empat Perintah dan Fungsinya #

Sebelum praktik, peta kerjanya perlu jelas. Empat perintah pemeliharaan memiliki tujuan yang tidak saling menggantikan:

Kita siapkan dua tabel latihan dengan dua storage engine yang berbeda — InnoDB sebagai engine utama, dan MyISAM sebagai engine lawas yang masih dipasang MySQL 8.4. Perbedaan engine ini sengaja diambil karena REPAIR TABLE berperilaku berbeda pada keduanya:

DROP DATABASE IF EXISTS belajar_admin;
CREATE DATABASE belajar_admin;
USE belajar_admin;
CREATE TABLE pelanggan (id INT PRIMARY KEY, nama VARCHAR(50), kota VARCHAR(30)) ENGINE=InnoDB;
CREATE TABLE kategori (id INT PRIMARY KEY, nama VARCHAR(30)) ENGINE=MyISAM;
INSERT INTO pelanggan VALUES (1, 'Budi', 'Jakarta'), (2, 'Siti', 'Bandung'), (3, 'Ani', 'Surabaya');
INSERT INTO kategori VALUES (1, 'elektronik'), (2, 'buku');

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

ANALYZE TABLE: Menyegarkan Statistik Optimizer #

Optimizer memilih rencana eksekusi berdasarkan statistik — berapa baris kira-kira cocok dengan filter, seberapa unik nilai sebuah index. Statistik itu disimpan sebagai perkiraan dan menua seiring data berubah. ANALYZE TABLE memaksa server mengambil sampel ulang:

ANALYZE TABLE pelanggan, kategori;

Output yang Dihasilkan:

+-------------------------+---------+----------+----------+
| Table                   | Op      | Msg_type | Msg_text |
+-------------------------+---------+----------+----------+
| belajar_admin.pelanggan | analyze | status   | OK       |
| belajar_admin.kategori  | analyze | status   | OK       |
+-------------------------+---------+----------+----------+
2 rows in set (0.00 sec)

Kedua tabel melapor status OK — tugas terberatnya memang hanya pada tabel besar, tempat penyampelan ulang memindai sebagian halaman data. Kolom Msg_type dan Msg_text pada hasil pemeliharaan selalu berpola begini: status OK berarti pekerjaan berhasil tanpa temuan, sedangkan baris note berisi keterangan tambahan yang akan segera kita jumpai pada OPTIMIZE TABLE.

CHECK TABLE: Menguji Konsistensi #

CHECK TABLE memeriksa struktur dan data sebuah tabel tanpa mengubah apa pun — aman dijalankan kapan pun, termasuk saat tabel sedang dipakai:

CHECK TABLE pelanggan, kategori;

Output yang Dihasilkan:

+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| belajar_admin.pelanggan | check | status   | OK       |
| belajar_admin.kategori  | check | status   | OK       |
+-------------------------+-------+----------+----------+
2 rows in set (0.00 sec)

Kedua tabel sehat, dan untuk tabel sekecil ini pemeriksaan selesai seketika. Skala pekerjaannya mengikuti ukuran data, sehingga pada tabel ber-gigabyte CHECK TABLE memindai lama — tersedia opsi QUICK yang hanya menguji struktur tanpa memeriksa tautan antarbaris, cukup untuk pemeriksaan rutin. Waktu paling tepat menjalankannya adalah setelah peristiwa mencurigakan: server mati mendadak, disk penuh saat operasi tulis, atau galat aneh yang muncul dari query yang biasanya aman.

OPTIMIZE TABLE: Merapikan Ruang #

Menghapus baris tidak serta-merta mengembalikan ruang file ke sistem operasi — bekas baris menjadi lubang internal yang akan dipakai ulang oleh INSERT berikutnya. OPTIMIZE TABLE menata ulang tabel fisik untuk membuang lubang tersebut:

OPTIMIZE TABLE pelanggan, kategori;

Output yang Dihasilkan:

+-------------------------+----------+----------+-------------------------------------------------------------------+
| Table                   | Op       | Msg_type | Msg_text                                                          |
+-------------------------+----------+----------+-------------------------------------------------------------------+
| belajar_admin.pelanggan | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| belajar_admin.pelanggan | optimize | status   | OK                                                                |
| belajar_admin.kategori  | optimize | status   | OK                                                                |
+-------------------------+----------+----------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

Hasilnya menampilkan perilaku engine yang berbeda secara jujur. MyISAM merapikan tabelnya secara langsung dan melapor satu baris status OK. InnoDB justru menjawab dengan note: “Table does not support optimize, doing recreate + analyze instead” — engine ini tidak punya mode defragmentasi khusus, sehingga MySQL mencapai hasil yang sama dengan membuat ulang tabel (membuat salinan rapat, menukar, lalu menyegarkan statistiknya). Efek akhirnya setara dan baris status OK yang mengikuti menegaskan penataan ulang berhasil.

REPAIR TABLE: Spesialis MyISAM #

REPAIR TABLE adalah tombol darurat untuk tabel yang benar-benar rusak — dan di sinilah pilihan dua storage engine pada latihan kita terbayar:

REPAIR TABLE kategori, pelanggan;

Output yang Dihasilkan:

+-------------------------+--------+----------+---------------------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                                |
+-------------------------+--------+----------+---------------------------------------------------------+
| belajar_admin.kategori  | repair | status   | OK                                                      |
| belajar_admin.pelanggan | repair | note     | The storage engine for the table doesn't support repair |
+-------------------------+--------+----------+---------------------------------------------------------+
2 rows in set (0.00 sec)

Tabel MyISAM kategori berhasil diperiksa dan dilaporkan status OK — tidak ada yang perlu diperbaiki, namun perintahnya sah. Tabel InnoDB pelanggan menolak dengan note “The storage engine for the table doesn’t support repair”: InnoDB tidak menyediakan mekanisme repair manual karena tidak membutuhkannya. Struktur internal InnoDB dijaga oleh redo log dan crash recovery — saat server menyala kembali setelah mati mendadak, engine memutar ulang transaksi yang belum selesai dan membatalkan yang setengah jalan, sehingga tabel kembali konsisten tanpa campur tangan kita. MyISAM tanpa jaring pengaman seperti itu, sehingga kerusakan file harus diperbaiki secara eksplisit.

mysqlcheck: Pemeliharaan dari Terminal #

Semua perintah di atas bisa dijalankan tanpa membuka sesi interaktif lewat mysqlcheck — alat bawaan MySQL yang menghubungkan perintah pemeliharaan ke daftar tabel secara otomatis:

mysqlcheck -h 127.0.0.1 -P 3306 -u root --analyze belajar_admin
mysqlcheck -h 127.0.0.1 -P 3306 -u root --optimize belajar_admin
mysqlcheck -h 127.0.0.1 -P 3306 -u root --all-databases --auto-repair

Argumen pertama dan kedua sama seperti klien mysql biasa: alamat server dan kredensial. Argumen --analyze atau --optimize memilih jenis pemeliharaan, nama database di akhir membatasi sasarannya, dan kombinasi --all-databases --auto-repair pada baris ketiga memeriksa seluruh server lalu hanya memanggil REPAIR TABLE untuk tabel yang benar-benar bermasalah. Karena berupa satu perintah shell, mysqlcheck pas dipasang di cron — pemeliharaan mingguan pukul dini hari tidak lagi menunggu administrator untuk bangun.

Jadwal Pemeliharaan dan Monitoring #

Pemeliharaan sehat tidak menjalankan semuanya setiap hari. Statistik segarkan ANALYZE TABLE cukup setelah perubahan data besar atau saat rencana eksekusi terasa melenceng; InnoDB sudah menyegarkannya sendiri secara berkala. Penataan ruang OPTIMIZE TABLE layak dijadwalkan bulanan untuk tabel transaksi yang rajin kehilangan baris, dan ukuran lubang internalnya bisa dipantau dari kolom DATA_FREE di information_schema.TABLES — angka yang terus membesar adalah undangan untuk merapikan. CHECK TABLE mengikuti kalender insiden: dijalankan setelah kejadian mencurigakan, bukan rutin. Gabungkan ketiganya dengan monitoring SHOW STATUS dari chapter server status — tren Innodb_buffer_pool_reads yang menanjak dan slow log yang menebal adalah sinyal jadwal pemeliharaan perlu dimajukan.

FLUSH TABLES dan FLUSH PRIVILEGES #

Dua perintah FLUSH menutup daftar peralatan pemeliharaan. FLUSH TABLES meminta server menutup semua tabel yang sedang terbuka — dipakai untuk memaksa file tabel disinkronkan ke disk atau melepas kunci file sebelum menyalinnya secara manual. FLUSH PRIVILEGES meminta server memuat ulang grant tables dari database mysql ke memori:

FLUSH TABLES;
FLUSH PRIVILEGES;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES terutama relevan berhubungan dengan aturan di chapter database sistem: setelah pengubahan hak akses lewat GRANT atau CREATE USER, server memperbarui cache memorinya sendiri, jadi flush tidak diperlukan. Flush menjadi wajib hanya bila grant tables diedit secara manual — atau bila kita menyalin file tabel mysql dari server lain. Keluarga FLUSH ini masih punya satu anggota yang sudah kita pakai: FLUSH STATUS di chapter server status untuk mereset counter statistik.

Apakah MySQL Butuh VACUUM? #

Pembaca yang berasal dari PostgreSQL pasti mengenali kebiasaan VACUUM — membersihkan baris mati peninggalan mekanisme multiversion concurrency control (MVCC). MySQL dengan InnoDB memakai MVCC juga: baris yang diperbarui atau dihapus versi lamanya tetap tersimpan sementara sampai transaksi pembaca selesai. Bedanya, pembersihannya tidak menunggu perintah manual — thread purge internal InnoDB menghapus baris mati di latar belakang secara terus-menerus, sehingga tidak ada padanan VACUUM di MySQL dan tidak ada tabel pg_stat_user_tables yang harus kita awasi untuk vacuum. Persoalan yang tersisa hanyalah ruang file: lubang internal yang dibersihkan purge tetap dimiliki file tabel, dan satu-satunya cara mengembalikannya ke sistem operasi adalah OPTIMIZE TABLE — itulah padanan terdekat kebiasaan VACUUM FULL di dunia PostgreSQL.

Membersihkan Latihan #

Database latihan dengan dua engine berbeda ini kita hapus agar server uji kembali bersih:

DROP DATABASE belajar_admin;

Output yang Dihasilkan:

Query OK, 2 rows affected (0.00 sec)

Referensi #

• Optimizing Tables — MySQL 8.4 Reference Manual
• FLUSH Statement — MySQL 8.4 Reference Manual
• Alternative Storage Engines — MySQL 8.4 Reference Manual
• InnoDB Introduction — MySQL 8.4 Reference Manual

Kita telah menuntuhkan perjalanan administrasi: konfigurasi server, storage engine, koneksi, process list, status, database sistem, log, hingga pemeliharaan rutin. Namun seluruh perawatan ini hanya menjaga server yang hidup — kehilangan disk tetap memusnahkan semuanya sekaligus. Section berikutnya membahas Backup dan Recovery: membuat cadangan dengan mysqldump, memulihkannya, dan memutar ulang binary log untuk mengejar titik waktu sesaat sebelum musibah.