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
