MySQL Forums
Forum List  »  MyISAM

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

Hi,

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


Subject
Views
Written By
Posted
Slow UPDATE's on a large table
14748
June 24, 2010 10:11PM
6260
June 27, 2010 02:57PM
5432
June 27, 2010 09:16PM
5598
June 27, 2010 09:47PM
4233
June 27, 2010 10:36PM
4150
June 27, 2010 11:52PM
3894
June 28, 2010 08:38PM
4031
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.