pingcap / pingcap/tidb

`select` very slow on an empty table from `delete from xx`

Open
#52,905 7 comments 0 reactions 0 assignees View on GitHub
type/question
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
- create cluster with `tiup playground`
- do the following, we can see query from the empty table takes about `60ms`. also test another table with `524288` rows, then empty it with `delete from`, it takes `440ms` to query it.
```sql
mysql> create table t(a int);
Query OK, 0 rows affected (0.10 sec)

.... insert data

mysql> delete from t;
Query OK, 65536 rows affected (0.38 sec)

mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.06 sec)

mysql> show table t regions;
+-----------+-----------+--------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS | SCHEDULING_STATE |
+-----------+-----------+--------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+
| 14 | t_107_ | t_281474976710649_ | 15 | 1 | 15 | 0 | 23118061 | 16450774 | 24 | 133569 | | |
+-----------+-----------+--------------------+-----------+-----------------+-------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-04-26 11:34:15 |
+---------------------+
1 row in set (0.00 sec)

```
- at first, i thought it related to MVCC, so wait GC and query again, still very slow
```sql
...... wait gc finished on table t

mysql> select count(1) from t;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.07 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-04-26 14:18:50 |
+---------------------+
1 row in set (0.00 sec)
```

- for a new created empty table, query time < 10ms
```sql
mysql> create table tt(a int);
Query OK, 0 rows affected (0.11 sec)

mysql> select count(1) from tt;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
```

### 2. What did you expect to see? (Required)
very fast to query
### 3. What did you see instead (Required)
slow compared to a new created empty table
### 4. What is your TiDB version? (Required)
master

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.