I have a large table (2.3TB of data, 1.9TB index, 33 billion rows). A user recently executed a DELETE statement (32k rows) against the table, which caused the server to eat up all memory and then fail over. The DELETE never finished. Converting the DELETE to SELECT ran in 30 seconds with no adverse effects.
Here's the table schema:
CREATE TABLE `bigtable` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`business_unit_id` tinyint(3) unsigned NOT NULL,
`visitor_id` bigint(20) unsigned NOT NULL,
`visit_id` bigint(20) unsigned DEFAULT NULL,
`keyword_id` int(10) unsigned DEFAULT NULL,
`publisher_id` mediumint(8) unsigned DEFAULT NULL,
`placement_id` bigint(20) DEFAULT NULL,
`creative_id` mediumint(8) unsigned DEFAULT NULL,
`unknown_referer_id` int(10) unsigned DEFAULT NULL,
`insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`insert_time`),
KEY `ix_bigtable_visitor_id` (`business_unit_id`,`visitor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=198936326312 DEFAULT CHARSET=utf8
There are no triggers or foreign keys. Binlog format is row, and it is being replicated to another Aurora cluster using standard replication.
The delete was of the form:
delete from `bigtable` where id in (large list of values)
What would cause this behavior?
Note: I'm not looking for advice in tuning the table or query. I'm just trying to find out what's happening.
