pingcap / pingcap/tidb

Save partial results when executing time-consuming analyze

Open
#42,120 2 comments 0 reactions 0 assignees View on GitHub
component/statistics sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

When we analyze one large table, if some error occurs during the execution(such as oom, request timeout, and so on), the analyze job would fail and we need to re-run the analyze job from the very beginning. It sometimes bothers users since re-analyze may take hours for the large table.

### Non-partitioned tables

For large non-partition tables, they may consist of thousands of regions. When executing the analyze job, samples are collected from each region. If one analyze request to some region fails(e.g., `Coprocessor task terminated due to exceeding the deadline` when tikv coprocessor is under high pressure), the whole analyze job fails.

When some error occurs during the analyze job, we hope to save samples that have already been collected. Maybe we should save the samples on disk to avoid taking up too much tidb memory. Also, we need to record which regions have already finished the analyze request. When the user re-runs the same analyze command, we can skip the regions that have already finished the last analyze request.

### Partitioned tables

For large partitioned tables, they may have hundreds of partitions and each partition is relatively small. Assume `tidb_partition_prune_mode` is `dynamic`. When analyzing a large partitioned table, we launch analyze tasks for each partition(just treat each partition as a non-partitioned table). After all partitions' stats are collected and saved, we merge partitions' stats into global stats.

After some error occurs during the execution of analyzing the partitioned table, we hope to skip the partitions whose stats hvave already been collected when the user re-analyzes the partitioned table. Though we have the syntax `analyze table t partition p0, p1, p2` to skip some partitions, it is really inconvenient for the user to check which partitions have already collected stats and to specify the partitions which need to collect stats. At least, we should provide the script to check which partitions have already collected stats and generate the re-analyze command. Further, a better way is the single command `analyze table t` can skip partitions which have already collected stats.

In order to avoid the failure of analyzing the partitioned table, some users choose to analyze each partition one by one as follows:
```
analyze table t partition p0;
analyze table t partition p1;
...
analyze table t partition p299;
```
But the method also has a problem. When executing `analyze table t partition p0`, collecting stats for `p0` only takes seconds but merging global stats takes minutes since there are 300 partitions. Actually, we only need to do one merging after all partition stats are collected. In order to solve the issue, the user needs to do as follows:

1. `set @@tidb_partition_prune_mode = 'static'`
2. Analyze each partition one by one.
3. `set @@tidb_partition_prune_mode = 'dynamic'`
4. Analyze partition `p0` again to trigger merging global stats.

As you can see, it is also very user-unfriendly.

Contributor guide

Open the contributing guide

Research direction

The issue names no implementation files or tests. Start by reproducing the documented ANALYZE TABLE scenarios for non-partitioned and partitioned tables, including a failed region or partition. Done should preserve completed samples or partition statistics, allow a rerun to skip completed work, and avoid repeated global-statistics merging.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases, performance
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.