One thing that sucks really bad with MyISAM is table level locking which can cause some serious issues at times when INSERT/UPDATE/DELETE load is very high. If you are experiencing problems in your application associated with locking, try converting your tables to InnoDB.
I was asked today a question as to how one can convert a MyISAM table to InnoDB. I will blog about it in detail later but for right now here's one way:
-ALTER TABLE table_name ENGINE = InnoDB;
and here's another way:
- dump the table to a SQL file
- change table type from MyISAM to InnoDB in the dumped file
- disable keys (to speed up the process)
- SET AUTOCOMMIT to 0 (for speeding up the process)
- load the table
- SET AUTOCOMMIT to 1
- enable keys (the index is rebuilt at this point)
One is preferred over the other based on various factors such as time limit in which the conversion must be done.
Also see:
4 comments:
Frank,
Couple of things which can help to speed up conversion, if data size is significant.
1) disable keys do not work for Innodb tables. So it is not needed.
2) SET UNIQUE_CHECKS=0 is handy if loading data with unique keys - can speed up things dramatically.
3) Make sure you load data in primary key order. MyISAM is frequently unsorted which may make load 10+ time slower than it can be.
Peter,
Of course you're right. My bad for not paying attention. DISABLE KEYS would only help with MyISAM.
Thank you for the tips. They are greatly appreciated.
Frank
I'm converting a 1 table that's 1.5 GB in size as a MyISAM table to Innodb.
I'm doing this conversion on a dedicated server by dumping the db, copying to the new server, and doing a mysql db < file.
I've used the optimizations you've mentioned (set innodb buffer pool to 3GB, set autocommit=0, set unique_checks=0).
But it's been >8 hours and the conversion is still underway.
What am I doing wrong?
May I document it at my blog?
it's a useful trick
ykzir.wordpress.com
thanks
Post a Comment