Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

query results of information_schema.tables not accurate #55803

Open
fubinzh opened this issue Sep 2, 2024 · 7 comments · May be fixed by #56287
Open

query results of information_schema.tables not accurate #55803

fubinzh opened this issue Sep 2, 2024 · 7 comments · May be fixed by #56287
Assignees
@Rustin170506
Labels
affects-7.1 affects-7.5 affects-8.1 affects-8.5 component/statistics report/customer Customers have encountered this bug. severity/major sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@fubinzh
Copy link

fubinzh commented Sep 2, 2024 •
edited
Loading

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

  1. br restore a backup with 10 tables, each table 500k rows.
  2. all table is analyzed (auto/manual)
  3. Run below query to get information of the tables.
    select table_name, avg_row_length, max_data_length, data_length, table_rows from information_schema.tables where table_schema = 'db500k';

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

The table information should be accurate.

3. What did you see instead (Required)

The query results not accurate, there is no max_data_length . and some tables has no results.
Also, run the query multple times, result might be different.

mysql> SHOW STATS_META where db_name='db500k';
+---------+-------------+----------------+---------------------+--------------+-----------+---------------------+
| Db_name | Table_name  | Partition_name | Update_time         | Modify_count | Row_count | Last_analyze_time   |
+---------+-------------+----------------+---------------------+--------------+-----------+---------------------+
| db500k | index_Data4 |                | 2024-09-02 13:28:24 |            0 |    500000 | 2024-09-02 13:28:24 |
| db500k | index_Data3 |                | 2024-09-02 13:28:23 |            0 |    500000 | 2024-09-02 13:28:23 |
| db500k | index_Data1 |                | 2024-09-02 13:28:24 |            0 |    500000 | 2024-09-02 13:28:24 |
| db500k | index_Data2 |                | 2024-09-02 13:28:23 |            0 |    500000 | 2024-09-02 13:28:23 |
| db500k | index_Data5 |                | 2024-09-02 13:28:24 |            0 |    500000 | 2024-09-02 13:28:24 |
| db500k | Data4       |                | 2024-09-02 13:30:39 |            0 |    500000 | 2024-09-02 13:30:39 |
| db500k | Data5       |                | 2024-09-02 13:30:38 |            0 |    500000 | 2024-09-02 13:30:38 |
| db500k | Data2       |                | 2024-09-02 13:30:41 |            0 |    500000 | 2024-09-02 13:30:41 |
| db500k | Data3       |                | 2024-09-02 13:30:40 |            0 |    500000 | 2024-09-02 13:30:40 |
| db500k | Data1       |                | 2024-09-02 13:30:39 |            0 |    500000 | 2024-09-02 13:30:38 |
+---------+-------------+----------------+---------------------+--------------+-----------+---------------------+
10 rows in set (0.05 sec)
mysql> select table_name, avg_row_length, max_data_length, data_length, table_rows from information_schema.tables where table_schema = 'db500k';
+-------------+----------------+-----------------+-------------+------------+
| table_name  | avg_row_length | max_data_length | data_length | table_rows |
+-------------+----------------+-----------------+-------------+------------+
| index_Data4 |            135 |               0 |    67991573 |     500000 |
| index_Data3 |              0 |               0 |           0 |          0 |
| index_Data1 |              0 |               0 |           0 |          0 |
| index_Data2 |              0 |               0 |           0 |          0 |
| index_Data5 |              0 |               0 |           0 |          0 |
| Data4       |              0 |               0 |           0 |          0 |
| Data5       |              0 |               0 |           0 |          0 |
| Data2       |              0 |               0 |           0 |          0 |
| Data3       |              0 |               0 |           0 |          0 |
| Data1       |              0 |               0 |           0 |          0 |
+-------------+----------------+-----------------+-------------+------------+
10 rows in set (0.05 sec)

4. What is your TiDB version? (Required)

Master
Edition: Community
Git Commit Hash: 94666a2
@fubinzh fubinzh added the type/bug The issue is confirmed as a bug. label Sep 2, 2024
@Rustin170506
Copy link
Member

debug PR: #55789

@Rustin170506 Rustin170506 added the sig/planner SIG: Planner label Sep 3, 2024
@Rustin170506 Rustin170506 self-assigned this Sep 3, 2024
@Rustin170506
Copy link
Member

Rustin170506 commented Sep 3, 2024 •
edited
Loading

Test logs:

[root@upstream-tidb-2 log]# grep "debug:" tidb.log
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=data1] [tableID=11439]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=data1] [tableID=11439] [rowCount=500000]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data1] [tableID=11439] [column=model_id] [length=8] [totalLength=4000000]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data1] [tableID=11439] [column=object_id] [length=8] [totalLength=4000000]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=data1] [tableID=11439] [column=object_value] [length=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data1] [tableID=11439] [column=version] [length=8] [totalLength=4000000]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=data1] [tableID=11439] [dataLength=12000000]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=data1] [tableID=11439] [dataLength=12000000] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=data1] [tableID=11439] [dataLength=12000000] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=data1] [tableID=11439] [rowCount=500000] [avgRowLength=24] [dataLength=12000000] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=index_data3] [tableID=11423]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=index_data3] [tableID=11423] [rowCount=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data3] [tableID=11423] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data3] [tableID=11423] [column=reference_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=index_data3] [tableID=11423] [column=guid] [length=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data3] [tableID=11423] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=index_data3] [tableID=11423] [dataLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data3] [tableID=11423] [indexColumn=guid] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data3] [tableID=11423] [indexColumn=reference_id] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=index_data3] [tableID=11423] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=index_data3] [tableID=11423] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=index_data3] [tableID=11423] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=index_data1] [tableID=11425]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=index_data1] [tableID=11425] [rowCount=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data1] [tableID=11425] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.946 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data1] [tableID=11425] [column=reference_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=index_data1] [tableID=11425] [column=guid] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data1] [tableID=11425] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=index_data1] [tableID=11425] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data1] [tableID=11425] [indexColumn=guid] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data1] [tableID=11425] [indexColumn=reference_id] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=index_data1] [tableID=11425] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=index_data1] [tableID=11425] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=index_data1] [tableID=11425] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=index_data2] [tableID=11427]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=index_data2] [tableID=11427] [rowCount=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data2] [tableID=11427] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data2] [tableID=11427] [column=reference_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=index_data2] [tableID=11427] [column=guid] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data2] [tableID=11427] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=index_data2] [tableID=11427] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data2] [tableID=11427] [indexColumn=guid] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data2] [tableID=11427] [indexColumn=reference_id] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=index_data2] [tableID=11427] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=index_data2] [tableID=11427] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=index_data2] [tableID=11427] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=data4] [tableID=11431]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=data4] [tableID=11431] [rowCount=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data4] [tableID=11431] [column=model_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data4] [tableID=11431] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=data4] [tableID=11431] [column=object_value] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data4] [tableID=11431] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=data4] [tableID=11431] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=data4] [tableID=11431] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=data4] [tableID=11431] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=data4] [tableID=11431] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=data3] [tableID=11437]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=data3] [tableID=11437] [rowCount=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data3] [tableID=11437] [column=model_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data3] [tableID=11437] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=data3] [tableID=11437] [column=object_value] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data3] [tableID=11437] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=data3] [tableID=11437] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=data3] [tableID=11437] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=data3] [tableID=11437] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=data3] [tableID=11437] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=index_data4] [tableID=11421]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=index_data4] [tableID=11421] [rowCount=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data4] [tableID=11421] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data4] [tableID=11421] [column=reference_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=index_data4] [tableID=11421] [column=guid] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data4] [tableID=11421] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=index_data4] [tableID=11421] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data4] [tableID=11421] [indexColumn=guid] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data4] [tableID=11421] [indexColumn=reference_id] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=index_data4] [tableID=11421] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=index_data4] [tableID=11421] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=index_data4] [tableID=11421] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=index_data5] [tableID=11429]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=index_data5] [tableID=11429] [rowCount=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data5] [tableID=11429] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data5] [tableID=11429] [column=reference_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=index_data5] [tableID=11429] [column=guid] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=index_data5] [tableID=11429] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=index_data5] [tableID=11429] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data5] [tableID=11429] [indexColumn=guid] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:335] ["debug: Index length calculated"] [table=index_data5] [tableID=11429] [indexColumn=reference_id] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=index_data5] [tableID=11429] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=index_data5] [tableID=11429] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=index_data5] [tableID=11429] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=data5] [tableID=11433]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=data5] [tableID=11433] [rowCount=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data5] [tableID=11433] [column=model_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data5] [tableID=11433] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=data5] [tableID=11433] [column=object_value] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data5] [tableID=11433] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=data5] [tableID=11433] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=data5] [tableID=11433] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=data5] [tableID=11433] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=data5] [tableID=11433] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:153] ["debug: Estimating data length"] [table=data2] [tableID=11435]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=data2] [tableID=11435] [rowCount=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data2] [tableID=11435] [column=model_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data2] [tableID=11435] [column=object_id] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:300] ["debug: Variable storage length column"] [table=data2] [tableID=11435] [column=object_value] [length=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:291] ["debug: Fixed storage length column"] [table=data2] [tableID=11435] [column=version] [length=8] [totalLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:310] ["debug: Data length calculated"] [table=data2] [tableID=11435] [dataLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:342] ["debug: Final lengths calculated"] [table=data2] [tableID=11435] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:164] ["debug: Got data and index length"] [table=data2] [tableID=11435] [dataLength=0] [indexLength=0]
[2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:194] ["debug: Final estimated data length"] [table=data2] [tableID=11435] [rowCount=0] [avgRowLength=0] [dataLength=0] [indexLength=0]

As you can see, the key point is: [2024/09/03 12:09:17.947 +08:00] [INFO] [stats_table_row_cache.go:158] ["debug: Got table rows"] [table=data2] [tableID=11435] [rowCount=0]

The row count is 0.

// GetTableRows gets the count of table rows.
func (c *StatsTableRowCache) GetTableRows(id int64) uint64 {
	c.mu.RLock()
	defer c.mu.RUnlock()
	return c.tableRows[id]
}

So the problem is that StatsTableRowCache doesn't cache the row count correctly.

@hawkingrei
Copy link
Member

In fact, StatsTableRowCache and StatsCache can be merged into one object, but in history, they are two different objectives.

@Rustin170506
Copy link
Member

I have identified the root cause of the problem.

This issue comes from the UpdateByID function.

When we attempt to retrieve the information_schema.tables table, it calls a function to fetch information from the mysql.stats_meta table. However, we encounter an issue because the modifyTime is utilized to check if the cache is outdated. In the UpdateByID function, even when we only fetch information for one table, the current modifyTime is reset. This causes problems when attempting to access information for other tables, as it skips the reload, leading to empty caches for those tables and resulting in seeing some 0 values.

@Rustin170506
Copy link
Member

I have fixed it in my debug pull request in a very messy way to demonstrate the root cause:

See: #55789

The test result:

mysql> select table_name, avg_row_length, max_data_length, data_length, table_rows from information_schema.tables where table_schema = 'uds500k';
+-------------+----------------+-----------------+-------------+------------+
| table_name  | avg_row_length | max_data_length | data_length | table_rows |
+-------------+----------------+-----------------+-------------+------------+
| Data1       |             24 |               0 |    12000000 |     500000 |
| index_Data4 |            135 |               0 |    67991573 |     500000 |
| index_Data1 |            136 |               0 |    68002490 |     500000 |
| Data4       |             24 |               0 |    12000000 |     500000 |
| Data2       |             24 |               0 |    12000000 |     500000 |
| Data3       |             24 |               0 |    12000000 |     500000 |
| index_Data3 |            136 |               0 |    68001335 |     500000 |
| index_Data2 |            136 |               0 |    68003939 |     500000 |
| index_Data5 |            136 |               0 |    68004062 |     500000 |
| Data5       |             24 |               0 |    12000000 |     500000 |
+-------------+----------------+-----------------+-------------+------------+
10 rows in set (0.05 sec)

@Rustin170506
Copy link
Member

I will find a more reasonable way to fix it.

@Rustin170506
Copy link
Member

Introduced by 0df9170#diff-b0b21264081ac10d96418a5bc918a4b7d48d46d634ba5a0837186f13ca9a1bf3

@Rustin170506 Rustin170506 linked a pull request Sep 25, 2024 that will close this issue
Draft
13 tasks
@ti-chi-bot ti-chi-bot bot added the report/customer Customers have encountered this bug. label Oct 11, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@Rustin170506 Rustin170506

Labels
affects-7.1 affects-7.5 affects-8.1 affects-8.5 component/statistics report/customer Customers have encountered this bug. severity/major sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

statistics: Do not refresh the modification time if only update for one table Rustin170506/tidb
4 participants
@hawkingrei @jebter @fubinzh @Rustin170506