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
Last activity: Sep 27, 2016, 03:24 AM