List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:September 28 2010 11:16pm
Subject:RE: Migrating my mindset from MyISAM to InnoDB
View as plain text  
Also note, 5.5 isn't production ready.  5.1 is the current GA release.


-----Original Message-----
From: Hank [mailto:heskin@stripped]
Sent: Tuesday, September 28, 2010 3:29 PM
To: mysql@stripped
Subject: Migrating my mindset from MyISAM to InnoDB

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've been using MySQL 4.1.14 for years in my production environment,
including one master and several slaves for report and long running
queries.

Every 6 to 12 months the master MYI index files grow fairly large, so
I take the production database offline, and run myisamchk -r on the
index files to rebuild them and shrink them back down again. I usually
get a 20% to 30% space saving and improved performance after the
rebuilds. This has worked very well for me for, well, almost 10 years
now!

And when I say "large" my two main tables have about 200 million rows,
and the myisamchk can take between 60-160 minutes to complete.

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?
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?
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?

I'm sure I'll have more questions later, but many thanks for your
comments and thoughts.

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


This message contains confidential information and is intended only for the individual
named.  If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. 
Please notify the sender immediately by e-mail if you have received this e-mail by
mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not
accept liability for any loss or damage caused by viruses or errors or omissions in the
contents of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
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