MySQL Forums
Forum List  »  MyISAM

Slow UPDATE's on a large table
Posted by: Srdan Dukic
Date: June 24, 2010 10:11PM


I have a MyISAM table of approximately 13 million rows. This table contains rows which must be processed. Once a row is processed, its status is changed from "unprocessed" to "processed".

I've written a program to grab 1000 rows at a time, process them and then update the status of the rows to "processed".

My problem is that the program starts out fast, but the "update" part of the program slows down, at what seems, an exponential rate. This means that when I've left the program running for 5-6 hours, waiting for the "update" step to finish takes 99% of the programs running time, while at the start it's only about 20%. Consequently, the overall program running time increases as well.

How can I improve the update performance of this very large MyISAM table? I've tried using a 'LOCK TABLE aTable WRITE' command before the update, with a 'UNLOCK TABLES' after the update, but this resulted only in a marginal improvement in performance, with the time to update still increasing.

Are there any server variables which I can tweak?

Thank you

Options: ReplyQuote

Written By
Slow UPDATE's on a large table
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
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.