技术控

    今日:48| 主题:49369
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] MySQL 8.0: Scaling and Performance of INFORMATION_SCHEMA

[复制链接]
房叔 发表于 2016-10-5 04:33:48
165 1

立即注册CoLaBug.com会员,免费获得投稿人的专业资料,享用更多功能,玩转个人品牌!

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
Overview

   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.
  Tests

  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,
      information_schema.columns c
  WHERE t.table_schema = c.table_schema
        AND t.table_name = c.table_name
        AND t.engine='InnoDB';
[/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.
   
MySQL 8.0: Scaling and Performance of INFORMATION_SCHEMA-1 (different,overview,provided,provides,reading)
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
  FROM INFORMATION_SCHEMA.TABLES
  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.

MySQL 8.0: Scaling and Performance of INFORMATION_SCHEMA-2 (different,overview,provided,provides,reading)

   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.
  [code]SELECT TABLE_ROWS
  FROM INFORMATION_SCHEMA.TABLES
  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.
12下一页
友荐云推荐




上一篇:Stop Coding Machine Learning Algorithms From Scratch
下一篇:Guccifer 2.0 Hacked Clinton Foundation
酷辣虫提示酷辣虫禁止发表任何与中华人民共和国法律有抵触的内容!所有内容由用户发布,并不代表酷辣虫的观点,酷辣虫无法对用户发布内容真实性提供任何的保证,请自行验证并承担风险与后果。如您有版权、违规等问题,请通过"联系我们"或"违规举报"告知我们处理。

晔伽从 发表于 2016-10-7 03:46:17
我消极对待减肥,能不能取消我胖子的资格啊
回复 支持 反对

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

我要投稿

推荐阅读

扫码访问 @iTTTTT瑞翔 的微博
回页顶回复上一篇下一篇回列表手机版
手机版/CoLaBug.com ( 粤ICP备05003221号 | 文网文[2010]257号 )|网站地图 酷辣虫

© 2001-2016 Comsenz Inc. Design: Dean. DiscuzFans.

返回顶部 返回列表