WR250RとRMX250Sのおふろーど日記

mysqltunerというのを発見したので使ってみました。
環境はCentOS5の吊るしの環境です。

# wget http://mysqltuner.com/mysqltuner.pl
# perl mysqltuner.pl --help
MySQLTuner 1.0.1 - MySQL High Performance Tuning Script
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Maintained by Major Hayden (major@mhtx.net) - Licensed under GPL

Important Usage Guidelines:
To run the script with the default options, run the script without arguments
Allow MySQL server to run for at least 24-48 hours before trusting suggestions
Some routines may require root level privileges (script will provide warnings)
You must provide the remote server's total memory when connecting to other servers

Connection and Authentication
--host <hostname> Connect to a remote host to perform tests (default: localhost)
--socket <socket> Use a different socket for a local connection
--port <port> Port to use for connection (default: 3306)
--user <username> Username to use for authentication
--pass <password> Password to use for authentication

Performance and Reporting Options
--skipsize Don't enumerate tables and their types/sizes (default: on)
(Recommended for servers with many tables)
--checkversion Check for updates to MySQLTuner (default: don't check)
--forcemem <size> Amount of RAM installed in megabytes
--forceswap <size> Amount of swap memory configured in megabytes

Output Options:
--nogood Remove OK responses
--nobad Remove negative/suggestion responses
--noinfo Remove informational responses
--nocolor Don't print output in color

#perl mysqltuner.pl

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 463M (Tables: 84)
[--] Data in InnoDB tables: 73M (Tables: 10)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 5

-------- Performance Metrics -------------------------------------------------
[--] Up for: 42d 15h 22m 52s (390K q [0.106 qps], 5K conn, TX: 165M, RX: 35M)
[--] Reads / Writes: 13% / 87%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (25% of installed RAM)
[OK] Slow queries: 0% (216/390K)
[OK] Highest usage of available connections: 21% (21/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/278.3M
[OK] Key buffer hit rate: 96.7% (342M cached / 11M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (75 temp sorts / 13K sorts)
[OK] Temporary tables created on disk: 14% (2K on disk / 16K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 36% (64 open / 173 opened)
[OK] Open file limit used: 9% (102/1K)
[OK] Table locks acquired immediately: 99% (349K immediate / 349K locks)
[!!] InnoDB data size / buffer pool: 73.3M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
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
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 73M)

というわけでmy.cnfをお勧め設定にしてみます。