MySQLTuner: Dapatkan rekomendasi setting terbaik MySQL

MySQL merupakan software database server yang sangat populer dan dapat dikatakan sebagai standar untuk web hosting atau Virtual Private Server, ini karena rata – rata CMS juga bisa menggunakannya untuk menyimpan data. Aslinya tergantung seberapa banyak frekuensi akses dan querynya (berbanding lurus dengan jumlah pengunjung) maka setting defaultnya sudahlah cukup mampu. Sangat jarang kita membutuhkan perubahan konfigurasinya.

Tapi kalau kita sudah mengincar performa terbaik dan stabilitas yang lebih bagus dari sebelumnya tidak ada salahnya untuk diutak – atik settingnya. Kendalanya adalah: mau dimulai dari mana? Darimana kita tahu setting mana dan fungsinya apa. Semuanya memang bisa diketahui dari membaca manual MySQL atau dari situs – situs lain yang mengajarkan cara setting MySQL tapi ini bisa membutuhkan waktu lama dan belum tentu cocok untuk kebutuhan server anda.

Nah… ada satu solusi yang bisa anda gunakan sebagai langkah awal yaitu dengan menggunakan MySQLTuner, sebuah skrip Perl yang tugasnya menganalisa konfigurasi MySQL server yang terinstall di server dan membuat rekomendasi – rekomendasi modifikasi setting dari informasi yang didapatkannya.

Bagaimana cara menggunakan MySQLTuner? Silahkan eksekusi perintah – perintah berikut secara berurutan:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
perl mysqltuner.pl

Perintah pertama untuk mendownload program MySQLTuner, yang kedua adalah daftar password yang sering digunakan (tidak aman), dan ketiga adalah menjalankan programnya. Bisa anda jalankan juga chmod +x mysqltuner.pl sehingga tidak perlu memanggil perl lagi saat mengeksekusinya lagi, jadi bisa langsung ketikkan perintah ./mysqltuner.pl lain kali di terminal.

Tergantung anda login sebagai root atau tidak pada servernya maka sebelum dilanjutkan prosesnya akan diminta mengisikan username dan password untuk akun root database MySQL.

Please enter your MySQL administrative login:
Please enter your MySQL administrative password:

Kalau sudah maka akan muncul hasil diagnosa dari konfigurasi MySQL dan berbagai macam penilaian mengenai optimal tidaknya konfigurasinya.

mysqltuner

Semuanya spesifik khusus untuk yang terinstall dalam VPS anda, jadi kalau di server lain anda wajib menjalankan lagi karena saran yang diberikan bisa jadi berbeda. Disini saya mencobanya pada VPS yang terpasang VestaCP.

[OK] Logged in using credentials from debian maintenance account.
 >>  MySQLTuner 1.6.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.44-0+deb7u1
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 20M (Tables: 52)
[--] Data in InnoDB tables: 5M (Tables: 47)
[!!] Total fragmented tables: 16

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User 'admin_default@%' hasn't specific host restriction.
[!!] User 'servernesia_wp@%' hasn't specific host restriction.
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 19h 1m 50s (2M q [2.413 qps], 82K conn, TX: 8B, RX: 358M)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Total buffers: 168.0M global + 2.7M per thread (200 max threads)
[OK] Maximum reached memory usage: 227.1M (22.18% of installed RAM)
[OK] Maximum possible memory usage: 705.5M (68.90% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 11% (22/200)
[OK] Aborted connections: 2.96%  (2436/82173)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 281K sorts)
[!!] Temporary tables created on disk: 70% (58K on disk / 82K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 81% (176 open / 217 opened)
[OK] Open file limit used: 17% (177/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 73.8% (6M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/7.4M
[OK] Read Key buffer hit rate: 100.0% (14M cached / 4K reads)
[!!] Write Key buffer hit rate: 17.7% (131K cached / 108K writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/5.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 12.98% (1063 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.99% (6952221 hits/ 6952905 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 24449 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

Dari sini anda bisa mempertimbangkan untuk melakukan modifikasi setting sesuai daftar rekomendasinya di akhir. Ingat ini rekomendasi bukan wajib dilaksanakan.

Bagaimana mengedit konfigurasi MySQL? Silahkan edit file yang berlokasi di /etc/mysql/my.cnf dengan editor teks favorit anda sesuai dengan rekomendasi diatas dan harap dibackup dulu sebelum diubah. Bisa sudah maka restart saja MySQL: /etc/init.d/mysql restart atau service mysql restart.

Oh ya, MariaDB juga didukung penuh tapi untuk versi 10 keatas dan ini berlaku untuk semua jenis distro Linux. Jadi Ubuntu, CentOS, Debian dan sebagainya sudah termasuk.

Semoga bermanfaat. 🙂

19 Comments

  1. saat eksekusi perintah : perl mysqltuner.pl
    muncul error berikut :

    Can't exec "which": No such file or directory at mysqltuner.pl line 436 (#1)
        (W exec) A system(), exec(), or piped open call could not execute the
        named program for the indicated reason.  Typical reasons include: the
        permissions were wrong on the file, the file wasn't found in
        $ENV{PATH}, the executable in question was compiled for another
        architecture, or the #! line in a script points to an interpreter that
        can't be run for similar reasons.  (Or maybe your system doesn't support
        #! at all.)
    
    Use of uninitialized value $mysqladmincmd in scalar chomp at mysqltuner.pl line 438 (#1)
        (W uninitialized) An undefined value was used as if it were already
        defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
        To suppress this warning assign a defined value to your variables.
    
        To help you figure out what was undefined, perl will try to tell you the
        name of the variable (if any) that was undefined. In some cases it cannot
        do this, so it also tells you what operation you used the undefined value
        in.  Note, however, that perl optimizes your program and the operation
        displayed in the warning may not necessarily appear literally in your
        program.  For example, "that $foo" is usually optimized into "that "
        . $foo, and the warning will refer to the concatenation (.) operator,
        even though there is no . in your program.
    
    Use of uninitialized value $mysqladmincmd in -e at mysqltuner.pl line 439 (#1)
    Use of uninitialized value $mysqladmincmd in -e at mysqltuner.pl line 444 (#1)
    [!!] Couldn't find mysqladmin in your $PATH. Is MySQL installed?

    padahal seharusnya support mariadb 10

      1. Sep mbak. 😀

        Ada lagi sih mbak, kalau mau coba beberapa konfigurasi default bawaan dari MySQL tergantung spesifikasi servernya. Bisa dicopy dari sini: /usr/share/mysql dan direname menjadi my.cnf pada lokasi konfigurasi MySQL.

        Contoh isinya:

        /usr/share/mysql/my-innodb-heavy-4G.cnf
        /usr/share/mysql/my-medium.cnf
        /usr/share/mysql/my-huge.cnf
        /usr/share/mysql/my-large.cnf
        /usr/share/mysql/my-small.cnf

        Dulu saya selalu pakai yang small karena memang spesifikasi VPS saya masih imut – imut. Hehe. 🙂 Setelah itu mengenal MySQLTuner ya selalu saya jalankan setelahnya.

    1. Untuk VPS 512MB ya mbak? Saya ubek – ubek notepad saya ketemu template MySQL untuk ukuran tersebut:

      [mysql]
      
      # CLIENT #
      port                           = 3306
      socket                         = /var/lib/mysql/mysql.sock
      
      [mysqld]
      
      # GENERAL #
      user                           = mysql
      default-storage-engine         = InnoDB
      socket                         = /var/lib/mysql/mysql.sock
      pid-file                       = /var/lib/mysql/mysql.pid
      
      # MyISAM #
      key-buffer-size                = 32M
      myisam-recover                 = FORCE,BACKUP
      
      # SAFETY #
      max-allowed-packet             = 16M
      max-connect-errors             = 1000000
      
      # DATA STORAGE #
      datadir                        = /var/lib/mysql/
      
      # BINARY LOGGING #
      log-bin                        = /var/lib/mysql/mysql-bin
      expire-logs-days               = 14
      sync-binlog                    = 1
      
      # CACHES AND LIMITS #
      tmp-table-size                 = 32M
      max-heap-table-size            = 32M
      query-cache-type               = 0
      query-cache-size               = 0
      max-connections                = 500
      thread-cache-size              = 50
      open-files-limit               = 65535
      table-definition-cache         = 1024
      table-open-cache               = 2048
      
      # INNODB #
      innodb-flush-method            = O_DIRECT
      innodb-log-files-in-group      = 2
      innodb-log-file-size           = 64M
      innodb-flush-log-at-trx-commit = 1
      innodb-file-per-table          = 1
      innodb-buffer-pool-size        = 160M
      
      # LOGGING #
      log-error                      = /var/lib/mysql/mysql-error.log
      log-queries-not-using-indexes  = 1
      slow-query-log                 = 1
      slow-query-log-file            = /var/lib/mysql/mysql-slow.log

      Saya lupa asalnya darimana tapi dan kadang perlu disetel lagi. Tapi ini dasar template saya untuk setiap VPS baru dengan ukuran memori 512MB. Yang ngefek aslinya di setting InnoDB sih seingat saya. Dan query cache mungkin.

      1. Masih sama mas, sampai saat ini MariaDB kompatibel dengan setting MySQL.

        Ada kesulitan dimananya mas?

      2. Untuk Server Local dengan memory 16GB konfigurasinya yang bagaimana gan, mohon pencerahan, karena terkadang mysql crash terlalu banyak query yang muncul pada saat di tampilkan di bagian proses phpmyadmin

      3. Hasil lognya sebelum crash apa mas isinya? Yakin karena memori? Linux atau Windows mas?

        Kalau pakai MySQLTuner ada panduan yang disarankan?

  2. untuk setingan innodb saya pakai ini mas,, mungkin ada koreksi 😀

    default-storage-engine = InnoDB
    
    innodb_file_format=Barracuda
    innodb_purge_threads=1
    innodb_doublewrite = 1
    innodb_file_per_table = 1
    innodb_open_files = 1000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_instances = 1
    innodb_buffer_pool_size = 256M
    
    innodb_log_files_in_group = 2
    innodb_log_file_size = 64M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency = 4
    innodb_lock_wait_timeout=50
    innodb_flush_method=O_DIRECT_NO_FSYNC
    innodb_support_xa=1
    
    innodb_io_capacity = 4600
    innodb_read_io_threads = 64
    innodb_write_io_threads = 64
    innodb_flush_neighbors = 0
    
    innodb_stats_persistent=On
    innodb_adaptive_max_sleep_delay=0
    innodb_adaptive_flushing=On
    innodb_adaptive_flushing_lwm=10
    1. Saya bukan ahli setting MySQL lho mbak. Sama – sama belajar ya. 🙂

      Seingat saya yang disarankan untuk dioptimalkan:

      • innodb_buffer_pool_size – Ambil minimal 50% memori VPS yang bebas, jadi kalau Dropletnya 512MB ya ga salah disetting 256MB.
      • innodb_buffer_pool_instances – Ini berguna kalau banyak proses yang membutuhkan MySQL, paralel jadinya. Tapi makan memori lebih banyak.
      • innodb_flush_method – Kalau bisa gunakan DIRECT_IO agar langsung ditulis databasenya. Ini mengurangi beban I/O, tapi efeknya tidak ada cache penulisan data. Ini opsional, kalau pas ga hoki bisa korup datanya.
      • innodb_flush_log_at_trx_commit Sedangkan ini kalau nilainya 2 memaksimalkan performa tapi ada resiko korup karena integritas data tidak diperiksa secara menyeluruh. Istilah teknisnya ACID Compliant. Terserah mau milih performa atau keamanan data.
      • skip_name_resolve – Karena saya yakin rata – rata database server MySQL diinstall satu lokasi dengan web servernya maka nonaktifkan ini. Fungsinya buat mendapatkan hostname.

      Tentu saja sebaiknya lakukan satu per satu jangan bersamaan dan backup jangan lupa, nanti daripada jadi masalah. 🙂

  3. Setelah config mariadb rekomendasinya seperti ini

    -------- Recommendations ---------------------------------------------------------------------------
    General recommendations:
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Dedicate this server to your database for highest performance.
        Performance should be activated for better diagnostics
        Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        performance_schema = ON enable PFS
        innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=40M) if possible.

    apakah ada saran mas chandra untuk performance_schema = ON enable PFS saya tidak tau cara setupnya apakah menggunakan mysql query atau edit config my.cnf
    terimakasih banyak 🙂

    1. Itu editnya di my.cnf semua mas.

      Tapi disarankan agar mas menjalankan tunernya setelah agak lama biar semakin akurat analisanya, kan sudah ketahuan jadinya bagaimana pemakaiannya sehari – hari. Ambil seminggu lah kalau mau cepat atau sebulan saran saya.

  4. root@cbat:/home/cbat# perl mysqltuner.pl
     >>  MySQLTuner 1.7.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    [!!] Attempted to use login credentials from debian maintenance account, but they failed.

    failed kang

Tinggalkan Balasan ke Chandra Batalkan balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *