MySQLのパフォーマンスチューニングに重要そうなパラメーターの解説の続き。
sort_buffer_size
ORDER BYやGROUP BYなどのソートの処理をする時に割り当てられるバッファーです。
クエリーが、バッファーメモリを割り当てる必要がある場合に、このパラメーターに指定したメモリ量が、割り当てられる。
コネクションごとに、メモリが割り当てられるので、あまり大きな値を割り当てすぎるとサーバーのメモリを使い果たしてしまう可能性がある。
大量に割り当てても性能が上がるというものではないのでデフォルトの値の２倍ぐらいを割り当てておけばいいと思います。
read_buffer_size
インデックスを使用しないテーブルスキャンのときに割り当てられるバッファーです。
クエリーが、バッファーメモリを割り当てる必要がある場合に、このパラメーターに指定したメモリ量が、割り当てられる。
コネクションごとに、メモリが割り当てられるので、あまり大きな値を割り当てすぎるとサーバーのメモリを使い果たしてしまう可能性がある。
大量に割り当てても性能が上がるというものではないのでデフォルトの値の２倍ぐらいを割り当てておけばいいと思います。
read_rnd_buffer_size
ソートの実行後に、ソートした順番でテーブルから行を読み込む際に使用されるバッファーです。ORDER BYの性能の向上が期待できる。
クエリーが、バッファーメモリを割り当てる必要がある場合に、このパラメーターに指定したメモリ量を上限値として必要な分だけ、割り当てられる。
コネクションごとに、メモリが割り当てられるので、あまり大きな値を割り当てすぎるとサーバーのメモリを使い果たしてしまう可能性がある。
大量に割り当てても性能が上がるというものではないのでデフォルトの値の２倍ぐらいを割り当てておけばいいと思います。
join_buffer_size
インデックスを使用しないテーブルの結合のときに割り当てられるバッファーです。
クエリーが、バッファーメモリを割り当てる必要がある場合に、このパラメーターに指定したメモリ量が、割り当てられる。
コネクションごとに、メモリが割り当てられるので、あまり大きな値を割り当てすぎるとサーバーのメモリを使い果たしてしまう可能性がある。
大量に割り当てても性能が上がるというものではないのでデフォルトの値の２倍ぐらいを割り当てておけばいいと思います。
query_cache_type
0の場合、クエリーキャッシュを使用しない。
1の場合、SELECT SQL_NO_CACHE構文を使用しない限り、クエリーキャッシュを使用する。
2の場合、SELECT SQL_CACHE構文を使用しない限り、クエリーキャッシュを使用しない。
query_cache_size
クエリーの実行内容を、キャッシュするためのバッファーです。余計なクエリーの発行をしないようにすることでパフォーマンスの向上をすることができる。
場合によっては、下記のような理由で性能が落ちる可能性がある。
データーを、DBから読み込む際に、キャッシュの内容をチェックする必要があるので読み込みのオーバーヘッドが発生する。
データーを、DBに書き込む際にキャッシュの内容のチェックをする必要があるので、キャッシュ量の割り当て過ぎとキャッシュの断片化が発生しているばあい、書き込みのパフォーマンスが落ちる。
クエリーのヒット率が、30%以上あれば、クエリーの発行のコストを考慮してもクエリーキャッシュを有効にする価値があるようです。
クエリーキャッシュのヒット率の算出式は下記のようになります。
mysqlにログインする。
$ mysql -u ユーザー名 -p
次に下記のコマンドを実行して、キャッシュ率の算出に必要なパラメーターの出力をする。
mysql> SHOW STATUS LIKE ‘Qcache%’;
Qcache_free_blocks 33114
Qcache_free_memory 102841120
Qcache_hits 831209037
Qcache_inserts 255767189
Qcache_lowmem_prunes 87894271
Qcache_not_cached 388777325
Qcache_queries_in_cache 84753
Qcache_total_blocks 220151
出力結果を元に下記の計算式を使用して、ヒット率を計算する。
ヒット率 = Qcache_hit / (Qcache_hit + Qcache_inserts + Qcache_not_cached) * 100
出力結果をもとに計算をすると
56.23 ≒ 831209037/(831209037+255767189+388777325)*100
となり、ヒット率は、56.23%となるのでクエリーキャッシュを有効にしたほうが性能が出るといえる。