0

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
  • 1
    I have since found the index_page_splits metric on INFORMATION_SCHEMA.INNODB_METRICS which may be what I am looking for. In my testing, in-order inserts into a table with an integer auto_increment key generate ~40% fewer page splits than random inserts into a table with an integer PK. Other metrics such as index_page_merge_attempts may also be relevant but I've yet to test them thoroughly. – Dan Sep 27 '16 at 3:27
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.