Index causing lock up to 5 minutes
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.