From: Tyler Poland (tpolandengineyard.com)
Date: Mon Nov 22 2010 - 08:24:18 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Machiel,

Each table will be write locked while it is being altered so this will
most likely impact the application. In addition to the write lock, the
conversion causes each table to be completely rewritten in the new
format so this will have a high impact on IO write activity and so it
will impact overall IO throughput. If your application is mostly reads,
is well cached in memory, and the tables are small this should be pretty
fast and relatively pain free. If you aren't sure about the impact and
conversion time you may want to restore a backup of the database to
another location and run through the conversion while monitoring
performance numbers.

Tyler

On 11/22/10 5:55 AM, Machiel Richards wrote:
> Thank you John
>
> I have in the meantime fond this to be the case (** someone
> changed config files without my knowledge it seems as this was setup
> properly and working**)
>
> Anyhow, in order for the innodb to be active again I need to
> restart the database, however aftewards I assume the tables will still
> be MyIsam.
>
> In this event I will need to manually alter each table, and I am
> concerned about the impact of this on the system performance.
>
> Regards
> Machiel
>
>
> -----Original Message-----
> From: John Daisley<daisleyjohngooglemail.com>
> To: Machiel Richards<machiel.richardsgmail.com>
> Cc: mysql mailing list<mysqllists.mysql.com>
> Subject: Re: Changing database tables to different storage engine.
> Date: Mon, 22 Nov 2010 10:51:23 +0000
>
> I have frequently seen Innodb 'silently' disabled if the
> innodb_log_file_size is different to the files size on disk (quite
> common when moving systems about). You wont be able to use innodb until
> you resolve this either by deleting the log files and restarting
> mysqld so they get recreated or changing the innodb_log_file_size to
> match the size of the files on disk.
>
> If the Innodb engine is not available then MySQL will use the default
> (usually MyISAM) storage engine even if Innodb was specified. You can
> stop this behaviour by setting sql-mode=NO_ENGINE_SUBSTITUTION
>
> Regards
>
> John
>
>
> On 22 November 2010 10:12, Machiel Richards<machiel.richardsgmail.com>
> wrote:
>
> Hi All
>
> Sorry but things have now changed, and I found the following.
>
>
> The tables was in fact restored as Innodb, however
> someone seems
> to have gone and changed something causing innodb to be
> disabled, this
> caused the tables to be defaulted back to MyIsam.
>
> Should this not rather have just resulted in an error
> allowing
> to fix the problem in the first place instead of changing the
> storage
> engines?
>
> Anyone have some thoughts on the best solution to fix
> this? I
> will look into the innodb not working soon.
>
> Machiel
>
>
>
>
> -----Original Message-----
> From: Machiel Richards<machielrrdc.co.za>
> To: mysql mailing list<mysqllists.mysql.com>
> Subject: Changing database tables to different storage engine.
> Date: Mon, 22 Nov 2010 11:59:03 +0200
>
>
> Good day all
>
> Hope all is well.
>
> I have something to ask as someone might have done
> this as
> well and may have a good solution on how to fix this.
>
> During a database migration this weekend to move a
> MySQL
> database from windows to linux, we created a backup and restore
> of the
> database.
>
> However, form my part I made a mistake by overlooking
> the
> fact that the windows database was configured to use default
> storage
> engine as Innodb.
>
> On the new server, the default was set to MyIsam.
>
> This resulted in all the tables being restored to
> the new
> system as MyIsam instead of Innodb.
>
> In order to fix this, I know you can use alter
> table to
> change the storage engine, however I need to know the following:
>
> 1. this is a production system and can't
> afford any
> downtime or as little performance degration as possible.
>
> What is the best way to do this in
> order to
> have the least amount of effect on the database and it's
> performance?
>
>
> Regards
> Machiel
>
>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]