mysqltuner.pl を実行するだけ。
$ git clone git://github.com/rackerhacker/MySQLTuner-perl.git $ cd MySQLTuner-perl $ perl mysqltuner.pl
"!!"の項目が改善すべき項目。
$ perl mysqltuner.pl
>> MySQLTuner 1.2.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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- 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: 60B (Tables: 1)
[--] Data in InnoDB tables: 2G (Tables: 2400)
[!!] BDB is enabled but isn't being used
[OK] Total fragmented tables: 0
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 372d 3h 41m 45s (558M q [17.365 qps], 33M conn, TX: 643B, RX: 220B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 1.5G global + 12.4M per thread (400 max threads)
[OK] Maximum possible memory usage: 6.4G (40% of installed RAM)
[OK] Slow queries: 0% (1M/558M)
[OK] Highest usage of available connections: 8% (32/400)
[OK] Key buffer size / total MyISAM indexes: 512.0M/68.0K
[OK] Key buffer hit rate: 99.5% (3B cached / 18M reads)
[OK] Query cache efficiency: 50.9% (113M cached / 222M selects)
[!!] Query cache prunes per day: 36136
[OK] Sorts requiring temporary tables: 0% (94K temp sorts / 11M sorts)
[!!] Joins performed without indexes: 1100276
[OK] Temporary tables created on disk: 1% (469K on disk / 24M total)
[OK] Thread cache hit rate: 99% (32 created / 33M connections)
[!!] Table cache hit rate: 7% (512 open / 6K opened)
[OK] Open file limit used: 0% (9/4K)
[OK] Table locks acquired immediately: 100% (215M immediate / 215M locks)
[!!] InnoDB data size / buffer pool: 2.2G/1.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 512)
innodb_buffer_pool_size (>= 2G)innodb_buffer_pool_size を大きくしたら良い等ということがわかる。
