Executing Large SQL Reports 9005 - Region is Unavailable, Despite Table Data Being Only 20 Million

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

Original topic: 执行大SQL 报9005 - Region is unavailable,关键是表数据量才2千万

| username: 付先生Mr

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v7.5.1
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
A table with more than 20 million records, both createtime and channel have indexes.
Executing the following query in native MySQL just takes a bit longer, but in TiDB, it doesn’t return any results at all:

select createtime from loginfo aag where aag.channel = 'laiyuan' and aag.createtime >= 1708012800000

Experts, do you have any good optimization and query suggestions for large tables?

| username: 付先生Mr | Original post link

To add: The system’s daily load is very low.

| username: zhaokede | Original post link

Encountered an issue when accessing a certain storage region. Check if the TiKV node is unavailable or if there is a PD scheduling problem.

| username: kkpeter | Original post link

Occasionally or consistently appearing

| username: zhaokede | Original post link

Is the network connection normal?

| username: zhaokede | Original post link

Check the status of the TiKV nodes and see if the logs are normal.

| username: 友利奈绪 | Original post link

Is the data volume large? Try adding it to TiFlash.

| username: 路在何chu | Original post link

Is the result set of the query very large? Can you share the execution plan?

| username: 这里介绍不了我 | Original post link

Post the execution plan and let’s take a look.

| username: DBAER | Original post link

Refer to this for troubleshooting: TiDB 集群问题导图 | PingCAP 文档中心

| username: TIDB-Learner | Original post link

Timeout, error service unavailable? Is the channel field highly distinct? Check with explain. It might not be using the index. Can you add a limit 1 to see if it returns a result?

| username: 小于同学 | Original post link

Does it keep happening?

| username: 随缘天空 | Original post link

Use the tiup cluster display <cluster-name> command to check if there are any abnormalities in the cluster status. Additionally, when a Region of a table is undergoing Split or Merge operations, the Region may be temporarily unavailable. This situation usually does not require manual intervention; just wait for the operation to complete. So you need to check whether this issue is occasional or consistently unavailable.

| username: yytest | Original post link

Need to check the network.

| username: 付先生Mr | Original post link

The result set is not large, but the base data volume is very large.

| username: 付先生Mr | Original post link

As long as it is executed, it will appear.

| username: 付先生Mr | Original post link

The channel differentiation is not high.

| username: 付先生Mr | Original post link

How to force the use of TiFlash?

| username: Kongdom | Original post link

Try using a composite index and check the execution plan. Currently, the two fields are indexed separately, so only one index can be used per query.

| username: 付先生Mr | Original post link

force index