mysqltunerを使ってみた

おもしろそうなので使ってみた。

尋常じゃなさそうなのは、メモリを最大でこれぐらい食いますよっていう項目なんだけど、

[!!] Maximum possible memory usage: 36.7G (37557% of installed RAM)

これ。搭載メモリの37000パーセントって、370倍ってことでしょうか。でもこれ8Gぐらい積んでるはずなので、300倍ってこたーないでしょう。

%mysqltuner.pl –host db.l2tp.org

>>  MySQLTuner 1.0.0 – Major Hayden <[email protected]>

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

>>  Run with ‘–help’ for additional options and output filtering

[!!] The –forcemem option is required for remote connections

reagan.l3tp.org%mysqltuner.pl –host buchanan.l3tp.org –forcemem

Option forcemem requires an argument

>>  MySQLTuner 1.0.0 – Major Hayden <[email protected]>

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

>>  Run with ‘–help’ for additional options and output filtering

[!!] The –forcemem option is required for remote connections

reagan.l3tp.org%mysqltuner.pl –host buchanan.l3tp.org –forcemem 100

>>  MySQLTuner 1.0.0 – Major Hayden <[email protected]>

>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

>>  Run with ‘–help’ for additional options and output filtering

[–] Performing tests on buchanan.l3tp.org:3306

Please enter your MySQL administrative login: yousan

Please enter your MySQL administrative password:

[–] Assuming 100 MB of physical memory

[!!] Assuming 0 MB of swap space (use –forceswap to specify)

——– General Statistics ————————————————–

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.1.33-log

——– Storage Engine Statistics ——————————————-

[–] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[–] Data in MyISAM tables: 9G (Tables: 157)

[!!] Total fragmented tables: 22

——– Performance Metrics ————————————————-

[–] Up for: 21d 23h 44m 19s (304M q [160.050 qps], 10M conn, TX: 28194B, RX: 40B)

[–] Reads / Writes: 88% / 12%

[–] Total buffers: 432.0M global + 12.4M per thread (3000 max threads)

Use of uninitialized value in string eq at /usr/local/bin/mysqltuner.pl line

647, <> line 2 (#1)

(W uninitialized) An undefined value was used as if it were already

defined.  It was interpreted as a “” or a 0, but maybe it was a mistake.

To suppress this warning assign a defined value to your variables.

To help you figure out what was undefined, perl tells you what operation

you used the undefined value in.  Note, however, that perl optimizes your

program and the operation displayed in the warning may not necessarily

appear literally in your program.  For example, “that $foo” is

usually optimized into “that ” . $foo, and the warning will refer to

the concatenation (.) operator, even though there is no . in your

program.

[!!] Maximum possible memory usage: 36.7G (37557% of installed RAM)

[OK] Slow queries: 0% (678K/304M)

[OK] Highest usage of available connections: 34% (1040/3000)

[OK] Key buffer size / total MyISAM indexes: 384.0M/2.8G

[OK] Key buffer hit rate: 99.9% (45B cached / 50M reads)

[OK] Query cache efficiency: 49.5% (109M cached / 221M selects)

[!!] Query cache prunes per day: 426273

[OK] Sorts requiring temporary tables: 0% (23K temp sorts / 10M sorts)

[!!] Temporary tables created on disk: 42% (8M on disk / 19M total)

[OK] Thread cache hit rate: 97% (215K created / 10M connections)

[!!] Table cache hit rate: 2% (512 open / 18K opened)

[OK] Open file limit used: 5% (624/11K)

[OK] Table locks acquired immediately: 99% (208M immediate / 208M locks)

——– Recommendations —————————————————–

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Reduce your overall MySQL memory footprint for system stability

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

Variables to adjust:

*** MySQL’s maximum memory usage is dangerously high ***

*** Add RAM before increasing MySQL buffer variables ***

query_cache_size (> 32M)

tmp_table_size (> 16M)

max_heap_table_size (> 16M)

table_cache (> 512)

http://ambiesoft.ddo.jp/blog/archives/723

コメントを残す