List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 29 2010 8:38pm
Subject:Re: Migrating my mindset from MyISAM to InnoDB
View as plain text  
> 2.  Don't stare at the screen.  Start it, script the process & have it
> email your phone when it's done.  Do something else in the mean time.

I don't literally stare at the screen -- of course I script it and do
other things.. but when I have a resource limited environment, it sure
would be nice to have *some idea* of the progress of the rebuild.  By
staring at the blank screen, I really meant to say that there is
absolutely no feedback at all during the process, to get even any idea
of how far it has completed and how far it has to go.

From my initial tests at rebuilding a 5.6 million record table (4.75
hours), trying to rebuild a 200 million record table would take more
than 7 days. And I have two of those tables to rebuild.  I can
accomplish the same myISAM rebuild in two hours.

>Unfortunately, no.  MySQL threads should really make periodic updates to
>their status so you can see the progress of long-running queries in the
>"show processlist" output.  http://bugs.mysql.com/bug.php?id=26182 included
>a patch that adds progress updates to select statements, so it should be
>possible to do the same for ALTER TABLEs as well.

Wow, that sure would be nice... even with some extended information
like myisamchk output. That would be an awesome feature to add to 5.5.

>Expect to see anywhere from a 1.5x to a 3x increase in size when converting
>from myisam to innodb, depending on your field types and indexes.  It's the
>penalty you pay for supporting transactions and concurrent read/write
>access, and for switching to an index-organized table.

Now that you put it that way, I'm thinking of just sticking with
myisam.  I can't spend two weeks upgrading the two 200 million row
tables.

Thanks for all your comments.
Thread
Migrating my mindset from MyISAM to InnoDBHank29 Sep
  • RE: Migrating my mindset from MyISAM to InnoDBGavin Towey29 Sep
    • Re: Migrating my mindset from MyISAM to InnoDBHank29 Sep
  • RE: Migrating my mindset from MyISAM to InnoDBGavin Towey29 Sep
    • Re: Migrating my mindset from MyISAM to InnoDBDan Nelson29 Sep