Strategies for Monitoring InnoDB Page Splits in MySQL 8 Using Performance Schema

In MySQL, particularly with the InnoDB storage engine, a "page split" occurs when a new row is inserted into a full index page, causing the page to split into two to accommodate the new row. This can have performance implications, especially if it happens frequently.

As of my last update in September 2021, MySQL does not provide direct instrumentation in the Performance Schema specifically for tracking page splits. However, there are indirect methods to monitor activities that might indicate or lead to page splits. Here are a few approaches:

1. InnoDB Buffer Pool and Row Operations

Monitor the buffer pool's activity and row operations, as frequent insertions and modifications can lead to page splits:

SELECT NAME, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL;        

This query shows read and write operations on index pages, which can give you an idea of the indexes with the most write activity, potentially leading to page splits.

2. InnoDB Row Insertions

Track the number of rows inserted into InnoDB tables:

SHOW STATUS LIKE 'Innodb_rows_inserted';        

A high rate of row insertions might indicate a higher likelihood of page splits, especially if the tables have secondary indexes.

3. InnoDB I/O-Related Status Variables

Review the I/O-related status variables to understand the overall I/O load on your InnoDB instance:

SHOW STATUS LIKE 'Innodb_buffer_pool_write_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_flushed';        

While these do not directly indicate page splits, a high number of write requests and page flushes might correlate with a high rate of page changes due to splits.

4. Monitoring Index Size Growth

Regularly monitor the growth of your table indexes. A rapid increase in index size can sometimes be attributed to page splitting, especially in tables with high insertion rates:

SELECT TABLE_NAME,
       INDEX_NAME,
       STAT_VALUE * @@innodb_page_size as INDEX_SIZE
FROM mysql.innodb_index_stats
WHERE STAT_NAME='size';        

This query shows the estimated size of each index, which can be used to monitor growth over time.

5. Slow Query Log

Enable the slow query log to capture queries that are taking a long time to execute. While not specific to page splits, this can help you identify inefficient queries that might be causing heavy index modifications:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2        

Conclusion

Although MySQL does not provide direct metrics for tracking page splits in the Performance Schema, by monitoring index activity, row insertions, and I/O statistics, you can infer patterns and conditions that are conducive to page splits. Regular monitoring and analysis of these metrics are key to understanding and optimizing the performance of your InnoDB tables.

Remember that dealing with page splits also involves a broader strategy of optimizing your table schema, indexes, and queries to balance performance with the overhead of maintaining your database structure.