Found the problem :) It ended up being a problem with the preparation of statements. i.e. the code I mentioned before wasn't complete, it was missing a while loop:
while (1){
...
$dbh->do('LOCK TABLE BaseMessage WRITE');
$query = "UPDATE BaseMessage SET statusId = 3 WHERE baseMessageId= ?";
$updateSth = $dbh->prepare($query) or die "Prepare failed. ERROR: $DBI::errstr\n";
# Update the staus of the records to 'Processed'
foreach $messageId (@processedMessages) {
$updateSth->execute($messageId) or die "Unable to execute query: $query. ERROR: $DBI::errstr\n";
}
$updateSth->finish();
$dbh->do('UNLOCK TABLES');
...
}
The extra while loop meant that it was calling the $dbh->prepare(...) statement every time through the loop. Moving the call to the 'prepare' function outside of this loop stopped the increasing time to run the updates. The new code is as follows:
...
$updateSth = $dbh->prepare("UPDATE BaseMessage SET statusId = 3 WHERE baseMessageId IN (" . '?,' x ($numRows - 1). "?)") or die "Prepare failed. ERROR: $DBI::errstr\n";
...
while(1){
...
$updateSth->execute(@processedMessages) or die "Unable to execute query: $query. ERROR: $DBI::errstr\n";
...
}
I figured this out after running iostat and finding that the disk was seeing barely any write throughput.