MySQLのパラメータチューニング

« 年賀状の話題から古物営業法へ | トップページ | 文字サイズ変更ボタンをつけてみた。 »

2007年12月25日

MySQLのパラメータチューニング

PostgreSQLのパラメータチューニングで、PostgreSQLのパラメータチューニングをやってみたので、MySQLのパラメータについても調べてみた。

まずはじめにMySQLのパラメータ設定を行うために、理解しておく必要があるのが、バッファには「グローバル」と「スレッド」の2種類があり、「グローバル」は、mysql全体で1つ確保されるだけだが、「スレッド」は、スレッド単位（コネクション単位）で1つ確保される。このパラメータはグローバルなのか？スレッドなのか？をしっかり意識する必要があるということをまず認識する必要がある。

5分でできる、MySQLのメモリ関係のチューニング！（魚拓）

バッファには2つのタイプがあります。

* グローバルバッファ
* スレッドバッファ

グローバルバッファはmysqld全体でそのバッファが1つだけ確保されるもので、これに対し、スレッドバッファはスレッド(コネクション)ごとに確保されるものです。

チューニングの際にはグローバル/スレッドの違いを意識するようにしましょう。なぜなら、スレッドバッファに多くのメモリを割り当てると、コネクションが増えたとたんにアッという間にメモリ不足になってしまうからです。

次に認識する必要があるのが、「MyISAM」と「InnoDB」によって設定するパラメータが異なるということ。インデックスバッファ、データバッファ、ログバッファがどのパラメータで設定できるのかをチェックすること。以下のサイトに表でうまくまとまっている。

MyISAMとInnoDBの違い（魚拓）

MyISAM InnoDB

インデックスバッファ key_buffer innodb_buffer_pool_size

データバッファ OSのブロックバッファ innodb_buffer_pool_size

ログバッファなし innodb_log_buffer_size

以下は、MySQLに関する参考になるエントリーをいくつか書いてくれているfutsu-9さんがいいリファレンスになるとして英語エントリーを和訳したもの。実メモリがどのくらいの時の設定値なのかが書かれていないので、想像するしかないが、512MB～1GBくらいの感じがする。

MyISAM利用時に参考となるパラメータ値（魚拓）

グローバルバッファ
  ・key_buffer = 128M
  ・query_cache_size = 128MB
  ・query_cache_limit = 4MB
  ・table_cache = 512
  ・delay_key_write = 1

スレッドバッファ
  ・sort_buffer_size = 32M
  ・myisam_sort_buffer_size = 32M
  ・tmp_table_size = 64MB
  ・wait_timeout = 60

futsu-9さんのMyISAMとkey_bufferも読んでおくと参考になりそう。

次は、さきほども紹介したKLabさんが、実メモリ4GBのDBサーバで実際に設定している設定値で、上記のMyISAM利用時に参考となるパラメータ値と比べると、スレッドバッファの値が抑え目にしてある。

InnoDB利用時に参考となるパラメータ値（魚拓）

グローバルバッファ
  ・innodb_buffer_pool_size=512MB
  ・innodb_additional_mem_pool_size=20MB
  ・innodb_log_buffer_size=16MB
  ・innodb_log_file_size=128MB
  ・key_buffer_size=MyISAMを使ってないのならば小さくてもOK

スレッドバッファ
  ・sort_buffer_size=2MB
  ・read_rnd_buffer_size=1MB
  ・join_buffer_size=256KB
  ・read_buffer_size=1MB
  ・myisam_sort_buffer_size=1MB

futsu-9さんのInnoDBを使うときのパフォーマンスチューニング（魚拓）というのもある。この中では、現状分析からチューニングの流れがあるので、パフォーマンスが悪いときにチェックすると役に立つかもしれない。

初期設定で設定しておいた方がいいかなと思ったのが、InnoDBのIO方式。

innodb_flush_methodで指定する
Linuxの場合O_DIRECTを使うとOSとMySQLで二重のバッファしなくて効率的

ただ、futsu-9さんによるInnoDBのflush_methodによる違い（魚拓）というエントリーもある。これによると・・・

結論としては、0.1%ぐらいしか差はありませんでした、と。この設定に変える効用はむしろカーネルとアプリ側でメモリを二重に確保してしまうことによる無駄を回避することなので単純な書き込み性能は劣化しませんよという程度。InnoDBしか使わないのであれば、 innodb_buffer_pool_sizeをメモリいっぱいまで確保してやるという手もある。

とのこと。
やっぱりサーバやアプリケーションによってどの設定値が正しい（有効）かは変わってくるので、このパラメータをコピペして使うだけというのはいけない。
いくつかの設定値を変更させならがテストしたり、運用しながら設定値を変えていく必要があると思う。もちろん時間のかかる重い処理などではそのスレッドのみ設定値を変更するとかも考える必要があるのかもしれない。

迷ったら基本に立ち戻って、「MySQL AB」をチェックしてみよう！

MySQL AB :: MySQL 5.1 リファレンスマニュアル :: 4.2.3 システム変数
 MySQL AB :: MySQL 5.1 リファレンスマニュアル :: 13.5.11 InnoDB パフォーマンスチューニング
 MySQL AB :: MySQL 5.0 Reference Manual :: 5.1.3 System Variables

以下、参考サイト
drk7.jp - MySQL のパラメータチューニング
 【MySQLウォッチ】第14回サーバー設定を見直してMySQLの性能を引き出す

トラックバック

このエントリーのトラックバックURL:
http://shoulder.jp/mt/shoulder.cgi/7545

Shoulder.jp

ブログする男は秒きざみでずるくなる。

2007年12月25日