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=arch...@jab.org
