After using TiFlash, the query speed becomes slower

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

Original topic: 使用tiflash之后,查询速度变慢

| username: 等一分钟

[TiDB Usage Environment] Production Environment /
[TiDB Version] 6.1.0

| username: 等一分钟 | Original post link

The images you provided are not accessible. Please provide the text content for translation.

| username: 等一分钟 | Original post link

The main reason is that the default value of the tidb_distsql_scan_concurrency parameter is 15, which means that each query will use 15 threads to scan data. If the concurrency is too high, it will cause too many threads to be created, resulting in high CPU usage. You can try to reduce the value of this parameter to see if it can alleviate the problem.

| username: 等一分钟 | Original post link

After using the TiFlash node, I found that the execution speed slowed down, but the memory usage decreased.

| username: xfworld | Original post link

Isn’t it great? It gives you another direction to choose from. :slightly_smiling_face:

| username: terry0219 | Original post link

From the execution plan, when using TiFlash, all three tables are fully scanned, whereas with TiKV, indexes are used.

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

Try using only a to go through TiFlash, and let others go through TiKV to see the execution efficiency.

| username: zhaokede | Original post link

Time for space

| username: 等一分钟 | Original post link

Not to mention using TiFlash, will it become faster?

| username: TiDBer_0p0BD6le | Original post link

This should be a critical point issue. Combining TiKV and TiFlash is the fastest. Simply using TiKV alone is probably not the most efficient, and it doesn’t necessarily mean that using TiFlash will always be faster.

| username: hacker_77powerful | Original post link

Please provide the following information:

  1. The size of the table?
  2. How long does it take to use TiKV, and how long does it take to use TiFlash?
  3. Check the execution plan for TiFlash. Is it fully utilizing TiFlash, or are some parts still using TiKV?
  4. For table join queries, have all the related tables been synchronized to TiFlash?