Another option, if your data hasn't changed in the mean time (I know, rare
scenario) could be to set up a secondary instance from the same binaries and
changing only the datafile location and the port in the config,
re-importing, shutting both instances down and switching out the datafiles.
You'll get some performance impact from the import's disk activity, but the
switch should take almost no time - if the datafiles are on the same
physical disk, of course.
On Mon, Nov 22, 2010 at 3:24 PM, Tyler Poland <tpoland@stripped>wrote:
> 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<daisleyjohn@stripped>
>> To: Machiel Richards<machiel.richards@stripped>
>> Cc: mysql mailing list<mysql@stripped>
>> 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.richards@stripped>
>> 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<machielr@stripped>
>> To: mysql mailing list<mysql@stripped>
>> 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
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
