What is Performance Schema in MySQL?
The performance schema contains information about recent statement events and aggregates that information in summary tables. It is essential to have access to the performance schema for any modern MySQL installation. However, while many leading cloud providers give access to the performance schema, some do not provide access for security reasons.
Here is one example of how a performance schema can be helpful. One of the tables which can help us understand long-running queries is the event statement summary by digest table.
SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 SUM_TIMER_WAIT_SEC, AVG_TIMER_WAIT/1000000000000 AVG_TIMER_WAIT_SEC, SUM_LOCK_TIME/1000000000000 SUM_LOCK_TIME_SEC FIRST_SEEN, LAST_SEEN FROM events_statements_summary_by_digest;
The above script provides information about how long any statement was locked and how often it occurred. This query offers valuable insight into when the event occurred and its latest occurrence. Now, if this query is not allowed to run, the user may not have access to vital information about the application’s behavior.
Monyog, a.k.a. SQL Diagnostic Manager for MySQL, is an industry-leading MySQL monitoring tool that helps database administrators effortlessly identify and solve performance issues in physical, virtual, and cloud environments.
Learn how Monyog can make monitoring your cloud-based MySQL databases easier, then start for free for 14 days. Start today.
Looking for more MySQL tools for the cloud? Consider SQLyog–it’s as versatile as it is powerful. With SQLyog, you can use a single tool to manage MySQL across physical, virtual, and cloud environments.
Get reliable database backups and data synchronization with SQLyog: a powerful MySQL GUI tool that helps database developers automatically compare and synchronize schema, schedule backups and queries, and so much more.
Try SQLyog Ultimate free for 14 days. See how it helps your database developers do higher quality work in less time. Start today.