We recently upgraded to MariaDB 10.3. All of our tables use InnoDB.
Occasionally we have a process that may take a while inserting records into a table with about 200 million records. Here’s the export for it:
CREATE TABLE `assigned_dates` (
`id` int(10) UNSIGNED NOT NULL,
`uid` int(11) UNSIGNED NOT NULL,
`cid` int(11) UNSIGNED NOT NULL,
`assign_date` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `assigned_dates`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `cid_uid` (`cid`,`uid`),
ADD KEY `cid` (`cid`),
ADD KEY `uid` (`uid`);
ALTER TABLE `assigned_dates`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `assigned_dates`
ADD CONSTRAINT `assigned_dates_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `assigned_dates_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `content` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
We have a process that inserts records into this table using INSERT IGNORE, which may run batches trying to insert a few thousand records at a time, ignoring them if they already exist. While this process runs, I see other database processes that are paused, apparently waiting for this process to finish first. If I use SHOW PROCESSLIST, the status of all of the other queries that are waiting is “Updating.” This is the export for one table where the insert queries are typically waiting for the above process to finish:
CREATE TABLE `oauth_access_tokens` (
`access_token` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
`client_id` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
`user_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`expires` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`scope` varchar(2000) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `oauth_access_tokens`
ADD PRIMARY KEY (`access_token`),
ADD KEY `user_id` (`user_id`);
ALTER TABLE `oauth_access_tokens`
ADD CONSTRAINT `oauth_access_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE;
These tables really have nothing in common other than they both have a foreign key to the users table (but not even the same column).
How can I figure out why hundreds of other database processes are waiting for the INSERT IGNORE into assigned_dates to finish before they finish? This wasn’t a problem in MySQL 5.6, which we upgraded from, obviously a lot of changes have been made since then though.