Workload Analysis with MySQL's Performance Schema

Earlier this spring, a database cluster was upgraded to MySQL 5.6, introducing new features to the performance schema, which is a set of tables that track internal performance metrics. These enhancements provide insights into database operations, such as query execution and IO wait statistics, with new tables like table_io_waits_summary_by_index and events_statements_summary_by_digest offering valuable data on query performance and index use. By importing this data into a metrics system, the team was able to identify and address inefficiencies; for example, a single UPDATE query was initially responsible for over 25% of all updates on a critical table. After a simple code modification to update the health status of repositories only when necessary, the query's impact was reduced to less than 2%. Tools like the performance schema have proven useful for identifying and solving database bottlenecks, as demonstrated by the reduced load achieved through minor code adjustments. The experience underscores the potential benefits of leveraging such tools for performance optimization.