Ilya,
ALTER TABLE should be faster: InnoDB does not have special optimization
for separate index creation. Therefore the fastest way is to do the inserts
directly to an InnoDB table.
You should set the InnoDB buffer pool size big during the
transition, to reduce disk i/o. Not bigger than 80 % of the physical
memory, though. You should set InnoDB log files big, as described in the
manual at http://www.innobase.fi, and also the log buffer to 8 MB,
for example.
Make sure you do not run out of tablespace: InnoDB tables take a lot
more space than MyISAM tables. If an ALTER TABLE runs out of space,
it will start a rollback, and that can take hours if it is disk bound.
In that case it is better that you kill the database process and
delete all InnoDB files and all InnoDB table .frm files, and start
your job again, rather than wait millions of disk i/os to complete.
Regards,
Heikki Tuuri
Innobase Oy
>Hi,
>
>We recently decided to start switching on InnoDB for some our web
>projects. We have tested InnoDB on test machines and have been
>satisifed with results. Now the question: how can we minimize downtime
>on production server during transition?
>
>We see two choises:
>
>1) change mysql server to "single-user" mode and change types of
> tables with 'alter table TABLE name=InnoDB', start mysql server to
> normal mode again
>
>2) change mysql server to "single-user" mode and dump tables with
> mysqldump --quick, create new tables of InnoDB type without
> indexes, restore their content from dump, restore indexes, start
> mysql server to normal mode again.
>
>We can afford several hours of downtime so we don't want to bother
>with too complex solutions for this transitions like setting up
>replication server and so on.
>
>Some info on tables: most of them are quite small except one which has
>about 1,800,000 records (size of MyISAM files about 70Mb).
>
>--
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>| Ilya Martynov (http://martynov.org/) |
>| GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 |
>| AGAVA Software Company (http://www.agava.com/) |
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
>---------------------------------------------------------------------------
-----
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
