MySQL – Identifying and Optimizing the Busiest Tables

When doing performance tuning for a MySQL database, it is extremely helpful to know which tables have the highest overall activity. This allows you to focus optimization efforts on the most critical parts of the database for maximum impact. Let us learn Identifying and Optimizing the Busiest Tables in MySQL.

MySQL provides an easy way to identify the busiest tables using the performance_schema. First, enable the performance_schema by setting performance_schema=ON in your MySQL configuration.

Next, run this query:

SELECT 
OBJECT_SCHEMA, 
OBJECT_NAME,
SUM(COUNT_READ) AS TOTAL_READS,
SUM(COUNT_WRITE) AS TOTAL_WRITES, 
SUM(COUNT_FETCH) AS TOTAL_FETCHES,
SUM(COUNT_INSERT) AS TOTAL_INSERTS,
SUM(COUNT_UPDATE) AS TOTAL_UPDATES,
SUM(COUNT_DELETE) AS TOTAL_DELETES,
SUM(COUNT_STAR) AS TOTAL
FROM performance_schema.table_io_waits_summary_by_table
GROUP BY OBJECT_SCHEMA, OBJECT_NAME
ORDER BY TOTAL DESC
LIMIT 10;

This will show the top 10 busiest tables in the database ordered by total activity. It totals up reads, writes, fetches, inserts, updates, and deletes for each table.

Once you’ve identified the busiest tables, there are several optimization techniques you can apply:

• Add indexes – Indexes speed up reads by enabling the database to find rows faster without scanning the entire table. Determine if any large tables are missing indexes on commonly queried columns.
• Tune expensive queries – Dig deeper into the specific queries hitting busy tables. The performance_schema and EXPLAIN can help uncover complex joins, missing indexes, etc.
• Partition tables – Breaking large tables into partitions limits the amount of data read by queries. Range partitioning on date columns is common.
• Use a memory engine – For read-heavy workloads, switching to InnoDB or another memory-optimized engine can improve performance.

This query also reveals whether your overall workload is more read or write-heavy. If TOTAL_READS is much higher than TOTAL_WRITES for the busy tables, your system is likely read-intensive.

Knowing the busiest MySQL tables provides an easy start to understanding database performance pain points. The performance_schema provides many other useful tools for deeper analysis as well. Focus your optimization efforts on the hot spot tables first to get the biggest performance wins. Here is the link to my twitter and LinkedIn profile.

Reference: Pinal Dave (https://blog.sqlauthority.com)