ぺーぺーSEのブログ

備忘録・メモ用サイト。

トップ > MySQL > MySQL5.6の設定の参照およびチューニング

MySQL5.6の設定の参照およびチューニング

MySQL

インストールについては下記を参照。

blog.pepese.com

MySQLの設定や状態に関するパラメータはshowコマンドで見ることができる。
showコマンド以外にinformation_schemaやperformance_schemaで見ることもできる。
MySQLの今後の動向としてはshowコマンドを無くしてxxx_schemaによせる方向らしいが、現状showコマンドでしか見れない情報があったり、showコマンドがコンパクトで使いやすいなどがあり、まだまだshowコマンドは残る模様。

showコマンド

コマンド 説明
show [global/session] variables; システム変数を表示するコマンド
show [global/session] status; 各種統計情報であるステータス変数を表示するコマンド
show processlist; 接続しているセッションを表示するコマンド
show databases; データベースの一覧を表示するコマンド
show tables [in データベース名]; テーブルの一覧を表示するコマンド
show open tables; 現在Openされているテーブルの一覧を表示するコマンド
show procedure status; ストアドプロシージャの一覧を表示するコマンド
show function status; ストアドファンクションの一覧を表示するコマンド
show triggers; 現在のデータベースのトリガの一覧を表示するコマンド
show events; 現在のデータベースのイベントスケジューラ用のイベントを表示するコマンド
show table status; テーブルの詳細な情報を表示するコマンド
show [full] columns/fields [in]; テーブル内で定義されているカラムの一覧を表示するコマンド
show index; テーブルのインデックス情報を表示するコマンド
show create table テーブル名; テーブル作成時のcreate文を表示するコマンド
show grants; 現在のユーザの権限を表示するコマンド
show privileges; 権限にどのような種類があるかを表示するコマンド
show enginges; ストレージエンジンの一覧を表示するコマンド
show engine エンジン名 status; ストレージエンジンのステータスを表示するコマンド
show binary logs; バイナリログの一覧を表示するコマンド
show binlog events [in] [from] [limit]; バイナリログをに記録されたクエリを表示するコマンド
show master status; レプリケーションに必要なバイナリログ情報を表示するコマンド
show slave status; レプリケーションのステータスを表示するコマンド
show slave hosts; マスターへ接続しているスレーブの一覧を表示するコマンド
show warnings/errors; 警告、エラーを表示するコマンド
show character set; 利用できる文字コードの一覧を表示するコマンド
show profile(s); プロファイリング情報を表示するコマンド

showコマンドでは「like」や「where」を使用することができる。

show global variables like "innodb_%";

また、セッション（session）毎のステータス変数は下記のタイミングでグローバル（global）へ反映される。

  • スレッド（セッション）が終了したとき
  • 「flush status;」コマンドを発行したとき
  • 「show global status;」コマンドを発行したとき


パラメータ

MySQLのパラメータ設定の方法には以下がある。

  1. コマンドライン引数（Cmd-Line）
    • MySQL起動時のコマンドライン引数
  2. オプションファイル（Option File）
    • my.cnfのことで、コマンドライン引数が長い場合に使用する位置付け
    • 設定は下記の順で読み込まれ、項目がかぶった場合は後勝ち
      1. /etc/my.cnf
      2. /etc/mysql.cnf
      3. sysconfdir/etc/my.cnf
      4. $MYSQL_HOME/my.cnf
      5. 「--defaults-extra-file」オプション
      6. ~/.my.cnf
  3. システム変数（System Variables）
    • MySQL自体が持つパラメータでMySQLにログインして設定する
    • Cmd-LineやOption Fileで設定できないパラメータもある

上記は優先順位の高い順に記載している。
「show variables;」コマンドで得られる結果は、上記の優先順位で反映された結果が見える。
また、パラメータには以下のような分類がある。

  • Status Variables
    • MySQLの状態を表すパラメータ
  • Variables Scope
    • GlobalかSessionか両方かのスコープに所属するパラメータ
  • Dynamic
    • MySQL起動中に変更できるパラメータとできないパラメータ

詳しくは下記の公式サイトで。
http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html

システム変数の設定方法

「show variables;」コマンドで表示されるシステム変数は「set [global|session] 変数名=値;」で変更できる。

チューニング

網羅はできないのでこれくらいやっておいた方がいいのでは、意識といたほうがいいのでは、という設定を書く。
InnoDB前提で書いてるところがあるかも。

■スロークエリログ
サーバ変数 デフォルト 設定値 説明
slow_query_log OFF(0) ON(1) スロークエリログを有効にするかどうか
slow_query_log_file どこか スロークエリログの出力先
long_query_time 10 要件による スロークエリと判断する秒数

log_queries_not_using_indexesを設定すると、long_query_timeの設定に関わらずインデックスが使用されていないクエリを出力できる。

■バイナリログ
サーバ変数 デフォルト 設定値 説明
log-bin なし mysqld-bin バイナリログ名を指定する（Cmd-Line or Option File）
log_bin OFF ON 「--log-bin」を指定するとONになる
binlog_format STATEMENT MIXED STATEMENT/ROW/MIXEDから選択。READ-COMMITEDだとSTATEMENT未対応
expire_logs_days 0 要件による バイナリログを残す日数
sync_binlog 0 1 commit何回毎にバイナリログをディスクにフラッシュするか
binlog_cache_size 32k ※ 未コミットのトランザクションをキャッシュするメモリサイズ

※・・・スレッドバッファだから一番でかいトランザクションくらいでいい。

■エラーログ
サーバ変数 デフォルト 設定値 説明
log_error OFF どこか エラーログの出力先


■トランザクション
サーバ変数 デフォルト 設定値 説明
transaction_isolation REPEATABLE-READ READ-COMMITTED トランザクション分離レベル

Oracle脳の人はこれで。

■コネクション数
サーバ変数 デフォルト 設定値 説明
max_connctions 151 要件による クライアントからのコネクション数の最大

limits.confでFD増やしとくのも忘れずに。

■バッファ、キャッシュ
サーバ変数 デフォルト 設定値 説明
join_buffer_size 256k そのままで インデックスを使用しないテーブル結合する際に使用されるメモリ領域
sort_buffer_size 256k 4M ソート処理（order by, group by等）で使用されるメモリ領域
read_buffer_size 128K 1M インデックスを使用しないテーブルスキャンで使用するメモリ領域
read_rnd_buffer_size 256K 2M インデックスを使用するソート処理で使用するメモリ領域（order by等）
query_cache_limit 1M そのままで ここで設定した値より大きな結果はキャッシュしない（クエリキャッシュ※１）
query_cache_size 1M 要件による クエリキャッシュの合計サイズ
query_cache_type 0 要件による クエリキャッシュのタイプ(0/1/2)、0はキャッシュしない
table_open_cache 2000 そのままで 接続が終わってもテーブル情報（ファイルポインタ）をメモリに保持しておく個数（※２）
thread_cache_size -1(auto) そのままで 接続が終わってもスレッドを解放せず置いておくメモリ領域
thread_stack 256k そのままで スレッドスタックで利用する領域
tmp_table_size 16M 16M テンポラリテーブル(※３)で使用される領域
max_allowed_packet 4M 16M パケットメッセージ（※４）バッファで使用されるメモリ領域、SQLの最大長
※１
  • クエリキャッシュはselectの結果をKey-Value形式（Keyがselect文、Valueが結果）でメモリ上に保持しておく仕組み。
※２
  • 「テーブル数 × 最大コネクション数」まで伸び得る。OSが処理できる記述子数（cat /proc/sys/fs/file-max）以内にする。
※３
  • テンポラリテーブル（ORDER BY、GROUP BY、JOIN、ALTER TABLE等の処理で利用）は接続単位接続単位で作成・削除され、そのサイズがtmp_table_sizeより小さい場合は物理メモリ上にMEMORYテーブルとして作成され、大きい場合はディスク上にMyISAMテーブルとして作成される。また、MEMORYテーブルはmax_heap_table_sizeの影響も受けるため、tmp_table_sizeとmax_heap_table_sizeは同じにしておく。
※４
  • パケットメッセージをバッファするメモリ領域はnet_buffer_lengthで初期化されmax_allowed_packetまで拡張される。クライアントが実行できるSQL文の最大長はmax_allowed_packetによって制限される。



MySQLのバッファにはグローバルバッファとスレッドバッファがある。

  • グローバルバッファ
    • mysqlデーモン全体で1つ確保されるバッファやキャッシュ
      • innodb_additional_mem_pool_size, innodb_buffer_pool_size, innodb_log_buffer_size, query_cache_size, tmp_table_size, max_heap_table_size(MEMORY), thread_cache_size, table_open_cache, key_buffer_size(MyISAM)
  • スレッドバッファ
    • mysqlのスレッド（コネクション）単位で確保されるバッファやキャッシュ
      • join_buffer_size, read_buffer_size, read_rnd_buffer_size, sort_buffer_size, thread_stack, binlog_cache_size, max_allowed_packet, net_buffer_length

なのでMySQLが使用する物理メモリは
「グローバルバッファ　＋　（スレッドバッファ　×　最大コネクション数）」
ということになる。
※コネクション自身に物理メモリ2M程度使うので、最後に「最大コネクション数 × 2M」足しとくといい。
ちなみにインデックスサイズは
「インデックスに含まれるカラムのデータサイズ ＋ プライマリーキーのサイズ」
になる。

■文字コード
サーバ変数 デフォルト 設定値 説明
character_set_server latin1 utf8 日本語使うときはutf8、4バイト文字があるときはutf8mb4


■InnoDBの各種パラメータ調整
サーバ変数 デフォルト 設定値 説明
innodb_additional_mem_pool_size 8M 20M InnoDBのデータディクショナリやデータ構造情報のバッファ
innodb_buffer_pool_size 128M ※１ InnoDBのデータとインデックスをキャッシュするプール
innodb_flush_method fsync O_DIRECT データファイル、ログファイルへの書き込み方式（※５）
innodb_flush_log_at_trx_commit 1 そのままで データファイル、ログファイルへの書き込みタイミング(※２)
innodb_file_format Antelope Barracuda ファイルフォーマット（Barrcudaは圧縮機能付き）(※３)
innodb_log_file_size 48M 128M InnoDBログ(※４)ファイルサイズ
innodb_log_buffer_size 8M そのままで InnoDBログファイルのためのバッファ
innodb_doublewrite ON そのままで doublewriteファイルに書き込んでからdataファイルに書き込む
innodb_read_io_threads 4 8 InnoDB読み込み要求に使用されるバックグラウンドスレッド数
innodb_write_io_threads 4 8 InnoDB書き込み要求に使用されるバックグラウンドスレッド数
innodb_io_capacity 200 HDDによる RAID(500-1000)/SSD(2000-5000)/IO-Drive(10000-50000)くらい
innodb_io_capacity_max 2000 HDDによる innodb_io_capacity（上記）と同じにする
innodb_support_xa TRUE そのままで two-phase commitの許可
innodb_thread_concurrency 0 そのままで InnoDBの処理を同時に実行するスレッドの数（0は無限）
※１
  • 公式には物理メモリの8割、とある
  • MySQLに乗せる全データ分のサイズがあればOK
      • さらっとむちゃ書いてる感。。。
  • バッファプールはInnoDBのインデックスやレコード、ダーティページ（後述）をキャッシュする領域
※２
項目＼設定値 0 1 2
書き込み 1秒毎 commit毎 commit毎
同期 1秒毎 commit毎 1秒毎
性能 high low middle
安定性 low high middle
※３
  • innodb_file_formatはテーブルスペースを持つテーブル単位で有効なのでinnodb_file_per_tableをonにする必要がある。
※４
  • InnoDBログはコミットされたトランザクションをテーブルスペースに反映する前に一旦全て書き出しておくためのファイル。
  • この仕組みはWAL（Write Ahead Log）という。InnoDBログへの書き込みはシーケンシャルなのに対してテーブルスペースへの書き込みはランダムアクセスとなるため高コストとなる。
  • InnoDBログへ書き込まれてもテーブルスペースへ反映されていないものはバッファプールに存在する。これをダーティページといい、テーブルスペースへ反映されたタイミングで削除される。ダーティページがテーブルスペースへ下記出される処理をチェックポイント処理という。
  • InnoDBログへ何バイト書き込んだか、という情報はshow engine innodb statusコマンドのLSN（Log Sequence Number）で確認できる。
  • チェックポイント処理はInnoDBログの古い順から実行され、下記の契機で実行される。
    1. InnoDBログファイルを使いきってしまったとき
      • InnoDBログの最大値は、「innodb_log_file_size（1つのInnoDBログサイズ） × innodb_log_files_in_group（InnoDBログの数）」
      • 「innodb_log_file_size × innodb_log_files_in_group ≦ innodb_buffer_pool_size」となるようにする
    2. innodb_max_dirty_pages_pctに達したとき
  • InnoDBログファイルのサイズが大きいほどチェックポイント処理の回数が減り性能向上が見込めるが、同時にクラッシュリカバリのコストが増大する。
  • MySQLがクラッシュした場合、再起動時にInnoDBログ内容をテーブルスペースへ反映することによってクラッシュリカバリされる。
  • innodb_log_file_sizeは128Mくらいから様子見して、性能でなければ増やしていく。
  • innodb_log_file_sizeを変更した場合、前回のInnoDBログが残っているとサイズが変わっているのでMySQLリスタートに失敗することがあるので以下の手順で変更・再起動する。
    1. 「mysql> SET GLOBAL innodb_fast_shutdown=0;」
    2. 停止「$sudo service mysqld stop」
    3. InnoDBログの削除or退避「mv /var/lib/mysql/ib_logfile* /tmp」
    4. dataディレクトリをバックアップ「cp -pr /var/lib/mysql /var/lib/mysql.backup.$(date +'%Y%m%d')」
    5. my.cnfのinnodb_log_file_sizeを変更
    6. 起動「$sudo service mysqld start」
※５
innodb_flush_method データファイル への書き込み ログファイル への書き込み
設定値 open() fsync()有無 open() fsync()有無
fsync 通常 有 通常 有
O_DSYNC 通常 有 O_SYNC 無
O_DIRECT O_DIRECT 有 通常 有
O_DIRECT_NO_FSYNC O_DIRECT 無 O_DIRECT 無

通常のファイルオープン（open()）で書き込み（write()）を行うとディスクに書き込んでいるように見えて実はメモリに書き込んでいるだけ。
このメモリに存在していて、いずれディスクに書き込むページのことをダーティページ（InnoDBバッファプールのダーティページでなくLinuxカーネルが管理するダーティページ）と言う。
何もしなくてもダーティページは定期的にカーネルによってディスクに書き込まれるがタイムラグがある。
ダーティページをすぐにディスクに書き込みたい場合はfsync()（ファイルメタデータの同期も保障）システムコールする。
fsync()に対してfdatasync()というシステムコールもあり、これはファイルメタデータの同期は保障されない。
O_SYNCフラグでファイルオープンすると、同期I/O（ファイルメタデータの同期も保障）モードでオープンされる。
この場合、書き込み（write()）は、メモリ（ページキャッシュ）だけでなくディスクに書き込むまで返り値は返却されない（ブロックされる）ため、fsync()をコールする必要はない。
O_DIRECTフラグでファイルオープンすると、メモリ（ページキャッシュを作成せず）を介さず直接ディスクからファイルをI/O（ファイルメタデータは同期されない）する。直接ディスクからデータをI/Oするため性能は期待できない。
innodb_flush_methodの設定としてO_DIRECTを推奨するのは、InnoDBバッファプールとLinuxカーネルが管理するページキャッシュの２つの領域に同じダーティページがあると無駄なのでInnoDBバッファプールだけにして効率的にメモリを使おう、という理由。

★ダーティページのディスク書き込み条件★

  • ユーザープロセスによる明示的な同期書き出しの指定
    • ファイルをO_SYNCモードでオープンする
    • ファイルシステムが -o sync オプションでマウントする
    • fsync(2) / fdatasync(2) を発行する
    • sync(2) を発行する
  • カーネルスレッド (pdflush) によるバックグラウンドでの書き出し
    • 一定時間ごとに起床して汚れたページを書き出す
    • 空きページが少なくなってきたときに起床して書き出す（backgroud_writeback()）
    • 汚れたページキャッシュの割合があまり増えないようにするためのページキャッシュ書き出し（balance_dirty_pages_ratelimited()）

★読み込み（read()）処理の流れ★

  1. まずページキャッシュを検索
  2. ページキャッシュがあった場合
    1. そのページキャッシュにデータがちゃんと載ってるか確認
    2. ページキャッシュのデータをプロセス空間にコピー
  3. ページキャッシュがなかった場合
    1. 新規ページキャッシュを作成
    2. ファイルシステムにデータの読み取り命令を発行
    3. I/O の完了を待ち合わせる



上記は全てオプションで変更可能なパラメータなのでmy.cnfはこうなる。

書く予定