List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 29 2010 3:17pm
Subject:Re: Migrating my mindset from MyISAM to InnoDB
View as plain text  
In the last episode (Sep 28), Gavin Towey said:
> Also note, 5.5 isn't production ready.  5.1 is the current GA release.

5.5 is really really close, though (5.5.6 is marked as Release Candidate),
Better to switch now while you're already doing a migration, and then
install 5.5.x updates as they happen.

> From: Hank [mailto:heskin@stripped]
>> Primarily due to many positive posts I've seen about MySQL 5.5 and
>> advances in InnoDB, I'm seriously considering converting all my MyISAM
>> databases to InnoDB.  I don't need many of the InnoDB features, but if
>> I'm going to upgrade from 4.1.14 to 5.5, I might as well bit the bullet
>> since that seems to be the direction of MySQL/Oracle.
>>
>> I very much like how verbose myisamchk is in detailing which index it
>> is currently rebuilding, and the progress in terms of records
>> re-indexed.
>>
>> SO, my questions are this:
>> 
>> 1. With InnoDB, do the indexes ever need to be rebuilt to reduce index
>> size and improve performance like I get with MyISAM?

All databases can benefit from occasional index rebuilds/optimizations to
recover slack space.  Once a system gets big enough, though, the downtime
required for the OPTIMIZE TABLE may outweigh the benefits.  Just add more
disk and RAM :)

>> 2. If so, are there any tools like myisamchk to monitor the InnoDB index
>> rebuild process, other than issuing a "repair table..." and staring
>> indefinitely at a blank screen until it finishes hours later?

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.

>> 3.  I've been testing the rebuild process during upgrading using "alter
>> table <table_name> engine=innodb" to convert my tables from 4.1.14 to
>> 5.5.6, and I'm seeing a 130% increase (more than double) in the raw disk
>> space required for the new InnoDB tables compared to their old MyISAM
>> counterparts.  (I am using single-file-per-table).  Is this normal?  If
>> not, how can I adjust the space requirements for these tables so they
>> don't take up so much additional space?

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.

-- 
	Dan Nelson
	dnelson@stripped
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