Setelan MySQL dan MariaDB yang Paling Berpengaruh

Kalau kamu hanya sempat mengubah satu setelan MySQL atau MariaDB, ubah innodb_buffer_pool_size, lalu habiskan sisa waktumu untuk memperbaiki query yang lambat.

Mengapa urutan ini penting? Banyak orang memulai optimasi database dengan menyalin file konfigurasi "sakti" dari internet berisi puluhan baris. Hasilnya sering tidak terukur, kadang malah membuat server kehabisan memori. Sebagian besar masalah performa di aplikasi web, terutama WordPress, toko online, dan aplikasi bisnis, berasal dari dua hal: data yang tidak muat di memori dan query yang membaca jauh lebih banyak baris dari yang diperlukan. Bagian di bawah berfokus pada keduanya.

Buffer pool InnoDB, setelan nomor satu

InnoDB, mesin penyimpanan bawaan MySQL dan MariaDB, menyimpan data dan index yang sering dipakai di area memori bernama buffer pool. Kalau data aktif muat di sana, query dilayani dari RAM. Kalau tidak, database harus membaca disk berulang kali, dan server terasa lambat walau CPU santai.

Nilai bawaannya kecil karena dirancang aman untuk mesin apa pun. Pada server yang khusus menjalankan database, dokumentasi MySQL menyebut buffer pool boleh mengambil sampai sekitar 80 persen RAM, dan banyak admin memulai dari angka lebih konservatif seperti setengah sampai tiga perempat, sambil menyisakan ruang untuk sistem operasi dan koneksi. Pada VPS yang juga menjalankan web server dan PHP, porsinya harus jauh lebih kecil, karena aplikasi lain juga butuh memori.

Cara mengecek apakah ukurannya cukup: bandingkan ukuran total data dan index database kamu dengan buffer pool. Kalau database 500 MB, buffer pool beberapa GB tidak memberi manfaat tambahan. Kalau database jauh lebih besar dari buffer pool, pertimbangkan menambah RAM atau memisahkan database ke server sendiri.

Berapa max_connections yang wajar?

max_connections membatasi jumlah koneksi bersamaan. Nilai bawaannya 151, dan untuk banyak aplikasi web itu sudah cukup. Godaan menaikkannya ke ribuan biasanya muncul setelah melihat error "Too many connections".

Masalahnya, setiap koneksi memakai memori sendiri untuk buffer per sesi. Ribuan koneksi aktif bisa menghabiskan RAM jauh sebelum CPU penuh. Error terlalu banyak koneksi lebih sering menandakan query yang lambat (koneksi menumpuk karena tidak kunjung selesai) atau aplikasi yang tidak menutup koneksinya, bukan batas yang terlalu kecil. Perbaiki penyebabnya dulu, lalu naikkan batas secukupnya bila memang perlu. Cek puncak koneksi dengan SHOW GLOBAL STATUS LIKE 'Max_used_connections';.

Slow query log: temukan pelakunya

Slow query log mencatat query yang berjalan lebih lama dari batas tertentu. Aktifkan dengan slow_query_log = 1 dan atur long_query_time, misalnya 1 detik, karena nilai bawaannya 10 detik terlalu longgar untuk aplikasi web. Pada tahap investigasi, log_queries_not_using_indexes juga membantu, walau bisa membuat log cepat membesar.

Setelah beberapa hari, rangkum log dengan mysqldumpslow atau alat analisis lain untuk melihat query mana yang paling sering lambat dan paling banyak memakan waktu total. Query yang berjalan 0,5 detik tapi dipanggil ribuan kali sehari sering lebih penting daripada satu laporan yang butuh 30 detik sebulan sekali.

Index dan EXPLAIN, tempat penghematan terbesar

Index ibarat daftar isi buku. Tanpa index pada kolom yang dipakai di WHERE, JOIN, atau ORDER BY, database harus memeriksa seluruh tabel. Di tabel kecil tidak terasa, di tabel berisi jutaan baris setiap query menjadi berat.

Tambahkan EXPLAIN di depan query lambat untuk melihat rencana eksekusinya. Perhatikan beberapa hal:

• type bernilai ALL berarti seluruh tabel dibaca. Ini kandidat utama untuk index.
• rows menunjukkan perkiraan baris yang diperiksa. Bandingkan dengan jumlah baris yang benar-benar dibutuhkan.
• key menunjukkan index yang dipakai. Kosong berarti tidak ada index yang membantu.
• Extra yang berisi Using filesort atau Using temporary menandakan pengurutan atau tabel sementara yang mahal.

Index juga punya harga. Setiap index memperlambat penulisan sedikit dan memakan ruang. Jangan menambahkan index pada setiap kolom, cukup pada kolom yang benar-benar dipakai untuk mencari dan menggabungkan data. Untuk kondisi dengan beberapa kolom, index gabungan yang urutan kolomnya sesuai query sering lebih efektif daripada beberapa index terpisah.

Bantuan dari mysqltuner, dengan catatan

MySQLTuner adalah skrip yang membaca status dan variabel server lalu memberi saran. Alat ini berguna untuk melihat gambaran cepat: rasio cache, pemakaian memori maksimum, jumlah koneksi gagal, dan tabel yang perlu dirawat. Jalankan setelah server berjalan beberapa hari, karena statistik yang baru direset belum mencerminkan beban nyata.

Perlakukan sarannya sebagai bahan pertimbangan, bukan perintah. Skrip tidak tahu bahwa server yang sama juga menjalankan PHP, atau bahwa lonjakan terjadi hanya saat promo. Ubah satu setelan dalam satu waktu, ukur hasilnya, lalu lanjutkan.

Kebiasaan kecil yang sering terlupa

Selain setelan, beberapa kebiasaan berpengaruh besar. Ambil hanya kolom yang dibutuhkan, bukan SELECT *. Batasi hasil dengan LIMIT dan gunakan paginasi yang tidak memakai offset sangat besar pada tabel besar. Pisahkan proses laporan berat dari jam ramai. Pastikan tabel memakai InnoDB, bukan mesin lama seperti MyISAM yang mengunci seluruh tabel saat menulis.

Perhatikan juga server di sekitarnya. Database yang berbagi disk lambat dengan backup harian akan tersendat setiap malam. Tanda-tanda seperti ini dibahas di server lambat padahal CPU rendah. Kalau kamu masih menimbang antara MySQL, MariaDB, atau PostgreSQL, perbandingannya ada di PostgreSQL vs MySQL vs MariaDB. Bila mengurus tuning, backup, dan update sendiri terasa berat, opsi cloud database terkelola bisa jadi pertimbangan.

Ringkasan prioritas