MySQL 8.0 comes with the new design of INFORMATION_SCHEMA subsystem. The blog MySQL 8.0: Improvements to Information_schema provides an overview of the improvements we made. This blog focuses mainly to demonstrate performance of the INFORMATION_SCHEMA in MySQL 8.0, giving us an idea on the kind of performance gain that one can expect.
Let us look at three different cases focusing of A) Area of major performance gain, B) the performance gain when reading the static table meta-data and C) the performance of a query reading dynamic table meta-data. The configuration details of MySQL server instances and the machine details are provided at the end of the blog.
A) Read column names of all InnoDB tables:
One of the major performance bottlenecks of INFORMATION_SCHEMA queries in MySQL 5.7 is seen a query using more than one INFORMATION_SCHEMA table. Let us consider one such query for our study. For example, the following query retrieves column names for all the InnoDB tables,
[code]SELECT t.table_schema, t.table_name, c.column_name
FROM information_schema.tables t,
WHERE t.table_schema = c.table_schema
AND t.table_name = c.table_name
[/code] The following chart shows us the huge performance gain we get in MySQL 8.0. We see that the query executes several hundred times faster than MySQL 5.7. It is ~140 times faster on the Server instance 1 and ~1000 times faster on Server instance 4.
On Server Instance 5, the query takes an average of 40 seconds on 8.0 and we estimate that it would approximately take 20 hours on 5.7. On Server Instance 6, the query takes an average of 80 seconds on 8.0 and we estimate that it would approximately take 40 hours on 5.7. We also need to repeat the test several times for statistical significance, so for practical reasons, we have skipped this.
B) Read static table meta-data:
This sections looks at performance of INFORMATION_SCHEMA query which retrieves static table meta-data. Let us consider following query which reads some of static table meta-data from all the tables in every schema name like ‘db%’.
[code]SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT
WHERE TABLE_SCHEMA LIKE 'db%';
[/code] The below chart shows that this query executes about ~30 times faster in MySQL 8.0 compared to MySQL 5.7.
C) Read dynamic table meta-data:
INFORMATION_SCHEMA queries also get us dynamic table meta-data. Dynamic meta-data frequently changes (for example: the auto_increment value will advance after each insert). In many cases the dynamic meta-data will also incur some cost to accurately calculate on demand, and accuracy may not be beneficial for the typical query. Consider the case of the DATA_FREE statistic which shows the number of free bytes in a table – a cached value is usually sufficient. In MySQL 8.0, the dynamic table meta-data will default to being cached. This is configurable via the setting information_schema_stats (default cached), and can be changed to information_schema_stats=latest in order to always retrieve the dynamic information directly from the storage engine (at the cost of slightly higher query execution). See manual section Information schema optimization for more details.
For our study, let us consider the following query to read TABLE_ROWS dynamic table meta-data from all the tables in schema name like ‘db%’. Let us execute this query in both information_schema_stats=cached and the information_schema_stats=latest configuration setting.
WHERE TABLE_SCHEMA LIKE 'db%';
[/code] C.1) When we execute the above query in setting information_schema_stats=cached (the default) setting, we see that this query also executes about ~30 times faster in MySQL 8.0 compared to MySQL 5.7, approximately it takes the same amount of time as in the case of INFORMATION_SCHEMA query that reads static table meta-data.