Issue with Hints Not Taking Effect

Translated
1

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

Original topic: hints不生效问题

| username: TiDBer_Ok0TdPp0

The issue I encountered is that I added a hint in the SQL to make table A use the KV engine, but it didn’t take effect. What could be the reason?

2
| username: h5n1 | Original post link
  1. The MySQL command-line client, before version 5.7.7, by default, cleared Optimizer Hints. If you need to use Hint syntax in these earlier versions of the client, you need to add the --comments option when starting the client.
  2. Use SET tidb_isolation_read_engines='tidb,tikv' to test with this variable.
  3. Use SHOW VARIABLES LIKE 'tidb_capture_plan_baselines' to check the value of this variable.
3
| username: TiDBer_Ok0TdPp0 | Original post link

Currently, it is turned off. Is it related to that?

4
| username: Jasper | Original post link

Are there any warnings when executing the SQL? If so, use “SHOW WARNINGS” to see the details.

5
| username: TiDBer_Ok0TdPp0 | Original post link

Thank you for the help. When I turned on both of these, it might have followed the hint. But if I forcefully turn off MPP, the optimizer fully uses MPP. Then I was quite curious. Using the KV index was not as fast as MPP. I have no confidence in handling the performance of such complex SQL.

6
| username: Jasper | Original post link

The main thing is to look at the specific amount of data you are scanning. If you are scanning a particularly large amount of data using the KV index without using TiFlash MPP, it is expected that the efficiency will be lower. KV is more suited for point queries and small data range scans, while TiFlash is more suited for AP-type full table scans.

7
| username: TiDBer_Ok0TdPp0 | Original post link

The SQL I posted above has about 100 million rows per table. The largest table, B, has 300 million rows. Each table has indexes created based on the SQL query, and the indexes are being used as expected. However, I noticed that the computation between the root layer and the KV layer has become slower than using MPP. I’m not sure if this is related to the deployment topology. Currently, the db-server and kv-server are deployed on the same machine.

8
| username: h5n1 | Original post link

When performing an index scan and returning to the table, the index records are first returned to TiDB, then TiKV is scanned to return the data to TiDB, and then it is associated with another table.

9
| username: Jasper | Original post link

Could you provide the execution plans for both TiKV and TiFlash? Let’s check the specific scan data volume of the index scan and the number of rows returned.

10
| username: TiDBer_Ok0TdPp0 | Original post link

Memory keeps exploding. I’ll first check the data volume. Several terabytes of data might be a bit too much for the current environment configuration.

11
| username: system | Original post link

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