未加星标

OTN appreciation day: The Performance Schema of MySQL 5.6+

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

To focus on just one point for OTN appreciation day on October 11 2016 and to the benefit of all users of mysql is to consider the extremely convenient and rich value of information available in the MySQL Performance Schema to understand what SQL queries are running in a MySQL instance now. The MySQL Performance Schema in MySQL 5.6 is enabled by default, (performance_schema=on).

The following one off SQL statement will enable the instrumentation of SQL statements in the most detailed level of assessment.

The following query will show you the longest running queries in your database at this present time.

This ease of accessing what is running in a MySQL instance replaces many different and creative techniques as I describe in Improving MySQL Performance with Better Indexes in versions of MySQL before version 5.6.

If your organization does not have dedicated performance experts reviewing new functionality consistency and monitoring your production systems regularly, the cost of having the MySQL performance schema available and with a large number of different forms of instrumentation out ways any reason not to.

One of the best presentations at Percona Live Amsterdam last week in the last time slot of the event (before beer and food) was Performance schema and sys schema by Mark Leith . I hope to provide a review of this presentation soon and my interest to explore the new MySQL 5.7 and 8.0 performance schema instruments. A few of my live tweets included:

MySQL 5.7 & 8.0 Performance Schema #MySQL 5.7 Performance Schema has 35 new tables (now 87) and 419 new instruments (now 972) #PerconaLive @MarkLeith #MySQL 8.0.0 (first DMR release) has 7 new tables(now 94) and 143 new instruments (now 1115) #PerconaLive @MarkLeith http://dev.mysql.com/doc/refman/8.0/en/performance-schema.html #MySQL 5.7 Performance Schema now has tables for replication solving how to use SQL for SHOW SLAVE STATUS data #PerconaLive @MarkLeith Want more information on #InnoDB thread locking. @MarkLeith talking about new sys.innodb_lock_waits tables in #MySQL 5.7 #PerconaLive Producing a visual in-depth per call graph. No debugger required. #MySQL 5.7 sys.ps_trace_thread() @MarkLeith #PerconaLive #MySQL 5.7 sys.metrics view is a single result set for global status and InnoDB metrics statistics. #PerconaLive @MarkLeith sys.session has 30x improvement using #MySQL 8.0 Performance Schema indexes (fake indexes) WL#6616 @MarkLeith #perconalive Why I wrote this appreciation?

On Friday I was asked to review the MySQL performance and load of a newly developed product during simulated tested. When I was first given access to the MySQL database server I was very disappointed that for a new and unreleased product the MySQL version chosen was 5.5. This is in no way disrespectful for the great stability, functionality and features of MySQL 5.5, however for any new system under development MySQL 5.6 and MySQL 5.7 are both much more appropriate options for many reasons. If for no other reason to look at upgrading to at least MySQL 5.6 to enable you to become a better expert with this functionality is one key consideration.

OTN Appreciation Day

This post format was suggested by Tim Hall , a well known community champion among Oracle users, who maintains a rich web site of news and free technical info. According to his suggestion, this post wants to add to the OTN appreciation day , a distributed community effort to show something useful, or pleasant, or both related to the Oracle world.

For those not used to the Oracle Technology Network (OTN) , it is the center of Oracle technology, the place where users can get all software (proprietary or open source) and other resources related to Oracle products. In the image below you may find several familiar names.


OTN appreciation day: The Performance Schema of MySQL 5.6+

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: SQLMySQLInnoDB
分页:12
转载请注明
本文标题:OTN appreciation day: The Performance Schema of MySQL 5.6+
本站链接:http://www.codesec.net/view/482331.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(37)