Simple queries of Performance Schema tables can boost MySQL database performance without the heavy lifting.
TL;DR: Identify indexes that are no longer being used, spot the queries that take longest to process, and diagnose other hiccups in your MySQL database’s performance by querying the Performance Schema tables.
When Oracle released MySQL 5.7 last year, the company boasted that the new version handled queries at twice the speed of its predecessor, as InfoWorld’s Joab Jackson reported in a March 31, 2014, article. Companies could therefore use fewer servers to run large jobs, or take less time to run complex queries on the same number of servers.
MySQL 5.7 also added new performance schema for diagnosing and repairing bottlenecks and other problems. The Performance Schema tool is designed to monitor MySQL Server execution at a low level to avoid imposing a performance hit of its own. The 52 tables in “performance_schema” can be queried to report on the performance of indexes, threads, events, queries, temp tables, and other database elements.
In his January 12, 2015, overview of MySQL Workbench Performance reports, Database Journal’s Rob Gravelle points out that using Performance Schema entails a lot of “instrumentation,” including server defaults for monitoring coverage, using canned Performance Reports, and retrieving the SQL statement.
The MySQL Reference Manual explains that Performance Schema monitors all server events, including mutexes and other synchronization calls, file and table I/O, and table locks. It tracks current events as well as event histories and summaries, but tables stored in the corresponding performance_schema are temporary and do not persist on disk. (Note that the complementary Information Schema — also called the data dictionary or system catalog — lets you access server metadata, such as database and table names, column data types, and access privileges.)
Putting MySQL’s Performance Schema tool to use
Starting with MySQL version 5.6.6, Performance Schema is enabled by default. Database Journal’s Gravelle explains in a December 8, 2014, article that you can enable or disable it explicitly by starting the server with the performance_schema variable set to “on” via a switch in your my.cnf file. Verify initialization by using the statement “mysql> SHOW VARIABLES LIKE ‘performance_schema’;”.
Initialize Performance Schema via a switch in the my.cnf file, and verify initialization by running the “SHOW VARIABLES LIKE” command. Source: Database Journal
The 52 Performance Schema tables include configuration tables, object tables, current tables, history tables, and summary tables. You can query these tables to identify unused indexes, for example.
Run this query to determine which of your database’s indexes are merely taking up space. Source: Database Journal
Adding “AND OBJECT_SCHEMA = ‘test'” to the WHERE clause lets you limit results to a specific schema. Another query helps you determine which long-running queries are monopolizing system resources unnecessarily.
This Performance Schema query generates a process list by the time required for the queries to run. Source: Database Journal
While most performance_schema tables are read-only, some setup tables support the data manipulation language (DML) to allow configuration changes to be made. The Visual Guide to the MySQL Performance Schema lists the five setup tables: actors, objects, instruments, consumers, and timers.
There’s no simpler or more-efficient way to monitor the performance of heterogeneous MySQL, MongoDB, Redis, and ElasticSearch databases than by using the new Morpheus Virtual Appliance. Morpheus lets you seamlessly provision and manage SQL, NoSQL, and in-memory databases across hybrid clouds via a single point-and-click dashboard. Each database instance you create includes a free full replica set for built-in fault tolerance and fail over.
With the Morpheus database-as-a-service (DBaaS), you can migrate existing databases from a private cloud to the public cloud, or from public to private. A new instance of the same database type is created in the other cloud, and real-time replication keeps the two databases in sync. Visit the Morpheus site for pricing information and to create a free account.
