Another thing to keep in mind is to make sure all your foreign keys
are re-created if you have any. We had a similar "accident" in our
prod box a few years back and converting MyIsam to InnoDB won't
necessarily re-create the foreign keys.
Kyong
On Mon, Nov 22, 2010 at 6:39 AM, Johan De Meersman <vegivamp@stripped> wrote:
> 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
>
