MySQLのパフォーマンスチューニング勉強会に参加した

MySQL 勉強会

MySQL8.0のパフォーマンスチューニング勉強会に参加した．クエリのチューニングよりかはDBA目線のチューニングを知りたいという部分が目的だったが，クエリのチューニングについても勉強になった．資料すべてを解説したわけではないので，お話していただいた箇所のメモをまとめる．

eventreg.oracle.com

Oracleアカウントをお持ちの方は資料を以下のサイトからダウンロードできる．

www.mysql.com

この勉強会では，前半でチューニングの概論とMySQL ServerのチューニングのようなDBA目線のチューニングについて話され，後半で開発者目線のSQLチューニングについて話された．

チューニング概論

キューイング

複数のユーザ/リクエストがある場合基本的にはキューイングされていき，システムが飽和状態に近づくとキューイングによる遅延が急激に増大する現象がある．これを「ホッケースティック」と呼び，「ホッケースティック」に陥るとボトルネックがCPU，ネットワーク，ディスクのどこにあるのか見極める必要がある

ベンチマークテストのありがちな間違い

  • データサイズが想定されるサイズと全然違う
  • 同時接続数を意識せずツールでただ回すだけのテスト

ビジネス面からの考慮

  • チューニングするよりハードウェア交換の方が早いのではないか
  • そのパフォーマンス，スケーラビリティ，信頼性は本当に必要かどうか

MySQL ServerチューニングTips

ここではDBA目線でMySQL Serverのチューニングを学ぶ．パラメータ設定が主なチューニングとなる．

システム変数の変更

  • my.cnfを変更して再起動
  • SET GLOBALで再起動せずとも変更できるが，再起動すると元に戻る
  • mysql8.0ではSET PERSISTで再起動しなくても永続的に変更できるが，設定はmy.cnfではなくmysqld-auto.cnfに格納されるので注意

ステータス変数

  • mysql> show statusで今の状況を確認できる
  • mysql> variablesはSET GLOBALで書き換えられたMySQLサーバーの設定を見ることができる

パフォーマンス・スキーマ/sysスキーマ

  • 性能統計情報分析のためのしくみ
  • パフォーマンス・スキーマはかなり詳細の統計まで表示されるが項目が多くややこしい
  • sysスキーマはパフォーマンススキーマを便利に使うためのビュー，プロシージャ，ファンクションのセット

チューニングされるパラメータ&チューニングの際よく使われる指標

  • max_connections(デフォルト151)
    • サーバが許容可能なコネクション数
    • 多すぎるとメモリを消費しきる可能性がある

  • Max_used_connections

    • mysql>show statusで確認できる
    • サーバーが開始されてから同時に使用された接続の最大数
    • 「Max_used_connectionsがmax_connectionsを超えていると接続できなかったコネクションがある」のように，max_connectionsの設定の指標になる

  • thread_cache_size(デフォルトは「9=8+(max_connections/100)」による自動計算)

    • コネクションの受け口となるスレッドをキャッシュとしてプールしておく数
    • MySQL的にはFileIO（ファイル開いて，書き込んで，閉じてみたいな作業）が一番のコストなのでFileIOが発生しないようにスレッドを用意しておくのがこのオプション

  • threads_created

    • 接続を処理するために作成されたスレッドの数
    • 通常運用で増えていることがあればthread_cache_sizeを増やすことを検討する

  • sort_buffer_size(デフォルト256KB)

    • ソート用のメモリサイズ

  • sort_merge_passes

    • sort_buffer_sizeが足りず，一時ファイルを利用してソートした回数
    • これが増えているようならsort_buffer_sizeをあげるかインデックスを貼ることを検討

  • innodb_buffer_pool_size(デフォルト128MB)

    • テーブルおよびインデックスのデータをキャッシュするメモリ
    • デフォルトで小さく設定してある
    • サーバがMySQL専用なのであればメインメモリの80%程度を割り当てる

  • innodb_log_file_size(デフォルト48MB)

    • リカバリ用にUPDATEやINSERTといった更新処理を貯めておくログファイルのサイズ
    • innodb_buffer_pool_sizeの25~100%
    • 値を大きくするとクラッシュ後のリカバリ時間が長くなる

  • innodb_file_per_table(デフォルトON)

    • テーブル単位でOS上のファイルを分ける設定
    • ONにしているとデータを削除すればディスクは空くが，OFFにしているとデータはibdataファイルに貯められ，データを削除してもibdataからは消えない
  • innodb_flush_log_at_trx_commit(デフォルト1)
    • 1（遅い）コミット時にログバッファがログファイルへ書き込まれ，ログファイルをディスクへフラッシュ（真のACID）
    • 2（速い）コミット時にはログファイルへ書き込み，ディスクへのフラッシュは毎秒一回
    • 0（最速）ログを毎秒一回（またはそれ以下）でディスクにフラッシュ
    • スレーブでマスターに昇格しないものが確定しているのであれば0や2でもよいが，マスターは必ず1にする
  • innodb_io_capacity(デフォルト200)
    • InnoDBのバックグラウンドタスクに使用するI/Oキャパシティ（IOPS）の上限を設定
    • SSDを使うことしか想定しない場合はもう少しあげてもよい

SQLチューニングの基本

ここからは開発者目線のSQLチューニングに関する内容である．システムが遅い時を想定してMySQLのチューニングを学んでいく．

問題の特定

システムが遅い時，クエリが問題な可能性もあればネットワークやディスクが問題の可能性もあるので多角的な調査が必要．以下はMySQLで問題があるかどうかを調べる際に有効な手段．

  • スロークエリログ
    • 実行時間が指定した時間以上のクエリをスロークエリとしてファイルに出力する
    • 有事の際は普通のクエリもスロークエリとして吐き出されてしまうので，何かが起きる瞬間を捉えるために利用すべき
  • mysql> SHOW FULL PROCESSLIST
    • 今動いているクエリやリクエストされているクエリなどのプロセスを表示
  • パフォーマンススキーマでの確認
    • SHOW FULL PROCESSLIST+αの情報が得られる
    • SQLで確認できる
  • MySQL Query Analyzer
    • 商用のみ
    • スロークエリが発生した時，CPUやネットワークなどの他の要因と相関を可視化できる

クエリの問題である可能性がある時

システムが遅い原因がクエリにあると仮定したら，まずは実行計画をとりtypeを確認する．以下の表は上から順にtypeの望ましい値となっている．

typeの値 意味
system 1行しかないテーブル（systemテーブル）※constの特殊な例
const PRIMARY/UNIQUEインデックスによる等価検索（一意検索）
eq_ref PRIMARY/UNIQUEインデックスによるJOIN
ref ユニークでないインデックスによる等価検索，JOIN
fulltext 全文検索インデックスを使用した全文検索
ref_or_null ユニークでないインデックスによる等価検索とIS NULLのOR
index_merge 複数のインデックスをマージ
unique_subquery サブクエリ内で，PRIMARY/UNIQUEインデックスで等価検索（一意検索）
range 範囲検索
index インデックスのフルスキャン
ALL フルテーブルスキャン

indexとALLを対応すれば遅いクエリが解消されることが多い．indexはたいていrefかeq_refにできる．ただしindexの場合でも対象行数が少ない場合は例外としてindexでもOK．基本的にはrowsをなるべく小さくするような方針で解消していく．rowsとはクエリを実行するためにフェッチされる行数の推測値である．

遅いクエリを解消する方法に関する追加情報としてExtraという項目がある．

  • Using filesort
    • 使えるインデックスがなかったので一時ファイルを使ってソートしてしまったが，インデックスがあれば解消できるかもしれない
  • Using temporary
    • 同上
  • Using index
    • クエリが1つのインデックスにアクセスするだけで完結するので非常に高速
    • これが出てくるようにチューニングすべき
  • Using where
    • テーブルから行をフェッチした後にさらにWHERE条件での絞り込みが必要なもの
    • インデックスが使われない場合に出てくるが，インデックスが使われてもインデックスでの検索後にさらにもう一段階のWHEREによる絞り込みが必要な場合にも出てくる

SELECTの種類を示すselect_typeという項目がある．

  • DEPENDENT UNIONとDEPENDENT SUBQUERYは可能な限り避けた方がよい
  • DEPENDENT UNIONはUNION内の2つめ以降のSELECTステートメントで外側のクエリに依存していることを表し，DEPENDENT SUBQUERYはサブクエリ内の最初のSELECTで外側のクエリに依存していることを表している
  • どちらも，結果1行1行に対して外側の表のマッチング処理が行われるため非常に工数が大きい

遅いクエリの対応方法

クエリに原因があると分かればSQLチューニングを実施する．

SQLチューニングの基本
  • インデックスの活用
  • 複数テーブルのJOIN
    • 取り出す行数が少量の小さなテーブルから順番にJOINするのが基本
  • 従来はチューニングのためにサブクエリをJOINに書き換えることが多かったが，MySQL5.6以降ではJOINに書き換えなくてもパフォーマンスよく実行できるケースが多い
インデックスの考慮事項
  • 表データを全件取得する場合はインデックスを使わない方が高速になる
  • UPDATE文でインデックスが使えていない場合は，ロック待ちを過剰に発生させる可能性があるので要注意
    • InnoDBでは，処理した行ではなくアクセスした行に対してロックを取得するため，1件しか更新しないUPDATE文であってもインデックスが使えていない場合はテーブルロックになってしまう
    • トランザクション分離レベルをREAD COMMITTEDに変更している場合は，処理した行に対してのみロックを取得する（ギャップロックが無効化されるため）
  • インデックスをつけすぎない
    • 参照時の性能は向上するが，更新時はオーバヘッドになる
    • 重複するようなインデックスは利用しない
      • key(a,b)があるならkey(a)は削除
    • インデックス内で順序が先の列のみ利用可能
      • インデックスがkey(a,b)だとWHERE b = 5のような場合には使われない
      • ただしMySQL8.0からはスキップスキャンによりインデックスを使用する場合もある
    • 基本的にはカーディナリティが低い列にはインデックスを付けない
      • 例外として，化粧品メーカーで男性向けにメールを打ちたいといった場合は男性はかなり少ないと思われるので，性別のようなカーディナリティが低い列でも有用かも
  • converting indexesはインデックスにアクセスするだけで必要なデータを取り出せるため高速
    • SELECT c FROM t WHERE b = 5の場合key(b,c)がよい
  • インデックス列はそのまま参照しないと効力がない
    • WHERE a*100>=90ではインデックスが使えないので，WHERE a>=0.9と指定する

オプティマイザの制御

  • インデックスヒント
    • 使うインデックスを明示的に指示する
    • EXPLAINでpossible_keysに表示されるオプティマイザが使用可能なインデックスとEXPLAINでkeyに表示されるオプティマイザが実際に使用したインデックスを見ることでインデックスヒントを使うべきかどうか判断する
    • OUTER JOINの時は使えない
  • STRAIGHT_JOIN
    • JOINの順番を明示的に指示する
    • プログラムで明示的に順番指定できるなら使う必要はない

Q & A

Q1. table_open_cacheはどれくらいにしておけばいい？メモリが足りなくなるのではと怖い．
A. table_open_cacheはmax_connectionsによって動的に設定されるのでそれで十分なはず．メモリを気にするならmax_connectionsを設定する方がいい．

Q2.クエリキャッシュのヒット率ってどれくらいがいい？
A. mysql8.0からなくなる．キャッシュアウト・インが頻繁になるところがあるので，MySQL8.0ではキャッシュせずInnoDBをもっと速くする方がよいという考えかららしい．

まとめ

  • MySQL8.0のパフォーマンスチューニング勉強会に参加した
  • MySQL8.0に限らず以前のバージョンにも言えるチューニングの話をされた
  • 個人的にはDBA目線のチューニングはまだあまり知らなかったので参考になったし，SQLチューニングも新たな学びがあってよかった