List:General Discussion« Previous MessageNext Message »
From:Kyong Kim Date:November 22 2010 5:54pm
Subject:Re: Changing database tables to different storage engine.
View as plain text  
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
>
Thread
Changing database tables to different storage engine.Machiel Richards22 Nov
  • Re: Changing database tables to different storage engine.Machiel Richards22 Nov
    • Re: Changing database tables to different storage engine.John Daisley22 Nov
      • Re: Changing database tables to different storage engine.Machiel Richards22 Nov
        • Re: Changing database tables to different storage engine.Johan De Meersman22 Nov
        • Re: Changing database tables to different storage engine.Tyler Poland22 Nov
          • Re: Changing database tables to different storage engine.Johan De Meersman22 Nov
            • Re: Changing database tables to different storage engine.Kyong Kim22 Nov