# Batch deletes from big table causing wsrep system lock

**URL:** <https://forums.percona.com/t/batch-deletes-from-big-table-causing-wsrep-system-lock/5910>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [October 3, 2017, 3:06am UTC](https://forums.percona.com/t/batch-deletes-from-big-table-causing-wsrep-system-lock/5910 "2017-10-03T03:06:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![raghupradeep](https://avatars.discourse-cdn.com/v4/letter/r/c5a1d2/32.png) [@raghupradeep](https://forums.percona.com/u/raghupradeep)\
**Post date:** [October 3, 2017, 3:06am UTC](https://forums.percona.com/t/batch-deletes-from-big-table-causing-wsrep-system-lock/5910/1 "2017-10-03T03:06:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![DBennett](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/dbennett/32/17_2.png) [@DBennett](https://forums.percona.com/u/DBennett)\
**Post date:** [October 3, 2017, 12:08pm UTC](https://forums.percona.com/t/batch-deletes-from-big-table-causing-wsrep-system-lock/5910/2 "2017-10-03T12:08:31Z")

</div>

Hi Raghu,

There is a good [blog article about this warning](https://www.percona.com/blog/2016/05/27/galera-error-failed-to-report-last-committed-interrupted-system-call/). The warning itself is just a notification that the thread reporting the last committed transaction was interrupted. The server node will retry automatically.

Regarding the wait, there are a few things you might want to try to optimize your SP. First, I believe the range lookup on passageTime is not using a good index. EXPLAIN will help point you in the right direction, ANALYZE TABLE may help. You might also try reducing your batch size. For further assistance in optimizing your application for PXC, please [contact support](https://www.percona.com/services/support/mysql-ha-cluster-support).

–Dave

---

<div class="post-metadata">

**Author:** ![raghupradeep](https://avatars.discourse-cdn.com/v4/letter/r/c5a1d2/32.png) [@raghupradeep](https://forums.percona.com/u/raghupradeep)\
**Post date:** [October 6, 2017, 2:30am UTC](https://forums.percona.com/t/batch-deletes-from-big-table-causing-wsrep-system-lock/5910/3 "2017-10-06T02:30:06Z")

</div>

Hi Dave,

Thank you for the reply.

I checked the SP’s and it is using the best index for the delete operation based on the explain plan. Moreover, I did reduce the batch size to 5000 and introduced sleep in the SP so that the delete process is not continuous. The single call of this SP will delete records in the range of 2 million in batches. Until I introduced sleep in the SP, the delete process was causing continuous system lock on the other nodes holding all transaction in it until the SP gets executed. With sleep I was able to introduce a small gap so that the other nodes can catch up or can process the DML’s it receive.

What amuse me is why a small batch delete is causing system lock on other nodes. Is this an intended behaviour of Galera replication ? This system lock does not happen for batch inserts. Is this a bug ? Just curious.

Regarding support, we are still evaluating PXC and the servers are pre-prod. So, we have not decided if PXC is the best solution for us.

It will be of great help if you can share if this behaviour (system lock) is an intended one or not.

With Regards,  
Raghu