OTN appreciation day: The Performance Schema of MySQL 5.6+

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 consistently and monitoring your production systems regularly for database optimization, 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.