| Bug #86170 | information_schema returns NULL or MAXINT for all table stats | ||
|---|---|---|---|
| Submitted: | 3 May 2017 17:43 | Modified: | 6 Jun 2018 4:20 |
| Reporter: | Bill Karwin (Candidate Quality Contributor) (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.1-dmr | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | data dictionary, information_schema | ||
[4 May 2017 7:38]
MySQL Verification Team
Hello Bill Karwin,
Thank you for the report and feedback.
Could you please share exact conf file in use, create table statement, and OS details? I tried to reproduce the issue at my end but not seeing the reported issue?
######
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 8.0.1-dmr MySQL Community Server (GPL)
.
.
root@localhost [(none)]> show variables like 'information_schema_stats';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| information_schema_stats | CACHED |
+--------------------------+--------+
root@localhost [test]> use test;
root@localhost [test]> CREATE TABLE `keyvalue` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `name1` varchar(250),
-> `name2` varchar(250),
-> `name3` varchar(250),
-> `name4` varchar(250),
-> PRIMARY KEY (`id`, name1(10))
-> ) ENGINE=innodb;
. -- inserted 5M rows
Query OK, 5000000 rows affected (1 min 40.84 sec)
Records: 5000000 Duplicates: 0 Warnings: 0
root@localhost [test]> show table status like 'keyvalue'\G
*************************** 1. row ***************************
Name: keyvalue
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: 2017-05-04 09:22:33
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
root@localhost [test]> analyze table keyvalue;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.keyvalue | analyze | status | OK |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)
root@localhost [test]> show table status like 'keyvalue'\G
*************************** 1. row ***************************
Name: keyvalue
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 4944456
Avg_row_length: 186
Data_length: 923795456
Max_data_length: 0
Index_length: 0
Data_free: 6291456
Auto_increment: 5000005
Create_time: 2017-05-04 09:22:33
Update_time: 2017-05-04 09:24:16
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
root@localhost [test]> set global information_schema_stats=latest;
Query OK, 0 rows affected (0.00 sec)
root@localhost [test]> \q
Bye
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 8.0.1-dmr MySQL Community Server (GPL)
.
.
root@localhost [test]> show variables like 'information_schema_stats';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| information_schema_stats | LATEST |
+--------------------------+--------+
root@localhost [(none)]> use test
root@localhost [test]> show table status like 'keyvalue'\G
*************************** 1. row ***************************
Name: keyvalue
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 4944456
Avg_row_length: 186
Data_length: 923795456
Max_data_length: 0
Index_length: 0
Data_free: 6291456
Auto_increment: 5000005
Create_time: 2017-05-04 09:22:33
Update_time: 2017-05-04 09:24:16
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Thanks,
Umesh
[4 May 2017 9:13]
Gopal Shankar
We tried the scenario on mysql-8.0.1-dmr-release and unable to reproduce the problem with MAXINT values for the dynamic statistic columns in information_schema_stats=latest mode. Following is the test we conducted, ... CREATE TABLE t22 (a int , b int); show table status like 't22%';; Name t22 Engine InnoDB Version 10 Row_format Dynamic Rows NULL Avg_row_length NULL Data_length NULL Max_data_length NULL Index_length NULL Data_free NULL Auto_increment NULL Create_time 2017-05-04 06:13:33 Update_time NULL Check_time NULL Collation utf8mb4_0900_ai_ci Checksum NULL Create_options Comment analyze table t22; Table Op Msg_type Msg_text test.t22 analyze status OK show table status like 't22%';; Name t22 Engine InnoDB Version 10 Row_format Dynamic Rows 0 Avg_row_length 0 Data_length 16384 Max_data_length 0 Index_length 0 Data_free 0 Auto_increment NULL Create_time 2017-05-04 06:13:33 Update_time NULL Check_time NULL Collation utf8mb4_0900_ai_ci Checksum NULL Create_options Comment set information_schema_stats=latest; show table status like 't22%';; Name t22 Engine InnoDB Version 10 Row_format Dynamic Rows 0 Avg_row_length 0 Data_length 16384 Max_data_length 0 Index_length 0 Data_free 0 Auto_increment NULL Create_time 2017-05-04 06:13:33 Update_time NULL Check_time NULL Collation utf8mb4_0900_ai_ci Checksum NULL Create_options Comment DROP TABLE t22; ... Perhaps there is some information missing in the bug report ?
[5 Jun 2017 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[21 Apr 2018 21:53]
Bill Karwin
Trying out MySQL 8.0.11 GA release, I am seeing a similar problem. I have to set set information_schema_stats_expiry=0 to get accurate table stats. Even after running ANALYZE TABLE on all my tables, the stats were way off. mysql> select table_name, table_rows, data_length, data_length+index_length from tables where table_schema='StackExchange'; +------------------+------------+-------------+--------------------------+ | TABLE_NAME | TABLE_ROWS | DATA_LENGTH | data_length+index_length | +------------------+------------+-------------+--------------------------+ | Badges | 10294 | 409600 | 835584 | | BadgesJson | 0 | 16384 | 16384 | | BadgeTypes | 41 | 16384 | 16384 | | CloseReasonTypes | 12 | 16384 | 16384 | | Comments | 3528 | 1589248 | 1818624 | | CommentsJson | 0 | 16384 | 16384 | | PostHistory | 4727 | 6832128 | 7258112 | | PostHistoryJson | 0 | 16384 | 16384 | | PostHistoryTypes | 32 | 16384 | 16384 | | Posts | 1786 | 2637824 | 2899968 | | PostsJson | 1586 | 3686400 | 3948544 | | PostTypes | 8 | 16384 | 16384 | | Users | 7557 | 2637824 | 2637824 | | UsersJson | 6670 | 3686400 | 3686400 | | Votes | 13215 | 507904 | 1196032 | | VotesJson | 0 | 16384 | 16384 | | VoteTypes | 14 | 16384 | 16384 | +------------------+------------+-------------+--------------------------+ mysql> set information_schema_stats_expiry=0; Query OK, 0 rows affected (0.00 sec) mysql> select table_name, table_rows, data_length, data_length+index_length from tables where table_schema='StackExchange'; +------------------+------------+-------------+--------------------------+ | TABLE_NAME | TABLE_ROWS | DATA_LENGTH | data_length+index_length | +------------------+------------+-------------+--------------------------+ | Badges | 222200 | 8929280 | 18399232 | | BadgesJson | 220967 | 24690688 | 32063488 | | BadgeTypes | 195 | 16384 | 16384 | | CloseReasonTypes | 12 | 16384 | 16384 | | Comments | 216153 | 54083584 | 67747840 | | CommentsJson | 211939 | 74022912 | 88735744 | | PostHistory | 401282 | 586088448 | 617070592 | | PostHistoryJson | 459294 | 643809280 | 675840000 | | PostHistoryTypes | 32 | 16384 | 16384 | | Posts | 117053 | 274448384 | 291831808 | | PostsJson | 149744 | 329023488 | 346406912 | | PostTypes | 8 | 16384 | 16384 | | Users | 111982 | 21544960 | 21544960 | | UsersJson | 115451 | 43581440 | 43581440 | | Votes | 493496 | 18366464 | 45154304 | | VotesJson | 491337 | 58277888 | 85065728 | | VoteTypes | 14 | 16384 | 16384 | +------------------+------------+-------------+--------------------------+
[21 Apr 2018 22:18]
Bill Karwin
My environment: MySQL 8.0.11 MacOS 10.13.4 Here's example DDL for the first two tables: CREATE TABLE Badges ( Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, BadgeTypeId SMALLINT UNSIGNED NOT NULL, UserId INT NOT NULL, CreationDate DATETIME NULL ); CREATE TABLE BadgesJson ( Id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Data JSON NOT NULL ); Full SQL code for my DDL is in github: https://github.com/billkarwin/bk-tools/tree/master/stackexchange Data to import is Stack Exchange data in XML format: https://archive.org/details/stackexchange Any of the archives on that site, large or small, should work equally well for testing with my import script.
[5 Jun 2018 9:25]
Gopal Shankar
The behavior seen on 8.0.11 is as expected based on the way 'information_schema_stats_expiry' works. Please look at comment on "5 Jun" at https://bugs.mysql.com/bug.php?id=91038 This should probably give the clarity on what is expected.
[6 Jun 2018 2:24]
Bill Karwin
Thanks Gopal. Now I know that one must set information_schema_stats_expiry=0.
[6 Jun 2018 4:20]
MySQL Verification Team
Thank you Gopal, Bill. For now I'll close the report as !bg but if you come across any discrepancies then feel free to add it to this bug and change the status back to 'Open'. Regards, Umesh
[5 Apr 2021 6:00]
MySQL Verification Team
Related - Bug #103197
Description: The new information_schema_stats has the value 'cached' by default, but this means that all table stats are NULL until the first time we run ANALYZE TABLE. This is a confusing difference from previous versions of MySQL Server. One suggested workaround is to set information_schema_stats=latest so that the stats are queried from the I_S storage engine on every query. But this returns bogus values, which appear to be MAXINT (32-bit unsigned) for all the following stats: Rows: 4294967295 Avg_row_length: 4294967295 Data_length: 4294967295 Max_data_length: 4294967295 Index_length: 4294967295 Data_free: 4294967295 Auto_increment: 4294967295 Running ANALYZE TABLE does not fix these values. This might also apply to some other I_S tables. I only checked the I_S.TABLES table. How to repeat: Demonstrate that stats are NULL initially when they're cached. root@localhost [test] > show table status like 'TestTable1'\G Name: TestTable1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: NULL Avg_row_length: NULL Data_length: NULL Max_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: 2017-04-25 09:48:13 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: Fill the stats by using ANALYZE TABLE: root@localhost [test] > analyze table TestTable1; root@localhost [test] > show table status like 'TestTable1'\G Name: TestTable1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 750093 Avg_row_length: 5468 Data_length: 4102029312 Max_data_length: 0 Index_length: 0 Data_free: 5242880 Auto_increment: NULL Create_time: 2017-04-25 09:48:13 Update_time: 2017-04-25 09:49:17 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: Change to 'latest': root@localhost [test] > set global information_schema_stats=latest; <Exit mysql client and then reconnect.> root@localhost [test] > show table status like 'TestTable1'\G Name: TestTable1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 4294967295 Avg_row_length: 4294967295 Data_length: 4294967295 Max_data_length: 4294967295 Index_length: 4294967295 Data_free: 4294967295 Auto_increment: 4294967295 Create_time: 2017-04-25 09:48:13 Update_time: 2106-02-06 22:28:15 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: Run ANALYZE TABLE and re-query. You see the same values. Suggested fix: When table stats are cached, the first query against I_S since server restart should prime the cache. This will understandably make the first I_S query take a little more time (probably equivalent to running ANALYZE TABLE for every table matching the query), but subsequent queries will use the cache. Besides, reading the stats the "hard way" should be no worse than earlier versions of MySQL. As for the bogus MAXINT values for table status when using 'latest', I have no idea of the root cause, but this is currently being calculated incorrectly.