Index causing lock up to 5 minutes
Posted by: Paul Michaels
Date: October 08, 2019 07:47AM

Hi,

We currently have a fairly large database. We have noticed an issue with one of the smaller tables whereby 2 specific queries to this table are taking upwards of 3 minutes to execute. Sometimes, it reaches innodb_lock_wait_timeout which is currently set to 240. The table is a queue and can have 100 rows or 40000 rows, the delay will still be evident.

cpu is between 10% and 20% during high workloads. RAM is between 64% and 80%. Disk usage high - we are replacing our Dell H330 with a H730 which should improve disk capacity quite a bit.

The query that is taking long is:

UPDATE OutQueue SET ConfigID = 434 WHERE BindID = 327 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,434) OR ConfigID IS NULL) LIMIT 100

The table structure is:

| OutQueue | CREATE TABLE `OutQueue` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`DestinationAddr` varchar(20) NOT NULL,
`SourceAddr` varchar(23) DEFAULT NULL,
`Messagetext` varchar(1024) NOT NULL,
`MessageTypeID` int(11) NOT NULL DEFAULT 1,
`StatusID` int(11) NOT NULL DEFAULT 1,
`NotifyStatusID` int(11) NOT NULL DEFAULT 1,
`ReasonID` int(11) NOT NULL DEFAULT 0,
`AccountUserID` int(11) NOT NULL,
`Options` varchar(200) DEFAULT NULL,
`ValidityPeriod` varchar(20) DEFAULT NULL,
`UserReference` varchar(120) DEFAULT NULL,
`GatewayReference` varchar(64) DEFAULT NULL,
`BindID` int(11) NOT NULL,
`ConfigID` int(11) DEFAULT NULL,
`CreateTime` datetime NOT NULL,
`ScheduledTime` datetime NOT NULL,
`SubmitTime` datetime DEFAULT NULL,
`DeliverTime` datetime DEFAULT NULL,
`CompleteTime` datetime DEFAULT NULL,
`ExpireTime` datetime DEFAULT NULL,
`Priority` int(11) NOT NULL DEFAULT 0,
`Cost` double NOT NULL DEFAULT 0,
`ErrorCount` int(11) NOT NULL DEFAULT 0,
`PortabilityLookup` bit(1) NOT NULL DEFAULT b'0',
`SourceID` int(11) DEFAULT NULL,
`ServiceType` varchar(20) DEFAULT NULL,
`NotifiedTime` datetime DEFAULT NULL,
`RequestedSourceAddr` varchar(23) DEFAULT NULL,
`ReplyDestination` varchar(200) DEFAULT NULL,
`ReplyTypeID` int(11) DEFAULT NULL,
`NotifyTypeID` int(11) DEFAULT NULL,
`NotifyDestination` varchar(200) DEFAULT NULL,
`MessageState` int(11) DEFAULT NULL,
`MobileNetworkID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `idx_OutQueue_2` (`StatusID`,`GatewayReference`),
KEY `idx_OutQueue_3` (`GatewayReference`,`DestinationAddr`,`SourceAddr`),
KEY `idxBindAllocateWork` (`BindID`,`ScheduledTime`,`StatusID`,`ConfigID`,`Priority`)
) ENGINE=InnoDB AUTO_INCREMENT=116576286 DEFAULT CHARSET=latin1 |


Below is the output of show engine innodb status, which appears to indicate that the cause of the delay is a lock on the "OutQueue_2" index:

UPDATE OutQueue SET ConfigID = 434 WHERE BindID = 327 AND ScheduledTime < now() AND StatusID = 1 AND (ConfigID IN (0,434) OR ConfigID IS NULL) LIMIT 100
------- TRX HAS BEEN WAITING 71 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 910 page no 289 n bits 744 index idx_OutQueue_2 of table `sms`.`OutQueue` trx id 426470182 lock_mode X


We can receive 10-20 of the above update queries in a second. Then nothing for a while. So, we receive them in batches.

Any help / suggestions to improve our situation is much appreciated.

Options: ReplyQuote


Subject
Written By
Posted
Index causing lock up to 5 minutes
October 08, 2019 07:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.