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)