Is there a way to track page splits on an InnoDB table?

Question

Is there a way to track the number of page splits on an InnoDB table? I've inherited a couple high-volume tables with integer primary keys in which values are often inserted out of order and I'd like some indication of how much my insert performance is suffering due to page splits from out-of-order inserts. I know SQL Server exposes metrics on that, but I've been unable to find anything in the MySQL docs about that.

Here's the schema for the table with the highest insert volume. Even though the PK is defined as AUTO_INCREMENT, the keys are manually inserted out of order:

CREATE TABLE `data1` (
`data1_id` bigint(20) NOT NULL AUTO_INCREMENT,
`uuid` char(36) DEFAULT NULL,
`headline` varchar(255) DEFAULT NULL,
`status` varchar(10) DEFAULT NULL,
`starred` tinyint(1) DEFAULT '0',
`invalid` tinyint(1) NOT NULL DEFAULT '0',
`reason_invalid` varchar(255) DEFAULT NULL,
`created_by` bigint(20) NOT NULL,
`associated_team_id` bigint(20) DEFAULT NULL,
`calculated_result` bigint(20) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`version` int(11) NOT NULL,
`updated_from_legacy` tinyint(1) DEFAULT '0',
`customer_id` bigint(19) DEFAULT NULL,
`result_description` varchar(16) DEFAULT NULL,
`sort_date` datetime DEFAULT NULL,
PRIMARY KEY (`data1_id`),
KEY `ix_data1_createdBy_16` (`created_by`),
KEY `ix_data1_calculated_result_17` (`calculated_result_id`),
KEY `created_at` (`created_at`),
KEY `updated_at` (`updated_at`),
KEY `headline` (`headline`),
KEY `starred` (`starred`),
KEY `status` (`status`),
KEY `fk_data1_customer_id` (`customer_id`),
KEY `result_description` (`review_disposition`),
KEY `associated_team_id` (`associated_team_id`,`status`,`invalid`),
KEY `sort_date` (`sort_date`),
KEY `associated_team_id_2` (`associated_team_id`,`sort_date`,`status`,`invalid`),
CONSTRAINT `fk_data1_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
CONSTRAINT `fk_data1_createdBy_16` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`),
CONSTRAINT `fk_data1_calculated_restult_17` FOREIGN KEY (`calculated_result_id`) REFERENCES `calculated_results` (`calculated_result_id`),
CONSTRAINT `data1_ibfk_1` FOREIGN KEY (`associated_team_id`) REFERENCES `teams` (`team_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Answer 1

I don't think there is that specific metric. However, looking at

SHOW STATUS LIKE 'Innodb%';

Some interesting things:

Innodb_pages_written / Innodb_buffer_pool_write_requests --
    over, say, 15% is "too high"
Innodb_buffer_pool_pages_flushed / Uptime --
    I/O writes per second
Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests --
    "wait to get into buffer_pool" - don't want that to go over about 1%

To cut back on the physical I/O, be sure to have innodb_buffer_pool_size set to about 70% of available RAM.

If you have RAID or SSD, we should discuss further.

If you have less than 4GB of RAM, consider getting more.

Indexing UUIDs is evil, even when not the PRIMARY KEY.

Please provide SHOW CREATE TABLE; I want to see the row size and table size.

TEXT and BLOB can cause issues. Please provide SHOW CREATE TABLE.