TiFlash Query Error with Large Data: TiFlash Server Timeout

translator_bot · June 23, 2024, 5:33am

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

Original topic: tiflash查询大量数据报错TiFlash server timeout

| username: polars

【TiDB Usage Environment】Production Environment or Test Environment or POC
Test Environment
【TiDB Version】
v6.2
【Problem Encountered】
Querying a large table with 600 million rows via JDBC, returning all data, and encountering an error: TiFlash server timeout at 25 minutes and 59 seconds.
Executed SQL:

SELECT
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`id`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`data_id`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`duplicate_id`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`order_number`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`flag`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`amount`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`scenario`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`version`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`account`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`years`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`period`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`entity`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`industrial`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`building`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`section`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`contract_num`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`contract_first`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`contract_second`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`contract_content`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`misc_project`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`misc1`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`misc2`,
  `arch_safe_demo_building_plan_10y_1000partition_7index`.`misc3`
FROM
  `test`.`arch_safe_demo_building_plan_10y_1000partition_7index`

Checked timeout parameters, wait_timeout is 28800, and others are default values, no issues found.
A table with 100 million rows can be queried normally, taking about 70 minutes.
Screenshot of the 100 million query

【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】

【Attachments】 Related logs and monitoring (https://metricstool.pingcap.com/)
SQL execution status

TiFlash monitoring

TiDB monitoring

TiDB error logs, no error logs found in TiFlash
2022-08-25T17:51:25.661604677+08:00 [2022/08/25 17:51:25.660 +08:00] [INFO] [coprocessor.go:865] ["[TIME_COP_PROCESS] resp_time:1m1.796102256s txnStartTS:435531161164316675 region_id:5264227 store_addr:stresstest-tiflash-0.stresstest-tiflash-peer.tidb.svc:3930 backoff_ms:41764 backoff_types:[tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,tiflashRPC,regionMiss,t

translator_bot · June 23, 2024, 5:33am

| username: zzzzzz | Original post link

Hi, the tikv server timeout could be due to a load issue with tikv. You can refer to:

for related SQL troubleshooting.

translator_bot · June 23, 2024, 5:33am

| username: wuxiangdong | Original post link

Is it possible that the gc_life_time is shorter than your query time?

translator_bot · June 23, 2024, 5:33am

| username: polars | Original post link

The GC is set to 8 hours, which should be sufficient.

translator_bot · June 23, 2024, 5:33am

| username: flow-PingCAP | Original post link

Try using batch cop:
set tidb_allow_batch_cop = 2; # Force use of batch cop, default is 1
Or mpp:
set tidb_enforce_mpp = ON; # Force use of mpp, default is OFF