Tuning Perfoma MySQL

Setelah beberapa project menggunakan DB Engine MySQL akhirnya mendapatkan masalah pertama  yaitu Eksekusi Select Query yang sangat lamban pada saat data sudah mencapai ribuan baris record, terlebih untuk query  yang kompleks. Akhirnya diputuskan lah untuk menlakukan tunning pada MySQL, setelah browsing sana-sini akhirnya didapatkan kesimpulan tunning atau increasing perfomance MySQL sendiri secara umum terdiri dari 3 bagian, yaitu :

1. Optimasi MySQL variable di file my.ini (my.cnf).
   
2. Optimasi Struktur Table.
   
3. Optimasi SQL Query.
   

Artikel bagian pertama ini yang akan dibahas cuma Optimasi MySQL variable. Yaitu kita akan men-edit variable-variable bawaan dari instalasi awal MySQL. Karena secara default settingan variable MySQL adalah memungkinkan untuk berjalan dikomputer minim resource (spesifikasi hardware rendah), untuk kebutuhan production/live terlebih lagi apabila spesifikasi hardware Database Server kita tinggi, maka hal ini tentunya kurang relevan lagi.

Proses edit variable MySQL bisa dengan berbagai cara, tapi yang akan kita pakai adalah langsung mengedit file my.ini (my.cfg). Matikan dulu service MySQL apabila sendang berjalan, masuk ke commandline (cmd) Windows, ketik :

    mysqladmin -u[username] -p[password] shutdown

Untuk mengeksekusi perintah diatas, pastikan mysql console telah terdaftar di Environment Variable Windows, dan user yang digunakan memiliki hak Akses penuh (sysdba). Kemudian silakan ke folder instalasi MySQL, kemudian cari file my.ini (my.cfg)
Contoh lokasinya

• Pada windows di : “C:\Program Files\MySQL\MySQL Server 5.5\my.ini”
  
• Pada linux di : “/etc/my.cnf”
  
• Buka dengan Notepad atau Text Editor lainnya, cari & edit variable-variable berikut menjadi :
  

• key_buffer_size
  
  Untuk kebutuhan table yang menggunakan engine MyISAM. Max 25% dari total memory (RAM) Hardware dalam satuan MB, misal memory saya 3GB (3002 MB * 25%= 750,5 MB) maka saya edit menjadi 750M. apabila tidak ada table MyISAM sebaiknya disetting menjadi 16-32M.
  
  
• innodb_buffer_pool_size
  
  Untuk kebutuhan table yang menggunakan engine InnoDB. Max 70% dari total memory (RAM) Hardware dalam satuan MB, misal memory saya 3GB (3002 MB * 25%= 2101,4 MB) maka saya edit menjadi 2101M.
  
  
• innodb_additional_mem_pool_size
  
  Edit menjadi 20M.
  
  
• innodb_log_buffer_size
  
  Edit menjadi 8M.
  
  
• table_cache
  
  Edit menjadi 1024.
  
  
• thread_cache_size
  
  Edit menjadi 16.
  
  
• sort_buffer_size
  
  Edit menjadi 512K
  
  
• read_buffer_size
  
  Nilai variable sort_buffer_size diatas dibagi 2, maka dengan contoh diatas adalah 256K
  
  
• innodb_flush_log_at_trx_commit
  
  Yang ini hanya opsional, apabila edit menjadi 0 (nol) bisa menjadi sedikit lebih cepat, namun kekurangannya adalah memungkinkan untuk kehilangan data yang sedang ditransaksikan apabila server crash.
  
  Setelah itu silakan jalankan kembali service MySQL, kembali ke commandline (cmd) ketik :
  
      net start mysql (pada windows)
      /etc/init.d/mysql start (pada linux)
  
  Apabila service gagal dijalankan dan terdapat error, maka periksa kembali hasil edit variable diatas, mungkin saja ada nilai yang kelebihan dan hal ini tentu saja tergantung spesifikasi hardware masing-masing
  
  Dari Tunning bagian pertama ini saja, yang saya dapatkan adalah perfomance dalam menginsert 1000 record (20 kolom) sekaligus ke table InnoDB menjadi 2-3x lebih cepat, dibanding settingan variable awal bawaan instalasi MySQL
  
  Akhir kata, janganlah sekaligus menaikan nilai variable sampai maksimal, naikanlah secara bertahap, lihat hasil perkembangan & kebutuhan. Sekian artikel kali ini, natikan lanjutan artikel berikutnya. semoga bisa bermanfaat