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
>
>
