Mysql – Converting live MyISAM table to InnoDB

innodbmyisamMySQLmysql-5.5mysqldump

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of converting the table's storage engine from MyISAM -> InnoDB. Below is what I'm planning to do based on information I could gather from other posts. Please tell me if I'm missing anything.

mysqldump -u -p --lock-tables=false mydb tracking > tracking.sql – from what I understand this should not lock "tracking" table
Rename table name in the sql file to tracking_new
mysql -u -p mydb < tracking.sql
ALTER TABLE mydb.tracking_new ENGINE=InnoDB;

Insert new rows to "tracking_new" table that were written to the original table while performing dump

SELECT @last_id := tracking.id FROM mydb.tracking_new ORDER BY tracking.id DESC LIMIT 1;    
INSERT INTO mydb.tracking_new
SELECT * FROM mydb.tracking WHERE tracking.id > @last_id;

Rename tables

RENAME TABLE 
    mydb.tracking TO mydb.tracking_old, 
    mydb.tracking_new TO mydb.tracking;

Best Answer

Assuming you don't have any triggers on that table, the simplest way to do this without locking the table is to use pt-online-schema-change.

Make sure you have enough extra disk space to accommodate this change. The table will be larger when you convert it to InnoDB, and you need to maintain both copies of the table during the conversion. For example, if your table is 100 GB make sure you have at least 200+ GB of free disk space.

You should test this out in a non-production environment first to make sure it works for you and to find out exactly how much disk space you need.

The syntax for your case is quite simple.

First do a dry run to verify that it will work:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --dry-run

Then execute it:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --execute

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run of the the following to physically shrink the InnoDB table mydb.mytable:
- OPTIMIZE TABLE mydb.mytable;
- ALTER TABLE mydb.mytable ENGINE=InnoDB;

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)

Mysql – Select “,* from table

You can do the following:

CREATE TABLE tablename_temp (id int AUTO_INCREMENT PRIMARY KEY)
ENGINE=InnoDB 
SELECT * FROM tablename;

ALTER TABLE b
ADD UNIQUE INDEX some_index_name (your_original_key);

You can find the documentation here: http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html And a small example on SQLFiddle.

Best Answer

Related Solutions

Mysql – InnoDB vs MyISAM with many indexes

REASON #1

REASON #2

REASON #3

REASON #4

Mysql – Select “,* from table

Related Question