KAL
Menu

Dasar Konfigurasi

innodb_buffer_pool_size dan pengaturan kunci yang memengaruhi kinerja server.

Daftar isi

Perilaku server MySQL diatur oleh ratusan variabel sistem (system variable) yang nilainya dapat dibaca, sebagian diubah saat server berjalan, dan sebagian lagi ditetapkan lewat berkas konfigurasi sebelum server dihidupkan. Topik ini membahas cara membaca nilai variabel beserta lingkup sesi dan globalnya, peran berkas konfigurasi, serta dua pengaturan yang paling memengaruhi kinerja dan daya tahan server, yaitu innodb_buffer_pool_size dan innodb_flush_log_at_trx_commit. Setelah membacanya, kondisi konfigurasi sebuah server dapat dipetakan dan dampak tiap perubahan dapat ditimbang sebelum dijalankan.

Membaca nilai variabel sistem#

SHOW VARIABLES menampilkan variabel yang berlaku pada koneksi saat itu, sedangkan sintaks @@nama_variabel membacanya sebagai ekspresi di dalam query. Keduanya sama-sama berguna, dan pola LIKE pada SHOW VARIABLES membantu mencari variabel dari nama yang belum dihafal.

bash 
# membaca karakter set default server
mysql -u root -e "SHOW VARIABLES LIKE 'character_set_server';"
text Keluaran 
Variable_name	Value
character_set_server	utf8mb4

Setiap variabel memiliki lingkup sesi (SESSION), global (GLOBAL), atau keduanya. Nilai global menjadi nilai awal bagi koneksi yang baru terbuka, sedangkan nilai sesi hanya berlaku pada koneksi yang mengaturnya dan musnah bersama koneksi tersebut. Variabel sql_mode memakai keduanya, sehingga perbedaan lingkungan dapat dibuktikan langsung.

bash 
# membandingkan nilai global dan sesi pada sql_mode
mysql -u root -e "SELECT @@GLOBAL.sql_mode AS mode_global\G"
text Keluaran 
*************************** 1. row ***************************
mode_global: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Pernyataan SET SESSION mengubah perilaku satu koneksi saja, misalnya mengetatkan mode untuk satu proses impor data. Pernyataan SET GLOBAL mengubah nilai bagi semua koneksi yang terbuka setelahnya, tetapi tidak menyentuh koneksi yang sudah berjalan, dan nilainya kembali ke nilai berkas konfigurasi ketika server dimulai ulang.

Berkas konfigurasi dan pemberlakuan nilainya#

Nilai yang bertahan antarhidupkan server dituliskan pada berkas konfigurasi, lazimnya my.cnf atau my.ini, di dalam bagian [mysqld]. Berkas berikut meringkas pengaturan yang berlaku pada server latihan jalur ini, dan nilainya tampak berbeda dari bawaan pabrik ketika dibaca lewat SHOW VARIABLES.

ini my.cnf 
[mysqld]
innodb_buffer_pool_size = 256M
max_connections         = 100
character-set-server    = utf8mb4
innodb_flush_log_at_trx_commit = 1

Bukti pemberlakuannya dapat diuji: nilai bawaan pabrik untuk innodb_buffer_pool_size adalah 134217728 byte, yaitu 128 MiB, sedangkan server latihan menampilkan 268435456 byte karena berkas konfigurasinya menetapkan 256M. Ketidakcocokan antara nilai di berkas dan nilai di SHOW VARIABLES biasanya berarti berkas itu tidak terbaca oleh server, misalnya salah lokasi atau salah nama bagian, sehingga pemeriksaan ini menjadi langkah diagnosis pertama.

bash 
# membaca nilai buffer pool yang berlaku saat ini
mysql -u root -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
text Keluaran 
Variable_name	Value
innodb_buffer_pool_size	268435456

Sejak MySQL 8.0, pernyataan SET PERSIST menulis perubahan variabel langsung ke berkas mysqld-auto.cnf di direktori data, sehingga nilainya bertahan antarhidupkan tanpa menyunting my.cnf. Perubahan lewat SET PERSIST dan SET GLOBAL sama-sama tidak mengubah berkas konfigurasi teks yang dibaca manusia, sehingga sumber kebenaran nilai server perlu diperiksa pada dua tempat.

innodb_buffer_pool_size#

InnoDB bekerja di atas halaman (page) berukuran tetap, pada server ini 16384 byte, dan innodb_buffer_pool_size menentukan ukuran area memori yang menyimpan cache halaman data dan indeks. Buffer pool yang cukup besar membuat pembacaan berulang terjadi di memori, sedangkan buffer pool yang terlalu kecil memaksa setiap query mengambil data dari disk.

bash 
# menghitung ukuran buffer pool dalam MiB dan jumlah halamannya
mysql -t -u root -e "SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS ukuran_mib, @@innodb_buffer_pool_size / @@innodb_page_size AS jumlah_halaman;"
text Keluaran 
+--------------+----------------+
| ukuran_mib   | jumlah_halaman |
+--------------+----------------+
| 256.00000000 |     16384.0000 |
+--------------+----------------+

Angkanya saling terkait secara presisi: 268435456 byte dibagi 1024 dua kali memberi 256 MiB, dan dibagi ukuran halaman memberi 16384 halaman. Pedoman umumnya menjadikan buffer pool porsi terbesar dari memori pada server basis data khusus, umumnya sekitar 50 sampai 75 persen RAM, dengan sisa untuk sistem operasi dan proses lain. Nilai terbaik tetap ditentukan oleh ukuran data dan pola akses, bukan oleh satu angka ajaib.

MySQL 8.0 memungkinkan perubahan ukuran buffer pool saat server berjalan tanpa hidupkan ulang, namun perubahan semacam itu memindahkan banyak memori dan menambah beban pada server produksi. Pada latihan ini perintahnya ditampilkan tanpa dieksekusi karena server yang sama dipakai bersama.

sql Perubahan ukuran buffer pool saat server berjalan 
-- tidak-dijalankan
SET GLOBAL innodb_buffer_pool_size = 536870912;

max_connections dan daya tahan commit#

Variabel max_connections membatasi jumlah koneksi yang boleh terbuka serentak. Aplikasi yang membuka koneksi melebihi batas ini menerima error Too many connections, dan menaikkan batas tanpa menimbang memori membuat tiap koneksi tambahan ikut menyedot RAM. Pada server latihan, batasnya ditetapkan 100, berbeda dari bawaan pabrik 151.

bash 
# membaca batas koneksi dan pengaturan daya tahan commit
mysql -u root -e "SHOW VARIABLES LIKE 'max_connections'; SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';"
text Keluaran 
Variable_name	Value
max_connections	100
Variable_name	Value
innodb_flush_log_at_trx_commit	1

Variabel innodb_flush_log_at_trx_commit mengatur seberapa sering log transaksi didorong ke disk pada setiap commit. Nilai 1 berarti setiap commit dibalutkan (flush) ke disk sebelum dianggap selesai, sehingga transaksi yang dikonfirmasi selamat dari pemadaman listrik. Nilai 2 menunda pembalutannya hingga satu detik sekali, sedangkan nilai 0 tidak membalutkan pada commit sama sekali, dan keduanya mempercepat penulisan dengan risiko kehilangan transaksi terakhir ketika server mati mendadak.

Kesalahan umum#

Kesalahan pertama adalah menaikkan innodb_buffer_pool_size hingga memakan hampir seluruh RAM. Sistem operasi dan proses lain kehabisan ruang, lalu server dipaksa menukar memori ke disk (swap) dan kinerjanya justru runtuh. Ukuran yang aman selalu menyisakan ruang untuk sistem operasi, proses mysqld itu sendiri, dan koneksi-koneksinya.

Kesalahan kedua adalah menganggap SET GLOBAL berlaku seketika untuk semua keadaan. Koneksi yang sudah terbuka mempertahankan nilai sesinya, dan beberapa variabel, misalnya character-set-server, sama sekali tidak dapat diubah saat server berjalan sehingga menuntut hidupkan ulang. Perubahan yang ingin dipertahankan antarhidupkan server perlu dituliskan lewat SET PERSIST atau berkas konfigurasi.

Kesalahan ketiga adalah menurunkan innodb_flush_log_at_trx_commit demi kecepatan pada basis data yang benar-benar penting. Peningkatan laju penulisan itu dibayar dengan kemungkinan kehilangan transaksi terakhir saat server mati mendadak, dan untuk basis data transaksional nilai 1 tetap menjadi pilihan yang aman.

Ringkasan#

  • Nilai variabel dibaca dengan SHOW VARIABLES atau sintaks @@nama, dan setiap variabel berlaku pada lingkup sesi, global, atau keduanya.
  • Nilai yang bertahan antarhidupkan server ditetapkan pada bagian [mysqld] berkas konfigurasi, dan SET PERSIST menawarkan jalur penulisan tanpa menyunting berkas teks.
  • innodb_buffer_pool_size menentukan cache data dan indeks InnoDB, dengan nilai server latihan 268435456 byte atau 256 MiB.
  • innodb_flush_log_at_trx_commit bernilai 1 menjamin setiap commit dibalutkan ke disk, sedangkan nilai 0 dan 2 menukar daya tahan dengan kecepatan.

Referensi#