List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:February 21 2009 9:22am
Subject:Re: InnoDB - CREATE INDEX - Locks table for too long
View as plain text  
Hi Baron!

I am going to try your solution on preprod on monday.

In the meantime, using your great slow-query-log analyzer, the strategy 
I thought of was similar to yours,
but using only one select that only put a READ lock on the records 
because, while the table is very 'selected' also at night,
I have only one insert and two updates in one week, the idea then is to 
do exactly what you said but with single insert with a READ lock.

So what I have tried on preprod is:

create table new_table like old_table;
alter table new_table add key(.....);
insert into new_table select * from old_table FOR UPDATE

the strange thing is that while I used (mistake) 'FOR UPDATE' instead of 'LOCK IN SHARE
MODE'
having AUTOCOMMIT=1 behaved like the 'LOCK IN SHARE MODE', is it possible?
In fact , during the 1 hour insert I could read records from old_table, insert new records
in old_table, but updates where queued. 

In my case, if I do this between 2:00am and 4:00am , I can at first try the single insert
READ locked,
and if I have problems I will switch to your solution, What do you think?


Thanks!

Claudio








Baron Schwartz wrote:
> Hi!
>
> On Thu, Feb 19, 2009 at 2:14 PM, Claudio Nanni <claudio.nanni@stripped>
> wrote:
>   
>> I need to add an index on a table on a production server.
>> It is one 7Gb InnoDB table with single .ibd file (one_file_per_table),
>> the index creation on preprod server took 40 minutes but table was smaller.
>> I tried to add the index but was locking all applications on production and
>> had to kill it.
>> I have requested a maintenance window but it will take long time.
>> Since this application is scanning like crazy I'd like to do it a.s.a.p.
>>
>> Do you have any hint for a non locking solution?
>>     
>
> Try something like this:
>
> create table new_table like old_table;
> alter table new_table add key(.....);
> insert into new_table select * from old_table where <primary_key>
> between ? and ?;
>
> Repeat the last statement until nearly finished, then:
>
> lock tables new_table write, old_table write;
> insert into new_table select * from old_table where <primary_key> > ?;
> rename table old_table backup, new_table old_table;
> unlock tables;
>
> Baron
>
>   

Thread
Left join does not work with Count() as expectedArtem Kuchin19 Feb
  • Re: Left join does not work with Count() as expectedOlaf Stein19 Feb
    • InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni19 Feb
      • Re: InnoDB - CREATE INDEX - Locks table for too longBaron Schwartz20 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni21 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni16 Mar
  • Re: Left join does not work with Count() as expectedPeter Brawley19 Feb
  • RE: Left join does not work with Count() as expectedMartin Gainty19 Feb