On our production server we have a big table which runs into 450 Gb. The table is partitioned based on time into monthly partitions.
The delete is performed using a stored procedure. When we execute the SP we are getting following warning in server logs on all the nodes and at the same time all other transactions are waiting for this SP to complete.
2017-10-03 09:43:10 12355 [Warning] WSREP: Failed to report last committed 33482996, -4 (Interrupted system call)
2017-10-03 09:43:12 12355 [Warning] WSREP: Failed to report last committed 33482997, -4 (Interrupted system call)
2017-10-03 09:43:20 12355 [Warning] WSREP: Failed to report last committed 33482999, -4 (Interrupted system call)
2017-10-03 09:43:33 12355 [Warning] WSREP: Failed to report last committed 33483002, -4 (Interrupted system call)
2017-10-03 09:43:50 12355 [Warning] WSREP: Failed to report last committed 33483007, -4 (Interrupted system call)
2017-10-03 09:44:25 12355 [Warning] WSREP: Failed to report last committed 33483018, -4 (Interrupted system call)
The delete is performed in a while loop and each batch is limited to 10K rows.
Below is the SP, used for the delete operation.
CREATE spc_Util_DeleteTsPassages(p_tsId INT, p_startDate DATE, p_endDate DATE)
BEGIN
tspassages_Block:BEGIN
DECLARE v_tsId, v_deleteStartDate, v_deleteEndDate INT;
DECLARE v_Process VARCHAR(256);
DECLARE v_count BIGINT;
SET v_count = 0;
SET v_tsId = p_tsId;
SET v_Process = ‘tspassages - Data Deletion’;
SET v_deleteStartDate = UNIX_TIMESTAMP(p_startDate);
SET v_deleteEndDate = UNIX_TIMESTAMP(p_endDate);
– select v_tsId, v_deleteStartDate, v_deleteEndDate;
INSERT INTO dataDeleteAuditLogs(PROCESS,LogDate,Descriptions)
VALUES(v_Process,NOW(),CONCAT('Deletion started for tsId : ', v_tsId, '; FromDate : ',p_startDate, ’ ToDate : ', p_endDate));
WHILE EXISTS(SELECT passageTime FROM tspassages WHERE passageTime >= v_deleteStartDate AND passageTime < v_deleteEndDate AND tsId = v_tsId LIMIT 1)
DO
– Deleting from tspassages
DELETE FROM tspassages
WHERE passageTime >= v_deleteStartDate AND passageTime < v_deleteEndDate AND tsId = v_tsId
LIMIT 10000;
SET v_count = v_count + ROW_COUNT();
END WHILE;
INSERT INTO dataDeleteAuditLogs(PROCESS,LogDate,Descriptions)
VALUES(‘tspassages - Data Deletion’,NOW(),CONCAT('Deletion process completed for tsId : ', v_tsId, '; Total records deleted is : ',v_count));
END;
END$$
DELIMITER ;
Table definition
CREATE TABLE tspassages (
rowId bigint(20) unsigned NOT NULL AUTO_INCREMENT,
tsId mediumint(8) unsigned NOT NULL,
camId tinyint(3) unsigned NOT NULL,
passageTime int(11) unsigned NOT NULL,
passageMs smallint(3) unsigned NOT NULL,
passageId smallint(5) unsigned NOT NULL,
lP varchar(15) NOT NULL,
isCalib tinyint(1) unsigned NOT NULL,
Confidence smallint(5) unsigned NOT NULL,
RDWType tinyint(2) unsigned NOT NULL,
PRIMARY KEY (rowId,passageTime),
UNIQUE KEY uidx_tspassages (tsId,camId,passageTime,passageMs),
KEY Idx_tspassage_passagetime (passageTime),
KEY idx_tspassage_tsId (tsId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (PassageTime)
(PARTITION 2017JAN VALUES LESS THAN (1485903600) ENGINE = InnoDB,
PARTITION 2017FEB VALUES LESS THAN (1488322800) ENGINE = InnoDB,
PARTITION 2017MAR VALUES LESS THAN (1490997600) ENGINE = InnoDB,
PARTITION 2017APR VALUES LESS THAN (1493589600) ENGINE = InnoDB,
PARTITION 2017MAY VALUES LESS THAN (1496268000) ENGINE = InnoDB,
PARTITION 2017JUN VALUES LESS THAN (1498860000) ENGINE = InnoDB,
PARTITION 2017JUL VALUES LESS THAN (1501538400) ENGINE = InnoDB,
PARTITION 2017AUG VALUES LESS THAN (1504216800) ENGINE = InnoDB,
PARTITION 2017SEP VALUES LESS THAN (1506808800) ENGINE = InnoDB,
PARTITION 2017OCT VALUES LESS THAN (1509490800) ENGINE = InnoDB,
PARTITION 2017NOV VALUES LESS THAN (1512082800) ENGINE = InnoDB,
PARTITION 2017DEC VALUES LESS THAN (1514761200) ENGINE = InnoDB)
Our Prod setup is of 3 nodes running on Server version: 5.6.35-80.0-56-log.
Why am I getting a WSREP system lock for a batch delete operation and how can I mitigate this issue ?
With Regards,
Raghupradeep