List:General Discussion« Previous MessageNext Message »
From:mos Date:February 8 2008 5:34am
Subject:Re: Very slow update
View as plain text  
Phil,
      What happens if you drop the index when you do the update? Does it 
run faster? If so then the index is slowing you down. Try increasing the 
key_buffer_size to 756M. If there isn't much difference, try the update 
without the "Order By" clause. If it runs considerably faster, then you 
need to increase your sort_buffer_size. See the link 
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html for more 
info on optimizing the sort.

BTW,  I would definitely change the VarChar(10) to Char(10) because fixed 
length records should be faster.

Mike

At 06:49 PM 2/7/2008, Phil wrote:
>I'm trying to write an update which generates ranking data for a table.
>
>Table is as follows
>
>CREATE TABLE `A` ( `id` INT NOT NULL ,
>`score` DOUBLE NOT NULL ,
>`projrank` INT NOT NULL ,
>`other` VARCHAR( 10 ) NOT NULL
>) ENGINE = MYISAM
>
>Real table actually contains 30 or so more fields but it gives a similar
>issue
>
>Score changes often, so 4 times per day I want to re-rank the data.
>
>Primary index is on score desc, id
>
>So I run the following
>
>set @rank:=0;
>update A set projrank=@rank:=rank+1 order by score desc,id
>
>For 20,000 rows the update takes 0.8 seconds



>For 50,000 rows it takes 1.9 seconds
>For 140,000 rows it takes ~ 5 seconds
>
>Scale up to 400,000 and it takes 7 minutes??
>
>I'm sure it's probably some setting in my.cnf but I've tweaking them to no
>avail. Currently I have them set as follows
>
>key_buffer_size=256M
>max_allowed_packet=16M
>thread_stack=128K
>thread_cache_size=8
>sort_buffer_size=48M
>join_buffer_size=3M
>read_buffer_size=4M
>query_cache_size=4M
>query_cache_limit=4M
>table_cache=100
>max_connections=20
>max_heap_table_size=64M
>myisam_sort_buffer_size=64M
>
>which is probably too high in places.
>
>Ideas? The full table actually has 1.7M rows in it which takes over an hour
>to update.. I've been puzzling on this for weeks now.
>
>Server is a 4core opteron 275 with 2Gb ram
>
>Thanks
Thread
Very slow updatePhil8 Feb
  • Re: Very slow updatemos8 Feb