インデックスに基づいたチューニング

このスライドでは、分散型NewSQLデータベース「TiDB」でより効率良くインデックスを活用できる趣旨に沿いながら、パフォーマンス向上を図ってTiDBのインデックスの活用方法を各観点から解説いたします。インデックスの作成におけるTiDBの内部処理を紹介した上で、インデックス作成のパフォーマンスに有効なパラメータ紹介、また実際のテスト結果を基づいたインデックスの作成によるオンライン業務の影響、インデックスを使用したオプティマイザ演算子、オプティマイザによるインデックスの使用基準について説明します。

トピック
・インデックスのメンテナンス方法
・インデックスの追加によるサービスの影響
・インデックスを使用したオプティマイザ演算子
・オプティマイザによるインデックスの使用基準

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

QUIZ.1(資料P33)の解答：AとD
（解説）
・Aは正しいです。インデックスを作成する際に、対象列に書き込みがある場合、Write Conflict によりonline インデックス作成が失敗することがあります。
・インデックスの作成スピットは対象テーブルのカラム数や既存のインデックスの数とは関係ないため、B、Cは誤りです。
・Tidb_ddl_reorg_worker_cntは索引のデータ作成プロセス re organize の並列度をコントロールするパラメータとなります。大きくすると、インデックスの作成スピードが早まりますので、Dが正しいです。

QUIZ.2(資料P35)の解答：D
（解説）
・ご質問でもいただきました Oracle にあるIndex Skip Scan はTiDB では現時点で実装されておりませんので、DはTiDB のスキャン演算子ではございません。
・他の選択肢A、B、C、EはTiDB には実装されている演算子となります。

Transcript

1. Copyright ©2022 PingCAP. All Rights Reserved. インデックスに関するチューニング
 Xinke Qiao / 喬 心軻(キョウ　シンカ) 
 Technical support engineer 
 PingCAP Japan

2. 本日の位置づけ
 https://pingcap.co.jp/event/
 
 
 2022年10月28日(金) 14:00-15:00 
 PingCAP Education：TiDB でのロック競合のトラブルシューティング 
 2022年11月25日(金) 14:00-15:00 
 PingCAP Education： TiDBでのスロークエリの特定と対応方法 
 2022年12月09日(金) 14:00-15:00 
 PingCAP Education：TiDB Data Migrationのご紹介 
 2022年12月22日(木) 14:00-15:00 
 PingCAP Education：TiDB でのインデックス設計 
 2023年01月13日(金) 14:00-15:00 
 PingCAP Education：インデックスに関するチューニング 
 2023年01月20日(金) 14:00-15:00 
 PingCAP Education：TiCDC、Dumpling、Lightningの紹介 
 2023年02月03日(金) 14:00-15:00 
 TiDB 6.5 LTS新機能の紹介 
 ※過去開催アーカイブ　：　https://www.youtube.com/channel/UCatxrGZANSnii2fe7FeEwvg/playlists 

3. Copyright ©2022 PingCAP. All Rights Reserved. インデックスに関するチューニング
 Xinke Qiao / 喬 心軻(キョウ　シンカ) 
 Technical support engineer 
 PingCAP Japan

4. Copyright ©2022 PingCAP. All Rights Reserved. アジェンダ
 • インデックスのメンテナンス方法
 
 • インデックスの追加によるサービスへの影響
 
 • オプティマイザ演算子
 
 • インデックスの使用基準
 
 • まとめ&QA

5. Copyright ©2022 PingCAP. All Rights Reserved. メンテナンス方法
 01
6. Copyright ©2022 PingCAP. All Rights Reserved. TiDB での Online DDL
 
 • Online Asynchronous Schema Change Algorithm in Google F1 
 • v6.3 から提供している Metadata lock 機能
 
 • コマンド例
 現在実行中の DDL を確認する) 
 ADMIN SHOW DDL; 
 
 DDL の実行状況を確認する) 
 ADMIN SHOW DDL JOBS; 
 
 手動で DDL jobs をキャンセルする) 
 ADMIN CANCEL DDL JOBS job_id [, job_id] ...; 

7. Copyright ©2022 PingCAP. All Rights Reserved. TiDB での Online Index Creation
 TiDB Server TiKV start job TiDB Server job queue history queue DDL workers PD … owner Protocol Layer Parse Compile schema load schema load start job DDL workers 1. Send DDL SQL 6. Return 2. Put job to job queue or add index job queue 3.If the owner isn’t itself， do nothing 3.1.If the owner is itself 3.2. get the first job from job queue ( add index queue ) handle it DDL add index worker 5. if it can get the job from history job queue, finish 4. Move job from job queue ( add index queue ) to job history queue job add index job queue 過去ウェビナー: TiDBでのSQL実行の仕組み 

8. Copyright ©2022 PingCAP. All Rights Reserved. Parameter
 Default Value
 Description
 tidb_ddl_reorg_worker_cnt
 4
 DDL の re-organize の並列度(ワーカーの数)を調整する
 tidb_ddl_reorg_batch_size
 256
 ワーカーが一度に処理する行数(Batch Size)
 tidb_ddl_reorg_priority
 PRIORITY_LOW
 re-organize のプライオリティ PRIORITY_LOW/PRIORITY_NORMAL/PRIORITY_HIGH
 tidb_ddl_enable_fast_reorg
 ON
 有効の場合、re-organize が高速化される
 (New in v6.3)
 tidb_ddl_error_count_limit
 512
 DDL が失敗した際に、リトライの回数
 リトライの回数を超えても作成されない場合は索引の作成
 が失敗する
 インデックスの作成におけるチューニング(1)

9. Copyright ©2022 PingCAP. All Rights Reserved. インデックスの作成におけるチューニング(2)
 Online でインデックスを作成する際に、以下の2つの項目を随時監視します。 
 状況に応じて、パラメータを動的に調整します。 
 1. 負荷状況
 - On premise 環境は Garafana 
 - Cloud 環境は「Monitoring」タブ 
 1. インデックスの作成スピード

10. Copyright ©2022 PingCAP. All Rights Reserved. 索引の作成におけるチューニング(3)
 Online でインデックスを作成する際に、以下の2つの項目を随時に監視する 
 状況に応じて、パラメータを動的に調整する 
 1. 負荷状況
 - On premise 環境は Garafana 
 - Cloud 環境は「Monitoring」タブ 
 1. インデックスの作成スピード

11. Copyright ©2022 PingCAP. All Rights Reserved. サービスへの影響
 02
12. Copyright ©2022 PingCAP. All Rights Reserved. 対象列に頻繁的な読み書きがあるシーン(1)
 tidb_ddl_reorg_worker_cnt
 add_index_durations(s)
 sysbench TPS
 sysbench TPS decrease %
 sysbench QPS
 sysbench QPS decrease %
 1
 402
 338.4
 3.39
 6776
 0.44
 2
 266
 330.3
 5.71
 6001
 11.82
 4(Default)
 174
 288.5
 17.64
 5769
 15.23
 8
 129
 280.6
 19.89
 5612
 17.54
 16
 90
 263.5
 24.78
 5273
 22.52
 32
 54
 229.2
 34.57
 4583
 32.66
 48
 57
 230.1
 34.31
 4601
 32.39
 sysbench TPS
 sysbench QPS
 350.31
 6806
 • ADD INDEX 操作がない場合: 
 • ADD INDEX 操作がある場合 
 tidb_ddl_reorg_batch_size = 32 (Default:256)に調整した場合: 
 参考) Interaction Test on Online Workloads and ADD INDEX Operations 

13. Copyright ©2022 PingCAP. All Rights Reserved. sysbench による負荷テストで、 TPS が 約 3% - 30% の性能劣化、QPS が約 1% - 30% の性能劣化。
 対象列に頻繁的な読み書きがあるシーン(2)

14. Copyright ©2022 PingCAP. All Rights Reserved. tidb_ddl_reorg_worker_cnt または tidb_ddl_reorg_batch_size を大きく調整すると、ADD INDEX と Online 業務による Column Update の同時処理で Write Conflict が多発し、以下の現象として顕在化される： 
 ❏ TiKV prewrite latch wait duration が大きくなり、書き込むが遅くなる 
 - On-Premise 環境: Grafana の Prewrite >>「Scheduler latch wait duration」
 - Cloud 環境: 「Monitoring」>>「Avg TiDB KV Request Duration」 >> [PessimisticLock]
 ❏ admin show ddl コマンドから DDL job の リトライが多発し、ADD INDEX の実行が長時間になる 
 例)
 mysql> admin show ddl\G 
 *************************** 1. row *************************** 
 SCHEMA_VER: 111
 …
 RUNNING_JOBS: ID:97, Type:add index,..., Err:Write conflict, txnStartTS xxx is stale [try again later], ErrCount:38,
 SnapshotVersion:xxx 
 …
 対象列に頻繁的な読み書きがあるシーン(3)

15. Copyright ©2022 PingCAP. All Rights Reserved. Cloud 環境: 「Avg TiDB KV Request Duration」 >> [PessimisticLock] 
 例)
 対象列に頻繁的な読み書きがあるシーン(4)

16. Copyright ©2022 PingCAP. All Rights Reserved. tidb_ddl_reorg_worker_cnt
 add_index_durations(s)
 sysbench TPS
 sysbench TPS decrease %
 sysbench QPS
 sysbench QPS decrease %
 1
 376
 548.9
 0.36
 8780
 0.37
 2
 212
 541.5
 1.88
 8523
 3.29
 4(Default)
 135
 538.6
 2.59
 8549
 2.99
 8
 114
 536.7
 3.11
 8393
 4.76
 16
 77
 533.9
 3.78
 8292
 5.91
 32
 46
 533.4
 4.05
 8103
 8.05
 48
 46
 532.2
 4.44
 8074
 8.38
 sysbench TPS
 sysbench QPS
 550.9
 8812.8
 対象列に Read-only のシーン(1)
 • ADD INDEX 操作がない場合: 
 • ADD INDEX 操作がある場合 
 tidb_ddl_reorg_batch_size = 32 (Default:256)に調整した場合: 

17. Copyright ©2022 PingCAP. All Rights Reserved. TPS が基本的に約 5% 以下の性能劣化、
 QPS が最大 8% の性能劣化。
 対象列に Read-only のシーン(2)

18. Copyright ©2022 PingCAP. All Rights Reserved. tidb_ddl_reorg_worker_cnt add_index_durations(s) sysbench TPS sysbench TPS decrease % sysbench QPS sysbench QPS decrease % 1 372 350.4 -0.03 6892 -1.26 2 207 344.2 1.74 6700 1.56 4(Default)
 140
 343.1
 2.06
 6672
 1.97
 8 121 339.1 3.2 6579 3.34 16 76 340 2.94 6607 2.92 32 42 343.1 2.06 6695 1.63 48 42 333.4 4.83 6454 5.17 sysbench TPS sysbench QPS 350.31 6806 • ADD INDEX 操作がない場合: 
 • ADD INDEX 操作がある場合 
 tidb_ddl_reorg_batch_size = 32 (Default:256)に調整した場合: 
 対象列に Write/Read ないシーン(1)

19. Copyright ©2022 PingCAP. All Rights Reserved. ADD Index による影響が 少ない
 対象列に Write/Read ないシーン(2)

20. Copyright ©2022 PingCAP. All Rights Reserved. サマリー
 1. 対象列に頻繁的な更新(UPDATE、INSERT および DELETE)がある場合、デフォルトの設定だ と Write Conflict が多発し、システムの負荷が高くなる 
 
 1. ADD INDEX の対象列は 読み込むだけ、もしくはOnline 業務による Write/Read がない場合、 デフォルトの設定で問題ない 
 
 1. ADD INDEX 処理は リトライにより時間がかかる場合がある 

21. Copyright ©2022 PingCAP. All Rights Reserved. オプティマイザ演算子
 03
22. Copyright ©2022 PingCAP. All Rights Reserved. Point Get と Batch Point Get(1)
 EXPLAIN SELECT * FROM t1 WHERE unique_key = 1234; +---------------------+---------+------+-----------------------------------------------------+---------------+ | id | estRows | task | access object | operator info | +---------------------+---------+------+-----------------------------------------------------+---------------+ | Point_Get_1 　　　　| 1.00 | root | table:t1, index:unique_key(unique_key) 　　　　　　　| 　　　 | +---------------------+---------+------+-----------------------------------------------------+---------------+ 1 row in set (0.00 sec) EXPLAIN SELECT * FROM t1 WHERE unique_key IN (1234, 123); +-------------------+---------+------+-----------------------------------------------------------------------+------------------+ | id | estRows | task | access object | operator info | +-------------------+---------+------+-----------------------------------------------------------------------+------------------+ | Batch_Point_Get_1 | 2.00 | root | table:t1, index:unique_key(unique_key) | keep order:false, desc:false | 　　　 | +-------------------+---------+------+-----------------------------------------------------------------------+------------------+ 1 row in set (0.00 sec)
23. Copyright ©2022 PingCAP. All Rights Reserved. オプティマイザにより PointGet Operator を実施するには、以下の条件を満たす必要がある： 
 
 • 一意インデックス(Primary Key もしくは Unique Key)が定義されている 
 
 • 戻り値は1つだけ、もしくは1つもない 
 Point Get と Batch Point Get(2)

24. Copyright ©2022 PingCAP. All Rights Reserved. Index Full Scan(1)
 mysql> explain select count(*) from t1; +----------------------------+--------------+------------+-------------------------------+-------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------+--------------+------------+-------------------------------+-------------------------------------+ | StreamAgg_20 | 1.00 | root | |funcs:count(Column#11)->Column#6 | | └─IndexReader_21 | 1.00 | root | | index:StreamAgg_8 | | └─StreamAgg_8 | 1.00 | cop[tikv] | | funcs:count(1)->Column#11 | | └─IndexFullScan_19 | 2560000.00 | cop[tikv] | table:t1, index:t1_ind_id(id) | keep order:false | +----------------------------+--------------+------------+-------------------------------+-------------------------------------+ 4 rows in set (0.01 sec)
25. Copyright ©2022 PingCAP. All Rights Reserved. mysql> explain select min(id) from t1; +--------------------------------------+-------------+-------------------------------------+-----------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------------+------------+--------------------------------------+-----------------------------------+ | StreamAgg_10 | 1.00 | root | | funcs:min(test.t1.id)->Column#6 | | └─Limit_15 | 1.00 | root | | offset:0, count:1 | | └─IndexReader_25 | 1.00 | root | | index:Limit_24 | | └─Limit_24 | 1.00 | cop[tikv] | | offset:0, count:1 | | └─IndexFullScan_23 | 1.00 | cop[tikv] | table:t1, index:t1_ind_id(id) | keep order:true | +--------------------------------------+------------+--------------------------------------+-----------------------------------+ mysql> explain select min(c1) from t1; +-----------------------------+----------------------------+-----------------------------+---------------------------------------+ | id | estRows | task | access object | operator info | +-----------------------------+----------------------------+-----------------------------+---------------------------------------+ | StreamAgg_10 | 1.00 | root | | funcs:min(test.t1.c1)->Column#6 | | └─TopN_11 | 1.00 | root | | test.t1.c1, offset:0, count:1 | | └─TableReader_21 | 1.00 | root | | data:TopN_20 | | └─TopN_20 | 1.00 | cop[tikv] | | test.t1.c1, offset:0, count:1 | | └─Selection_19 | 2557440.00 | cop[tikv] | | not(isnull(test.t1.c1)) | | └─TableFullScan_18 | 2560000.00 | cop[tikv] | table:t1 | keep order:false | +-----------------------------+----------------------------+-----------------------------+---------------------------------------+ Index Full Scan(2)
 Use Index
 Not Use Index

26. Copyright ©2022 PingCAP. All Rights Reserved. Index Range Scan
 mysql> explain select * from t1 where id > 2000 and id < 3000; +----------------------------+-----------+-------------+-------------------------------+--------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------+-----------+-------------+-------------------------------+--------------------------------------+ | IndexLookUp_10 | 255744.00 | root | | | | ├─IndexRangeScan_8(Build) | 255744.00 | cop[tikv] | table:t1, index:t1_ind_id(id) | range:(2000,3000),keep order:false | | └─TableRowIDScan_9(Probe) | 255744.00 | cop[tikv] | table:t1 | keep order:false | +----------------------------+-----------+-------------+-------------------------------+--------------------------------------+ 3 rows in set (0.01 sec)
27. Copyright ©2022 PingCAP. All Rights Reserved. mysql> explain select * from t1 where (id > 2000 and id < 3000) or (id > 4000 and id < 5000); +-----------------------------+-----------+------------+------------------------------------+--------------------------------------------------+ | id | estRows | task | access object | operator info | +-----------------------------+-----------+------------+------------------------------------+--------------------------------------------------+ | IndexLookUp_10 | 511488.00 | root | | | | ├─IndexRangeScan_8(Build) | 511488.00 | cop[tikv] | table:t1, index:t1_ind_id(id) | range:(2000,3000), (4000,5000), keep order:false | | └─TableRowIDScan_9(Probe) | 511488.00 | cop[tikv] | table:t1 | keep order:false | +-----------------------------+-----------+------------+------------------------------------+--------------------------------------------------+ 3 rows in set (0.01 sec) Index Range Scan(2)

28. Copyright ©2022 PingCAP. All Rights Reserved. インデックスの使用基準
 04
29. Copyright ©2022 PingCAP. All Rights Reserved. インデックスの選択基準 インデックスの選択基準 
 Where 句の条件列にインデックスがどのぐらいカバーしている 
 テーブル戻し必要か 
 ソートが必要か

30. Copyright ©2022 PingCAP. All Rights Reserved. インデックスの選択基準(2)
31. Copyright ©2022 PingCAP. All Rights Reserved. コストを基づいた選択 
 索引の行データの平均サイズ 
 インデックスを使用した検索の行数 
 テーブル戻しコスト
 Index Range Scan の範囲 
 インデックスの選択基準(3)
32. Copyright ©2022 PingCAP. All Rights Reserved. サマリー
 1. インデックスのメンテナンス方法 
 
 1. インデックスの追加によるサービスへの影響 
 
 1. オプティマイザ演算子
 
 1. インデックスの使用基準

33. Copyright ©2022 PingCAP. All Rights Reserved. Quiz (1) 以下の要素の中、Online インデックスの作成スピードに影響を生じるものは?(2つを選ぶ) 
 
 A. インデックスの対象列に 読み書きあるか
 
 A. 対象テーブルのColumn数
 
 A. 対象テーブルの既存インデックス数
 
 A. tidb_ddl_reorg_worker_cnt パラメータ値
 

34. Copyright ©2022 PingCAP. All Rights Reserved. Quiz (1) 以下の要素の中、Online インデックスの作成スピードに影響を生じるものは?(2つを選ぶ) 
 
 A. インデックスの対象列に 読み書きあるか
 
 A. 対象テーブルのColumn数
 
 A. 対象テーブルの既存インデックス数
 
 A. tidb_ddl_reorg_worker_cnt パラメータ値
 

35. Copyright ©2022 PingCAP. All Rights Reserved. Quiz (2) 下記の中、TiDB データベースにあるインデックススキャン演算子 ではないものは？
 
 A. Index Range Scan
 B. Index Full Scan
 C. Point Get
 D. Index skip Scan 
 E. Index Lookup
 

36. Copyright ©2022 PingCAP. All Rights Reserved. Quiz (2) 下記の中、TiDB データベースにあるインデックススキャン演算子 ではないものは？
 
 A. Index Range Scan
 B. Index Full Scan
 C. Point Get
 D. Index skip Scan 
 E. Index Lookup
 

37. Q&A

38. Thank You!
 https://www.pingcap.com/
 pingcapjp@pingcap.com