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 <vegiv...@tuxera.be> 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 <tpol...@engineyard.com>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<daisleyj...@googlemail.com> >>> To: Machiel Richards<machiel.richa...@gmail.com> >>> Cc: mysql mailing list<mysql@lists.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.richa...@gmail.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<machi...@rdc.co.za> >>> To: mysql mailing list<mysql@lists.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 >> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be >> >> > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
