Free SQL Help for Devs, DBAs, and the Curious

With the release of MySQL 8.0, a number of significant improvements have been made to the Performance Schema, designed to provide more detailed and accurate performance monitoring. This post will delve into these enhancements and how they can help users to optimize their MySQL databases. This discussion will also touch on relevant technologies from other database management systems such as SQL Server 2019, DB2 11.5, Azure SQL, and Azure Synapse.

One of the key improvements in MySQL 8.0 is the addition of more summary tables. These tables provide aggregated statistics for statement events, thereby making it easier to identify performance issues. The syntax structure is similar to the one used in SQL Server 2019's system views, with the main difference lying in the way the data is being aggregated. For example:

SELECT SUM(TIMER_WAIT)
FROM events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'db_name' AND DIGEST = 'digest_value';

Another major enhancement in MySQL 8.0 is the ability to measure the load on the CPU directly from the Performance Schema. This feature is not dissimilar to the sys.dm_os_ring_buffers dynamic management view in SQL Server 2019, which provides information about system health. In MySQL 8.0, the CPU usage can be tracked using the following SQL statement:

SELECT * 
FROM events_waits_summary_by_thread_by_event_name
WHERE EVENT_NAME = 'idle';

MySQL 8.0 also improves on memory allocation instrumentation. This is an upgrade on previous versions and similar to the memory clerk stats in SQL Server 2019 or the memory management in DB2 11.5. This feature allows users to track how much memory is being used by each thread. The memory usage details can be obtained with the following statement:

SELECT * 
FROM memory_summary_by_thread_by_event_name;

Lastly, the error instrumentation in MySQL 8.0 has been improved. This is comparable to the error logging functionality in Azure SQL and Azure Synapse. In MySQL 8.0, users can monitor all the server errors and warnings that are logged. The following SQL statement can be used to track this:

SELECT * 
FROM events_errors_summary_by_user_by_error;

In conclusion, MySQL 8.0 has made significant strides in enhancing the Performance Schema. These improvements make MySQL 8.0 more competitive with other leading database technologies such as SQL Server 2019, DB2 11.5, Azure SQL, and Azure Synapse. Users can now easily track and monitor the performance of their databases, leading to more efficient and effective database management.