2006年12月26日

5分でできる、MySQLのメモリ関係のチューニング!

はてなブックマークに登録

MySQLのチューニングにおいて非常に重要となるメモリ(バッファ)関連のパラメータについて、

  • チューニングのポイント
  • DSASのとあるDBサーバ(実メモリ4GB)の実際の設定値

をまとめてみます。
また、必要メモリの総量の計算や限界値を越えてないかチェックしてくれるスクリプトも紹介します。
是非、参考にしてみてください!


まず最初に注意点を。
バッファには2つのタイプがあります。

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

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

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


innodb_buffer_pool_size

用途
InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域です。
バッファ種別
グローバル
DSAS値
512MB

グローバルバッファなので、どかんと割り当ててください。
MySQL ABのドキュメントには、搭載メモリの80%とも書かれています。

innodb_additional_mem_pool_size

用途
InnoDBの内部データなどを保持するための領域です。
バッファ種別
グローバル
DSAS値
20MB

それほど大量に割り当てる必要はありません。足りなくなったらエラーログにその旨、警告が出ますので、そしたら増やせばいいのではないかと思います。

innodb_log_buffer_size

用途
InnoDBの更新ログを記録するメモリ上の領域です。
バッファ種別
グローバル
DSAS値
16MB

たいていは8MB、多くても64MBで十分で、あんまり多くする必要はありません。 なぜなら、バッファはトランザクションがCOMMITされるごと、または毎秒ディスクにフラッシュされるので、ほかのパラメータを厚くしたほうが得策です。

innodb_log_file_size

用途
InnoDBの更新ログを記録するディスク上のファイルです。
メモリではないのですがチューニングの上で重要なので解説します。
バッファ種別
---
DSAS値
128MB

大きくした方がパフォーマンスが向上します。
なぜなら、innodb_log_fileがいっぱいになると、メモリ上のinnodb_buffer_poolの中の更新された部分のデータを、ディスク上のInnoDBのデータファイルに書き出すしくみになっているからです。
ですので、innodb_buffer_pool_sizeを大きくしたら、innodb_log_file_sizeもあわせて調整しないとパフォーマンスが向上しません。

innodb_log_file_sizeの値は、1MB以上で、32bitマシンの場合は4GB以下にしなければならないとMySQL ABのドキュメントには書いてあります。
また、もうひとつ上限があります。innodb_log_fileはinnodb_log_files_in_groupの数(デフォルトは2)だけつくられるのですが、innodb_log_file_size×innodb_log_files_in_groupがinnodb_buffer_pool_sizeを越えてもいけません。
まとめるとこのようになります。

1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB

MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group

もうひとつ注意しなければならないのは、innodb_log_file_sizeを大きくすればするほど、InnoDBのクラッシュリカバリの時間が長くかかるようになるという点です。

sort_buffer_size

用途
ORDER BYやGROUP BYのときに使われるメモリ上の領域です。
バッファ種別
スレッド
DSAS値
2MB

スレッドバッファなので、むやみに大きくするとメモリが足りなくなるので注意です。
DSASでは2MBか4MBにしています。

read_rnd_buffer_size

用途
ソート後にレコードを読むときに使われるメモリ上の領域です。
ディスクI/Oが減るのでORDER BYの性能向上が期待できます。
バッファ種別
スレッド
DSAS値
1MB

これもスレッドバッファなので、割り当て過ぎには注意が必要です。
DSASでは512KB〜2MBにしています。

join_buffer_size

用途
インデックスを用いないテーブル結合のときに使われるメモリ上の領域です。
バッファ種別
スレッド
DSAS値
256KB

スレッドバッファです。
そもそもインデックスが使われないようなテーブル結合はパフォーマンス向上の観点からすると避けるべきなので、このパラメータはそれほど大きくする必要はないでしょう。

read_buffer_size

用途
インデックスを用いないテーブルスキャンのときに使われるメモリ上の領域です。
バッファ種別
スレッド
DSAS値
1MB

これも、パフォーマンスを考えるならば、インデックスを使うようなクエリを発行するべきなので、それほど多くする必要はないでしょう。

key_buffer_size

用途
MyISAMのインデックスをキャッシュするメモリ上の領域です。
バッファ種別
グローバル
DSAS値
256MB

グローバルバッファです。
多く割り当てた方がパフォーマンスが向上します。グローバルバッファなのでどかんと割り当てられます。
もし、MyISAMを(あまり)使ってないのならば、小さくしてほかのパラメータに回すのもアリです。

ちなみに、キーキャッシュのヒット率は、SHOW STATUSの値を使って、以下の式で算出できます。

キーキャッシュのヒット率 = 100 - ( key_reads / key_read_requests × 100 )

myisam_sort_buffer_size

用途
MyISAMで以下の時のインデックスのソートに使われるメモリ上の領域です。
  • REPAIR TABLE
  • CREATE INDEX
  • ALTER INDEX
バッファ種別
スレッド
DSAS値
1MB

通常のクエリ(DML)では使われないようなので、それほど多くする必要はないでしょう。


mymemcheck - メモリ関連のチェックツール

最後にmymemcheckという自家製のツールを紹介します。
ダウンロードはこちらから。

mymemcheckは、my.cnfもしくはSHOW VARIABLESの結果を元に、以下の3つのチェックを行います。

  • 最低限必要な物理メモリの大きさ
  • IA-32のLinuxでのヒープサイズの制限
  • innodb_log_file_sizeの最大サイズ

実行結果例はこのようになります。

$ ./mymemcheck my.cnf

[ minimal memory ]
ref
  * 『High Performance MySQL』, Solving Memory Bottlenecks, p125

global_buffers
  key_buffer_size                     268435456   256.000 [M]
  innodb_buffer_pool_size             536870912   512.000 [M]
  innodb_log_buffer_size               16777216    16.000 [M]
  innodb_additional_mem_pool_size      20971520    20.000 [M]
  net_buffer_length                       16384    16.000 [K]

thread_buffers
  sort_buffer_size                      2097152     2.000 [M]
  myisam_sort_buffer_size               1048576  1024.000 [K]
  read_buffer_size                      1048576  1024.000 [K]
  join_buffer_size                       262144   256.000 [K]
  read_rnd_buffer_size                  1048576  1024.000 [K]

max_connections                             250


min_memory_needed = global_buffers + (thread_buffers * max_connections)
                  = 843071488 + 5505024 * 250
                  = 2219327488 (2.067 [G])

[ 32bit Linux x86 limitation ]
ref
  * http://dev.mysql.com/doc/mysql/en/innodb-configuration.html

  * need to include read_rnd_buffer.
  * no need myisam_sort_buffer because allocate when repair, check alter.

          2G > process heap
process heap = innodb_buffer_pool + key_buffer
               + max_connections * (sort_buffer + read_buffer + read_rnd_buffer)
               + max_connections * stack_size
             = 536870912 + 268435456
               + 250 * (2097152 + 1048576 + 1048576)
               + 250 * 262144
             = 1919418368 (1.788 [G])

          2G > 1.788 [G] ... safe

[ maximum size of innodb_log_file_size ]
ref
  * http://dev.mysql.com/doc/mysql/en/innodb-start.html

  1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB

MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group
                         = 536870912 * 1/2
                         = 268435456 (256.000 [M])

    innodb_log_file_size < MAX_innodb_log_file_size
               134217728 < 268435456
             128.000 [M] < 256.000 [M] ... safe

いずれもMySQL ABのドキュメントなどに書かれている制限事項、そのままです。
パラメータ同士が関係しあっていることが多々あるので、パラメータを変更する際は、このようなチェックツールで確認するのがいいのではないかと思います。


参考


klab_gijutsu2 at 08:30│Comments(0)TrackBack(10)mysql 

トラックバックURL

この記事へのトラックバック

1. links for 2006-12-26  [ Project MultiBurst ]   2006年12月27日 04:26
ウェブデザインに便利なツールをまとめた「Web Design Tools」:phpspot開発日誌 (tags: CSS WebDesign To...
研究員の石橋利真です。こんにちは。 今月18日(2007/06/18)発売のSoftwareDesign誌2007年7月号の特集記事 「MySQL最前線2007」 にて、パフォーマンスチューニングの章を書かせていただきました。 チューニ...
3. [MySQL]5分でできたら困ります(笑)メモリ関連チューニング  [ 浅く広くをモットーに - WEBプログラマ メモ ]   2007年11月06日 12:00
http://dsas.blog.klab.org/archives/50860867.html こういった設定関連の記事は人気がありますよね。実際によくまとまっていて有益だと思います。ただし理解もせず盲目的に真似するのは御法度。 http://dev.mysql.com/doc/refman/4.1/ja/show-variables.html read_rnd_buff
4. MySQLのパラメータチューニング  [ Shoulder.jp ]   2007年12月25日 07:16
PostgreSQLのパラメータチューニングで、PostgreSQLのパラメータチューニングをやってみたので、MySQLのパラメータについても調べてみた。 まずはじめにMySQLのパラメータ設定を行うために、理解しておく必要があるのが、バッファには「グローバル」と「スレッド」の2種類が...
5. MySQLチューニング  [ K blog ]   2008年07月06日 14:41
MySQLのチューニングに役立つ情報
6. MySQLチューニング  [ ぬきすぽラボ ]   2008年08月26日 12:13
ふと思い立ってMySQLのチューニングをしてみようと思った。 サイトが重くなる理由っていくつかあるけど、DBを見直すと改善することも多々あるので 前もってやっておこうと思った。 my.cn...
7. Senna、MySQL 周りの「sugokuii beta」対処法的チューニング  [ ゆーすけべー日記 ]   2009年07月07日 11:06
昨日公開した「sugokuii beta」。 ある程度のトラフィックは予想していたものの、 深夜0時を超えた当たりから急に負荷が高くなった。 この深夜0時前後...
8. MySQLの「innodb_buffer_pool_size」と「innodb_log_file_size」の設定  [ FlatLabs Blog ]   2010年07月28日 10:24
innodb_log_file_sizeの設定を変更しようとして、MySQLが起動しないエラーに遭遇してしまって、あれ?となったんですが、原因がそういうえばそうだった。というとこだったりしたので、書いておきます。 「innodb_buffer_pool_size」とセットな感じの「innodb_log_file_size」...
9. [MySQL]MySQLにおける10の最適化設計  [ 憂鬱なプログラマの形而上学 ]   2010年08月23日 02:39
さて、ここでコーヒーブレークして、MySQLの最適化について考えてみたいと思います。MySQLデータベース分散処理では、SQLを複数のサーバーやテーブルへと分散する方法についてご紹介させて頂きましたが、こちらは主に、個々のDBサーバーの最適化について考えてみたいと思い
10. mysql  [ Pearltrees ]   2012年05月05日 19:41
Discover a selection of related articles on Pearltrees

この記事にコメントする

名前:
URL:
  情報を記憶: 評価: 顔   
 
 
 
このブログについて
DSASとは、KLab が構築し運用しているコンテンツサービス用のLinuxベースのインフラです。現在7ヶ所のデータセンタにて構築し、運用していますが、我々はDSASをより使いやすく、より安全に、そしてより省力で運用できることを目指して、日々改良に勤しんでいます。
このブログでは、そんな DSAS で使っている技術の紹介や、実験してみた結果の報告、トラブルに巻き込まれた時の経験談など、広く深く、色々な話題を織りまぜて紹介していきたいと思います。
KLabについて
KLab株式会社は、信頼性の高いクラウドサービス、ソフトウェアパッケージ、自社で企画・開発したソーシャルアプリやデジタルコンテンツを提供しています。
Blog内検索
最新コメント
最新トラックバック
Archives