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. =A0 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. =A0I've been testing the rebuild process during upgrading using
"alter table engine=3Dinnodb" 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). =A0Is
this normal? =A0If 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Migrating my mindset from MyISAM to InnoDB

1. Generally reducing fragmentation in the data/index files will reduce the=
footprint of tables on disk, and can be more efficient to query. With inn=
odb you need to be using the innodb-file-per-table option, and then you can=
use OPTIMIZE TABLE table; to rebuild it. You don't get detailed progress =
like with myisamchk, but that's not important anyway. You can estimate how=
long it will take by keeping track of how long any given ALTER / OPTIMIZE =
takes in GB/hr.

2. Don't stare at the screen. Start it, script the process & have it emai=
l your phone when it's done. Do something else in the mean time.

3. Yes, innodb table will take more space on disk. If you have a really l=
ong primary key, and lots of secondary indexes, then it can take a *lot* mo=
re. Disk is cheap, don't worry about it.

Regards,
Gavin Towey

-----Original Message-----
From: Hank [mailto:heskin@gmail.com]
Sent: Tuesday, September 28, 2010 3:29 PM
To: mysql@lists.mysql.com
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 engine=3Dinnodb" 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=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Migrating my mindset from MyISAM to InnoDB

Also note, 5.5 isn't production ready. 5.1 is the current GA release.


-----Original Message-----
From: Hank [mailto:heskin@gmail.com]
Sent: Tuesday, September 28, 2010 3:29 PM
To: mysql@lists.mysql.com
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 engine=3Dinnodb" 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=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Migrating my mindset from MyISAM to InnoDB

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@gmail.com]
>> 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 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@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Migrating my mindset from MyISAM to InnoDB

> 2. =A0Don't stare at the screen. =A0Start it, script the process & have i=
t email your phone when it's done. =A0Do 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=3D26182 inclu=
ded
>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 convertin=
g
>from myisam to innodb, depending on your field types and indexes. It's th=
e
>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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg