MySQL Forums
Forum List  »  MyISAM

Re: Slow UPDATE's on a large table
Posted by: Srdan Dukic
Date: July 04, 2010 08:28PM

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";
$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";
$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.

Options: ReplyQuote

Written By
June 24, 2010 10:11PM
June 27, 2010 02:57PM
June 27, 2010 09:16PM
June 27, 2010 09:47PM
June 27, 2010 10:36PM
June 27, 2010 11:52PM
June 28, 2010 08:38PM
Re: Slow UPDATE's on a large table
July 04, 2010 08:28PM

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.