List:General Discussion« Previous MessageNext Message »
From:matt_lists Date:August 23 2006 2:23pm
Subject:Re: replace delayed not working
View as plain text  
chris smith wrote:
> On 8/23/06, matt_lists <matt_lists@stripped> wrote:
>> Not sure if this is a bug
>>
>> we are updating a large table with replace delayed from our feeder
>> systems, 1000 records at a time using limit's on the input data pull
>>
>>
>> this works great when there's fast selects from the table, while the
>> insert is going
>>
>>
>> when a site does a large pull, which can take 1 minute due to the size,
>> the replace is locked, I expected this, however, users doing small pulls
>> after the replace also get locked
>>
>> so I end up with 1 select running, 1 locked replace, and 50 locked
>> queries waiting behind the replace, everything backs up behind the
>> select that's running
>>
>>
>> is this normal?  We did not see this until 5.0.24, we were using 5.0.19
>> and load data infile all at once, with no locking issues, we put 5.0.24
>> on, and load data infile stopped working so we switched to the replace
>> delayed loop
>>
>>
>> the replace is fast, 1000 records takes 2 seconds, but the locking of
>> other users is causing huge problems
>
> It's not a bug. I guess this is a myisam table?
>
> http://dev.mysql.com/doc/refman/5.1/en/table-locking.html
>
> Any time the table is changed with myisam, the whole table is locked.
> That's the way it works.
>
> Innodb doesn't do it this way but it might cause other issues for you.
> Try changing the table type on a test system and see if it
> helps/hinders your problem(s).
This is my load data infile line

load data concurrent infile 'blah' ignore into table blahhhh

zero locking with 5.0.19, but with 5.0.24 it locked all the users out, 
pretty much all day as these go on 24/7

now I figured replace delayed ignore would fix it, but nope, same problem

for now we are back on 5.0.19 using load data concurrent without problems


Thread
replace delayed not workingmatt_lists23 Aug
  • Re: replace delayed not workingchris smith23 Aug
    • Re: replace delayed not workingmatt_lists23 Aug
      • Re: replace delayed not workingChris24 Aug
        • Re: replace delayed not workingmatt_lists24 Aug
    • Re: replace delayed not workingmatt_lists23 Aug