概要MySQLのメモリ関係のシステム変数について、まとめてみました。wrapperメモリサイズの見積もりはじめに、MySQLのプロセスが必要とするメモリサイズの見積もり方法を確認します。以下の方法で計算します。max_heap_table_sizeは必ずしも割り当てられる訳ではありませんが、安全側に倒すため、計算に含めています。以下の方法で計算します。myisam_sort_buffer_sizeは通常のクエリでは使われないので、計算に含めていません。パケットメッセージバッファは必ずしも最大まで拡張される訳ではありませんが、安全側に倒すため、max_allowed_packetで計算しています。大きなパケットを送受信しないのであれば、代わりにnet_buffer_lengthを使うと(やや安全性が犠牲になりますが)より実際に近い値になるかもしれません。--helpに表示された値をもとにしています。デフォルトのまま解説key_buffer_sizeキーバッファはMyISAMのインデックスをキャッシュするメモリ上の領域です。このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上します。キーバッファには空きメモリの30%と、比較的小さな数字を割り当てます。これはMyISAMがデータのキャッシュにはファイルシステムのキャッシュを使用し、キーバッファにはインデックスしかキャッシュしないためです。innodb_buffer_pool_sizeInnoDBバッファプールはInnoDBのデータとインデックスをキャッシュするメモリ上の領域です。このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上します。InnoDBバッファプールを最大限に使う場合、ダブルキャッシュを回避するためにinnodb_flush_methodをO_DIRECTにします。InnoDBでは最も重要なパラメータです。innodb_additional_mem_pool_sizeInnoDBアディショナルメモリプールはInnoDBのデータディクショナリ情報(InnoDBテーブルの定義情報など)をキャッシュするメモリ上の領域です。この領域が不足するとOSのメモリ領域から追加で割り当てられるとともに、エラーログに警告が出力されます。最初は大きな値を割り当てず、エラーログに警告が出力されたら増やします。テーブル数が多い場合は少し増やしておいても良いでしょう。innodb_log_buffer_sizeInnoDBログバッファはInnoDBの更新ログ(トランザクションログ)を記録するメモリ上の領域です。単一トランザクションのサイズが大きい場合、このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上することがあります。一般的には、コミット(ロールバック)したとき、あるいは一定時間毎にディスクにフラッシュされるため、8MB以上に大きくしても効果は少ないと言われています。max_heap_table_sizeMEMORYテーブル(旧HEAPテーブル)はMEMORYストレージエンジンによってメモリ上に作成されるテーブルです。TABLE文を実行したときに適用されるため、max_heap_table_sizeを変更しても既存のMEMORYテーブルには影響ありません)。理由がなければtmp_table_sizeと同じ値にしておきます。tmp_table_sizeテンポラリテーブルはその接続でのみ現れ、接続が終了するとドロップされる一時的なテーブルです。テンポラリテーブルはそのサイズがtmp_table_size以下であればMEMORYテーブルとしてメモリ上に作成され、tmp_table_sizeを超えるとISAMテーブルとしてディスクに書き出されます。副問い合わせを含むような複雑なクエリを実行している場合、このパラメータを大きくするとテンポラリテーブルがMEMORYテーブルとして作成されるため、パフォーマンスが向上することがあります。MEMORYテーブルの大きさはmax_heap_table_sizeによっても制約されるため、tmp_table_sizeを大きくする場合は同時にmax_heap_table_sizeも大きくします。query_cache_sizeクエリキャッシュはSELECTクエリのテキストと結果をキャッシュするメモリ上の領域です。テーブルの更新が少なく、かつ同じクエリを繰り返す場合、このパラメータを大きくするとクエリの解析と実行をスキップできるため、パフォーマンスが向上します。テーブルの更新が多く、あるいは同じクエリを繰り返さない場合、オーバーヘッドによりパフォーマンスが低下(MySQLのリファレンスによると、最悪のシナリオでのオーバーヘッドは13%程度)することもあります。max_connections最大同時接続数は一般ユーザーが同時に接続できる最大数です。1であり、+1は管理ユーザーのために予約されています。TABLE文がMyISAMテーブルのインデックスを作成するときに使うメモリ上の領域です。DMLでは使われないので、大きな値を割り当てる必要はありません。sort_buffer_sizeソートバッファはファイルソートを実行するときに使うメモリ上の領域です。sort_buffer_sizeを超えた分はテンポラリファイルに書き出され、ソートが実行されます。大きなデータをソートする場合、このパラメータを大きくするとメモリ上でソートが実行されるため、パフォーマンスが向上します。OLTP系では概ねデフォルト(2MB)のままで問題ありません。DWH系で大きなデータをソートする場合、セッションごとに動的に調整すると良いでしょう。read_rnd_buffer_sizeこのバッファはインデックスによるソート(ファイルソートではない)を実行するとき、ソートしたレコードの読み出しに使うメモリ上の領域です。大きなデータをフェッチする場合、このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上します。OLTP系ではデフォルト(256KB)のままで問題ありません。DWH系で大きなデータをフェッチする場合、セッションごとに動的に調整すると良いでしょう。join_buffer_sizeJOINバッファはインデックスを使わない結合に使うメモリ上の領域です。これによりフルテーブルスキャンが実行されます。このパラメータを大きくするとメモリ上で結合が実行されるため、パフォーマンスが向上します。そもそもインデックスを使わないテーブル結合は避けるべきであり、このパラメータを操作するよりも先にインデックスの利用を検討するべきです。read_buffer_sizeリードバッファはインデックスを使わないテーブルスキャンに使うメモリ上の領域です。このパラメータを大きくするとメモリ上でテーブルスキャンが実行されるため、パフォーマンスが向上します。これもパフォーマンスを考えるならば、このパラメータを操作するよりも先にインデックスの利用を検討するべきです。OLTP系ではあまり大きな値は必要ありません。net_buffer_lengthmax_allowed_packetを参照max_allowed_packetパケットメッセージバッファは送受信するパケットを格納するメモリ上の領域です。net_buffer_lengthで初期化され、必要に応じてmax_allowed_packetまで拡張されます。クライアントが実行できるSQL文の最大長はmax_allowed_packetによって制限されます。一般的にはもっとも大きいBLOBまたはTEXTと同じサイズにします。thread_stackスレッドスタックは各スレッドがコールスタックとして使うメモリ上の領域です。一般的にはデフォルトのままで問題ありません。Log)と呼ばれるもので、コミットされたトランザクションをテーブルスペースに反映する前に、いったんすべて書き出しておくためのファイルです。伴う、とてもコストのかかる処理です。InnoDBログファイルへの書き込みはトランザクションログをシーケンシャルに書き込むため、ずっと少ないコストで実現できます)。このパラメータを大きくするとテーブルスペースへの反映が少なくなるため、パフォーマンスが向上します。InnoDBログファイルの大きさに比例してクラッシュリカバリにかかる時間が長くなる点には注意が必要です。InnoDBログファイルの大きさは以下の制約を受けます。innodb_buffer_pool_sizeinnodb_log_files_in_groupこの値はInnoDBログファイルの数を意味します。thread_cache_sizeスレッドキャッシュは接続終了後のサーバースレッドを解放せず、次の接続時に再利用できるようにキャッシュするメモリ上の領域です。値はキャッシュに保持するスレッドの最大数を意味します。最大同時接続数が多い場合、このパラメータを大きくすると再接続時のオーバーヘッドが軽減されるため、パフォーマンスが向上します。table_open_cacheテーブルキャッシュは接続終了後もテーブルをメモリ上に維持しておき、次の接続時に再利用できるようにキャッシュするメモリ上の領域です。値はキャッシュに保持するテーブルの最大数を意味します。5.1以前はtable_cacheという名前でした。最大同時接続数が多い場合、このパラメータを大きくすると再接続時のオーバーヘッドが軽減されるため、パフォーマンスが向上します。注意すべきは、MySQLは同じテーブルに対して同時にアクセスするスレッドがあった場合、それぞれが同じテーブルを重複して開く、ということです。することによってマルチスレッド環境でのパフォーマンスを向上させています。そのため、MySQLが開くテーブルの最大数は最低でもmax_connectionsと同数になります。n(nはjoinによって結合されるテーブル数)が最大数となります。wait_timeoutタイムアウト待ち時間は反応のない接続を終了する前に、サーバーが待機する時間です。最後の実行からこの秒数が経過すると、サーバーは接続を終了します。この値はTCP/IPとUnixソケットによる接続だけに適用されます。binlog_cache_sizeバイナリログキャッシュは未コミットのトランザクション情報(単一トランザクションの中で実行されたSQLステートメント)をキャッシュするメモリ上の領域です。値はキャッシュに保持するSQL文の最大数を意味します。InnoDBなどトランザクションに対応したストレージエンジンを使用し、かつバイナリログを有効にしている場合にのみ割り当てられ、バイナリログファイルへの出力はトランザクションがコミットされるまでバイナリログキャッシュに保留されます。トランザクション情報がbinlog_cache_sizeを超えると、スレッドはトランザクション情報を保存するためにテンポラリテーブルを作成します。大きなトランザクションを実行する場合、このパラメータを大きくするとテンポラリテーブルの作成を回避できるため、パフォーマンスが向上します。9.1.2をインストールします。アーカイブはてなブログをはじめよう!tetsuyaiさんは、はてなブログを使っています。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。