Selain melakukan konfigurasi pada server, kita juga dapat melakukan pengaturan pada storage engine. Pada bagian ini kita akan membahas pengaturan pada engine InnoDB, engine yang paling populer digunakan dan sepertinya akan menggantikan MyISAM. Beberapa konfigurasi penting yang dapat kita atur adalah sebagai berikut.
1. innodb_buffer_pool_size
Konfigurasi ini digunakan untuk menentukan ukuran buffer dari storage engine InnoDB, ukuran buffer disini maksudnya adalah besarnya alokasi memory (RAM) yang dapat digunakan untuk menyimpan index dan data tabel selama proses eksekusi query. Dengan disimpan di memory maka akses data menjadi sangat cepat (jika dibanding dengan akses ke hardisk), dengan demikian, semakin besar nilai ini maka semakin bagus.
Untuk memaksimalkan performa, buat ukuran buffer sebesar mungkin, dengan nilai buffer besar, maka ketika memproses data, storage engine cukup membaca tabel (index maupun data) dari hardisk sekali saja, kemudian menyimpannya di RAM, selanjutnya tabel tersebut dibaca dari RAM.
Nilai konfigurasi ini dapat dilihat dengan menjalankan perintah berikut:
mysql> SHOW VARIABLES LIKE "innodb_buffer_pool_size";
+---------------------------+-------------+ | Variable_name | Value |+---------------------------+-------------+| innodb_buffer_pool_size | 134217728 |+---------------------------+-------------+1 row in set (0.01 sec)+---------------------------+-------------+
Pada contoh diatas, nilai innodb_buffer_pool_size adalah 134M, nilai ini merupakan nilai default MySQL. pada MariaDB, nilai ini lebih kecil yaitu 16M:
MariaDB [(none)]> SHOW VARIABLES LIKE "innodb_buffer_pool_size";
+---------------------------+-------------+| Variable_name | Value |+---------------------------+-------------+| innodb_buffer_pool_size | 16777216 |+---------------------------+-------------+1 row in set (0.001 sec)
Pertanyaannya, berapa nilai yang ideal? Nilai ideal untuk konfigurasi ini adalah 75% dari total free RAM. Free RAM disini maksudnya adalah RAM yang tersedia setelah dikurangi penggunaan oleh resource lainnya seperti Operating Sistem.
Misal kita memiliki server dengan RAM 8 GB, OS yang digunakan adalah Windows 10, server tersebut digunakan untuk server database MySQL dan server web (apache+PHP), Maka free RAM nya dapat diperkirakan sebagai berikut:
Total RAM 8GB - OS (1 GB) - PHP (1 GB) = 6GB, maka nilai innodb_buffer_pool_size dapat dimaksimalkan menjadi 4,5GB (75% x 6 GB).
Kenapa 75%? Karena 25% sisanya, yaitu 1,2GB digunakan oleh server MySQL untuk keperluan lain seperti sorting data, log buffer, dll.
2. innodb_buffer_pool_instance
Selanjutnya ukuran dari buffer pool tadi dapat dipecah menjadi beberapa bagian yaitu menggunakan konfigurasi buffer_pool_instance, dengan memecah menjadi beberapa bagian, maka storage engine dapat lebih cepat mencari data karena dapat langsung menuju ke bagian (instance) dimana data tersebut disimpan, dibanding harus mencari di satu bagian yang berukuran besar, hal ini dapat diibaratkan seperti RAM pada komputer, RAM 2 x 4GB akan lebih baik daripada 1 x 8GB.
Nilai konfigurasi ini dapat di lihat dengan menjalankan perintah berikut:
mysql> SHOW VARIABLES LIKE "innodb_buffer_pool_instances";
+--------------------------------+---------+| Variable_name | Value || innodb_buffer_pool_instances | 1 |+--------------------------------+---------+1 row in set (0.11 sec)+--------------------------------+---------+
Pada contoh diatas, terlihat bahwa nilai konfigurasi ini adalah 1 (nilai default pada MySQL) sedangkan pada MariaDB, konfigurasi ini bernilai 8:
MariaDB [(none)]> SHOW VARIABLES LIKE
"innodb_buffer_pool_instances";
| Variable_name | Value |+--------------------------------+---------+| innodb_buffer_pool_instances | 8 |+--------------------------------+---------+1 row in set (0.001 sec)+--------------------------------+---------+
Wow.. ternyata nilai konfigurasi pada MariaDB jauh lebih banyak dibanding dengan MySQL
Apakah lebih banyak lebih baik?
Belum tentu, kita perlu lakukan tes dan ukur. Banyaknya instances ini idealnya adalah rata-rata per 1GB pool size, sehingga, jika besarnya innodb_buffer_pool_size adalah 4,8, maka innodb_buffer_pool_instance dapat di beri nilai 5.
3. innodb_log_file_size
InnoDB merupakan engine yang mensupport transaksi, dimana query yang telah dieksekusi dapat dikembalikan ke posisi semula (undo) atau dapat kembali mengulang proses eksekusi yang telah dilakukan (redo), sama seperti ketika kita menggunakan apliaksi MS. Word atau Ms Excel dimana didalamnya terdapat fitur undo dan redo. Pada database, implementasinya adalah:
Undo. Ketika kita menggunakan engine transaksional maka setiap perubahan yang ada (misal update atau insert data), tidak langsung ditulis ke tabel. Pertama tama perubahan tersebut akan disimpan ke sebuah file yang disebut transaction log (log transaksi – isitilah pada MySQL) atau redo log (istilah pada Oracle atau pada database lain), pada waktu yang sama, perubahan juga disimpan di memori (innodb buffer pool). Page yang berubah tersebut kemudian ditandai menjadi dirty yang akan dihapus kemudian.
Data original yang tidak mengalami perubahan, disimpan ke tempat khusus yang disebut rollback segment.
Selanjutnya jika transaksi dibatalkan dengan perintah ROLLBACK, maka data perlu dikembalikan ke posisi semula, hal ini sangat mudah dilakukan karena memang data asli belum berubah, InnoDB cukup mengambil data yang disimpan pada rollback segment, menghapus data pada buffer pool dan menulis ke file log transaksi bahwa transaksi dibatalkan.
Redo. Ketika transaksi di commit (baik commit manual maupun auto commit), maka innodb akan menerapkan semua perubahan yang pending ke tabel asli, namun sebelumnya, InnoDB menulis perubahan tersebut pada file log transaksi, sehingga jika terjadi crash dan masih terdapat perubahan yang belum diterapkan ke tabel, maka ketika server dinyalakan, InnoDB akan mencari checkpoint terakhir pada file log sebelum terjadinya crash dan mengulang kembali semua perubahan dengan membaca kembali bagian “to-be-modified”. Berikut ini contoh crash recovery (dari file log):
2018-09-13 8:34:23 17112 [Note] InnoDB: Completed initialization of buffer pool
2018-09-13 8:34:24 17112 [Note] InnoDB: Highest supported file format is Barracuda.
2018-09-13 8:34:24 17112 [Note] InnoDB: Starting crash recovery from checkpoint LSN=732897900
Crash: Crash ini bisa disebabkan karena Sistem operasi hang, server yang tiba-tiba mati karena listrik tidak stabil, hardware komputer hang, dll sehingga menyebabkan server MySQL mati secara mendadak, tidak melalui proses shutdown, hal ini dapat menyebabkan berbagai masalah, salah satunya seperti yang sedang kita bahas sekarang, InnoDB crash.
File log ini tidak sama dengan file log error, slow query log, dll, file log tersebut dapat kita gunakan untuk mengidentifikasi berbagai aktivitas server, sedangkan log InnoDB hanya digunakan oleh InnoDB untuk memastikan keandalan dan konsistensi data melalui manajemen transaksi dan kita tidak bisa melihat isinya.
Secara fisik, file log ini bernama ib_logfile0 dan ib_logfile1 tanpa ekstensi, perhatikan gambar berikut:
Banyaknya file log ini ditentukan oleh konfigurasi innodb_log_files_in_group yang nilai defaultnya adalah 2 sedangkan besarnya ukuran masing-masing file ditentukan oleh konfigurasi innodb_log_file_size, yang secara default nilainya adalah 48M baik pada MySQL atau Maria DB.
mysql> SHOW VARIABLES LIKE "%innodb_log_file_size%";
| Variable_name | Value |+------------------------+------------+| innodb_log_file_size | 50331648 |+------------------------+------------+1 row in set (0.10 sec)+------------------------+------------+
Note: 50331648 (byte) / 1024 / 1024 = 48M. Jika jumlah file log ada 2 maka total ukuran file log adalah 96MB.
Kita dapat mengubah ukuran file log dengan megubah nilai pada innodb_log_file_size, terkait ukuran ini, hal penting yang perlu diperhatikan adalah:
Secara umum, server crash jarang terjadi sehingga kita bisa menaikkan ukuran log ini, ukuran yang disarankan adalah 25% dari ukuran innodb_buffer_pool_size dan maksimal sama dengan innodb_buffer_pool_size, namun nilai ini tergantung aktifitas penulisan data di database, Anda dapat memulainya di angka 15% - 20% dari nilai innodb_buffer_pool_size
4. innodb_log_files_in_group
Konfigurasi ini menentukan banyaknya file log innoDb, nilai defaultnya adalah 2, kita dapat membiarkan nilai ini apa adanya. Jumlah file log akan berpengaruh pada performa, semakin banyak file log akan menurunkan performa.
5. innodb_log_buffer_size
Konfigurasi ini mendefinisikan besarnya buffer (memory) yang digunakan untuk menyimpan log InnoDB sebelum log tersebut ditulis ke file log hardisk, jadi ketika query dieksekusi, terlebih dahulu perubahan disimpan pada buffer memory, selanjutnya ketika terjadi commit, log ini ditulis ke log hardisk baru kemudian dilakukan perubahan pada tabel asli, file log ini nantinya digunakan untuk crash recovery (lihat pembahasan innodeb_log_file_size).
Pengaturan waktu penulisan log dari buffer memory ke file log diatur oleh konfigurasi innodb_flush_log_at_trx_commit
Nilai default untuk konfigurasi ini adalah 8Mb pada MariaDB dan 16Mb pada MySQL
mysql> SHOW VARIABLES LIKE "%innodb_log_buffer_size%";
| Variable_name | Value |+--------------------------+------------+| innodb_log_buffer_size | 16777216 |+--------------------------+------------+1 row in set (0.05 sec)+--------------------------+------------+
Nilai 16M ini cukup untuk database yang ringan.
Jika nilai terlalu kecil maka buffer akan cepat penuh dan penulisan dilakukan ke hardisk, untuk mengetahui apakah nilai buffer cukup, dapat di cek status pada innodb_log_waits, jika nilainya tidak 0 atau nilainya bertambah ketika dicek untuk kedua kalinya, maka nilai innodb_log_buffer_size perlu dinaikkan, berikut ini cara mengecek status log waits:
MariaDB [(none)]> SHOW STATUS LIKE "%innodb_log_waits%";
| Variable_name | Value |+--------------------------+---------+| Innodb_log_waits | 0 |+--------------------------+---------+1 row in set (0.001 sec)+--------------------------+---------+
6. innodb_flush_log_at_trx_commit
Konfigurasi ini mengatur kapan log InnoDB yang ada di buffer ditulis ke file log, nilai defaultnya adalah 1, nilai yang direkomendasikan untuk keamanan data. Nilai 1 berarti setiap terjadi commit, seketika itu juga log pada buffer memory ditulis ke file log hardisk (file ib_logfile).
Note: secara default, MySQL akan langsung melakukan commit setelah mengeksekusi query (autocommit transaction)
Nilai lainnya adalah 2 dimana log buffer ditulis ke log file log setiap terjadi commit, namun tidak langsung menulis data tersebut ke tabel data. Penulisan pada tabel data dilakukan setiap 1 detik, dengan demikian, jika terjadi crash, maka perubahan data masih tersimpan di log file sehingga masih bisa di recovery
Nilai terakhir adalah 0 dimana setiap 1 detik, log buffer ditulis ke log file dan dari log file tersebut data ditulis ke tabel data, sehingga jika terjadi crash dan ada data yang belum di flush (ditulis) dari log buffer ke log file, maka data tersebut akan hilang (komputer mati, data di ram hilang), karena data tidak ada di file log maupun di data tabel.
Penting diperhatikan bahwa jika kita memilih 0 atau 2, maka penulisan log dari buffer ke file log tidak langsung ditulis ke hardisk, melainkan ke Operating System cache terlebih dahulu, sehingga jika sistem crash maka akan berpotensi kehilangan data yang diproses dalam 1 detik tersebut.
Jika bernilai 1, maka InnoDB akan memaksa Operating System untuk menulis data langsung ke tabel hardisk, tidak melalui cache, sehingga data lebih reliable (handal). Karena langsung ditulis ke tabel hardisk, maka proses penulisan sedikit lebih lambat dibanding 0 atau 2.
Dengan demikian, untuk kendalan data, maka kita dapat memilih nilai 1 sedangkan jika lebih mengutamakan performance, maka kita dapat memilih nilai 2, misal ketika melakukan restore data dalam jumlah besar.
7. innodb_per_file_table
Konfigurasi ini mengatur bagaimana innodb menyimpan data dan index. Nilai defaultnya adalah ON yang artinya tiap tiap tabel memiliki tablespace (file .ibd) tersendiri untuk menyimpan data dan index, sedangkan jika nilainya OFF maka semua data dan index disimpan pada central tablespace (satu file ibdata1), berikut ini contoh untuk nilai OFF
Pada contoh diatas, terlihat bahwa semua data pada tabel disimpan pada file sistem yaitu ibdata1.
Meskipun konfigurasi ini tidak berpengaruh pada performa, dengan masing masing tabel memiliki file .idb maka kita akan memiliki kontrol untuk tiap tiap tabel seperti untuk backup tabel secara terpisah yaitu cukup copy dan paste file .ibd dan .frm tabel. Selain itu, jika berada dalam satu file, maka jika file tersebut corrupt (rusak) maka semua data tabel akan rusak. Dengan file .ibd terpisah, juga dapat menambah space kosong (ukuran file .ibd berkurang) ketika tabel dioptimasi menggunakan perintah OPTIMIZE TABLE
