pingcap / pingcap/tidb

mview: COMPLETE IN PLACE refresh can hit [executor:8175] memory limit on large materialized views while COMPLETE OUT OF PLACE and COMPLETE DELTA APPLY succeed

Open
#67,599 0 comments 0 reactions 0 assignees View on GitHub
component/mview feature/developing severity/moderate type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

Use a large materialized view that is fed by a single base table.

Stats row counts from `SHOW STATS_META` (not exact `COUNT(*)`):

- Base table: about `1,867,436,299` rows
- Materialized view: about `360,542,617` rows

Run:

```sql
REFRESH MATERIALIZED VIEW . COMPLETE IN PLACE;
```

It also reproduces after increasing the session memory quota:

```sql
SET SESSION tidb_mem_quota_query = 21474836480;
REFRESH MATERIALIZED VIEW . COMPLETE IN PLACE;

SET SESSION tidb_mem_quota_query = 25769803776;
REFRESH MATERIALIZED VIEW . COMPLETE IN PLACE;
```

For comparison, the following statements succeed on the same materialized view and source data:

```sql
REFRESH MATERIALIZED VIEW . COMPLETE;
REFRESH MATERIALIZED VIEW . COMPLETE OUT OF PLACE;
```

### 2. What did you expect to see? (Required)

`REFRESH MATERIALIZED VIEW ... COMPLETE IN PLACE` should be usable on the same large materialized view where other complete-refresh strategies succeed.

At minimum, it should not deterministically fail with a single-query memory-limit error while `COMPLETE` and `COMPLETE OUT OF PLACE` both succeed on the same object.

### 3. What did you see instead (Required)

`REFRESH MATERIALIZED VIEW ... COMPLETE IN PLACE` consistently fails in about 60 seconds with:

```text
ERROR 8175 (HY000): Your query has been cancelled due to exceeding the allowed memory limit for a single SQL query.
```

Observed failed attempts:

- default session quota (`tidb_mem_quota_query = 8589934592`): failed after about `60.05s`
- `tidb_mem_quota_query = 21474836480`: failed after about `59.97s`
- `tidb_mem_quota_query = 25769803776`: failed after about `60.73s`

On the same materialized view and dataset:

- `REFRESH MATERIALIZED VIEW . COMPLETE;`
- succeeds
- internal method recorded as `complete delta apply manual`
- duration about `13m17.76s`
- `REFRESH MATERIALIZED VIEW . COMPLETE OUT OF PLACE;`
- succeeds
- internal method recorded as `complete out of place manual`
- duration about `40m17.95s`

The schema and object names are redacted because they are confidential, but the data scale and behavior are preserved above.

### 4. What is your TiDB version? (Required)

`v8.5.4-10265`

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.