TiDB SQL Execution Plan Shows TiFlash Usage, Execution Time Increases

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb SQL执行计划显示走tiflash，执行时间变长

| username: qiuxb

To improve efficiency, please provide the following information. Clear problem descriptions can be resolved faster:
[TiDB Usage Environment]
tidb v.4.0.8

[Overview] Scenario + Problem Overview

±-----------------------------------±--------±----------±----------------------------------------------------------------------------------
| id | estRows | task | access object
±-----------------------------------±--------±----------±----------------------------------------------------------------------------------
| StreamAgg_15 | 1.00 | root |
| └─HashAgg_43 | 9.71 | root |
| └─IndexLookUp_44 | 9.71 | root |
| ├─IndexRangeScan_25(Build) | 7157.63 | cop[tikv] | table:t, partition:p20220727, index:idx_xxxx_hour(advertiser_id, report_time) |
| └─HashAgg_39(Probe) | 9.71 | cop[tikv] |
| └─TableRowIDScan_26 | 7157.63 | cop[tikv] | table:t, partition:p20220727 |
±-----------------------------------±--------±----------±----------------------------------------------------------------------------------
6 rows in set (0.01 sec)

51a92110-d18c-4230-98cc-6aba1c5dcb80
51a92110-d18c-4230-98cc-6aba1c5dcb801111×265 37.4 KB

[Background] What operations have been done

[Phenomenon] Business and database phenomena

[Problem] Current problem encountered
Two execution plans, the execution plan using tikv is more efficient, while using tiflash is slower. I don’t understand why the execution plan automatically chooses to execute on tiflash?

[Business Impact]

[TiDB Version]

[Application Software and Version]

[Attachments] Relevant logs and configuration information

  • TiUP Cluster Display information
  • TiUP Cluster Edit config information

Monitoring (https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana monitoring
  • TiDB Grafana monitoring
  • TiKV Grafana monitoring
  • PD Grafana monitoring
  • Corresponding module logs (including logs 1 hour before and after the problem)

If the question is related to performance optimization or fault troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output results and upload them.

2
| username: ddhe9527 | Original post link

Check the health of the statistics, manually collect the statistics, and then see if the optimizer chooses TiKV or TiFlash.

3
| username: h5n1 | Original post link

It might be a statistics issue. Try recollecting them and see if it helps. The intelligent selection in version 4.0 might not be smart enough. If it doesn’t work, try using SPM and bind the SQL to use TiKV with /*+ READ_FROM_STORAGE(TIKV[t2]) */.

4
| username: qiuxb | Original post link

Under normal circumstances, it uses TiKV, but sometimes it differs and uses TiFlash. The SQL is the same, and the probability of using TiFlash is relatively low. However, whenever it happens, the execution time reaches 50 seconds. Currently, the execution plan uses TiKV, and manually executing explain analyze also does not use TiFlash. The TiFlash execution plan is seen in the TiDB Dashboard slow log.

5
| username: qiuxb | Original post link

There are frequent updates and inserts of data, and it is a large partitioned table. Occasionally, the execution plan uses TiFlash, but it is almost impossible to reproduce manually.

6
| username: ddhe9527 | Original post link

You can consider doing execution plan binding, using SPM to add READ_FROM_STORAGE to the SQL, or consider upgrading the database version at an appropriate time.

7
| username: qiuxb | Original post link

The READ_FROM_STORAGE specifies TiFlash, but the actual execution plan still uses TiKV. The main purpose is to verify whether using TiFlash is really slower. This makes it seem like READ_FROM_STORAGE doesn’t work. What is the reason for this?

8
| username: ddhe9527 | Original post link

Doesn’t the following work?

/*+ READ_FROM_STORAGE(TIFLASH[t]) */
9
| username: qiuxb | Original post link

I found the reason. I was using MySQL client version 5.1, but hints will only take effect with version 5.7.7 or above. Thank you.

10
| username: system | Original post link

This topic will be automatically closed 60 days after the last reply. No new replies are allowed.