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.na...@gmail.com> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
