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 <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filteringPlease enter your MySQL administrative login: <-- rootPlease 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 properlyVariables 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 …..
