After configuring TiFlash for the table, the execution plan does not show TiFlash being used

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

Original topic: 表设置好tiflash设置后,执行计划看不到tiflash使用

| username: TiDBer_mVc0OtQv

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] 7.1
[Reproduction Path] After setting up the TiFlash replica for the table, the execution plan for the table query shows that the query does not use TiFlash; explain SQL
[Encountered Problem: Problem Phenomenon and Impact]
The execution plan for the query shows that the query does not use TiFlash; explain SQL;
However, according to online introductions, you can forcefully set MPP with:
set tidb_allow_mpp=‘ON’;
set tidb_enforce_mpp=‘ON’;
set tidb_opt_force_inline_cte=‘ON’;
But these settings are effective only within the session scope and cannot take effect globally. How can I make them effective globally? Or, if TiFlash is set but the execution plan does not use TiFlash, what could be the reason?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]

| username: Billmay表妹 | Original post link

Copy and paste the execution plan text as required.

| username: xfworld | Original post link

The reason is here:

Reference documentation:

Please set the isolation level according to the documentation, or use the hint method to force SQL to enable TiFlash data reading.

| username: 小龙虾爱大龙虾 | Original post link

MPP requires that all tables involved in the queried SQL must have TiFlash replicas, so it doesn’t make much sense to apply it globally. Having MPP for all SQL queries is not very appropriate.

| username: 有猫万事足 | Original post link

You should try show warnings, which will display the reason why MPP cannot be used.

| username: 江湖故人 | Original post link

You can try adding a hint to see if it slows down:
select /*+ read_from_storage(tiflash[t1,t2]) */ ...

Additionally, confirm the TiDB engines configuration:
select * from information_schema.cluster_config where KEY ='isolation-read.engines';

| username: tidb菜鸟一只 | Original post link

I think you should first check the setting with SHOW config WHERE NAME LIKE '%isolation-read.engines%'; to see if there is tiflash.

| username: dba远航 | Original post link

The effectiveness of TiFlash is related to both the configuration and the SQL query. Both need to be appropriate for it to work.

| username: zhanggame1 | Original post link

After setting it up, it still doesn’t work. You need to analyze the table with the TiFlash replica settings.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.