select very slow on an empty table from delete from xx #52905
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 with524288rows, then empty it withdelete from, it takes440msto query it.
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
...... 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
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