1

What are the considerations needed when converting myisam to innodb.

I have a table around 80 GB with engine myisam. I would like to convert into innodb. What are the considerations needed to convert.

I'm using percona MySQL. I checked the table indexes and there is no full text searching indexes for the above table.

I plan to use percona online scheme change.

What are the considerations/pre-requisite to keep in mind to do this activity?

Improve this question
3
  • 1) How much free space do you have in datadir right now ? 2) Please post the table structure of the 80GB table. 3) What is the output ofSHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table'; 4) How much RAM in the DB Server ? 5) How many CPU/cores ? 6) Is the DB Server bare metal or VM ?
    – RolandoMySQLDBA
    Aug 13, 2015 at 18:07
  • 1. Avail space 3.6T. 2. mysql> show create table edition.classic\G Table: classic Create Table: CREATE TABLE classic ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, classic_id int(11) unsigned NOT NULL, classic_date datetime NOT NULL, classic_name varchar(100) DEFAULT NULL, classic_value varchar(400) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=1236398635 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 3.innodb_file_per_table=ON 4.total mem=33gb, no swap 5. CPU(s) core=4 6.Its in amazon virtual.
    – s_ramesh
    Aug 14, 2015 at 8:34
  • Hi @RonaldoMySQLDBA, Do you have time to look my comment please.
    – s_ramesh
    Aug 15, 2015 at 2:39

3 Answers 3

Reset to default
4
  1. InnoDB tables tend to occupy more space than MyISAM tables do.
  2. While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I would say 120G)
  3. If you use Percona tools to make the change online, all modifications on the original table will be stored in a temporary table using triggers. So depending on the DML operations,i.e. inserts/updates/deletes, you may need more space. Be careful that on high traffic tables, triggers may affect the performance.
  4. It could be that some InnoDB related configuration should be tuned.

If in your setup you have two or more servers, like master-master, or master-slave(s), I think it is better to do it offline, one server at a time.

Improve this answer
1

I have an MyISAM2InnoDB blog that covers your question thoroughly.

It discusses index differences, especially PRIMARY KEY and FULLTEXT, space (2x-3x is typical), LOCK TABLES, decreasing key_buffer_size and increasing innodb_buffer_pool_size (very important), etc.

Improve this answer
2
  • Looks good. Can I use the same for using pt-osc to convert myisam to innodb
    – s_ramesh
    Aug 17, 2015 at 7:27
  • Good point; I added a section on pt-osc
    – Rick James
    Aug 17, 2015 at 17:56
1

Not sure if the issue is resolved. But as suggested here're some ways you can do this : 1. ALTER TABLE Approach : ALTER TABLE ENGINE=INNODB; Considerations for this approach : As you mentioned you have a big table this command will take time to execute.

  1. CREATE TABLE and INSERT INTO Approach : You can create a empty table with INNODB storage engine and start dumping data from your source table into this. If the data is huge you can do the conditional insert based on your Primary Key. Insert data in chunks.

Here's the few links you should consider looking at before doing this : http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html https://rtcamp.com/tutorials/mysql/myisam-to-innodb/ https://mariadb.com/kb/en/mariadb/converting-tables-from-myisam-to-innodb/

Hope this helps!!!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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