Kali ini kita akan mencoba optimasi Mysql server dengan menggunakan informasi yang ditampilkan oleh script mysqltuner. Sebelumnya silahkan download script tersebut menggunakan perintah dibawah ini :
wget http://mysqltuner.pl/ -O mysqltuner.pl
setelah didownload langsung saja dijalankan script tersebut :
# chmod +x mysqltuner.pl # ./mysqltuner.pl --user [usermysql] --password [passwordmysql]
setelah dijalankan nanti akan ada proses seperti dibawah ini
server1:~# ./mysqltuner.pl >> MySQLTuner 0.9.8 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: <-- root Please enter your MySQL administrative password: <-- yourrootsqlpassword -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software! Upgrade soon! [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster [--] Data in MyISAM tables: 301M (Tables: 2074) [--] Data in HEAP tables: 379K (Tables: 9) [!!] InnoDB is enabled but isn't being used [!!] ISAM is enabled but isn't being used [!!] Total fragmented tables: 215 -------- Performance Metrics ------------------------------------------------- [--] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M) [--] Reads / Writes: 78% / 22% [--] Total buffers: 2.6M per thread and 58.0M global [OK] Maximum possible memory usage: 320.5M (20% of installed RAM) [OK] Slow queries: 0% (17/1B) [OK] Highest usage of available connections: 32% (32/100) [OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M [OK] Key buffer hit rate: 99.9% [OK] Query cache efficiency: 99.9% [!!] Query cache prunes per day: 47549 [OK] Sorts requiring temporary tables: 0% [!!] Temporary tables created on disk: 28% [OK] Thread cache hit rate: 99% [!!] Table cache hit rate: 0% [OK] Open file limit used: 12% [OK] Table locks acquired immediately: 99% [!!] Connections aborted: 20% -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Add skip-isam to MySQL configuration to disable ISAM Run OPTIMIZE TABLE to defragment tables for better performance 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 Increase table_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (> 16M) tmp_table_size (> 32M) max_heap_table_size (> 16M) table_cache (> 64)
Perhatikan pada tulisan yang saya beri warna merah, tambahkan atau sesuaikan varibel-variabel tersebut yang berada didalam file /etc/mysql/my.cnf.
Setelah melakukan perubahan pada file my.cnf langsung lakukan restart pada service MySql atau perubahan tidak akan aktif.
/etc/init.d/mysql restart
Jika sudah, coba cek dan bandingkan penggunaan memory MySql pada server sebelum dan sesudah perubahan dengan perintah :
top
atau
htop
Selamat mencoba dan mohon maaf apabila ada kekurangan …..