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

Slide 1

Slide 1 text

Slide 2

Slide 2 text

本日の位置づけ  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  

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

Slide 6

Slide 6 text

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] ...;  

Slide 7

Slide 7 text

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 ﬁrst job from job queue ( add index queue ) handle it DDL add index worker 5. if it can get the job from history job queue, ﬁnish 4. Move job from job queue ( add index queue ) to job history queue job add index job queue 過去ウェビナー: TiDBでのSQL実行の仕組み  

Slide 8

Slide 8 text

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) 

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

Slide 12

Slide 12 text

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  

Slide 13

Slide 13 text

Slide 14

Slide 14 text

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) 

Slide 15

Slide 15 text

Slide 16

Slide 16 text

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)に調整した場合:  

Slide 17

Slide 17 text

Slide 18

Slide 18 text

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) 

Slide 19

Slide 19 text

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

Slide 22

Slide 22 text

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)

Slide 23

Slide 23 text

Copyright ©2022 PingCAP. All Rights Reserved. オプティマイザにより PointGet Operator を実施するには、以下の条件を満たす必要がある：     • 一意インデックス(Primary Key もしくは Unique Key)が定義されている     • 戻り値は1つだけ、もしくは1つもない   Point Get と Batch Point Get(2) 

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

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) 

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

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