List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:February 20 2008 9:59pm
Subject:Re: How bad is adding BLOB column and index at the same time?
View as plain text  
The only way you can do it is with KILL.  You will be able to see some
of the progress on rollback in SHOW INNODB STATUS.  I recommend
innotop for watching this if you wish.

If you see the process's status as Killed in SHOW PROCESSLIST, you
have killed it.

Have a copy of War and Peace at the ready :-)

On Wed, Feb 20, 2008 at 1:37 PM, Mike Spreitzer <mspreitz@stripped> wrote:
> I am feeling more motivated to interrupt this operation now.  What would
>  be an effective way to do that?  As a reminder, I submitted this operation
>  using the GUI administration tool --- specifically the table editor.  The
>  whole tool is unresponsive while this operation is running.  I can run and
>  use another instance of the GUI administration tool.
>
>  If I succeed in interrupting this operation, presumably the rollback will
>  take quite a while.  How can I tell whether I have successfully
>  interrupted the operation and the rollback is what's nailing my machine?
>
>  Thanks,
>  Mike Spreitzer
>  SMTP: mspreitz@stripped, Lotus Notes: Mike Spreitzer/Watson/IBM
>  Office phone: +1-914-784-6424 (IBM T/L 863-)
>  AOL Instant Messaging: M1k3Sprtzr
>
>
>
>  Mike Spreitzer/Watson/IBM@IBMUS
>  02/19/08 08:34 PM
>
>  To
>  "Baron Schwartz" <baron@stripped>
>  cc
>  mysql@stripped
>  Subject
>  Re: How bad is adding BLOB column and index at the same time?
>
>
>
>
>
>
>  Thanks, Baron.  Yes, the table is bigger than memory.  It took about 2.5
>  days to create the table, inserting about 7,000 rows at a time; this
>  column and index addition has been running for about a day now.  I notice
>  you did not say it was terribly stupid to create this index before putting
>
>  the final data in the new column.  So I infer there is no big motivation
>  to interrupt the operation I have going.
>
>  Thanks,
>  Mike
>
>
>
>
>  "Baron Schwartz" <baron@stripped>
>  Sent by: baron.schwartz@stripped
>  02/19/08 08:13 PM
>
>  To
>  Mike Spreitzer/Watson/IBM@IBMUS
>  cc
>  mysql@stripped
>  Subject
>  Re: How bad is adding BLOB column and index at the same time?
>
>
>
>
>
>
>  Hi,
>
>  On Feb 19, 2008 5:20 PM, Mike Spreitzer <mspreitz@stripped> wrote:
>  > I am new to MySQL, and wonder if I have done something terribly stupid.
>  I
>  > have an InnoDB table with 27 million rows.  Without thinking very much,
>  I
>  > issued the following command through the GUI administration tool:
>  >
>  > ALTER TABLE `wyky`.`externallinks` ADD COLUMN `el_p2` BLOB  NOT NULL
>  AFTER
>  > `el_index`,
>  >  ADD INDEX `el_p2`(`el_p2`(150))
>  >
>  > Of course, all the values in this column will be the same.  The index
>  > will, if this ever completes, indicate that one value is associated with
>  > all 27E6 rows.  My primary question is, will this take O(27E6) time or
>  > something worse (e.g., O(27E6 squared) time)?
>  >
>  > Of course my plan is to eventually put some interesting data in that new
>  > column.  I do not really need the index until the data is there.
>  >
>  > This database is not being used on-line, this is just for study, so I do
>  > not mind large batch operations.  I just don't want to be grossly stupid
>  > in my choice of batch operations.
>  >
>  > My second question is: if I have indeed done something grossly stupid,
>  > what is the best (if there is any at all!) way to interrupt it and
>  > proceed.
>
>  If the table is bigger than memory, building the index will be very slow.
>
>  If you interrupt it, it's just going to roll back everything it's done
>  so far.  So you have the choice of either letting it finish and then
>  dropping the index, or killing it and letting it roll back.  (You
>  can't prevent the roll back, even if you restart).
>
>
>
Thread
How bad is adding BLOB column and index at the same time?Mike Spreitzer19 Feb
  • Re: How bad is adding BLOB column and index at the same time?Baron Schwartz20 Feb
    • Re: How bad is adding BLOB column and index at the same time?Mike Spreitzer20 Feb
      • Re: How bad is adding BLOB column and index at the same time?Mike Spreitzer20 Feb
        • Re: How bad is adding BLOB column and index at the same time?Baron Schwartz20 Feb