MySQL Bugs: #98449: SELECT from I_S.INNODB

Bug #98449	SELECT from I_S.INNODB_COLUMNS is slow in 8.0
Submitted:	31 Jan 2020 12:25	Modified:	2 Nov 2023 6:26
Reporter:	Sergei Glushchenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S5 (Performance)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Selecting from I_S.INNODB_COLUMNS takes ~2 seconds on debug builds of MySQL 8. Release builds are little faster but still too slow.

How to repeat:
mysql> SHOW VARIABLES LIKE 'VERSION';
+---------------+--------------+   
| Variable_name | Value        |   
+---------------+--------------+   
| version       | 8.0.19-debug |   
+---------------+--------------+   
1 row in set (0.01 sec)            

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_COLUMNS;
+----------+                                                  
| COUNT(*) |                                                  
+----------+                                                  
|      581 |                                                  
+----------+                                                  
1 row in set (1.84 sec)                                       

To compare 5.7:

mysql> SHOW VARIABLES LIKE 'VERSION';
+---------------+------------------+ 
| Variable_name | Value            | 
+---------------+------------------+ 
| version       | 5.7.29-debug-log | 
+---------------+------------------+ 
1 row in set (0.00 sec)              

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS;
+----------+                                                      
| COUNT(*) |                                                      
+----------+                                                      
|      133 |                                                      
+----------+                                                      
1 row in set (0.01 sec)                                           

Suggested fix:
Profiling shows that a lot of time is spent reading from InnoDB and constructing table object by data dictionary's Storage_adapter. Only table name and schema name are then used from this object. It is possible to cache table name and schema name by table ID when scanning innodb_columns. Also it may be possible to avoid loading the table altogether just read all needed information from the "mysql/columns" table.

Hello Sergei,

Thank you for the report and feedback.

regards,
Umesh

Hi Umesh,

Thank you for looking into this issue. I disagree however with severity of this bug. This bug is not specific to Debug builds, you can compare release builds of recent 8.0 and 5.7. Things get much worse if you have thousands of tables with tenth columns each.

Also, I want to point out that this is recent regression caused by the fix for https://bugs.mysql.com/bug.php?id=93033

Thank you Sergei for the feedback.
Changing severity to S5(Performance). 

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming 8.0.21 release, and here's the proposed changelog entry from the documentation team:

A regression introduced in MySQL 8.0.18 affected
INFORMATION_SCHEMA.INNODB_COLUMNS query performance. Schema and table data
dictionary objects were fetched repeatedly to retrieve partition column
information.