TiDB cannot calculate the correct health for tables
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
1. Start the TiDB cluster.
2. Set the auto-analyze ratio to 0.2
```sql
mysql> set global tidb_auto_analyze_ratio=0.2;
Query OK, 0 rows affected (0.02 sec)
mysql> select @@tidb_auto_analyze_ratio;
+---------------------------+
| @@tidb_auto_analyze_ratio |
+---------------------------+
| 0.2 |
+---------------------------+
1 row in set (0.00 sec)
```
3. Create one table and insert 4400 rows into it
```python
import pymysql
import random
# Connect to TiDB
conn = pymysql.connect(host='localhost', port=4000, user='root', passwd='', db='test')
cursor = conn.cursor()
# Create partitioned table
cursor.execute("""
CREATE TABLE users (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
country VARCHAR(2) NOT NULL
)
PARTITION BY RANGE (age) (
PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (60)
);
""")
# Generate and insert data
for i in range(4400):
id = i
name = 'User {}'.format(i)
age = random.randint(15, 55)
country = random.choice(['US', 'CN', 'IN'])
cursor.execute("INSERT INTO users VALUES (%s, %s, %s, %s)", (id, name, age, country))
conn.commit()
print("Partitioned table created and data inserted successfully!")
```
4. Check the TiDB logs and the table will be analyzed.
5. Set the auto-analyze ratio to 3(300%)
6. Insert 440000 rows into the table
```python
for i in range(440000):
id = i
name = 'User {}'.format(i)
age = random.randint(15, 55)
country = random.choice(['US', 'CN', 'IN'])
cursor.execute("INSERT INTO users VALUES (%s, %s, %s, %s)", (id, name, age, country))
```
7. Check the logs and no auto-analyze
We should analyze this table, but we don't. The reason is that we cannot calculate the correct change range or health for this table.
https://github.com/pingcap/tidb/blob/8416dbe412e5d172e32f4c2efd8e5ed2427a3c44/pkg/statistics/table.go#L432-L433
Here because there is no workload on this table. So we cannot get the real analyze count then we fall back to using the real-time count as the total count and count the change percentage with modifyCount/realTimeCount. So we cannot get the ratio.
But if you issue a query to this table then we can get the right health.
Contributor guide
Assessment
This issue has not been assessed yet.