Application environment: Production
TiDB version: 5.4.0
Reproduction method:
SELECT * FROM [myTable] WHERE [myField1] IS NOT NULL OR [myField2] IS NULL;
Problem:
Depending from host, we are facing a different plan for the same query.
1st host is using TiKV
2nd host is using TiFlash
Resource allocation:
16 tidb (56 cpu & 500 Go RAM / TiDB)
6 tikv (112 cpu & 1To RAM / TiKV)
6 PD (on the TiKV servers)
2 tiflash (112 cpu & 1To RAM / TiFlash)
[myTable] has 165k rows, 26 fields and is replicated in TiFlash.
[myField1] as INT(11) DEFAULT NULL
[myField2] as ENUM (10values) DEFAULT NULL
Attachment:
If you have a support contract, please file a support case as well.
Can you run EXPLAIN ANALYZE and post the output?
Do the statistics look ok from both hosts?
We always recommend updating to the latest patch release for the major version that you’re using. So maybe consider upgrading to 5.4.3. And upgrading to the latest LTS (v6.5.0) might bring additional benefits.
The results of explain analyze:
From the host that uses TiKV :
From the host that uses TiFlash:
SHOW STATS_HEALTHY WHERE TABLE_NAME = ‘[myTable]’ :
From the host that uses TiKV : 94
From the host that uses TiFlash : 94
Could you upgrade to v5.4.3 as there have been many bugfixes for TiFlash? This looks like one of the bugs that was fixed
ok ok, we’re finishing the migration tests to 6.5.0.
there are still some issues with br and our ceph. we will probably open a topic soon.
thanks for the answers
