[D14] MySQL 5.6時代のパフォーマンスチューニング　*db tech showcase 2013 Tokyo

1 Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
MySQL 5.6時代の
パフォーマンスチューニング
日本オラクル株式会社
山崎由章 / MySQL Senior Sales Consultant,
Asia Pacific and Japan

以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。
また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことは
できません。以下の事項は、マテリアルやコード、機能を提供することをコミットメン
ト（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さ
い。オラクル製品に関して記載されている機能の開発、リリースおよび時期につい
ては、弊社の裁量により決定されます。
OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中
の社名、商品名等は各社の商標または登録商標である場合があります。
免責事項
また、以下の事項は、私個人の見解であり、私の所属するオラクルの見解を
必ずしも反映したものではありません。

パフォーマンスチューニングとは？
• 単純な言葉ではあるが多くの意味が
• チューニングの目的:
– ユーザを満足させるため
⇒限られたシステム・リソースの中で、最大限のパフォーマンス効果を出すこと
• パフォーマンスの指標の例
– スループット
– レスポンスタイム / レイテンシ
– スケーラビリティ
– 上記の組合せ
ばらつき
時間あたりのトランザクション数

チューニングのアプローチ
• 全体最適
– サーバ全体のパフォーマンス(主にスループット)を向上させる
• 個別最適
– 個別の処理のパフォーマンス(主にレスポンスタイム)を向上させる
– DBにおいては、SQLのチューニングが特に重要

チューニングに使えるツールの例
• sar,iostat,vmstatなどのシステム統計
• SHOW FULL PROCESSLIST
• SHOW GLOBAL/SESSION VARIABLES
• SHOW GLOBAL/SESSION STATUS
• SHOW ENGINE INNODB STATUS(innodb_monitor)
• インフォメーション・スキーマ(information_schema)
• EXPLAIN
• スロークエリログ/mysqldumpslow
• プロファイリング
• MySQL Enterprise Monitor
• MySQL Query Analyzer

MySQL 5.6で何が違うの？
• チューニングに活用できるツールが増えています！
機能強化されています！！
– EXPLAINの拡張機能
– MySQL Workbenchを使ったビジュアルEXPLAIN
– パフォーマンス・スキーマ、ps_helper

EXPLAIN
• SQLの実行計画を確認するための機能
– インデックスがどのように使われているか？
– テーブルがどんな順番でJOINされているか？
– ファイルソートが必要だったか？、、、など色々な情報を確認可能
• 取得方法
– mysql> EXPLAIN <SQL文>;
• EXPLAINの見方は、「漢のコンピュータ道」でも解説されている!!
– MySQLのEXPLAINを徹底解説!!
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
SQLの実行計画を確認

EXPLAINの強化ポイント
• SELECT以外でもEXPLAINを取得可能
– UPDATE/INSERT/DELETE/REPLACEでも使用可能
• JSON形式のEXPLAINが取得可能
– TEXT形式よりも多くの情報が含まれている
• ビジュアルEXPLAIN
– EXPLAIN結果をビジュアルに表示
– MySQL Workbenchから取得可能
• オプティマイザ・トレース
MySQL 5.6で機能強化！！

JSON形式のEXPLAIN
• 使い方
– EXPLAIN FORMAT=JSON <SQL文>;
• 取得情報の比較
TEXT形式よりも多くの情報
id
select
type
table type
possible
keys
Key
key
len
ref rows extra
1 SIMPLE t range PRIMARY PRIMARY 4 NULL 2 Using index condition;
Using where
例）TEXT形式の場合
mysql> EXPLAIN SELECT * FROM t WHERE i > 1 AND j < 3;

JSON形式のEXPLAIN
TEXT形式よりも多くの情報
例） JSON形式の場合
mysql> EXPLAIN FORMAT=JSON SELECT * FROM t WHERE i > 1 AND j < 3;
+-------------------------------------------------
| EXPLAIN
+-------------------------------------------------
| {
"query_block": {
"select_id": 1,
"table": {
"table_name": "t",
"access_type": "range",
"possible_keys": [
"PRIMARY"
] /* possible_keys */,
"key": "PRIMARY",
"key_length": "4",
"rows": 2,
"filtered": 100,
"index_condition": "(`test`.`t`.`i` > 1)",
"attached_condition": "(`test`.`t`.`j` < 3)"
} /* table */
} /* query_block */
} |
+-------------------------------------------------
TEXT形式には含まれない情報

ビジュアルEXPLAIN
• MySQL 5.6以降で、ビジュアルEXPLAINを実行可能
• MySQL Workbenchから取得
• オブジェクトへのアクセス
パターンを一目で確認可能
(色で判別可能)
EXPLAINの結果を見やすく表示
このボタンをクリック

• 赤色
– ALL(full table scan)
• 橙色
– Full index scan
– Full Text Index Search
• 緑色
– Range (>,<,…)
– Reference
• 青色(Good)
– EQ_REF
特に
注意！！

• MySQL 5.7では、更に見やすく拡張予定
– JOINの順番も一目で分かる！！

MySQL Workbench
• MySQLの公式GUIツール
• SQL開発、Server管理、データ
モデリングなどの機能が1つに
まとめられたツール
• チューニングに役立つ機能も有り
– ビジュアルEXPLAIN
– オブジェクト定義の確認
– 実行中のSQLをキャプチャ、、、など
MySQL Databaseの統合開発環境

MySQL Workbench
• コミュニティ版と商用版があるが、大半の機能は
コミュニティ版でも利用可能
http://www-jp.mysql.com/products/workbench/features.html
• ダウンロード先
http://dev.mysql.com/downloads/workbench/6.0.html
• マニュアル
http://dev.mysql.com/doc/index-gui.html
• ブログ
http://mysqlworkbench.org

MySQL Workbench
• MySQL Workbenchの活用例
– クエリを実行中のコネクションを探し、
実行時間が長いクエリをSQLエディタで確認
⇒ビジュアルEXPLAINで実行計画を確認、
スキーマツリーからテーブル定義や
インデックス定義を確認
⇒SQLをチューニング
SHOW FULL PROCESSLISTの情報をGUIで確認し、
気になるコネクションを右クリック(Show in Editorを選択)

オプティマイザ・トレース
• オプティマイザが実行計画を生成する際にどのような
判断をしたのか、詳細をトレース可能
• 取得手順
オプティマイザの判断結果を分析
SET SESSION optimizer_trace='enabled=on,one_line=off';
SET SESSION optimizer_trace_max_mem_size=102400; (※)
<任意のクエリーを実行>
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE¥G
SET SESSION optimizer_trace='enabled=off,one_line=off';
※必要に応じて設定(トレースを保持する量をバイト単位で指定、デフォルト値は16384)

パフォーマンス・スキーマ(P_S)
• MySQLサーバ内部の稼働統計を蓄積するスキーマ
– performance_schemaストレージエンジンとして実装されている
• MySQL 5.5から実装されていたが、5.5の時点では
取得できる情報が細かすぎて、一般のMySQLユーザ
には使い辛い状態だった
• MySQL 5.6で大幅に機能強化され、一般のMySQLユーザ
にも役立つ情報が取得可能に！！
MySQLサーバの稼働統計を確認

• 機能が有効になっていることを確認
– MySQL 5.6では、デフォルトで有効になっている
mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+

• 使い方
1．各種の設定を必要に応じて変更(次ページ以降で紹介)
2．performance_schemaストレージエンジン内のテーブルから、
確認したい情報をSELECT
(どんなテーブルがあるかは、show tables in performance_schema;
で確認可能。各テーブルの詳細については、こちらを参照)
※蓄積した情報を初期化したい場合はTRUNCATEを実行
mysql> TRUNCATE TABLE <テーブル名>;

• 各種設定(パラメータ) ※詳細はこちらを参照
mysql> SHOW GLOBAL VARIABLES LIKE 'performance_schema_%';
+-----------------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------------+-------+
| performance_schema_accounts_size | 100 |
| performance_schema_digests_size | 10000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_stages_history_size | 10 |
～中略～
| performance_schema_setup_actors_size | 100 |
| performance_schema_setup_objects_size | 100 |
| performance_schema_users_size | 100 |
+-----------------------------------------------------+-------+
例）accountsテーブルの
行数を設定

– setup_actors：稼働統計収集対象のユーザーを設定
– setup_consumers：どんな情報を蓄積するかを設定
– setup_instruments：収集対象の稼働統計を設定
– setup_objects：稼働統計収集対象のオブジェクトを設定
– setup_timers：イベントの種類ごとにタイマーを設定(通常変更する必要無し)
※設定を変更する場合は、それぞれのテーブルに対してUPDATE文を実行する

– setup_actors：稼働統計収集対象のユーザーを設定
– setup_consumers：どんな情報を蓄積するかを設定
– setup_instruments：収集対象の稼働統計を設定
– setup_objects：稼働統計収集対象のオブジェクトを設定
– setup_timers：イベントの種類ごとにタイマーを設定(通常変更する必要無し)
※特に重要な3つの設定テーブル

• 稼働統計収集対象のユーザーを設定(setup_actorsテーブル)
mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| % | % | % |
+------+------+------+
1 row in set (0.00 sec)
※ROLE列は、現時点(5.6.14時点)では未使用

• 稼働統計収集対象のオブジェクトを設定(setup_objectsテーブル)
mysql> SELECT * FROM performance_schema.setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| TABLE | mysql | % | NO | NO |
| TABLE | performance_schema | % | NO | NO |
| TABLE | information_schema | % | NO | NO |
| TABLE | % | % | YES | YES |
+-------------+--------------------+-------------+---------+-------+

• どんな情報を蓄積するかを設定(setup_consumersテーブル)
mysql> SELECT * FROM performance_schema.setup_consumers;
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | NO |
～中略～
| thread_instrumentation | YES |
| statements_digest | YES |
+--------------------------------+---------+

• 収集対象の稼働統計を設定(setup_instrumentsテーブル)
mysql> SELECT * FROM performance_schema.setup_instruments;
+---------------------------------------------+---------+-------+
| NAME | ENABLED |TIMED |
+---------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock | NO |NO |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync | NO |NO |
～中略～
| wait/io/socket/sql/server_unix_socket | NO |NO |
| wait/io/socket/sql/client_connection | NO |NO |
| idle | YES |YES |
+---------------------------------------------+---------+-------+

• 確認したい情報をSELECTする
– テーブルがたくさんあるが、先ずはsummaryテーブルを活用しよう！
– summaryテーブルの命名規則
• 例）events_waits_summary_global_by_event_name
何のサマリーか？何でグルーピングしているか？

• summaryテーブルの確認例：累積実行時間が最も長いSQLを特定
mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest order by sum_timer_wait desc limit 1¥G
*************************** 1. row ***************************
SCHEMA_NAME: mysqlslap
DIGEST: a0583512c4eb718088979fe23a35a893
DIGEST_TEXT: INSERT INTO t1 VALUES (...)
COUNT_STAR: 21900
SUM_TIMER_WAIT: 42961684472059
MIN_TIMER_WAIT: 288467946
AVG_TIMER_WAIT: 1961335479
MAX_TIMER_WAIT: 35197609565
SUM_LOCK_TIME: 1366001000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
～中略～
FIRST_SEEN: 2013-11-12 07:27:45
LAST_SEEN: 2013-11-12 07:27:58

• _currentテーブルでは、現在の情報を確認可能
– events_statements_currentテーブル
• 実行中のクエリの情報を確認可能
クエリ調査用SQLの例）
SELECT event_name,sql_text
FROM performance_schema.events_statements_current
WHERE lock_time>0 ：ロック時間で絞込み
WHERE no_index_used!=0 ：インデックスが使えていないクエリーを絞込み
WHERE created_tmp_tables>0 ：一時表を使用しているクエリーを絞込み
WHERE created_tmp_disk_tables>0 ：ディスク上に一時表を書き出したクエリーを絞込み
WHERE timer_end – timer_start>1000000000000：実行時間が長いクエリーを絞込み
※単位はピコセカンド(0.000 000 000 001秒)

補足：パフォーマンス・スキーマ(P_S)
• パフォーマンス・スキーマによるオーバーヘッドが気になる場合
– 設定を変更して、収集対象を絞り込むことで、オーバーヘッド削減可能
– システム変数“performance_schema_accounts_size”など、情報収集量を
変更できるパラメータを変更すれば、メモリ使用量も変化する
⇒サイズを小さくすることで、メモリ使用量も削減可能
– レプリケーションを活用するなどして複数台でシステムを構成している場合、
調査用に1台のサーバでだけ有効にする

2014年7月21日追記：補足
• MySQL Workbench 6.1には、P_Sの設定を変更するための
インターフェースが追加されました。GUIで簡単にP_Sの設定が
変更できます。

「パフォーマンス・スキーマ、有益そうだけど難しそう。。。」、と
感じた方に、、、

ps_helper
• パフォーマンス・スキーマの情報をより活用しやすく集計する
ビューやファンクション、ストアドプロシージャのセット
(一部、インフォメーションスキーマを検索しているものも有り)
• オラクル社の Senior Software Development Manager
である Mark Leith が作成
• 以下で公開している(最新のコードはgithubからダウンロードすることを推奨)
– http://www.markleith.co.uk/ps_helper/
(各ビュー、ファンクション、ストアドプロシージャの使い方説明も有り)
– https://github.com/MarkLeith/dbahelper/
パフォーマンス・スキーマをより便利に活用

ps_helper
• ビュー、ファンクション、ストアドプロシージャを作成する
SQL自体を公開しているので、任意でカスタマイズする
ことも可能

2014年7月21日追記：補足
• MySQL Workbench 6.1を使うと、ps_helperをベースに
作成されたsysスキーマをワンクリックでセットアップできます。
そして、sysスキーマの情報を活用して、様々なパフォーマンス
レポートが確認できます。
ここをクリック

ps_helper
• ps_helperの導入
$ git clone https://github.com/MarkLeith/dbahelper.git dbahelper
$ cd dbahelper
$ mysql -u user -p < ps_helper_<version>.sql
<version> ：55, 56, 57が存在する。それぞれ、5.5, 5.6, 5.7用のスクリプト
※ps_helperを導入するとps_helperスキーマが作成され、その中に
ビュー、ファンクション、プロシージャが作成される。

ps_helper
• ps_helperのファンクション
– より読みやすいフォーマットへの変換
• format_time() / format_bytes()
– コマンドラインインターフェースに合わせて、データを圧縮
• format_statement() / format_path()
– ファイル名からスキーマ名/テーブル名を抽出する
• extract_[schema | table]_from_file_name()

ps_helper
• ps_helperのプロシージャ(P_Sの設定をより簡単に)
– currently_enabled() / currently_disabled()
– enable_current_thread() / disable_current_thread()
– enable_background_threads() / disable_background_threads()
– save_current_config() / reload_saved_config()
– reset_to_default() / truncate_all()
– only_enable()

ps_helper
• ps_helperのプロシージャ(詳細な分析用途)
– 特定のスレッドを詳細に監視
• dump_thread_stack()
– 現在のトラフィックの中から、特定のSQLを詳細に分析
• analyze_statement_digest()

ps_helper
• ps_helperのビュー
– 特定のユースケースに合わせて、P_Sのデータをサマリー
– 見やすいフォーマットで表示

ps_helper 使用例

user_summary
ユーザー毎のアクティビティを集計
mysql> select * from user_summary¥G
*************************** 1. row ***************************
user: root
total_statements: 3072
total_latency: 00:04:44.92
avg_latency: 92.75 ms
current_connections: 1
total_connections: 10
unique_hosts: 1

user_summary_by_stages
ユーザー毎に、どのステージで最も時間がかかっているかを分析
mysql> select * from user_summary_by_stages;
+------+--------------------------------+-------+-----------+-----------+
| user | event_name | count | wait_sum | wait_avg |
+------+--------------------------------+-------+-----------+-----------+
| root | stage/sql/creating table | 8 | 1.42 s | 178.10 ms |
| root | stage/sql/System lock | 28 | 246.30 ms | 8.80 ms |
| root | stage/sql/checking permissions | 59 | 205.34 ms | 3.48 ms |
| root | stage/sql/Opening tables | 40 | 28.55 ms | 713.79 us |
| root | stage/sql/query end | 45 | 18.51 ms | 411.42 us |
| root | stage/sql/updating | 4 | 16.94 ms | 4.23 ms |
| root | stage/sql/statistics | 18 | 11.96 ms | 664.43 us |
| root | stage/sql/init | 75 | 3.57 ms | 47.62 us |
| root | stage/sql/Sending data | 18 | 1.28 ms | 71.21 us |

user_summary_by_statement_type
コマンドの種類毎、ユーザ毎に稼働統計を集計
mysql> select * from user_summary_by_statement_type;
+------+-----------------------+-------+---------------+-------------+--------------+-----------+----------------------+---------------+------------+
| user | statement | count | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------+-----------------------+-------+---------------+-------------+--------------+-----------+----------------------+---------------+------------+
| root | create_view | 879 | 00:02:16.98 | 966.39 ms | 1.11 s | 0 | 0 | 0 | 0 |
| root | call_procedure | 5 | 00:02:06.75 | 00:01:05.80 | 00:02:06.13 | 0 | 11414288391619301677 | 0 | 2 |
| root | select | 114 | 6.15 s | 1.57 s | 1.00 s | 878 | 26038 | 0 | 65 |
| root | Field List | 181 | 2.90 s | 514.46 ms | 498.74 ms | 0 | 0 | 0 | 0 |
| root | drop_view | 858 | 2.05 s | 223.08 ms | 0 ps | 0 | 0 | 0 | 0 |
| root | show_tables | 33 | 1.80 s | 1.00 s | 814.10 ms | 795 | 795 | 0 | 33 |
| root | drop_function | 159 | 1.48 s | 966.11 ms | 1.38 s | 0 | 0 | 0 | 0 |
| root | create_table | 8 | 1.43 s | 520.83 ms | 0 ps | 0 | 0 | 0 | 0 |
| root | drop_db | 13 | 1.22 s | 249.29 ms | 1.15 s | 0 | 0 | 514 | 0 |
| root | show_fields | 14 | 913.79 ms | 424.08 ms | 514.58 ms | 192 | 192 | 0 | 14 |
| root | show_databases | 19 | 662.52 ms | 479.86 ms | 333.05 ms | 95 | 95 | 0 | 19 |
| root | create_procedure | 235 | 510.45 ms | 137.29 ms | 417.21 ms | 0 | 0 | 0 | 0 |
| root | set_option | 72 | 396.58 ms | 140.56 ms | 0 ps | 0 | 0 | 0 | 0 |

io_by_thread_by_latency
最もファイルI/Oに時間がかかっているスレッドを特定
mysql> select * from io_by_thread_by_latency limit 1¥G
*************************** 1. row ***************************
user: root@localhost
count_star: 8153
total_latency: 16.54 s
min_latency: 448.63 ns
max_latency: 279.73 ms
thread_id: 20
processlist_id: 1

io_global_by_file_by_latency
最もファイルI/Oに時間がかかっているファイルを特定
mysql> select * from io_global_by_file_by_latency limit 1¥G
*************************** 1. row ***************************
File: @@datadir/ps_helper/statement_analysis_raw.frm~
count_star: 30
total_latency: 513.96 ms
count_read: 0
read_latency: 0 ps
count_write: 5
write_latency: 132.13 us
count_misc: 25
misc_latency: 513.83 ms

io_global_by_file_by_bytes
最もファイルI/O量が多いファイルを特定
mysql> select * from io_global_by_file_by_bytes limit 1¥G
*************************** 1. row ***************************
file: @@datadir/ibtmp1
count_read: 0
total_read: 0 bytes
avg_read: 0 bytes
count_write: 48
total_written: 13.06 MiB
avg_write: 278.67 KiB
total: 13.06 MiB
write_pct: 100.00

io_global_by_wait_by_latency
ファイルI/O関連のイベントで最も時間がかかっているイベントを特定
mysql> select * from io_global_by_wait_by_latency limit 1¥G
*************************** 1. row ***************************
event_name: sql/file_parser
count_star: 2066
max_latency: 279.73 ms
read_latency: 0 ps
write_latency: 9.80 ms
misc_latency: 15.90 s
...

io_global_by_wait_by_latency (続き)
mysql> select * from io_global_by_wait_by_latency limit 1¥G
*************************** 1. row ***************************
...
count_read: 0
total_read: 0 bytes
avg_read: 0 bytes
count_write: 281
total_written: 878.16 KiB
avg_written: 3.13 KiB
ファイルI/O関連のイベントで最も時間がかかっているイベントを特定

ps_helper 使用例
Schema 分析
テーブルの使用状況
インデックスの使用状況
オブジェクトの概要
+-------------------------------------+
+-------------------------------------+
| schema_index_statistics |
| schema_object_overview |
| schema_table_statistics |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |
| schema_unused_indexes |
+-------------------------------------+

schema_table_statistics_with_buffer
mysql> select * from schema_table_statistics_with_buffer¥G
*************************** 1. row ***************************
table_schema: mem__quan
table_name: example_statements
rows_fetched: 544
fetch_latency: 672.82 ms
rows_inserted: 10815
insert_latency: 00:07:12.10
rows_updated: 522
update_latency: 11.88 s
rows_deleted: 0
delete_latency: 0 ps
InnoDBバッファプール上に存在するテーブルのIO情報を表示

schema_table_statistics_with_buffer (続き)
*************************** 1. row ***************************
...
io_read_requests: 36
io_read: 88.60 KiB
io_read_latency: 192.23 us
io_write_requests: 1947
io_write: 34.02 MiB
io_write_latency: 73.89 ms
io_misc_requests: 142
io_misc_latency: 1.48 s
...

schema_table_statistics_with_buffer (続き)
*************************** 1. row ***************************
...
innodb_buffer_allocated: 4.93 MiB
innodb_buffer_data: 6.32 MiB
innodb_buffer_pages: 631
innodb_buffer_pages_hashed: 631
innodb_buffer_pages_old: 631
innodb_buffer_rows_cached: 10450

schema_index_statistics
mysql> select * from schema_index_statistics limit 1¥G
*************************** 1. row ***************************
table_schema: mem__inventory
table_name: mysqlserver
index_name: PRIMARY
rows_selected: 2619
select_latency: 36.46 s
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 494
update_latency: 2.35 s
rows_deleted: 0
delete_latency: 0 ps
インデックスのIO情報を表示

ps_helper 使用例
Statement(SQL) 分析
標準化されたSQL文の概要
SQLの実行状況、遅延状況
+---------------------------------------------+
+---------------------------------------------+
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
+---------------------------------------------+
※DBのパフォーマンス問題の大半はSQLに
依存した問題の為、SQLを調査できるこれらの
ビューは、特にお勧め！！

statement_analysis
mysql> select * from statement_analysis limit 1¥G
*************************** 1. row ***************************
query: SELECT * FROM ( SELECT `digest ... ` ,
`sum_no_index_used` AS ...
full_scan: *
exec_count: 99
err_count: 0
warn_count: 0
max_latency: 2.47 s
lock_latency: 69.14 ms
...
実行に時間がかかっているSQLを特定(total_latencyでソートされている)

statement_analysis (続き)
mysql> select * from statement_analysis limit 1¥G
*************************** 1. row ***************************
...
rows_sent: 36136
rows_sent_avg: 365
rows_scanned: 1520720
tmp_tables: 693
tmp_disk_tables: 297
rows_sorted: 38566
sort_merge_passes: 0
digest: 50761c6a1818824328745d8a136b9ed6
実行に時間がかかっているSQLを特定(total_latencyでソートされている)

statement_with_full_table_scans
mysql> select * from statements_with_full_table_scans limit 1¥G
*************************** 1. row ***************************
query: SELECT COUNT ( * ) FROM `INFOR ... NE = ? AND
`SUPPORT` IN (...)
exec_count: 1206
no_index_used_count: 1206
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 1206
rows_examined: 10854
digest: 491ee7143ca1d98f36c24d7eb6d25272
インデックスが使えていないSQLを確認
(インデックスの未使用率が高い順に表示)

statement_with_temp_tables
mysql> select * from statements_with_temp_tables limit 1¥G
*************************** 1. row ***************************
query: SELECT * FROM `schema_object_o ... MA` ,
`information_schema` ...
exec_count: 1
memory_tmp_tables: 192
disk_tmp_tables: 33
avg_tmp_tables_per_query: 192
tmp_tables_to_disk_pct: 17
digest: c463377d1d2bce2b32cf0fe84d414669
一時表を最も使用しているSQLを特定

ps_helper 使用例
Statement(SQL) 分析(詳細)
特定のSQL文を分析
特定のスレッドを分析
analyze_statement_digest()
dump_thread_stack()

現在のトラフィックの中から特定のSQLの稼働統計を分析
digestが一致するSQLの稼働統計を取集
レポートされるもの
- サマリー
- 最も時間がかかったSQLの稼働情報
- EXPLAIN
概要

in_digest – The statement digest to analyze
in_runtime – How long to run analysis for
in_interval – How often to snapshot data
in_start_fresh – Whether to truncate all P_S data first
in_auto_enable – Whether to auto enabled required consumers
パラメーター

mysql> call analyze_statement_digest('6134e9d6f25eb8e6cddf11f6938f202a', 60, 0.1, true, true);
+--------------------+
| SUMMARY STATISTICS |
+--------------------+
| SUMMARY STATISTICS |
+--------------------+
+------------+-----------+-----------+-----------+---------------+------------+------------+
| executions | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scans |
+------------+-----------+-----------+-----------+---------------+------------+------------+
| 360 | 1.41 s | 138.39 ms | 720 | 0 | 0 | 0 |
+------------+-----------+-----------+-----------+---------------+------------+------------+
+--------------------------------+-------+-----------+
| event_name | count | latency |
+--------------------------------+-------+-----------+
| stage/sql/Sending data | 14 | 1.10 s |
| stage/sql/update | 198 | 667.34 ms |
| stage/sql/removing tmp table | 9 | 240.48 ms |
...

+---------------------------+
| LONGEST RUNNING STATEMENT |
+---------------------------+
| LONGEST RUNNING STATEMENT |
+---------------------------+
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
| thread_id | exec_time | lock_time | rows_sent | rows_examined | tmp_tables | full_scan |
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
| 23277 | 77.64 ms | 97.00 us | 2 | 0 | 0 | 0 |
+-----------+-----------+-----------+-----------+---------------+------------+-----------+
+-------------------------------------------------------------------------------------------+
| sql_text |
+-------------------------------------------------------------------------------------------+
| /* mem dbpool.default */ insert into `mem__quan`.`normalized_statements_by_server_by_schema`
(firstSeen, lastSeen, normalized_statement_id, `schema`, server, id) values (1378983745000,
1378998600000, x'808ACEB88FC4B45BC47C4FE9D86C7F26', 'mem', '753c939e-1b99-11e3-b6d4-bc761a1f4f2f',
x'7CFE81F98B1E3FE0895D1AA2C39B326D') ON DUPLICATE KEY UPDATE ...
+-------------------------------------------------------------------------------------------+

+--------------------------------+----------+
| event_name | latency |
+--------------------------------+----------+
| stage/sql/init | 10.41 ms |
| stage/sql/checking permissions | 2.35 us |
| stage/sql/Opening tables | 10.32 us |
| stage/sql/init | 13.53 us |
| stage/sql/System lock | 3.66 us |
| stage/sql/update | 61.74 ms |
| stage/sql/end | 1.11 us |
| stage/sql/query end | 1.31 us |
| stage/sql/closing tables | 5.44 ms |
| stage/sql/freeing items | 23.31 us |
+--------------------------------+----------+
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

dump_thread_stack()
特定のスレッドを監視
多くの情報をキャプチャ
イベントの階層をグラフ化できる .dot フォーマットのファイルを出力
概要

dump_thread_stack()
in_thread_id – The thread to analyze
in_outfile – The file to dump the data too (INTO
OUTFILE)
in_max_runtime – How long to run analysis for
in_interval – How often to snapshot data
in_start_fresh – Whether to truncate all P_S data first
in_auto_setup – Whether to auto enabled required config
in_debug – Whether to also print debug info (source
etc.)
パラメーター

mysql> call dump_thread_stack(27768, '/tmp/stack_27768.dot', 60, 0.1, true, true, true);
+------------------------------------------------+
| Info |
+------------------------------------------------+
| Data collection starting for THREAD_ID = 27768 |
+------------------------------------------------+
+---------------------------------------------+
| Info |
+---------------------------------------------+
| Stack trace written to /tmp/stack_27768.dot |
+---------------------------------------------+
+--------------------------------------------------------+
| Convert to PDF |
+--------------------------------------------------------+
| dot -Tpdf -o /tmp/stack_27768.pdf /tmp/stack_27768.dot |
+--------------------------------------------------------+
+--------------------------------------------------------+
| Convert to PNG |
+--------------------------------------------------------+
| dot -Tpng -o /tmp/stack_27768.png /tmp/stack_27768.dot |
+--------------------------------------------------------+
1 row in set (60.90 sec)+

「ps_helper、使いこなせるかなぁ。。。」、と
不安を感じた方に、、、

MEM(MySQL Enterprise Monitor)
• 商用版(MySQL Enterprise Edition)で
使用できる機能の一つ
http://www-jp.mysql.com/products/
• リアルタイムでMySQLの稼働状況と
パフォーマンスを監視
• 問題発生の予兆を知らせる警告機能
• エキスパートによる問題解決アドバイス
GUIでパフォーマンスの問題を素早く発見、警告で問題を通知

MySQL Query Analyzer
• MEMに含まれる機能の一つ
• クエリの情報を自動的に収集
• CPU使用率など、相関関係のある
グラフを見ながら高コストなクエリを
素早く発見
クエリの問題を素早く発見
「MySQL Query Analyzer を使用することで、問題のある
SQLコードを特定および解析して、データベースパフォー
マンスを3倍に改善することができました。さらに重要なこ
とに、これは、何週間もかからずに、わずか3日で実現で
きました」
Big Fish Games 社
ソフトウェア開発エンジニア
キース・ソーラダ氏 (Keith Souhrada)
※更に、クエリのチューニングが上手くいかない場合は、
「コンサルティングサポート」も利用可能！！
http://www-jp.mysql.com/support/consultative.html
⇒”クエリー・レビュー” 部分参照

MEM、MySQL Query Analyzerの試使用
• Oracle Software Delivery Cloud
http://edelivery.oracle.com/
• 製品パックを選択：“MySQL Database”
• 以下モジュールをダウンロード
– MySQL Enterprise Monitor Service
Manager 3.X.X
– MySQL Enterprise Monitor Agent 3.X.X (※)
– MySQL Enterprise Monitor Connector/XXX
Plugin Pack 3.0.2 (multiple platforms) (※)
※必要に応じてダウンロード
• 製品マニュアル
http://dev.mysql.com/doc/index-enterprise.html
30日間トライアル

MySQL Enterprise Monitor 3.0 デモビデオ
http://www-jp.mysql.com/products/enterprise/demo.html

[D14] MySQL 5.6時代のパフォーマンスチューニング *db tech showcase 2013 Tokyo

More Related Content