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.
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 <major@mhtx.net>
>> 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.
Untuk 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.
