TiDB Slow Query Issues

Translated
1

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

Original topic: tidb慢查询问题

| username: TiDBer_FMWXa7ja

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] 6.5
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact] Querying a single table with 150 million records in TiDB takes about 20 seconds, while in MySQL it takes less than 10 seconds.
[Resource Configuration]
TiDB Resource Configuration:

image
image1380×544 31.6 KB

MySQL Configuration: 16 cores, 32GB memory

[Attachments: Screenshots/Logs/Monitoring]

  1. Execution Plan
    8df4ec57dc02324354b7295cada7d37
    8df4ec57dc02324354b7295cada7d371380×380 45.5 KB

Execution Plan Execution Info Details:
First Line: time: 19.4s, loops: 2

Second Line: time: 19.4s, loops: 23638, cop_task: {num: 770, max: 1.97s, min: 441.3µs, avg: 292.5ms, p95: 937.8ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 31.4s, tot_wait: 176ms, rpc_num: 772, rpc_time: 3m45.2s, copr_cache_hit_ratio: 0.04, distsql_concurrency: 15}, backoff{regionMiss: 2ms}

Third Line: tikv_task: {proc max: 178ms, min: 0s, avg: 31.7ms, p80: 49ms, p95: 85ms, iters: 26649, tasks: 770}, scan_detail: {total_process_keys: 24095603, total_process_keys_size: 4728933683, total_keys: 24096342, get_snapshot_time: 126.9ms, rocksdb: {key_skipped_count: 24095603, block: {cache_hit_count: 70926, read_count: 5042, read_byte: 19.9 MB, read_time: 42.4ms}}}

Fourth Line: tikv_task: {proc max: 178ms, min: 0s, avg: 31.7ms, p80: 49ms, p95: 85ms, iters: 26649, tasks: 770}

  1. Table Creation Statement
    d2d194e1e497eb6f66c6d766574dd79
    d2d194e1e497eb6f66c6d766574dd791242×772 75.9 KB
2
| username: vcdog | Original post link

You haven’t used TiFlash, right? Loading the data into TiFlash will make it appear instantly.

3
| username: TiDBer_FMWXa7ja | Original post link

Does TiFlash have any effect on this kind of ordinary query, which is not an aggregate statistic?

4
| username: wakaka | Original post link
  1. Add TiFlash
  2. If you can’t add it, create an index on plan_time, then rewrite the limit to first find this id, and then query for ids greater than this id.
5
| username: TiDBer_FMWXa7ja | Original post link

May I ask, isn’t TiFlash designed for analytical and statistical scenarios? Does it also have an effect on regular queries?

6
| username: h5n1 | Original post link

Check the monitoring under tikv detail → thread cpu.

7
| username: Kongdom | Original post link

The configuration of those 3 standalone KV nodes is quite low, with only 16GB of memory, while the official standard configuration is 64GB+.

You can change the 3 mixed nodes to standalone KV nodes and convert the current 3 KV nodes to TiDB. It seems like this would be better.

8
| username: 考试没答案 | Original post link

How large is the amount of data returned by this query?

9
| username: TiDBer_FMWXa7ja | Original post link

But the configuration requirements for a standalone MySQL instance are not high either. Perhaps TiDB has higher requirements for the server.

10
| username: TiDBer_FMWXa7ja | Original post link

More than 24 million

11
| username: Kongdom | Original post link

:rofl: You can’t use the same configuration to compare the performance of a distributed database and a monolithic database.

12
| username: TiDBer_FMWXa7ja | Original post link

CPU usage is very low.

13
| username: 考试没答案 | Original post link

Try a four-column covering index.

14
| username: 考试没答案 | Original post link

Please post the execution plan and table structure of MySQL.

15
| username: TiDBer_FMWXa7ja | Original post link

The table structure of MySQL is the same as TiDB.

16
| username: BraveChen | Original post link

Haha, which post from yesterday?

17
| username: TiDBer_FMWXa7ja | Original post link

Yes :joy:

18
| username: 考试没答案 | Original post link

The “extra” column in MySQL shows “using where”. It is not using the ICP feature. Please add a 4-column covering index to see if the ICP feature can be used. Add it to both TiDB and MySQL for comparison.

19
| username: h5n1 | Original post link

Post them all for us to see.

20
| username: Raymond | Original post link

Planime is the primary key index, so there’s no need to perform a table scan.