MySQLをチューニング,そしてスケールアップ/スケールアウトへ

第6回 MySQLチューニング(5) パラメタチューニングの基礎

この記事を読むのに必要な時間:およそ 5 分

接続スレッド毎に確保されるメモリ

このカテゴリの代表的なパラメタはデータのソート時に使うメモリの領域,ソートバッファの最大値を設定するsort_buffer_sizeです。このパラメタのスコープはGlobalおよびSessionとなっており,サーバ全体に値を設定するだけではなく,現在接続中のセッションでの設定をSET文で一時的に変えることも可能です。

コマンドラインでの設定 -- sort_buffer_size=#
システム変数名 変数名 sort_buffer_size
スコープ Global,Session
動的変更 可能
設定値 整数型
デフォルト 262144
最小値 32768
最大値 4294967295(32bit版)
18446744073709551615(64bit版)

ファイルを利用したマージソートが行われるとパフォーマンスの面では不利になります。ファイルを利用したソートが行われている回数はSHOW GLOBAL STATUS文のSort_merge_passesに出力されます。また,実行計画を確認するEXPLAIN文の出力では,Extra列にUsing filesortと表示されます。

MySQLのファイルを使ったソートのアルゴリズムには2種類あります。BLOB型またはTEXT型が含まれたレコードをソートする場合には,ソートのキーとなる列と行のIDをソートバッファに格納して入りきらなかった場合にはファイルを使ったソートを行う,MySQLが以前から持っていたアルゴリズムでソートします。これらのデータ型が含まれておらず,かつソートキーの列とSELECT文で取得する列のサイズがmax_length_for_sort_dataを超えていない場合は,行のIDではなく行レコードそのものをソート対象レコードとするため,行を複数回のスキャンする必要がなくなり前者のアルゴリズムよりも高速化できます。

他に接続スレッド毎に設定可能なパラメタは以下の通りです。多くの場合はデフォルトで問題ありませんが,パーティショニングを行っているテーブルが多数ある場合や複雑なSQL文がある場合などにはthread_stackが小さすぎるというエラーが出ることがあります。

パラメタ名 概要
join_buffer_size インデックスを使用しないJOINで利用
read_buffer_size MyISAMのテーブルスキャン時のバッファ
read_rnd_buffer_size MyISAMのランダムスキャン時のバッファ
binlog_cache_size バイナリログに記録するトランザクションのバッファ
net_buffer_length データ送受信のバッファの初期値
max_allowed_packet データ送受信のバッファの最大値
thread_stack セッションのメタデータ

次項で紹介する一時表以外で最大メモリを利用する可能性があるサイズは下記の式で算出できます。

max_used_connections * ( 
    read_buffer_size + 
    read_rnd_buffer_size +
    join_buffer_size + 
    sort_buffer_size + 
    binlog_cache_size +
    thread_stack +
    2 * net_buffer_length
 )

一時表作成時に確保されるメモリ

MySQLではユーザがCREATE TEMPORARY TABLE文で明示的に一時表を作成できます。この一時表がMEMORYストレージエンジンを使ってメモリ上に作成されるサイズの上限がmax_heap_table_sizeです。この値を超えるサイズのテーブルはMyISAMストレージエンジンを使ってディスク上に作成されます。この値はテーブル毎のサイズの上限値であり,複数のメモリ上の一時表をそれぞれの接続で作成していくとMySQLサーバ全体では大量のメモリを使うことになりかねない点に注意が必要です。

コマンドラインでの設定 --max_heap_table_size=#
システム変数名 変数名 max_heap_table_size
スコープ Global,Session
動的変更 可能
設定値 整数型
デフォルト 16777216
最小値 16384
最大値 4294967295(32bit版)
18446744073709551615(64bit版)

UNION句を用いたクエリやTEMPTABLEアルゴリズムを用いたビュー,GROUP BYとORDER BYの列が異なる場合などは自動的に内部一時表が作成されます。この内部一時表がメモリ上に作成されるのは,格納されるデータがmax_heap_table_sizeまたはtmp_table_sizeのいずれかの最小値までとなります。

サーバ全体のパラメタ

図3 MySQLサーバのアーキテクチャ概要

図3 MySQLサーバのアーキテクチャ概要

MySQLにはSELECT文の結果をキャッシュするクエリキャッシュ機能がありますが,アクセスがシングルスレッドで実行されているため複数のクライアントからアクセスされている環境ではボトルネックになります。またフラグメントが起きやすく,処理効率が低いため,MySQL 5.6ではデフォルトでは無効になっているほか,利用することも推奨できません。

著者プロフィール

梶山隆輔

MySQL Sales Consulting Senior Manager。

日本オラクル(株)において,MySQLのお客様環境への導入支援や製品の技術解説を担当するセールスコンサルタントチームのアジア太平洋地域リーダー。多国籍なMySQL部門にて,オーストラリア,インド,台湾などに在籍するチームメンバーを束ね,アジア太平洋地域の25以上の国や地域でのMySQL普及やビジネスの拡大をミッションとする。