MySQLTunerでMYSQLの設定を見直す。

[root@colinux tools]#wget mysqltuner.pl

HTTP request sent, awaiting response... 302 Found
Location: http://mysqltuner.pl/mysqltuner.pl [following]
--11:52:57-- http://mysqltuner.pl/mysqltuner.pl
=> `mysqltuner.pl'
Reusing existing connection to mysqltuner.pl:80.
HTTP request sent, awaiting response... 200 OK
Length: 38,688 (38K) [text/plain]

100%[====================================>] 38,688 71.29K/s

11:52:58 (71.28 KB/s) - `mysqltuner.pl' saved [38688/38688]

[root@colinux tools]# ls -l
total 40
-rw-r--r-- 1 root root 38688 2008-12-01 18:37 mysqltuner.pl
[root@colinux tools]# chmod 0700 mysqltuner.pl
[root@colinux tools]# ls -l
total 40
-rwx------ 1 root root 38688 2008-12-01 18:37 mysqltuner.pl
[root@colinux tools]# ./mysqltuner.pl

>> MySQLTuner 1.0.0 - Major Hayden
>> 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:

[hup]

[!!] Key buffer size / total MyISAM indexes: 16.0K/155.0K
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 18 total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 18% (4 open / 22 opened)
[OK] Open file limit used: 0% (3/1K)
[OK] Table locks acquired immediately: 100% (21 immediate / 21 locks)
[!!] Connections aborted: 37%
[OK] InnoDB data size / buffer pool: 144.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Your applications are not closing MySQL connections properly
Variables to adjust:
key_buffer_size (> 155.0K)
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 4)

mysqltunner

■■■ サーバー① ■■■

[root@localhost tools]# ./mysqltuner.pl
<code>
&gt;&gt; MySQLTuner 1.0.0 - Major Hayden
&gt;&gt; Bug reports, feature requests, and downloads at http://mysqltuner.com/
&gt;&gt; Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
</code>
[root@localhost tools]#

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.25-rc-log
[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: 24M (Tables:98)
[--] Data in InnoDB tables: 384K (Tables:8) 
[!!] Total fragmented tables: 28

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 48m 57s (6K q [0.646 qps], 323 conn, TX: 38M, RX: 739K)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 42.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 277.9M (27% of installed RAM)
[!!] Slow queries: 8% (526/6K)
[OK] Highest usage of available connections: 17% (27/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.3M
[OK] Key buffer hit rate: 99.6% (22K cached / 100 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 369 sorts)
[!!] Temporary tables created on disk: 43% (497 on disk / 1K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 21% (64 open / 299 opened)
[OK] Open file limit used: 9% (99/1K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[OK] InnoDB data size / buffer pool: 384.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (&gt;= 8M)
tmp_table_size (&gt; 16M)
max_heap_table_size (&gt; 16M)
thread_cache_size (start at 4)

■■■ サーバー② ■■■

[root@localhost ~]$ ./mysqltuner.pl
<code>
&gt;&gt; MySQLTuner 1.0.0 - Major Hayden
&gt;&gt; Bug reports, feature requests, and downloads at http://mysqltuner.com/
&gt;&gt; Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:</code>
[root@localhost ~]$

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 403M (Tables: 145)
[--] Data in InnoDB tables: 47M (Tables: 14)
[!!] Total fragmented tables: 13

-------- Performance Metrics -------------------------------------------------
[--] Up for: 101d 8h 30m 57s (4M q [0.466 qps], 695K conn, TX: 3B, RX: 256M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 90.0M global + 18.3M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.9G (47% of installed RAM)
[OK] Slow queries: 0% (5/4M)
[OK] Highest usage of available connections: 54% (54/100)
[OK] Key buffer size / total MyISAM indexes: 64.0M/276.2M
[OK] Key buffer hit rate: 96.6% (261M cached / 8M reads)
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 24% (45K temp sorts / 189K sorts)
[OK] Temporary tables created on disk: 2% (27K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (4 open / 675K opened)
[OK] Open file limit used: 0% (8/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 47.2M/8.0M

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
sort_buffer_size (> 63K)
read_rnd_buffer_size (> 15M)
thread_cache_size (start at 4)
table_cache (> 4)
innodb_buffer_pool_size (>= 47M)