MySQL 5.7 トラブルシューティング性能解析入門編

MySQL 5.7MySQL 5.7
トラブルシューティングトラブルシューティング
性能解析入門編性能解析入門編
奥野幹也
Twitter: @nippondanji
mikiya (dot) okuno (at) gmail (dot) com
@DBTS-OSS 2017

免責事項
本プレゼンテーションにおいて示されている見解は、私
自身の見解であって、オラクル・コーポレーションの見
解を必ずしも反映したものではありません。ご了承くだ
さい。

自己紹介
●
MySQL サポートエンジニア
– 日々のしごと
●
トラブルシューティング全般
●
Q&A 回答
●
パフォーマンスチューニング
など
●
ライフワーク
– 自由なソフトウェアの普及
– 趣味はリカンベントに乗ること
●
最近は執筆と子育ての日々・・・
●
ブログ
– 漢のコンピュータ道
– http://nippondanji.blogspot.com/

アジェンダ
●
MySQL の性能解析の基本
– チューニングの基本
– スロークエリログ、 SHOW GLOBAL STATUS 、 SHOW ENGINE
INNODB STATUS 、 SHOW PROCESSLIST 、 EXPLAIN
– 基本的なパラメーターチューニング
●
モダンなツールを使いこなす
– パフォーマンススキーマ（ MySQL 5.5~ ）
– ダイジェストサマリテーブル（ MySQL 5.6~ ）
– InnoDB Metrics テーブル（ MySQL 5.6~ ）
– sys スキーマ（ MySQL 5.7~ ）
– オプティマイザトレース（ MySQL 5.6~ ）
●
MySQL 5.7 におけるオプション設定例

チューニングの基本
●
パフォーマンスの概念
– レスポンス vs スループット
– レスポンス・・・どれだけ短時間で処理が完了するか
– スループット・・・単位時間あたりにどれだけ多くの処理を
実行できるか
●
基準値を知る
– 平常時の負荷を継続的に測定、監視する
– ベンチマークで限界値を把握しておく
●
あとどのぐらいで限界に達するのか
●
目標値を設定する
– どこまでのパフォーマンスなら許容範囲なのか
●
そもそもチューニングする必要があるのかどうか。

チューニングの対象
●
ハードウェアや OS 等の環境
– CPU 性能、ディスク性能、メモリ容量
– ファイルシステムの種類と設定、カーネルパラメーター
●
サーバーのパラメーター
– 各種バッファサイズ
– 各種ファイルサイズ
– スレッド数等
●
個々のクエリ
– 効率が悪くリソースを多く消費するクエリ
– ロックなどによって他のクエリに影響を与えるクエリ

チューニングの狙い
●
レスポンスの改善
– 時間が掛かっている要因を特定する
●
クエリの実行計画の理解が第一
●
スループットの改善
– ボトルネックを探しだす
– 全体的なキャパシティを引き上げる
●
物理で殴る＝ハードウェアの増強
●
各種オプションの調整
– MySQL 本体、 OS 、ファイルシステム等

ボトルネックの特定
●
ボトルネックが発生する要因
– コンピュータソフトウェアは何の工夫も無しにリソースを使
い切れるわけではない
●
並列処理に適した仕組み
– 排他処理によるマルチスレッドプログラミング
– ロックを用いないシェアードナッシング型など
●
よくある原因
– ひとつのスレッドがロックを獲得したまま遅い処理（ I/O な
ど）をする
– プログラム内でシリアライズされている処理がある
●
SELECT … FOR UPDATE ですべてのスレッドが同じ行をロッ
クするなど
– とんでもなく効率が悪いクエリがある

アーキテクチャを理解する
●
パフォーマンスを改善するには、 MySQL のアーキテクチャに
対する理解が不可欠
– なぜ遅くなっているかという仮説を立てる
●
統計情報の解釈には、内部の動作を知る必要がある
– 問題に対する解決策を導き出す
●
アーキテクチャを知らなければ代替手段は出てこない
– パフォーマンス上問題がありそうな使い方を避ける
●
MySQL がどのような処理を苦手とするのかを知っている
必要がある

MySQL が持つ手段を理解する
●
問題箇所の特定手段
●
問題の分析手段
●
問題の改善手段
本日の
メインテーマ

スロークエリログとは
●
実行に時間がかかったクエリを記録する
– 効率が悪いクエリの発見
– 高負荷時のレスポンス低下の検知など
●
設定方法
– slow_query_log = ON
– slow_query_log_file = file_name
– log_output = {FILE|table}
– long_query_time = time -- 閾値を指定。 0 で全ての
クエリを記録
– log_queries_not_using_indexes = 1 -- インデック
スを使っていないクエリを全て記録
●
ログファイルは mysqldumpslow コマンドで解析

スロークエリログの例
# Time: 2017-06-15T07:19:44.679820Z
# User@Host: msandbox[msandbox] @ localhost [] Id: 3
# Query_time: 0.539501 Lock_time: 0.000229 Rows_sent: 1 Rows_examined: 11351
use world;
SET timestamp=1497511184;
SELECT
COUNT(1)
FROM
CountryLanguage c1
JOIN
Country c2 ON c1.CountryCode = c2.Code
JOIN
(SELECT
City.CountryCode, MAX(Population) Population
FROM
City
GROUP BY City.CountryCode) c3
WHERE
c1.Percentage * c2.Population > c3.Population
...

スロークエリログの見方
●
Query_time … クエリの実行にかかった合計時間。パケットの
受信やクエリキャッシュの操作など、前後の処理は含まれな
い。
●
Lock_time … テーブルのロックにかかった時間。行ロックの
クエリでも、クエリ実行前にテーブルの更新を許可する特殊
な共有ロックを取得する必要がある。
●
Rows_sent … クライアントへ返送された行数。
●
Rows_examined … テーブルあるいはテンポラリテーブルから
読み取られた行数。

mysqldumpslow
●
スロークエリログの中身をクエリごとにおまとめ！！
– スロークエリログ内の各クエリごとに
●
実行回数を表示
●
平均と合計を算出
– チューニング対象のクエリを選別するのに便利
shell$ mysqldumpslow hostname-slow.log
Reading mysql slow query log from starlessbb-slow.log
Count: 6 Time=0.54s (3s) Lock=0.00s (0s) Rows=1.0 (6), user[user]@localhost
SELECT
COUNT(N)
FROM
CountryLanguage c1
...

統計情報のソース
●
SHOW GLOBAL STATUS
●
SHOW ENGINE INNODB STATUS
●
情報スキーマ
●
パフォーマンス・スキーマ
●
sys スキーマ

統計情報の活用方法
●
変化を見る
– 値の差分が重要
●
多くの統計情報は起動時からの累積値
●
単発の実行結果だけでは何も分からない
– 一定間隔で情報を収集
– グラフ化して視覚的に変化を捉える
– いつから問題が始まったかを知る
– どのパラメーターが連動しているかを見る
●
比較する
– レスポンス、スループットと比較する
– 平常時の問題のない値と比較する
– ベンチマークで取得した限界値と比較する
変化を見ない
比較をしない
解析は無意味。

実行中の処理を見る
●
コマンド
– SHOW PROCESSLIST
– SHOW ENGINE INNODB STATUS
●
なぜ必要か
– スロークエリログだけで遅い原因が分からないケース
●
何らかの処理が他の処理の実行を妨げている
– 遅い時間帯に何が起きているかをひと目で把握

プロセスの中身を見る
●
Poorman's profiler
– GDB でスレッドのスタックトレースを収集
– どこで糞詰まっているかを頻度から分析
– GDB のプロセスへのアタッチは遅い・・・
●
松信さん作の quickstack あり
●
https://github.com/yoshinorim/quickstack

EXPLAIN
●
表形式、あるいは JOSN 形式で実行計画を取得
– 表形式を読み解くにはやや慣れが必要
●
拙著で詳しく解説 ⇒ エキスパートのための MySQL[ 運用
+ 管理 ] トラブルシューティングガイド (2010 年技術評
論社 )
●
書式
– EXPLAIN [EXTENDED|PARTITONS|FORMAT=JSON] SELECT …
– MySQL 5.7 では EXTENDED かつ PARTITIONS が常時有効

ビジュアル EXPLAIN
●
視覚的に実行計画を把握
– ツリー構造が解りやすい
– 赤いところはイケてない

パラメーター
チューニングの基本

InnoDB の設は超重要
●
innodb_buffer_pool_size
– できるだけ大きく（定説はシステムメモリの７８割）〜
●
innodb_flush_method
– O_DIRECT でダブルバッファリングを防ぐ
●
innodb_log_file_size
– 十分なサイズを確保
●
innodb_io_capacity
– ディスクの IOPS に合わせる
●
innodb_purge_threads, innodb_write_io_threads
– 更新が多ければ増やす

セッション関係はデフォルト推奨
●
大きくし過ぎる弊害もある
– メモリの確保に時間がかかる
– セッション数に応じてメモリの消費も増える
– ベンチマークテストで様子を見ながら変更すること
●
オプション例
– read_buffer_size
– read_rnd_buffer_size
– sort_buffer_size
– join_buffer_size
– tmp_table_size

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

パフォーマンススキーマとは
●
主にパフォーマンス情報を取得するための仕組み
– MySQL 独自の機能
– ストレージエンジンとして実装
– オーバーヘッドはごくわずか
– ややメモリを消費する
●
使い方はやや難しい
– 採取できる情報が膨大
– MySQL 内部の構造を知る必要がある

パフォーマンススキーマ
vs 情報スキーマ
パフォーマンススキーマ情報スキーマ
主目的パフォーマンスデータの取得メタデータの取得
アプリケーションパフォーマンスチューニング監視ツールや管理ツール
導入されたバージョン 5.5 5.1
SQL 標準？いいえはい
実装方法ストレージエンジンのひとつ情報スキーマ API
データ収集のタイミング mysqld 内部で任意のタイミ
ングでコード実行時
情報スキーマテーブルアクセ
ス時
通常時のオーバーヘッドありなし
表示によるオーバーヘッド少ない大きい
類似のツール DTrace, SystemTap SHOW コマンド

コンシューマー
●
計器をグループ化
●
パフォーマンスデータを保
存するメモリ領域

ダイジェスト・
サマリテーブル

まだスロークエリログで
消耗してるの？
●
スロークエリログの制限事項
– 実行時間が long_query_time 未満のクエリは記録されない
●
微妙だけど大量に実行されるクエリを見過ごす恐れ
– 集計には mysqldumpslow を実行する必要あり
●
面倒！！
●
ヒューマン・リーダブル・フォーマット＝加工が面倒
●
ダイジェスト・サマリテーブル登場！！
– MySQL 5.6 で追加
– クエリの種類ごとに行数や実行時間をカウント
– mysqldunpslow の結果相当をテーブルから取得
●
加工やモニタリングが容易
– performance_schema.events_statements_summary_by_digest

ダイジェストとは
●
クエリを識別する md5 ハッシュ
– パーサーによって構文解析の過程で生成される
– クエリをコンパクトな形式に変換してからハッシュ値を計算
●
コンパクトな形式はダイジェスストレージと呼ばれる
●
可読な形式であるダイジェストテキストに変換可能
●
ダイジェストストレージの生成
– トークン ⇒ 2 バイト
– リテラル ⇒ トークンと同じ
●
IN の中身はひとつにおまとめ
– 識別子 ⇒ ２バイトのヘッダ＋識別子名

ダイジェスト・サマリテーブルの例
*************************** 3. row ***************************
SCHEMA_NAME: world
DIGEST: aa4f0c22e7b99148cb31e2b80aef14e1
DIGEST_TEXT: SELECT COUNT (?) FROM （以下略）
COUNT_STAR: 14
SUM_TIMER_WAIT: 7558222784000
MIN_TIMER_WAIT: 534298792000
AVG_TIMER_WAIT: 539873056000
MAX_TIMER_WAIT: 545839254000
SUM_LOCK_TIME: 3266000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 14
SUM_ROWS_EXAMINED: 158914
・・・つづく・・・

ダイジェスト・サマリテーブルの例
・・・つづき・・・
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 28
SUM_SELECT_FULL_JOIN: 42
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 28
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 14
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2017-06-15 16:19:00
LAST_SEEN: 2017-06-15 17:03:27

InnoDB の情報スキーマ
●
InnoDB Plugin 以降情報スキーマが追加された
INNODB_SYS_TABLES
INNODB_SYS_FIELDS
INNODB_CMP_PER_INDEX_RESET
INNODB_BUFFER_PAGE
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_INDEX_TABLE
INNODB_FT_INDEX_CACHE
INNODB_SYS_TABLESPACES
INNODB_METRICS
INNODB_SYS_FOREIGN_COLS
INNODB_CMPMEM
INNODB_BUFFER_POOL_STATS
INNODB_SYS_COLUMNS
INNODB_SYS_FOREIGN
INNODB_SYS_TABLESTATS
INNODB_LOCKS
INNODB_TRX
INNODB_SYS_DATAFILES
INNODB_FT_CONFIG
INNODB_SYS_VIRTUAL
INNODB_CMP
INNODB_FT_BEING_DELETED
INNODB_CMP_RESET
INNODB_CMP_PER_INDEX
INNODB_CMPMEM_RESET
INNODB_FT_DELETED
INNODB_BUFFER_PAGE_LRU
INNODB_LOCK_WAITS
INNODB_TEMP_TABLE_INFO
INNODB_SYS_INDEXES

InnoDB Metrics テーブル
●
information_schema.INNODB_METRICS
– InnoDB 内部の情報を採取する情報スキーマ
●
種々の統計情報を記録
– InnoDB Metrics でしか採取できないもの有り
●
ページの種類ごとの IO 統計、パージの統計など
– 採取する情報の意味は COMMENT カラムに説明あり
●
デフォルトでは一部のみが有効化
– 有効化コマンド例
– UPDATE INNODB_METRICS SET status='enabled'
WHERE SUBSYSTEM='transaction';
InnoDB で性能問題が出たら
とりあえず使ってみる価値あり！！

Good bye Old InnoDB Monitors!
●
InnoDB テーブルモニター
– INNODB_SYS_TABLES
●
InnoDB テーブルスペースモニター
– INNODB_SYS_TABLESPACES
●
InnoDB ロックモニター
– INNODB_LOCKS/INNODB_LOCK_WAITS
●
http://d.hatena.ne.jp/sh2/20090618
– sys.innodb_lock_waits

sys スキーマとは
●
パフォーマンス・スキーマと情報スキーマを横断的にアクセ
スするビューのコレクション
●
MySQL 5.7 から標準でバンドル
– MySQL 5.6 用もあり
– https://github.com/mysql/mysql-sys
●
可読性の高い（？）テーブルと生データを表示するテーブル
– x$ で始まるものは生データ

代表的な sys スキーマのビュー
●
metrics … ステータス情報をまとめたもの
●
innodb_lock_waits … ロック解析
●
io_% … IO 統計情報
●
memory_% … メモリ割り当てに関する統計情報
●
statement_analysis … ダイジェストサマリテーブル相当
●
statements_with_temp_tables … テンポラリテーブルを使用し
たクエリの一覧
●
user_summary … ユーザーごとの統計情報
●
host_summary … ホストごとの統計情報

sys.diagnostics()
●
稼働中の MySQL サーバーの様々な情報を一括で採取
– サポートへデータを送るときに便利
– 週ごとなど、定期的に収集することで変化を追いかけられ
る
●
各種ステータス情報の採取
– 一定時間ごとにステータスを採取し、差分を計算
– 問題のあるときと平常時を比べるのに役立つ
mysql> CALL sys.diagnostics(1,1, 'full');
mysql> CALL sys.diagnostics(600,60,'current');

オプティマイザ
トレース

オプティマイザの選択を知る
●
EXPLAIN で知ることができるのは、選択された実行計画の情
報
●
思い通りの実行計画にならないとき知りたいことは
– なぜ想定していた実行計画が選ばれなかったのか
– それぞれのコストはどう評価されたか
●
どの部分のコストが大きいのか
●
読み解くのは結構難しいけれども、プロなら使いこなしたい
– 使いこなせば当然クエリのチューニングに役立つ
– 拙著で詳しく解説 ⇒ 詳解 MySQL 5.7 （ 2016 年翔泳社）

オプティマイザトレースの使い方
mysql> SET optimizer_trace_max_mem_size=10*1024*1024;
mysql> SET optimizer_trace='enabled=on';
mysql> SELECT … （クエリ実行） ;
mysql> SELECT * FROM
information_schema.optimizer_traceG

MySQL 5.7 時代の
オプション設定例

InnoDB
●
UNDO ログの自動縮退
– innodb_max_undo_log_size = 1G
– innodb_undo_tablespaces = 16
●
REDO ログの Read on Write 防止
– innodb_log_write_ahead_size = ファイルシステムのブロックサ
イズ（上限は innodb_page_size ）
●
NUMA 対策
– innodb_numa_interleave = ON
●
パージスレッドの複数可
– innodb_purge_threads = 16

レプリケーション
●
マルチスレッドスレーブ
– slave_parallel_workers = 128
– slave_parallel_type = LOGICAL_CLOCK
– slave_preserve_commit_order = 1
– log_bin=mysql-log
– log_slave_updates=1
●
クラッシュセーフなスレーブ
– relay_log_info_repository=TABLE
– relay_log_recovery=ON
– relay_log_purge=ON
●
バイナリログへのグループコミットのスループット向上
– binlog_group_commit_sync_delay = 2500
– binlog_group_commit_sync_no_delay_count = 256

パフォーマンス・スキーマと
情報スキーマ
●
メモリの割り当てを追跡
– performance_schema_instrument = memory/%=ON
●
メタデータロックを追跡
– performance_schema_instrument = wait/lock/metadata/%=ON
●
追加の INNODB_METRICS カウンタの有効化
– innodb_monitor_enable =
module_index,module_trx,module_purge,module_buffer_page,module
_metadata

まとめ
●
パフォーマンス解析はとても大切
– パフォーマンスが出ないアプリケーションは役に立たない！
– パフォーマンスのトラブルは日常茶飯事
– よって素早く解析することが重要
●
MySQL 5.7 の機能を活用する
– 取得できる情報の種類が豊富
●
使いこなすのが難しい機能から、すぐに役立つ機能まで
– 古いバージョンを使っている人はぜひアップグレードを！
●
そもそも古いバージョンよりパフォーマンスが向上してい
る
●
便利な新機能も多数！！

宣伝：「詳解 MySQL 5.7 」
●
MySQL 5.7 の新機能を網羅
– 175 もの新機能を解説
– 新機能の理解に欠かせ
ないアーキテクチャの話
も盛りだくさん
– MySQL の実装について詳
しくなれる！！

宣伝：サポートエンジニア募集中！！
●
MySQL サポートチームで一緒に働いてみませんか？！
●
技術力が物を言うポジションです！
– 技術力に自信のある方、技術を磨きたい方歓迎
– L1 から L3 までの問い合わせをすべて受け持ち
●
クエリのチューニングなども行います
●
オープンソースなのでソースコード見放題！！
– 英語より技術力重視
●
普段の業務は日本語オンリーです。
●
日本の顧客がターゲットです。
●
上司は海外
– やりとりは英語のみ
– ヘルプ可能
●
在宅勤務可能

Q&Aご静聴ありがとうございました。

MySQL 5.7 トラブルシューティング 性能解析入門編

More Related Content