mysqltuner
Install
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
아래는 이전 버젼 내용으로 추후 내용업데이트 예정
>> MySQLTuner 1.0.1 - 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: root패스워드
인증과정 없이는 실행할 수 없다.
이 프로그램 툴 역시 색상으로 구분해주므로, 어디가 그렇고 그런지 파악하기 쉽게 되어 있다. 특히 말머리에 !!, OK 만으로도 상태가 어떤지는 알 것이다.
Recommendations 부분에는 어떻게 튜닝해줘야 하는지 어드바이스까지 해준다.
>> MySQLTuner 1.0.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 493M (Tables: 304)
[--] Data in InnoDB tables: 282M (Tables: 12)
[!!] Total fragmented tables: 5
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 19h 32m 14s (110M q [704.797 qps], 11M conn, TX: 238B, RX: 6B)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 442.0M global + 16.4M per thread (5000 max threads)
[!!] Maximum possible memory usage: 80.4G (1379% of installed RAM)
[OK] Slow queries: 0% (4K/110M)
[OK] Highest usage of available connections: 15% (781/5000)
[OK] Key buffer size / total MyISAM indexes: 384.0M/163.0M
[OK] Key buffer hit rate: 100.0% (305M cached / 95K reads)
[OK] Query cache efficiency: 74.0% (52M cached / 71M selects)
[!!] Query cache prunes per day: 159148
[OK] Sorts requiring temporary tables: 0% (21K temp sorts / 2M sorts)
[OK] Temporary tables created on disk: 0% (8K on disk / 937K total)
[OK] Thread cache hit rate: 97% (269K created / 11M connections)
[OK] Table cache hit rate: 33% (512 open / 1K opened)
[OK] Open file limit used: 2% (680/25K)
[OK] Table locks acquired immediately: 99% (24M immediate / 24M locks)
[!!] InnoDB data size / buffer pool: 282.4M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 32M)
innodb_buffer_pool_size (>= 282M)