List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:February 19 2009 7:14pm
Subject:InnoDB - CREATE INDEX - Locks table for too long
View as plain text  
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?

I have created smaller indexes and brought the average rows retrieved 
via full table scan from 2 Million to 400.000 per second, now I just 
need this last index!
Thanks to Baron for the slow-query-log analyzer tool, it is simple but 
perfect!
And I used it without patching the server.
I have easily found the worst queries just watching three values: 
elapsed time, number of occurences, and rows retrieved.
And I discovered all the tables which needed indexing, so simple, so 
good! Thanks Baron!

Cheers

Claudio


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