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.
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. 🙂
saat eksekusi perintah :
perl mysqltuner.pl
muncul error berikut :
padahal seharusnya support mariadb 10
solved dengan perintah :
yum -y install which
😀belum terinstal di centos minimalis 😀
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 menjadimy.cnf
pada lokasi konfigurasi MySQL.Contoh isinya:
Dulu saya selalu pakai yang small karena memang spesifikasi VPS saya masih imut – imut. Hehe. 🙂 Setelah itu mengenal MySQLTuner ya selalu saya jalankan setelahnya.
trims infonya mas chandra,,masih nyari konfigurasi yg tepat untuk droplet $5 😀
Untuk VPS 512MB ya mbak? Saya ubek – ubek notepad saya ketemu template MySQL untuk ukuran tersebut:
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.
trims mas chandra, memang butuhnya yg default innodb karna ekperimen dengan xenforo 🙂
Ini cara menerapkannya di mariaDB bagaimana mas chandra?
Masih sama mas, sampai saat ini MariaDB kompatibel dengan setting MySQL.
Ada kesulitan dimananya mas?
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
Hasil lognya sebelum crash apa mas isinya? Yakin karena memori? Linux atau Windows mas?
Kalau pakai MySQLTuner ada panduan yang disarankan?
untuk setingan innodb saya pakai ini mas,, mungkin ada koreksi 😀
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 gunakanDIRECT_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. 🙂
mantap tutorialnya kaka
Setelah config mariadb rekomendasinya seperti ini
apakah ada saran mas chandra untuk
performance_schema = ON enable PFS
saya tidak tau cara setupnya apakah menggunakan mysql query atau edit configmy.cnf
terimakasih banyak 🙂
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.
failed kang
Ini di Debian/Ubuntu mas? Instalasi sendiri atau pakai script seperti EasyEngine?
Saya bahas sekilas hari ini: https://servernesia.com/2794/solusi-eksekusi-mysqltuner-gagal/
Semoga fixed. 🙂
Itu cara copy dan rename /usr/share/mysql dan direname menjadi my.cnf gimana ya mas candra
Langsung pakai
cp
saja mas seperti biasa, tapi nama file tujuannya ganti ke yang diinginkan.