# TiFlash for Analytical Queries ### Overview + In this exercise, you will be comparing the execution plans for analytical queries between TiKV and TiFlash storage engines. ### Notes + Some of the steps require that you type commands in two different terminal windows. You might find it convenient to launch two terminals and keep them open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The `$` in your terminal window is an OS prompt. + The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt. + These exercises can be run on a single machine, such as your laptop. ### Assumptions + You are using macOS or Linux. + You have cloned the [lab repository](https://github.com/pingcap/tidb-course-201-lab.git). + You have completed the Exercises - Creating a TiDB Cluster for Testing Purposes. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. Connect to the TiDB server. 2. Create two tables and insert mock data into them so that they can be joined later. 3. Run queries with TiFlash enabled and disabled, and compare their performance differences. ### Solution 1. Connect to the TiDB server. ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 2. Run the following script to create two tables which are `test.p` and `test.c`, and then insert mock data. The script typically takes about 3 minutes to run on a modern laptop. If you only have a small Linux instance, we have also prepared `11-demo-table-01-setup-small.sql` for you. ```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. By default, the TiFlash storage engine is not enabled for tables. Execute the sample analytical query to observe its performance and execution plan. Execute the query and note that in the sample output. Record the elapsed time for the query execution, which was completed in 3.67 seconds in the provided example. ```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) ``` Check the execution plan in `PAGER` view, pay attention to the `task` column, `cop[tikv]` indicates the tasks are executed by the coprocessors in TiKV servers, `root` indicates the tasks are executed by the TiDB server: ```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) ``` Hit `Q` to exit the `PAGER` view. 4. Enable TiFlash replica for both tables: ```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. Confirm the progress of replication by the following query. The progress column must display a value of `1`, indicating the completion of replication. If this is not the case, wait for a few minutes and execute the query again. ```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. Verify that TiFlash is included as one of the potential storage engines for the cost based optimizer of the TiDB server. ```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. Review the execution plan for the sample analytical query and focus on the `task` column. Notably, the `mpp[tiflash]` designation signifies that the tasks are executed via the TiFlash massive parallel processing mechanism, while the `root` designation denotes that the tasks are executed by the TiDB server. ```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) ``` Hit `Q` to exit the `PAGER` view. 8. Run the sample analytical query again, which was completed in 0.32 seconds in the provided example. TiFlash engine outperforms TiKV engine in analytical scenarios. ```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. Drop the example tables. ```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) ``` ## End of Exercises