Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Are there any pitfalls / things you need to know when changing from MyISAM to InnoDB

Tags:

mysql

innodb

myisam

change-management

One of my projects use the MyISAM engine in MySQL, but I'm considering changing it to InnoDB as I need transaction support here and there.

  • What should I look at or consider before doing this?
  • Can I just change the engine, or should the data be prepared for it?
like image 308
Jrgns Avatar asked Oct 22 '08 10:10

Jrgns

People also ask

Should I change MyISAM to InnoDB?

A lot of older sites are still using the MyISAM storage engine in their database. Over recent years, InnoDB has shown to perform better and be more reliable. A big reason to use InnoDB over MyISAM, is the lack of full table-level locking. This allows your queries to process faster.

Is InnoDB better than MyISAM?

The performance of InnoDB for large volumes of data is better as compared to MyISAM. MyISAM doesn't support transactional properties and is faster to read. As compared to InnoDB, the performance for a high volume of data is less.

Why is InnoDB slower than MyISAM?

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages. Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data.

1 Answers

Yes absolutely, there are many things, you should test your application extremely thoroughly:

  • Transactions can deadlock and need to be repeated. This is the case (in some circumstances) even with an autocommitted transaction which only inserts one row.
  • Disc usage will almost certainly increase
  • I/O load during writes will almost certainly increase
  • Behaviour of indexing will change because InnoDB uses clustered indexes - this may be a beneficial effect in some cases
  • Your backup strategy will be impacted. Consider this carefully.

The migration process itself will need to be carefully planned, as it will take a long time if you have a lot of data (during which time the data will be either readonly, or completely unavailable - do check!)

like image 82
MarkR Avatar answered Oct 26 '22 03:10

MarkR
Sign in to Comment

Related questions

Convert PostgreSQL dump file to MySQL?
How to store Birthdate and Age so that Age can be updated daily in PHP/MySQL?
Better understaning - Class.forName("com.mysql.jdbc.Driver").newInstance ();
windows batch file for mysqldump command
mysqldump version Error while exporting data from MySQL
PDO lastInsertId() returns 0 when using on duplicate key update?
Python MySQL escape special characters
MySQL fulltext search over multiple columns: result confusion
How to uninstall mysql on amazon ec2 micro instance
MySQL select coordinates within range
In SQL, what's the difference between JOIN and CROSS JOIN?
Enabling local-infile for loading data into remote mysql from rails
Can't run MySql Utilities
SQL - ORDER BY 'datetime' DESC [closed]
persist java LocalDate in MySQL
How to use 'where' on column with comma separated values in laravel
Change column in Rails to be unique
Join two MySQL tables in different databases on the same server with Laravel Eloquent
MySql Error 'max_allowed_packet' bytes during import SQL script on database hosted on AWS RDS
Insert array into database in a single row

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!