MySQLのチューニング

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

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

shell> mysqld --help

下記のように表示される変数の値を確認し、調整します。
innodb_force_recovery             0
interactive_timeout               28800
join_buffer_size                  131072
key_buffer_size                   8388572
long_query_time                   10
lower_case_table_names            TRUE
max_allowed_packet                1048576
max_binlog_cache_size             4294967295
max_binlog_size                   1073741824
max_connections                   100
max_connect_errors                10
max_delayed_threads               20
max_heap_table_size               16777216
max_join_size                     4294967295
max_relay_log_size                0
max_seeks_for_key                 4294967295
max_sort_length                   1024
max_tmp_tables                    32
max_user_connections              0
max_write_lock_count              4294967295
bulk_insert_buffer_size           8388608
myisam_block_size                 1024
myisam_max_extra_sort_file_size   268435456
myisam_max_sort_file_size         2147483647
myisam_repair_threads             1
myisam_sort_buffer_size           8388608
net_buffer_length                 16384
net_retry_count                   10
net_read_timeout                  30
net_write_timeout                 60
open_files_limit                  0
query_alloc_block_size            8192
query_cache_limit                 1048576
query_cache_size                  0
query_cache_type                  1
query_prealloc_size               8192
read_buffer_size                  131072
read_rnd_buffer_size              262144
record_buffer                     131072
relay_log_space_limit             0
slave_compressed_protocol         FALSE
slave_net_timeout                 3600
range_alloc_block_size            2048
read-only                         FALSE
slow_launch_time                  2
sort_buffer_size                  2097116
table_cache                       64
thread_concurrency                10
thread_cache_size                 0
tmp_table_size                    33554432
thread_stack                      196608
transaction_alloc_block_size      8192
transaction_prealloc_size         4096
wait_timeout                      28800
default-week-format               0

変数の値は
shell> mysqld_safe --key_buffer_size=64M --table_cache=256            --sort_buffer_size=4M --read_buffer_size=1M &

のようにコマンドで指定するかmy.cnfファイルで指定します。

バッファサイズ、長さ、およびスタックサイズの値はバイト単位です。
`K' または `M' を値に付けると、それぞれキロバイト、メガバイトを示します。
my.cnfには下記のように指定します。(*注)
[client]
# ここから クライアント全てに対するパラメターの設定を指定します

#[safe_mysqld]
# safe_mysqld に与えるオプション

#[mysqld_safe]

[mysqld]
# ここから サーバーに対するパラメターの設定を指定します

set-variable = key_buffer=32M
set-variable = table_cache=256

[mysql]
# password=my_password で、mysql コマンドに接続する場合に使用します

password=my_password

#[mysqldump]

#[odbc]

下記はMySQLサーバをチューニングする際に使用される変数のうちの主要なものです。

■key_buffer
検索に使われるインデックスをバッファに保存する際のメモリサイズです。
メモリに余裕がある場合増やすとパフォーマンスが向上します。
例)
key_buffer=256M

■table_cache
データのキャッシュサイズです。
値を大きくすることでディスクのI/Oが減りパフォーマンスが向上します。
例)
table_cache=256

■max_allowed_packet
入力データ保持のための最大バッファサイズです。
画像など、大きなデータ挿入をおこなう際に増やします。
例)
max_allowed_packet=1M

■sort_buffer
ソートをおこなう際に使用するバッファサイズです。
値を大きくすることでORDER BYやGROUP BYをクエリーに用いた場合のパフォーマンスが向上します。
例)
sort_buffer=1M

■record_buffer
値を大きくすることでインデックスを含まないクエリーの実行速度が上がります。
例)
record_buffer=1M

■long_query_time
mysqladmin statusコマンドで表示されるSlow queriesにカウントされる場合のしきい値(単位は秒)を指定します。
例)
long_query_time=1

■join_buffer
大量のjoin が発行される場合はjoin_bufferを増やします。
例)
join_buffer_size = 131072

(*注) --- my.cnfの読み込み順
後から読まれたほうのファイルの設定で、前の設定は上書きされます。

Linuxの場合

/etc/my.cnf

DATADIR/my.cnf

~/.my.cnf
の順に上書きされます。


Windowsの場合

windowsシステムディレクトリmy.ini

C:my.cnf

C:mysqldatamy.cnf
の順に上書きされます。

MySQL関連記事一覧


※記事内容は執筆時点のものです。最新の内容をご確認ください。
※OSやアプリ、ソフトのバージョンによっては画面表示、操作方法が異なる可能性があります。