Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to suspend it for about 2 minutes.

Obviously ALTER TABLE ... engine=innodb will not work. Therefor I had the plan to create a new table with the innodb engine and copy the content into it. And in the end, suspend the application log thread and RENAME TABLE.

Unfortunately even doing the copying in small batches of 100 rows generates significant lag after some time.

Edit: Existing rows are never changed, this table is used for logging.

share|improve this question
1  
Duplicate : dba.stackexchange.com/questions/310/… –  Joe Jan 9 '11 at 13:04
2  
Well, that question is about minimizing conversation time. I don't care if the conversations takes a couple of days or weeks. But it must work in the background without requiring down time of the application and without creating noticeable lag. –  Hendrik Brummermann Jan 9 '11 at 13:14
add comment

4 Answers 4

up vote 14 down vote accepted

Create a Master-Master setup as follows:

  • Create second master, MasterB
  • MasterB acts as slave to logTable
  • Create logTable_new as innodb
  • Run INSERT INTO logTable_new SELECT * FROM logTable (psuedocode) on MasterB, which sends the replication over to MasterA
  • When logTable_new on MasterA finishes syncing, swap out the tables
share|improve this answer
add comment

Unfortunately even doing the copying in small batches of 100 rows generates significant lag after some time.

Are you adding any delay between each batch, or just batching up the updates and running each batch directly after the previous one?

If so then try scripting the conversion in your favourite language with something like:

repeat
    copy oldest 100 rows that haven't been copied yet to new table
    sleep for as long as that update took
until there are <100 rows unprocessed
stop logging service
move the last few rows
rename tables
restart logging
delete the old table when you are sure the conversion has worked

This should ensure that that the conversion doesn't take more than more-or-less half your server's capacity even allowing for differences in load imposed as the system's use varies with time.

Or if you want to use as much time as possible when the service is relatively idle but back off (potentially pausing for quite a length of time) when the database needs to do some work for its users, replace sleep for as long as the update took with if the server's load is above <upper measure>, sleep for some seconds then check again, loop around the sleep/check until the load drops below <lower measure>. This will mean it can steam ahead in quiet times but will pause completely when the server is busy performing it's normal workload. Determining load will depend on your OS - under Linux and similar the 1-minute load average value from /proc/loadavg or the output of uptime should do. <lower measure> and <upper measure> may be the same value, though it is usual in controls like this to have a difference so your process doesn't keep starting then immediately pausing because of its own restarting having an influence on the load measure.

Of course this would not work for tables where old rows may get modified, but will work fine for a log table like the one you describe.

You will want to ignore the usual wisdom of creating indexes after populating the new table in this case. While that is indeed more efficient when you want things to be as fast as possible (the effect on the rest of the system be damned), in this case you don't want the big glut of load at the end of the process as the indexes are completely created in one go as this is a process you can't pause when things get busy.

share|improve this answer
add comment

Given the constraint of:

I don't care if the conversations takes a couple of days or weeks. But it must work in the background without requiring down time of the application and without creating noticeable lag

As you're doing logging, if you have some good way to set a marker so you can tell at what you you start the process, so you can then re-apply any logs, or have the logs written out to a text file so you can later ingest them with LOAD DATA INFILE

Part of the problem is that writing in smaller batches means that the indexes have to be recomputed over and over again; you're better off running it all at once, but this might cause some 'noticable' lag on the system .. but you don't have to do it on your production server.

  1. Pause the logging or set some marker so you can re-apply the logs from this point on later.
  2. Copy your MyISM table to another system
  3. On the other system, create an InnoDB table under a different name and migrate the data (it might even be faster to dump it and use LOAD DATA INFILE)
  4. Copy the InnoDB table back to the original system
  5. Set another marker for the logging.
  6. Reapply all of the logs to the new table from between the last two markers.
  7. (repeat steps 5 & 6 if step #6 took more than a minute or so, until such time as it's only a few seconds)
  8. Swap out the tables (rename old one to table_BACKUP, new one under the name of the old one)
  9. Catch up the logs since the last marker.
share|improve this answer
add comment

Would something like this work?

  1. Pause logging (so the $auto_increment on your logging table mytable doesn't change).
  2. Note the $auto_increment value using SHOW TABLE STATUS LIKE 'mytable'.
  3. CREATE TABLE mytable_new LIKE mytable
  4. ALTER TABLE mytable_new AUTO_INCREMENT=$auto_increment ENGINE=Innodb
  5. RENAME TABLE mytable TO mytable_old, mytable_new TO mytable
  6. Enable logging again. The Innodb table will now start populating.
  7. INSERT INTO mytable SELECT * FROM mytable_old.

You can do step 7 in batches or in one statement since it shouldn't be blocking to the normal logging.

share|improve this answer
    
it would still be blocking, because of the way innodb handles auto_increment,. by default innodb takes a table level lock when inserting into an auto_increment column, and releases the lock as soon as the insert is finished,. –  ovais.tariq May 6 '11 at 12:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.