momota.txt

hello, hello, hello, how low?

MySQL パフォーマンスチューニング

MySQL 5.7 のパフォーマンスチューニングについて、調べてたのでまとめる。

// 結構な文量になってしまった…

大きく、2つのアプローチがある。

  1. DBチューニング
    • システム変数 (my.cnf) のチューニング
    • 全体最適
  2. アプリ (SQL) チューニング
    • 個別最適

まあ、地道に、計測→問題点の特定→修正→計測… のサイクルを回すしかない。

1. DBチューニング

ディスク構成関連

  • ログファイルとデータファイル (たとえば、システム表領域データファイル) を別の物理ディスクに配置することでI/O性能が向上する
  • InnoDBデータファイルをRawデバイスに置くことで、I/O性能が向上する
  • OSマウントオプション noatime を無効化する
    • Linuxのファイルシステムには、ファイル読み込み時刻が「atime」として保存される。1ファイルアクセス当たりのオーバーヘッドはささやかだが、大量のファイルに対して頻繁にアクセスするシステムでは、atimeの更新にかかる時間も無視できなくなる。atimeが不要なシステムであれば、atimeの更新を無効化することでディスクのパフォーマンスが向上する可能性がある。

ストレージエンジン関連

  • バッファプールサイズ innodb_buffer_pool_size

    • バッファプールサイズ innodb_buffer_pool_size は InnoDB のデータとインデックスをキャッシュするメモリ上の領域。可能な限り大きくすることが基本方針。
    • MySQL (InnoDB) のみを利用していれば、バッファプールサイズ innodb_buffer_pool_size はメインメモリの 70-80% 程度を割り当てる。データとインデックスの両方をキャッシュする。(デフォルトは128MB)
    • ログファイル全体をOSがキャッシュできる程度の余裕を持たせると更新系の性能に利点が大きい。

  • バッファプールインスタンス innodb_buffer_pool_instances

    • バッファプールをいくつのインスタンスに分けるか指定する。デフォルト8
    • 十分なメモリがある場合、複数のバッファプールインスタンスに分割することで、グローバル Mutex の衝突を減らすことができ同時並行性を改善可能。キャッシュされたページに対して、異なるスレッドがI/Oを行うときの競合が減るため並列性が増す。
    • innodb_buffer_pool_size が大きく、mutex競合がオーバヘッドとなっている場合は、2以上に設定
    • innodb_buffer_pool_size は N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instancesに等しいサイズにする必要あり。
    • オンラインでもサイズ変更可能。
    • MySQL :: MySQL 5.7 Reference Manual :: 15.6.1 InnoDB Startup Configuration
      • mutex 競合確認方法の例は以下。
1
2
3
4
5
6
7
8
9

mysql> select event_name, count_star, sum_timer_wait/1000000000 sum_timer_wait_ms
-> from performance_schema.events_waits_summary_global_by_event_name
-> where event_name like '%buf_pool_mutex%';
+----------------------------------------+------------+-------------------+
| event_name                             | count_star | sum_timer_wait_ms |
+----------------------------------------+------------+-------------------+
| wait/synch/mutex/innodb/buf_pool_mutex | 0          | 0.0000            |
+----------------------------------------+------------+-------------------+
1 row in set (0.00 sec)

  • innoDB_page_size

    • デフォルト16k。16KBは、幅広いワークロードに適しているサイズ。特にテーブルスキャンを含むクエリだったり、バルクアップデートを含むDML操作など。
    • 環境によって調整する。 innodb file structure

  • データファイルアクセスに OSキャッシュを無駄に消費しないよう innodb_flush_method を O_DIRECT にする。

    • O_DIRECT にすると、OSのDirect I/O機能を利用し、OSのキャッシュをバイパスする。MySQL は自前のキャッシュ機構があるので、OSキャッシュは冗長なので、バイパスしたほうがよい。
    • Linuxでは、多くの場合 O_DIRECT にしたほうがオーバヘッドが下がる

  • innodb_log_file_size

    • ログファイル(WAL: Write Ahead Log)はトランザクション更新ログ。ログファイルがどの程度頻繁に切り替わっているかをチェックしておく。
    • 大きくしすぎるとOSキャッシュに載らず、性能劣化する場合あり。また、クラッシュ後のリカバリ時間が長くなる。
    • innodb_buffer_pool_size の25%～100%。デフォルト45MB。最大リカバリ時間に影響するので考慮しつつ大きく設定する。

  • innodb_log_buffer_size

    • InnoDB は、変更されたデータのレコードを、メモリ上のバッファに書き込み、トランザクションがコミットする前にログがディスクに書き込まれないようにすることで、大きなトランザクション のI/Oを節約する。
    • 大量・巨大な blob を書き込む必要がなければ、4MB-8MBが適切。

  • innodb_flush_log_at_trx_commit

    • innodb_flush_log_log_at_trx_commit が 1 の時、ログバッファはトランザクションがコミットされるたびにディスク上のログファイルに書き出され、データの整合性を最大限保つ。しかし、これはパフォーマンスへの影響がある。この値を2にすると、ログバッファはトランザクションのコミットごとにOSのファイルキャッシュへ書き出される。これにより、ACIDを意識しないのであればパフ ォーマンスを最適化・高速化できるが、OSのクラッシュ時には数秒分のトランザクションが消えてしまう可能

  • innodb_file_per_table

  • Double Write Buffer

    • クラッシュ時のpartial write (書き込み途中状態での終了) によるデータページ破壊への対策機能で、InnoDBで使われているファイルFlush技術。
      • InnoDBデータファイルにページを書き出す前に、隣接領域 (これをdoublewrite bufferと呼ぶ)にまず書き出す。double write bufferへの書き込みおよびFlushが完了したら、InnoDBはデータをデータファイルの適切な位置に書き込む。これによりページファイルへの書き込み途中にクラッシュしても (OSやストレージサブシステム、mysqldプロセス)、doublewrite buffer からクラッシュリカバリできる。ただし、これはI/Oオーバヘッドが2倍以上かかるため、性能とのトレードオフ。
    • 障害復旧可能性を高めるため、innodb_doublewrite = ON (デフォルト設定) が推奨

  • innodb_thread_concurrency で同時実行スレッド数を調整する。

    • 処理を並列に行うとスループットの向上が期待出来るが、同時に実行中のスレッドがあまりに多すぎるとロックの競合が多発してしまって返って性能を落とす原因になってしまう。
    • 5.5 以上であればリミットをかける必要はない。現代的なOSであれば、マルチコア・プロセッサのコンテキストスイッチを効果的に実施してくれる。0は上限なし。デフォルトでOK
    • 並列度で問題が出た場合は、値を調整する。この場合の推奨値は、CPU 数の 2 倍にディスクの数を足したもの。動的変数なのでオンラインで変更可能。

  • InnoDB compression (圧縮機能)

    • 以下のメリットがある
      • DBデータファイルサイズを縮小化
      • I/O削減
      • スループットの改善
      • データ量削減
    • 条件
      • innodb_file_per_table を有効化すること
      • innodb_file_format が Barracuda であること
    • CREATE TABLE もしくは ALTER TABLE で ROW_FORMAT=COMPRESSED か KEY_BLOCK_SIZE を指定する

  • innodb_io_capacity

    • InnoDB のバックグラウンドタスクに使用するI/Oキャパシティ(IOPS)の上限を設定する。デフォルト200。
    • 高速なストレージを使用している場合は拡大する。
    • デフォルト値 (200) は、ストライプされた2本のディスクを目安にした値
  • innodb_read_io_threads, innodb_write_io_threads
    • 高速なストレージを使用している場合は拡大する。
    • デフォルト値 (4) は、一般的には十分な値

コネクションスレッドプール関連

  • max_connections
    • サーバが許容可能なコネクション数。デフォルト151。
    • 多すぎるとメモリを消費し切る可能性あり
  • thread_cache_size
    • スレッドをコネクションの切断後にもキャッシュしておく数。
    • 一般的には max_connections / 3
    • 計算式 8 + (max_connections / 100) により自動計算。
  • sort_buffer_size
    • ソート用のメモリサイズ。このサイズを超えるとディスクを利用する。OLTPでは256KB〜1MBを割り当てる。

クエリキャッシュ関連

  • query_cache_size
    • クエリキャッシュに割り当てるメモリサイズ
    • 一般的には32MでOK。デフォルト1MB。
  • query_cache_type
    • 最悪のケースでは、パフォーマンスのオーバヘッドが約15%
    • select 比率が高いサーバで有効
    • DEMAND に設定すると、クエリ実行時にSQL_CACHE句をつけたクエリだけキャッシュ可能

その他

  • autocommit は高負荷なのでOFFが推奨。

  • Read-Ahead

    • read-aheadリクエストは、バッファプールへ近い将来必要になりそうな複数のページを先読みするI/Oリクエスト。1エクステントの全ページに対してリクエストする。I/O性能の改善効果がある。Linear read-head(デフォルト)とRandom read-headの2つのアルゴリズムがある
    • この関連パラメータはチューニングする余地あり

  • 再起動時のウォームアップ運用

2. アプリ (SQL) チューニング

  • 適切なSQLを使う (クエリ最適化)

    • まずはスロークエリの分析
    • SHOW FULL PROCESSLIST で現在実行中の時間がかかっているクエリを特定する
    • EXPLAIN で実行計画の評価
      • たとえば、全件フェッチしてからアプリケーション側で行を絞り込むというようなロジックはNG
    • フェッチする行数を減らすようなクエリを組み立てる。
      • サブクエリをJOINに書き換えたり、インデックスを適切に貼ったり。

  • テーブルの最適化

    • 適切なデータタイプを選択する
      • 数値をVARCHAR(桁数)はNG。INT (もしくはBIGINT) にすることで高速かつデータサイズが小さい
    • インデックスを適切に作成する
      • 付けすぎると更新時のオーバヘッドが増える
      • 無駄に付けない。たとえば、性別などの二値にインデックスを張っても意味がない。
    • 適切に正規化する
      • JOIN処理により性能が出ない場合は非正規化も検討する

  • 適切な粒度でcommitする

    • グループコミットの利用

  • レプリケーションを利用した負荷分散

    • 参照処理はSlave側を向ける、など

  • コネクションプーリングする

  • Innodb compression

    • テーブル単位に圧縮オプションを選択可能
    • DML操作の実行時に compresson failure を引き起こす可能性あり。パラメータをチューニングする。
    • 繰り返しの値や一般的に文字列 (CHAR, VARCHAR, TEXT or BLOB列) は圧縮効果が大きい
    • ほとんどバイナリデータ (integer or floating point number) や事前に圧縮されているデータ (たとえば JPEG た PNG) は圧縮効果が少ない
    • 画像データが入っていないテーブルに適用すると効果あり

性能測定

推測するな、計測せよ。

設定を変えたり、プログラムを変えたりしても、効果を測れないと意味がない。

MySQL のステータス

性能測定ツール

  • innotop
    • top 風に MySQL のステータスを表示するツール
    • innotop/innotop
  • dstat, sar
    • OSレイヤの性能値を取得する

ベンチマークツール

参考資料

Comments