mysql 字段 pk,PK和MySQL中另一个字段的表更新偶尔会变慢

This is interesting case where UPDATE in MySQL is sporadically slow. Background: 48GB Innodb buffer cache, 512MB ib logs. Innodb table with 40mln rows. Structure and indexes:

CREATE TABLE `VisitorCompetition` (

`VisitorCompetitionId` bigint(20) NOT NULL AUTO_INCREMENT,

`UserId` bigint(20) NOT NULL,

`CompetitionInstanceId` bigint(20) NOT NULL,

`Score` bigint(20) NOT NULL DEFAULT '0',

`Visits` bigint(20) DEFAULT NULL,

`Status` varchar(255) NOT NULL,

`RankAtCompletion` int(11) DEFAULT NULL,

`SessionId` varchar(36) DEFAULT NULL,

`SharedDate` timestamp NULL DEFAULT NULL,

`CreatedDate` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

`LastModifiedDate` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

`ModifiedBy` varchar(55) DEFAULT NULL,

`CaseId` int(11) NOT NULL,

PRIMARY KEY (`VisitorCompetitionId`),

UNIQUE KEY `uc_UserId_CompetitionInstanceId` (`UserId`,`CompetitionInstanceId`),

KEY `idx_VisitorCompetition_TI_S` (`CompetitionInstanceId`,`Status`),

KEY `IDX_CreatedDate` (`CreatedDate`),

CONSTRAINT `fk1` FOREIGN KEY (`CompetitionInstanceId`)

REFERENCES `CompetitionInstance` (`CompetitionInstanceId`)

) ENGINE=InnoDB AUTO_INCREMENT=74011154 DEFAULT CHARSET=utf8

When there is an update which looks like this:

update VisitorCompetition

set

Status='CLOSED',

score=770000,

visits=null,

RankAtCompletion=null,

sharedDate=null,

LastModifiedDate=current_timestamp(6),

ModifiedBy='11.12.12.200'

where VisitorCompetitionId=99999965 and Status = 'CLOSED';

Note PK in where clause and additional field as a condition. This update executes ~20 times/sec. On most cases this update runs instantaneously but few times a day it takes 100-300 seconds to complete and it shows up is slow log. What would be a reason for this behavior?

Update #1: Ruled out checkpointing, trigger and query cache as a possible root causes. events_stages_history_long shows this for one of the updates:

stage/sql/updating 188.025130

stage/sql/end 0.000004

stage/sql/query end 0.000002

stage/sql/closing tables 0.000004

stage/sql/freeing items 0.000002

stage/sql/logging slow query 0.000032

stage/sql/cleaning up 0.000001

Similar problem (but not exactly my case): MySQL update taking(too) long time

Update #2: The slow UPDATES in my case always correlate with spikes in mutex contention. Seems like this is root cause.

解决方案

While there can be many reasons for this, I want to mention what was the root cause in my case. It was an application bug where many hundreds of application sessions were trying to update the same rows, causing lock escalation, mutex contention and as a result slow execution. After our Dev team fixed the code, this issue was instantly resolved. Thanks all.