MySQL リファレンスマニュアル :: 5.5.2 サーバパラメータのチューニング

mysqld サーバで使用されるデフォルトのバッファサイズは次のコマンドで確認できます。

shell> mysqld --help

このコマンドによって、mysqld オプションと設定可能な変数すべての一覧が生成されます。この出力には、デフォルトの変数値も記載され、以下のように表示されます。

back_log                 current value: 5
bdb_cache_size           current value: 1048540
binlog_cache_size        current value: 32768
connect_timeout          current value: 5
delayed_insert_timeout   current value: 300
delayed_insert_limit     current value: 100
delayed_queue_size       current value: 1000
flush_time               current value: 0
interactive_timeout      current value: 28800
join_buffer_size         current value: 131072
key_buffer_size          current value: 1048540
lower_case_table_names   current value: 0
long_query_time          current value: 10
max_allowed_packet       current value: 1048576
max_binlog_cache_size    current value: 4294967295
max_connections          current value: 100
max_connect_errors       current value: 10
max_delayed_threads      current value: 20
max_heap_table_size      current value: 16777216
max_join_size            current value: 4294967295
max_sort_length          current value: 1024
max_tmp_tables           current value: 32
max_write_lock_count     current value: 4294967295
myisam_sort_buffer_size  current value: 8388608
net_buffer_length        current value: 16384
net_retry_count          current value: 10
net_read_timeout         current value: 30
net_write_timeout        current value: 60
read_buffer_size         current value: 131072
read_rnd_buffer_size     current value: 262144
slow_launch_time         current value: 2
sort_buffer              current value: 2097116
table_cache              current value: 64
thread_concurrency       current value: 10
tmp_table_size           current value: 1048576
thread_stack             current value: 131072
wait_timeout             current value: 28800

現在実行中の mysqld サーバがある場合は、次のステートメントで変数に実際に使用されている値を調べることができます。

mysql> SHOW VARIABLES;

また、次のステートメントでは、実行中のサーバの統計やステータスインジケータを調べることができます。

mysql> SHOW STATUS;

すべての変数の詳細説明については、本マニュアルの SHOW VARIABLES セクションを参照してください。 See 項4.6.8.4. 「SHOW VARIABLES」。 ステータス変数詳細については、項4.6.8.3. 「SHOW STATUS」 を参照してください。

サーバ変数とステータス情報は、mysqladmin でも入手できます。

shell> mysqladmin variables
shell> mysqladmin extended-status

MySQL は非常にスケーラブルなアルゴリズムを使用しているため、通常は実行時のメモリ消費が非常に小さくなります。しかし、MySQL に対するメモリを多く割り当てると、通常はパフォーマンスが向上します。

MySQL サーバをチューニングする際に使用される最も重要な変数は key_buffer_size と table_cache の 2 つです。他の変数の変更を行う前にこの変数をあらかじめ適切に設定しておくことで自信がつきます。

以下に典型的な変数を実行時に設定している例を示します。この例は mysqld_safe スクリプトを使用し、--name=value 構文で変数 name を値 value に設定しています。この構文は、MySQL 4.0 から利用できます。旧バージョンの MySQL の場合は、以下の相違点を考慮してください。

最小 256M のメモリで多数のテーブルがあり、中程度のクライアントで最大のパフォーマンスを得るには、次のように使用します。

shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
           --sort_buffer_size=4M --read_buffer_size=1M &

メモリが 128M で、テーブルは少数で大量のソートの実行が必要な場合は、次のように使用できます。

shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M

メモリがほとんどなく大量の接続がある場合は、次のように使用します。

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
           --read_buffer_size=100K &

また、次のようにもできます。

shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
           --table_cache=32 --read_buffer_size=8K -O net_buffer_length=1K &

使用可能メモリより大幅に大きいテーブルで GROUP BY または ORDER BY を実行する場合は read_rnd_buffer_size の値を大きくしてソート操作後のレコードの読み取りの速度を上げる必要があります。

MySQL をインストールしたときは、support-files ディレクトリに複数の my.cnf サンプルファイルの、my-huge.cnf、my-large.cnf、my-medium.cnf、および my-small.cnf が格納され、システム最適化のベースとして使用できます。

同時接続が非常に多い場合、接続ごとに mysqld で使用されるメモリを非常に小さくしていないとスワップの問題が発生することがあります。言うまでもなく、すべての接続に使用可能なメモリが十分ある場合は mysqld のパフォーマンスが向上します。

mysqld または mysqld_safe のコマンドラインでオプションを指定した場合、そのサーバの呼び出しでしか有効性が保持されないことに注意してください。 サーバ実行のたびにオプションを使用する場合は、オプション設定ファイルに配置します。

パラメータ変更の有効性を調べるには、次のように実行します。

shell> mysqld --key_buffer_size=32m --help

必ず --help を最後に指定します。最後にしないと、コマンドラインのそれ以降に記載されたオプションの効果が出力に反映されません。