Sample Header Ad - 728x90

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

0 votes
1 answer
472 views
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
Asked by Dan (545 rep)
Sep 19, 2016, 03:20 PM
Last activity: Sep 27, 2016, 03:24 AM