# 分析クエリ用のTiFlash ### 概要 + この演習では、TiKV とTiFlashストレージエンジンの分析クエリの実行計画を比較します。 ### 注意 + いくつかのステップでは、2つの異なるターミナルウィンドウでコマンドを入力する必要があります。2つのターミナルを起動し、演習の間ずっと開いておくと便利でしょう。 + この演習で利用する、`IP`、`port`、`username`、`password`、`directory`などは、あなたの環境と異なる場合があります。 + ターミナルウィンドウの"`$`"とはOSプロンプトです。 + ターミナルウィンドウ内の "`tidb>`" または "`mysql>`" は、SQL コマンドプロンプトです。 + これらの演習は、ノートパソコンなどの単一のマシン上で実行することができます。 ### 前提条件 + macOSまたはLinuxを使用していること。 + [lab repository](https://github.com/pingcap/tidb-course-201-lab.git)をクローン済みであること。 + 演習「テスト用にTiDBクラスタを作成する」を完了していること。 ### 所要時間 + この演習は約15分程度かかります。 ### タスク 1. TiDBサーバーに接続します。 2. 2つのテーブルを作成し、後で結合できるようにモックデータを挿入します。 3. TiFlashを有効または無効にしてクエリを実行し、パフォーマンスの違いを比較します。 ### 手順 1. TiDBサーバーに接続します。 ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 2. 次のスクリプトを実行して、`test.p`と`test.c` 2つのテーブルを作成し、モックデータを挿入します。このスクリプトは、現代のラップトップで実行されるのに通常約3分かかります。 ```sql tidb:4000> SOURCE 11-demo-table-01-setup.sql ``` ``` ... -------------- SELECT COUNT(*) FROM test.p -------------- +----------+ | COUNT(*) | +----------+ | 524288 | +----------+ 1 row in set (0.79 sec) -------------- SELECT COUNT(*) FROM test.c -------------- +----------+ | COUNT(*) | +----------+ | 8388608 | +----------+ 1 row in set (6.46 sec) ``` 3. デフォルトでは、TIFlashストレージエンジンはテーブルに対して有効になっていません。サンプルの分析クエリを実行して、そのパフォーマンスと実行計画を確認します。 クエリを実行して、サンプル出力を記録してください。クエリ実行の経過時間を記録します。提供された例では3.67秒で完了しました。 ```sql SELECT AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt FROM test.p as p JOIN test.c as c ON p.pid = c.pid AND c.insert_batch_id < p.insert_batch_id GROUP BY c.charname ORDER BY cnt, charname; ``` ``` tidb:4000> SELECT AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt -> FROM test.p as p JOIN test.c as c -> ON p.pid = c.pid -> AND c.insert_batch_id < p.insert_batch_id -> GROUP BY c.charname -> ORDER BY cnt, charname; -------------- SELECT AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt FROM test.p as p JOIN test.c as c ON p.pid = c.pid AND c.insert_batch_id < p.insert_batch_id GROUP BY c.charname ORDER BY cnt, charname -------------- +------------------------+----------+--------+ | AVG(c.insert_batch_id) | charname | cnt | +------------------------+----------+--------+ | 0.5000 | A | 262141 | | 0.5000 | B | 262141 | | 0.5000 | C | 262141 | | 0.5000 | D | 262141 | +------------------------+----------+--------+ 4 rows in set (3.67 sec) ``` `PAGER`ビューで実行計画を確認し、`task`(タスク)カラムに注目します。`cop[tikv]`はタスクがTiKV サーバーのコプロセッサーによって実行されることを示し、`root`はタスクがTiDBサーバーによって実行されることを示します。 ```sql PAGER LESS -S EXPLAIN SELECT AVG(p.insert_batch_id), AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt FROM test.p as p JOIN test.c as c ON p.pid = c.pid AND c.insert_batch_id < p.insert_batch_id GROUP BY c.charname ORDER BY cnt, charname; ``` ``` +-------------------------------------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------> | id | estRows | task | access object | operator info > +-------------------------------------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------> | Sort_9 | 4.04 | root | | Column#12, test.c.charname > | └─Projection_11 | 4.04 | root | | Column#10, Column#11, test.c.charname, Column#12 > | └─HashAgg_12 | 4.04 | root | | group by:Column#16, funcs:avg(Column#13)->Column#10, funcs:avg(Column#14)->Column#11, funcs:count(1)->Column#12, fun> | └─Projection_29 | 5794193.88 | root | | cast(test.p.insert_batch_id, decimal(10,0) BINARY)->Column#13, cast(test.c.insert_batch_id, decimal(10,0) BINARY)->C> | └─HashJoin_23 | 5794193.88 | root | | inner join, equal:[eq(test.p.pid, test.c.pid)], other cond:lt(test.c.insert_batch_id, test.p.insert_batch_id) > | ├─TableReader_27(Build) | 524288.00 | root | | data:TableFullScan_26 > | │ └─TableFullScan_26 | 524288.00 | cop[tikv] | table:p | keep order:false > | └─TableReader_25(Probe) | 5777926.00 | root | | data:TableFullScan_24 > | └─TableFullScan_24 | 5777926.00 | cop[tikv] | table:c | keep order:false > +-------------------------------------+------------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------> (END) ``` `Q`を押して`PAGER`ビューを終了します。 4. 両方のテーブルでTIFlashレプリカを有効にします。 ```sql ALTER TABLE test.p SET TIFLASH REPLICA 1; ALTER TABLE test.c SET TIFLASH REPLICA 1; ``` ``` tidb:4000> ALTER TABLE test.p SET TIFLASH REPLICA 1; -------------- ALTER TABLE test.p SET TIFLASH REPLICA 1 -------------- ALTER TABLE test.c SET TIFLASH REPLICA 1; Query OK, 0 rows affected (1.50 sec) tidb:4000> ALTER TABLE test.c SET TIFLASH REPLICA 1; -------------- ALTER TABLE test.c SET TIFLASH REPLICA 1 -------------- Query OK, 0 rows affected (1.63 sec) ``` 5. 次のクエリで複製の進行状況を確認します。`PROGRESS`(進行状況)カラムには、複製の完了を示す`1` 値が表示されている必要があります。そうでない場合は、数分待ってからクエリを再実行します。 ```sql NOPAGER SELECT * FROM information_schema.tiflash_replica WHERE table_schema='test' AND table_name in ('p', 'c'); ``` ``` tidb:4000> NOPAGER PAGER set to stdout tidb:4000> SELECT * FROM information_schema.tiflash_replica WHERE table_schema='test' AND table_name in ('p', 'c'); -------------- SELECT * FROM information_schema.tiflash_replica WHERE table_schema='test' AND table_name in ('p', 'c') -------------- +--------------+------------+----------+---------------+-----------------+-----------+----------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS | +--------------+------------+----------+---------------+-----------------+-----------+----------+ | test | p | 80 | 1 | | 1 | 1 | | test | c | 82 | 1 | | 1 | 1 | +--------------+------------+----------+---------------+-----------------+-----------+----------+ 2 rows in set (0.07 sec) ``` 6. TiFlashがTiDBサーバーのコストベースのオプティマイザーの利用可能なストレージエンジンの1つとして含まれていることを確認します。 ```sql SHOW VARIABLES like 'tidb_isolation_read_engines'; ``` ``` tidb:4000> SHOW VARIABLES like 'tidb_isolation_read_engines'; -------------- SHOW VARIABLES like 'tidb_isolation_read_engines' -------------- +-----------------------------+-------------------+ | Variable_name | Value | +-----------------------------+-------------------+ | tidb_isolation_read_engines | tikv,tiflash,tidb | +-----------------------------+-------------------+ 1 row in set (0.01 sec) ``` 7. サンプルの分析クエリの実行計画を見直して、`task`(タスク)カラムに注目します。注目すべきは、`mpp[tiflash]`は、タスクがTiFlashの大規模並列処理メカニズムを介して実行されることを意味し、`root`は、タスクがTiDBサーバーによって実行されることを示します。 ```sql PAGER LESS -S EXPLAIN SELECT AVG(p.insert_batch_id), AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt FROM test.p as p JOIN test.c as c ON p.pid = c.pid AND c.insert_batch_id < p.insert_batch_id GROUP BY c.charname ORDER BY cnt, charname; ``` ``` +------------------------------------------------------+------------+--------------+---------------+-------------------------------------------------------------------------------------------------> | id | estRows | task | access object | operator info > +------------------------------------------------------+------------+--------------+---------------+-------------------------------------------------------------------------------------------------> | Sort_9 | 4.04 | root | | Column#12, test.c.charname > | └─TableReader_69 | 4.04 | root | | data:ExchangeSender_68 > | └─ExchangeSender_68 | 4.04 | mpp[tiflash] | | ExchangeType: PassThrough > | └─Projection_12 | 4.04 | mpp[tiflash] | | Column#10, Column#11, test.c.charname, Column#12 > | └─Projection_64 | 4.04 | mpp[tiflash] | | div(Column#10, cast(case(eq(Column#31, 0), 1, Column#31), decimal(20,0) BINARY))->Column#10, div> | └─HashAgg_65 | 4.04 | mpp[tiflash] | | group by:test.c.charname, funcs:sum(Column#33)->Column#31, funcs:sum(Column#34)->Column#10, func> | └─ExchangeReceiver_67 | 4.04 | mpp[tiflash] | | > | └─ExchangeSender_66 | 4.04 | mpp[tiflash] | | ExchangeType: HashPartition, Hash Cols: [name: test.c.charname, collate: utf8mb4_bin] > | └─HashAgg_62 | 4.04 | mpp[tiflash] | | group by:Column#43, funcs:count(Column#39)->Column#33, funcs:sum(Column#40)->Column#34, funcs:co> | └─Projection_72 | 5794193.88 | mpp[tiflash] | | test.p.insert_batch_id, cast(test.p.insert_batch_id, decimal(10,0) BINARY)->Column#40, test.c.in> | └─HashJoin_51 | 5794193.88 | mpp[tiflash] | | inner join, equal:[eq(test.p.pid, test.c.pid)], other cond:lt(test.c.insert_batch_id, test.p.ins> | ├─ExchangeReceiver_33(Build) | 524288.00 | mpp[tiflash] | | > | │ └─ExchangeSender_32 | 524288.00 | mpp[tiflash] | | ExchangeType: Broadcast > | │ └─TableFullScan_31 | 524288.00 | mpp[tiflash] | table:p | keep order:false > | └─TableFullScan_34(Probe) | 5777926.00 | mpp[tiflash] | table:c | keep order:false > +------------------------------------------------------+------------+--------------+---------------+-------------------------------------------------------------------------------------------------> (END) ``` `Q`を押して`PAGER`ビューを終了します。 8. サンプルの分析クエリをもう一度実行します。提供された例では0.32秒で完了しました。分析シナリオでは、TIFlashエンジンはTiKV エンジンよりも優れています。 ```sql NOPAGER SELECT AVG(p.insert_batch_id), AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt FROM test.p as p JOIN test.c as c ON p.pid = c.pid AND c.insert_batch_id < p.insert_batch_id GROUP BY c.charname ORDER BY cnt, charname; ``` ``` tidb:4000> NOPAGER PAGER set to stdout tidb:4000> SELECT AVG(p.insert_batch_id), AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt -> FROM test.p as p JOIN test.c as c -> ON p.pid = c.pid -> AND c.insert_batch_id < p.insert_batch_id -> GROUP BY c.charname -> ORDER BY cnt, charname; -------------- SELECT AVG(p.insert_batch_id), AVG(c.insert_batch_id), c.charname, COUNT(*) AS cnt FROM test.p as p JOIN test.c as c ON p.pid = c.pid AND c.insert_batch_id < p.insert_batch_id GROUP BY c.charname ORDER BY cnt, charname -------------- +------------------------+------------------------+----------+--------+ | AVG(p.insert_batch_id) | AVG(c.insert_batch_id) | charname | cnt | +------------------------+------------------------+----------+--------+ | 87383.3333 | 0.5000 | A | 262141 | | 87383.3333 | 0.5000 | B | 262141 | | 87383.3333 | 0.5000 | C | 262141 | | 87383.3333 | 0.5000 | D | 262141 | +------------------------+------------------------+----------+--------+ 4 rows in set (0.32 sec) ``` 9. サンプルテーブルを削除します。 ```sql DROP TABLE test.p, test.c; ``` ``` tidb:4000> DROP TABLE test.p, test.c; -------------- DROP TABLE test.p, test.c -------------- Query OK, 0 rows affected (2.85 sec) ``` ## 演習終わり