[MDEV-19267] unnecessary scanned all databases for information_schema Created: 2019-04-17  Updated: 2020-03-06

Status: Open
Project: MariaDB Server
Component/s: Server
Fix Version/s: None

Type: Task Priority: Major
Reporter: Elliott Balsley Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-11655 Transactional data dictionary Open

 Description   

Any query to information_schema is extremely slow because it scans all databases. For example, this query takes over 3 seconds. This bug has been fixed in MySQL 8.0, so I tested in that version using the same data and this same query takes 0.03 seconds.
https://bugs.mysql.com/bug.php?id=81347

SELECT `update_time` FROM information_schema.tables;
 
...
 
5068 rows in set (3.289 sec)
 
 
 
EXPLAIN SELECT update_time FROM information_schema.tables;
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                                  |
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
 
|    1 | SIMPLE      | tables | ALL  | NULL          | NULL | NULL    | NULL | NULL | Open_full_table; Scanned all databases |
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
 
1 row in set (0.002 sec)



 Comments   
Comment by Marko Mäkelä [ 2020-03-06 ]

When it comes to InnoDB tables, there is no persistent update_time (or create_time, for that matter). That particular query might be executed without opening all tables internally, by simply returning 0 for those tables that do not exist in the cache.

MySQL 8.0 replaced the .frm file based data dictionary with the Global Data Dictionary, which stores the data dictionary information in InnoDB tables. They made most INFORMATION_SCHEMA tables views of existing tables. Those columns that are not stored in the dictionary would still be computed.

I believe that also MySQL 8.0 would open all tables for this query, to ask the storage engine to fill in the update_time, but that would be somewhat faster, because instead of reading a large number of .frm files, it would read the global data dictionary tables from the InnoDB buffer pool.

A long time ago, I wrote in MDEV-11655 some thoughts on designing and implementing a better data dictionary.

Generated at Thu Feb 08 08:50:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.