TiDBでのスロークエリの特定と対応方法

このスライドでは、分散データベースTiDBでのスロークエリの特定と対応方法について、データベースの性能問題が発生した場合の問題の切り分けに役立つよう、TiDB Dashboardで利用可能なTop SQLページ、SQLステートメントページの利用方法、またinformation_schema内に保存されるstatements_summary表を利用したSQL実行履歴の確認方法を説明します。

トピック：
・TiDB DashboardのTop SQLページとSQLステートメントページから遅いクエリを発見する方法
・TiDB Dashboard キービジュアライザーの利用方法
・statements_summary表、slow_query表の活用方法

アーカイブ動画：
https://youtu.be/UThN3Z4GQm4

Transcript

1. PingCAP Education： TiDBでのスロークエリの特定と 対応方法
 本多康夫 Technical support engineer at PingCAP Japan
2. 本日の位置づけ https://pingcap.co.jp/event/ 
 
 2022年7月28日(木) 14:00-15:00 PingCAP Education：TiDBでの表設計 2022年9月9日(金) 14:00-15:00 PingCAP Education：TiKV-トランザクション＆MVCC 2022年9月30日(金) 14:00-15:00 PingCAP Education：TiDBでのSQL実行の仕組み 2022年10月21日(金) 14:00-15:00 PingCAP Education：TiKV-データの整合性 2022年10月28日(金) 14:00-15:00 PingCAP Education：TiDB でのロック競合のトラブルシューティング 2022年11月25日(金) 14:00-15:00 PingCAP Education：TiDBでのスロークエリの特定と対応方法 2022年12月9日(金) 14:00-15:00 PingCAP Education：TiDB Data Migrationのご紹介 2022年12月22日(木) 14:00-15:00 PingCAP Education：TiDBでの索引設計 ※過去開催アーカイブ　：　https://pingcap.co.jp/event-video/
3. アジェンダ • TiDB Dashboardとは • TiDB Dashboardから遅いクエリを発見する方法 • TiDB Dashboardキービジュアライザーの利用方法 • TiDBでのスロークエリ関連のパラメータ • statements_summary表、slow_query表の活用方法 • Q&A, アンケートのお願い • お知らせ
4. TiDB Dashboardとは
5. • TiDBの稼働状況が一目でわかる情報を表示します ◦ TiDB クラスタのインストール時に PDサーバー上に自動的に構成されます ◦ TiDB Cloudでは Diagnosis タブに表示されます TiDB Dashboardとは
6. • 全体的なステータス (QPS, Latency, CPU,メモリ使用量)の表示 TiDB Dashboard : Overview
7. • TIDBクラスタトポロジー(TiDB, TiKV, PD)のステータス表示 TiDB Dashboard : Cluster Info
8. • 時間帯ごとの負荷の高い (CPU使用率の高い)上位5つのSQLを表示(デフォルトでは無効) TiDB Dashboard : Top SQL
9. • 過去一定期間(デフォルト30分)に実行されたSQLを表示 / statement_summaryテーブルの内容 TiDB Dashboard : SQL Statements
10. • しきい値(デフォルト0.3秒)より長く実行されたSQLを表示 TiDB Dashboard : Slow Queries
11. • TiKVの読み書きの変化をヒートマップで表示 TiDB Dashboard : Key Visualizer
12. TiDB Dashboardから 遅いクエリを発見する方法
13. • 性能関連のダッシュボードが複数ありますが、下記のように使い分けていただくことが可能です • Top SQL ◦ TiDB、TiKVサーバーにCPU使用率の高い時間帯がある場合 ◦ 特定のSQLによる負荷が高い可能性がある場合 • SQL Statements ◦ 一つ一つのSQLの実行時間は短いが、実行回数が多く累積の実行時間が長い場合 ◦ SQLは正規化されます(複数の実行が一つの SQLにまとめられます) • Slow Queries ◦ 特定のSQLの処理時間が長い場合 ◦ SQLは正規化されません (1回1回の実行が個別に記録されます ) • Key Visualizer ◦ IOのボトルネック、ホットスポットが疑われる場合 TiDB Dashboard使い分け
14. • TiDB 6.4.0 DMR ◦ PD x 2 ◦ TiDB x 2 ◦ TiKV x 3 • AWSのEC2インスタンス上にtiupを利用して構築 ◦ EC2インスタンス: M5.xlarge ◦ EBS : gp3 環境
15. • TiDBを構築する tiup コマンドでは、TPC-C、TPC-Hなどのベンチマークが実行可能です • 8つのwarehouses(倉庫)を8ハッシュパーティションでデータの準備 (prepare) • 8つのwarehouses(倉庫)を8ハッシュパーティションで 300秒ベンチマークの実行 (run) • ベンチマークで利用したテーブルの削除 (cleanup) tiupコマンドによるベンチマークの実行 $ tiup bench tpcc --warehouses 8 --parts 8 prepare --host $TIDB_SERVER1 --port 4000 $ tiup bench tpcc --warehouses 8 --parts 8 run --time 300s --host $TIDB_SERVER1 --port 4000 $ tiup bench tpcc --warehouses 4 --parts 4 cleanup --host $TIDB_SERVER1 --port 4000
16. • 実行例 tiupコマンドによるベンチマークの実行 $ tiup bench tpcc --warehouses 4 --parts 4 run --time 180s --host $TIDB_SERVER1 --port 4000 tiup is checking updates for component bench ... Starting component `bench`: /home/ec2-user/.tiup/components/bench/v1.12.0/tiup-bench tpcc --warehouses 4 --parts 4 run --time 180s --host 10.0.129.125 --port 4000 [Current] DELIVERY - Takes(s): 9.8, Count: 31, TPM: 189.8, Sum(ms): 1469.5, Avg(ms): 47.3, 50th(ms): …中略 … Finished [Summary] DELIVERY - Takes(s): 179.8, Count: 569, TPM: 189.9, Sum(ms): 27063.7, Avg(ms): 47.6, 50th(ms): 48.2, 90th(ms): 52.4, 95th(ms): 52.4, 99th(ms): 56.6, 99.9th(ms): 65.0, Max(ms): 65.0 [Summary] NEW_ORDER - Takes(s): 180.0, Count: 6701, TPM: 2234.2, Sum(ms): 93567.5, Avg(ms): 14.0, 50th(ms): 14.2, 90th(ms): 16.3, 95th(ms): 16.8, 99th(ms): 21.0, 99.9th(ms): 30.4, Max(ms): 50.3 [Summary] NEW_ORDER_ERR - Takes(s): 180.0, Count: 1, TPM: 0.3, Sum(ms): 6.7, Avg(ms): 6.6, 50th(ms): 6.8, 90th(ms): 6.8, 95th(ms): 6.8, 99th(ms): 6.8, 99.9th(ms): 6.8, Max(ms): 6.8 [Summary] ORDER_STATUS - Takes(s): 179.3, Count: 556, TPM: 186.0, Sum(ms): 3067.2, Avg(ms): 5.5, 50th(ms): 6.3, 90th(ms): 6.8, 95th(ms): 7.9, 99th(ms): 8.4, 99.9th(ms): 8.9, Max(ms): 12.6 [Summary] PAYMENT - Takes(s): 179.9, Count: 6166, TPM: 2056.0, Sum(ms): 49203.7, Avg(ms): 8.0, 50th(ms): 7.9, 90th(ms): 9.4, 95th(ms): 10.0, 99th(ms): 14.7, 99.9th(ms): 26.2, Max(ms): 32.5 [Summary] STOCK_LEVEL - Takes(s): 179.9, Count: 569, TPM: 189.8, Sum(ms): 4802.8, Avg(ms): 8.4, 50th(ms): 8.4, 90th(ms): 10.0, 95th(ms): 10.5, 99th(ms): 12.1, 99.9th(ms): 15.2, Max(ms): 16.8 tpmC: 2234.2, tpmTotal: 4855.9, efficiency: 4343.4%
17. TiDB DashboardでのSlow queryの絞り込み • 時間範囲、データベース、 SQL数の制限、キーワードによる絞り込みが可能です
18. TiDB DashboardでのSlow queryのカラム • TiDB Instance ◦ どのTiDBサーバー • Is Internal? ◦ 内部的に実行されたものか
19. TiDB DashboardでのSlow queryの並び替え • TiDB Instance, Finish Time, Latency, Max Memoryをクリックすると並び替え可能です • 昇順、降順
20. TiDB DashboardでのSQL全文表示 • Queryの部分にマウスカーソルを移動させると SQL全文が表示されます
21. TiDB DashboardでのSlow queryの詳細表示 • Queryの部分をクリックすると Execution Plan(実行計画)、Time(Queryの内訳)などが表示可能です
22. TiDB DashboardでのTop SQL • Top SQLはデフォルトでは無効になっています (約3%の性能への影響があるため ) • Enable Featureで有効にして、Saveすると1分後から利用できます (TiDBの再起動は不要です ) MySQL [test]> SET GLOBAL tidb_enable_top_sql = 1; MySQL [test]> select @@global.tidb_enable_top_sql; +------------------------------+ | @@global.tidb_enable_top_sql | +------------------------------+ | 1 | +------------------------------+ • システム変数 tidb_enable_top_sql でも設定可能です
23. TiDB DashboardでのTop SQLの絞り込み • 選択された範囲でTotal CPU Time時間の長い順から5つのSQLが表示されます • SQL文、実行計画などが表示されます • それ以外は Other Statementsとしてひとくくりにされ、詳細は表示されません
24. TiDB Dashboard キービジュアライザーの利用方法
25. • キービジュアライザー ◦ 横軸 : 時間 ◦ 縦軸 : テーブル(リージョンのサイズ) ◦ 色の濃淡 : IO量(色の濃いほどIO量が多い) TiDB Dashboardでのキービジュアライザー
26. Demo
27. TiDBでのスロークエリ関連のパラメータ
28. • システム変数`tidb_enable_slow_log`によってslow query logの設定が可能(デフォルトは有効) TiDBのスロークエリー設定 MySQL [(none)]> select @@global.tidb_enable_slow_log; +-------------------------------+ | @@global.tidb_enable_slow_log | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.00 sec)
29. • システム変数`tidb_slow_log_threshold`によってslow query log(デフォルトは300ミリ秒) • 今回のデモ環境は10ミリ秒(0.01秒)に値を変更しています TiDBのスロークエリー設定 MySQL [(none)]> select @@global.tidb_slow_log_threshold; +----------------------------------+ | @@global.tidb_slow_log_threshold | +----------------------------------+ | 300 | +----------------------------------+ MySQL [(none)]> set global tidb_slow_log_threshold = 10; MySQL [(none)]> select @@global.tidb_slow_log_threshold; +----------------------------------+ | @@global.tidb_slow_log_threshold | +----------------------------------+ | 10 | +----------------------------------+
30. • システム変数`tidb_slow_query_file`によってslow query logの位置が設定可能 • Slow query logは各TiDBサーバーのファイルシステム内に生成されます TiDBのスロークエリー設定 MySQL [(none)]> select @@session.tidb_slow_query_file; +------------------------------------------------+ | @@session.tidb_slow_query_file | +------------------------------------------------+ | /tidb-deploy/tidb-4000/log/tidb_slow_query.log | +------------------------------------------------+ $ head /tidb-deploy/tidb-4000/log/tidb_slow_query.log # Time: 2022-11-21T07:23:49.284869639Z # Txn_start_ts: 0 # User@Host: root[root] @ 10.0.5.38 [10.0.5.38] # Conn_ID: 3681431011607970199 # Query_time: 0.522435943 # Parse_time: 0.000103427
31. statements_summary表 slow_query表の活用方法
32. • システム変数`tidb_enable_stmt_summary`によって有効、無効が設定可能 (デフォルト有効) • システム変数 global.tidb_stmt_summary_refresh_interval 保持期間が設定可能 (デフォルト1800秒) • システム変数 tidb_stmt_summary_max_stmt_count 保持数が設定可能(デフォルト: 3000) TiDBのステートメントサマリー設定 MySQL [(none)]> select @@global.tidb_enable_stmt_summary; +-----------------------------------+ | @@global.tidb_enable_stmt_summary | +-----------------------------------+ | 1 | +-----------------------------------+ MySQL [(none)]> select @@global.tidb_stmt_summary_refresh_interval; +---------------------------------------------+ | @@global.tidb_stmt_summary_refresh_interval | +---------------------------------------------+ | 1800 | +---------------------------------------------+ MySQL [(none)]> select @@global.tidb_stmt_summary_max_stmt_count; +-------------------------------------------+ | @@global.tidb_stmt_summary_max_stmt_count | +-------------------------------------------+ | 3000 | +-------------------------------------------+
33. • TiDBサーバーで実行中のオペレーションを表示 • information_schema.slow_query ◦ 接続しているTiDBサーバーのみのslow queryを表示 • information_schema.cluster_slow_query ◦ すべてのTiDBサーバーでのslow queryを表示 ◦ どのTiDBサーバーかはINSTANCE列で区別可能 SLOW_QUERY / CLUSTER_SLOW_QUERY
34. • testデータベースに実行された SQLから、selectで時間がかかった順に 5つ表示 ◦ SQL全文は長いため、digestと併せて表示 select query_time, left(query, 100),digest from information_schema.cluster_slow_query where is_internal = false and db = 'test' and query like 'SELECT%' order by query_time desc limit 5; SLOW_QUERY / CLUSTER_SLOW_QUERY
35. Q&A、アンケートのお願い
36. PingCAP定期ウェビナー https://pingcap.co.jp/event/ ※過去開催アーカイブ　：　 https://pingcap.co.jp/event-video/
37. 次回以降のアジェンダ https://pingcap.co.jp/event/ 
 
 2022年12月9日(金) 14:00-15:00 PingCAP Education：TiDB Data Migrationのご紹介 2022年12月22日(木) 14:00-15:00 PingCAP Education：TiDBでの索引設計
38. PingCAP Education トレーニング トレーニング全体リンク 　　https://en.pingcap.com/education/ 無償コース（推奨） 　　TiDB Technical Essentials 101: 内容 　　- TiDB全体のアーキテクチャ 　　- TiDBの各コンポーネントのアーキテクチャ 　　- HTAP Overview & TiFlashのアーキテクチャ 　　- TiDB Cloudの始め方 　　- TiDB 6.0情報 Course: TiDB Essentials 101 トレーニングを通じ、TiDBの理解を深めることができます。
39. クラウドアプリのスタートをご支援！ TiDB Cloud 1年無料キャンペーン 日本進出から１周年を記念し、TiDB Cloudをご利用いただいたことがないお客様を対象にTiDB Cloud1年間相当の値引きキャンペーンを実施します。 キャンペーン期間 2022年5月18日～2023年3月31日　申込み分まで キャンペーン内容 最大700万円相当（TiDB Cloud典型構成で1年分相当）の値引きを提供します。 対象・適用条件 下記条件をみたすお客様 ※1（最大10社※2） 　- TiDB Cloudを使用したことがないお客様 　- 事例・プロモーションにご協力いただけるお客様 　- 設立から1年以上経過しているお客様 適用方法 フォームにご入力の上、キャンペーンにお申し込みください。審査結果およびキャンペーン の詳細について担当者より連絡いたします。 URL https://pingcap.co.jp/start-dash-202205/ ※1 別途所定の審査あり
 ※2 予定数に達し次第終了する可能性があります。

40. TiDB Cloud : 無償トライアルのご案内 https://tidbcloud.com/signup Serverless TierではTiDB Cloudを無償で1年間ご利用頂けます。 ※容量制限はありますが 本番と同等の機能を 提供しているため、 アプリとの接続試験 などが容易にできます。
41. OSS Insight : 活発度が分かる分析サービス 46億を超えるGitHub上のイベント分析するデータベースとしてTiDB (TiFlash)を活用 https://ossinsight.io/ ①各イベント発生の推移 ①GitHubイベント データを1分おきに同期 +TiFlash https://ossinsight.io/ ②Pull requests地域の表示 ②分析クエリを TiFlash(カラムストア ) で高速処理 ③ジャンル内での比較（人気言語等） etc…
42. TiDB / TiDB Cloud 日本語ドキュメント https://docs.pingcap.com/ja/tidb/stable 日本語訳問題の報告は こちらからお願いします。 https://github.com/pingcap/docs/issues issue作成時のLabel： i18n-ja type/bug-fix
43. @PingCAP_Japan Twitterのフォローをお願いします。
44. Thank You!
 https://www.pingcap.com/
 info@pingcap.com