使用tidb和tispark对1亿大表查询出现超时报错

kuronekonano · 2020 年8 月 22 日 16:44

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】：4.0.0
【问题描述】：对1亿大表查询时很久没有结果，最后出现错误：

ERROR 9006 (HY000): GC life time is shorter than transaction duration, transaction starts at 2020-08-22 14:24:54.847 +0800 CST, GC safe point is 2020-08-22 14:34:28.997 +0800 CST

查询语句：select count(*) from items where xxxx;
该表数据量大概1.8亿行，且字段有十多个。

后来以为是tidb性能问题就使用tispark进行查询，结果光count(*)没有where条件就报了大量错误。

toreClient.handleCopResponse(RegionStoreClient.java:679)
at com.pingcap.tikv.region.RegionStoreClient.coprocess(RegionStoreClient.java:623)
at com.pingcap.tikv.operation.iterator.DAGIterator.process(DAGIterator.java:219)
at com.pingcap.tikv.operation.iterator.DAGIterator.lambda$submitTasks$1(DAGIterator.java:90)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
20/08/21 18:16:32 ERROR DAGIterator: Process region tasks failed, remain 0 tasks not executed due to
com.pingcap.tikv.exception.SelectException: Execution terminated due to exceeding the deadline
at com.pingcap.tikv.region.RegionStoreClient.doCoprocessor(RegionStoreClient.java:708)
at com.pingcap.tikv.region.RegionStoreClient.handleCopResponse(RegionStoreClient.java:679)
at com.pingcap.tikv.region.RegionStoreClient.coprocess(RegionStoreClient.java:623)
at com.pingcap.tikv.operation.iterator.DAGIterator.process(DAGIterator.java:219)
at com.pingcap.tikv.operation.iterator.DAGIterator.lambda$submitTasks$1(DAGIterator.java:90)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
20/08/21 18:16:32 ERROR DAGIterator: Process region tasks failed, remain 0 tasks not executed due to
com.pingcap.tikv.exception.SelectException: Execution terminated due to exceeding the deadline
at com.pingcap.tikv.region.RegionStoreClient.doCoprocessor(RegionStoreClient.java:708)
at com.pingcap.tikv.region.RegionStoreClient.handleCopResponse(RegionStoreClient.java:679)
at com.pingcap.tikv.region.RegionStoreClient.coprocess(RegionStoreClient.java:623)
at com.pingcap.tikv.operation.iterator.DAGIterator.process(DAGIterator.java:219)
at com.pingcap.tikv.operation.iterator.DAGIterator.lambda$submitTasks$1(DAGIterator.java:90)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
20/08/21 18:16:32 ERROR DAGIterator: Process region tasks failed, remain 0 tasks not executed due to
com.pingcap.tikv.exception.SelectException: Execution terminated due to exceeding the deadline
at com.pingcap.tikv.region.RegionStoreClient.doCoprocessor(RegionStoreClient.java:708)
at com.pingcap.tikv.region.RegionStoreClient.handleCopResponse(RegionStoreClient.java:679)
at com.pingcap.tikv.region.RegionStoreClient.coprocess(RegionStoreClient.java:623)
at com.pingcap.tikv.operation.iterator.DAGIterator.process(DAGIterator.java:219)
at com.pingcap.tikv.operation.iterator.DAGIterator.lambda$submitTasks$1(DAGIterator.java:90)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

所以是无论在tidb上查还是使用tispark在tikv上查都会超时吗？
tidb对于上亿级别大表的单次查询count,min,max的正常时间预期大概是多长？
还有想问一下tispark查询时会用到tidb建表时创建的索引吗？
已经按照官方文档处理ERROR 9006 (HY000): GC life time is shorter的方法将GC life time调至30m.

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。