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 

14 thoughts on “Tuning Perfoma MySQL

    1. Untuk menjawab pertanyaan mas Erfan banyak parameter yg dibutuhkan,
      karena perfomance insert tergantung
      – Memory server
      – CPU Server
      – Jumlah kolom table
      – Byte data yang di insert
      – Jumlah index dan jenis index yang digunakan
      – dan masih banyak lagi😀

  1. innodb_buffer_pool_size = 2101M setelah sy seting terjadi error mysql nya tidak bisa di start,.jika innodb_buffer_pool_size = 1024M sy setting demikian bisa jalan mysqlnya, apakah ada yg salah ato beda mysqlnya sy menggukana mysq server 5.5.22 memory 3GB.

    terima kasih

    1. Bisa disertakan semua isi dari file konfigurasinya?
      Ini mungkin bukan bug. Kemungkinan server kehabisan ukuran proses.

  2. [client]

    port=3306

    [mysql]

    default-character-set=latin1

    [mysqld]
    basedir=”C:/Program Files/MySQL/MySQL Server 5.5/”
    datadir=”C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
    character-set-server=latin1
    default-storage-engine=INNODB
    sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
    max_connections=100
    query_cache_size=20M
    query-cache-type = 1
    table_cache=256
    tmp_table_size=18M
    thread_cache_size=8
    myisam_max_sort_file_size=100G
    myisam_sort_buffer_size=35M
    key_buffer_size=23M
    read_buffer_size=64K
    read_rnd_buffer_size=256K
    sort_buffer_size=256K
    innodb_additional_mem_pool_size=2M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=1M
    innodb_buffer_pool_size=1G
    innodb_log_file_size=10M
    innodb_thread_concurrency=10

  3. [client]

    port=3306

    [mysql]

    default-character-set=latin1

    [mysqld]
    basedir=”C:/Program Files/MySQL/MySQL Server 5.5/”
    datadir=”C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
    character-set-server=latin1
    default-storage-engine=INNODB
    sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
    max_connections=100
    query_cache_size=20M
    query-cache-type = 1
    table_cache=256
    tmp_table_size=18M
    thread_cache_size=8
    myisam_max_sort_file_size=2G
    myisam_sort_buffer_size=35M
    key_buffer_size=23M
    read_buffer_size=64K
    read_rnd_buffer_size=256K
    sort_buffer_size=256K
    innodb_additional_mem_pool_size=2M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=1M
    innodb_buffer_pool_size=2101M
    innodb_log_file_size=10M
    innodb_thread_concurrency=10

    tetap ga mau jalan pas di start mysql nya

  4. Coba ditambahkan parameter

    [mysqld_safe]
    log-error=”C:/Program Files/MySQL/MySQL Server 5.5/mysqld.log”
    biasanya size innodb_log_file_size tidak sesuai dg file fisiknya.
    kemudian distart trus dilihat errornya apa di C:/Program Files/MySQL/MySQL Server 5.5/mysqld.log

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s