pingcap / pingcap/tidb

TiDB cannot calculate the correct health for tables

Open
#51,753 0 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
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

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.