but before I do that, I would like to know how long it will take.

I plan to use ALTER TABLE ... ENGINE=INNODB to do that job and
currently there are 10 millions records in this particular table that
I would like to convert.

Can anyone tell me how long will it take to convert a 10-million
records table from MyISAM to InnoDB? If it will take a long long time,
what methods are available for me to achieve this?

Thank you.

quakey@gmail.com wrote:
> but before I do that, I would like to know how long it will take.
>
> I plan to use ALTER TABLE ... ENGINE=INNODB to do that job and
> currently there are 10 millions records in this particular table that
> I would like to convert.
>
> Can anyone tell me how long will it take to convert a 10-million
> records table from MyISAM to InnoDB? If it will take a long long time,
> what methods are available for me to achieve this?
>
> Thank you.


mysql> describe a;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | varchar(10) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.55 sec)


mysql> create table aa (a varchar(10),b int, c int) engine=innodb;
Query OK, 0 rows affected, 1 warning (1.01 sec)

mysql> insert into aa select * from a;
Query OK, 5 rows affected (0.07 sec)
Records: 5 Duplicates: 0 Warnings: 0

or you can do it in a procedure where you commit periodically (a lot of
trouble)

I thought about that method but the problem is the table is being
updated (add/update/remove) constantly.

By the time table aa is populated, the data in there is already stale.

Thanks.

On Apr 3, 4:39pm, Michael Austin <maus...@firstdbasource.com> wrote:
> qua...@gmail.com wrote:
> > but before I do that, I would like to know how long it will take.
>
> > I plan to use ALTER TABLE ... ENGINE=INNODB to do that job and
> > currently there are 10 millions records in this particular table that
> > I would like to convert.
>
> > Can anyone tell me how long will it take to convert a 10-million
> > records table from MyISAM to InnoDB? If it will take a long long time,
> > what methods are available for me to achieve this?
>
> > Thank you.
>
> mysql> describe a;
> +-------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | a | varchar(10) | YES | | NULL | |
> | b | int(11) | YES | | NULL | |
> | c | int(11) | YES | | NULL | |
> +-------+-------------+------+-----+---------+-------+
> 3 rows in set (0.55 sec)
>
> mysql> create table aa (a varchar(10),b int, c int) engine=innodb;
> Query OK, 0 rows affected, 1 warning (1.01 sec)
>
> mysql> insert into aa select * from a;
> Query OK, 5 rows affected (0.07 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> or you can do it in a procedure where you commit periodically (a lot of
> trouble)

On Apr 3, 4:55 pm, "qua...@gmail.com" <qua...@gmail.com> wrote:
> I thought about that method but the problem is the table is being
> updated (add/update/remove) constantly.
>
> By the time table aa is populated, the data in there is already stale.
>
> Thanks.
>

I would have to say that something as fundamental as changing a table
from ISAM to INNODB should probably NOT be performed on a production
database DURING PRODUCTION.

That's like taking your car and swapping out a Volkswagon engine for a
Porsche engine -- on the freeway --

I'd suggest copying the table to separate table. Perform the
conversion on the copy and time it. Then schedule enough down-time to
perform the conversion with at least 10% padding.

If scheduling downtime for such maintenance is not possible -- I don't
know what else to say.

<:-(

quakey@gmail.com wrote:
> but before I do that, I would like to know how long it will take.
>
> I plan to use ALTER TABLE ... ENGINE=INNODB to do that job and
> currently there are 10 millions records in this particular table that
> I would like to convert.
>
> Can anyone tell me how long will it take to convert a 10-million
> records table from MyISAM to InnoDB? If it will take a long long time,
> what methods are available for me to achieve this?

I actually converted a 15 million record table from MyISAM
to InnoDB on a production server using ALTER TABLE. It took about five hours.
Fortunately, that particular table is only updated periodically, so
there was no problem with locking out updates.

John Nagle

I assume during the conversion with ALTER, all updates were locked
out??

On Apr 3, 11:38pm, John Nagle <na...@animats.com> wrote:
> qua...@gmail.com wrote:
> > but before I do that, I would like to know how long it will take.
>
> > I plan to use ALTER TABLE ... ENGINE=INNODB to do that job and
> > currently there are 10 millions records in this particular table that
> > I would like to convert.
>
> > Can anyone tell me how long will it take to convert a 10-million
> > records table from MyISAM to InnoDB? If it will take a long long time,
> > what methods are available for me to achieve this?
>
> I actually converted a 15 million record table from MyISAM
> to InnoDB on a production server using ALTER TABLE. It took about five hours.
> Fortunately, that particular table is only updated periodically, so
> there was no problem with locking out updates.
>
> John Nagle