PK和MySQL中另一个字段的表更新偶尔会变慢 [英] Table update on PK and another field in MySQL is sporadically slow

问题描述

这是一个有趣的情况，其中MySQL中的UPDATE偶尔会变慢.背景:48GB Innodb缓冲区高速缓存，512MB ib日志.具有40mln行的Innodb表.结构和索引:

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';

请注意where子句中的PK和附加字段作为条件.此更新执行〜20次/秒.在大多数情况下，此更新是瞬时运行的，但一天几次，则需要100-300秒才能完成，并且显示的日志很慢.发生这种行为的原因是什么?

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?

更新#1::排除检查点，触发器和查询缓存是可能的根本原因. events_stages_history_long在其中一项更新中显示了这一点:

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

类似的问题(但不完全是我的情况): MySQL更新时间过长(

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

更新#2:就我而言，缓慢的更新始终与互斥锁争用的峰值相关.看来这是根本原因.

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.

这篇关于PK和MySQL中另一个字段的表更新偶尔会变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！