When to use MyISAM or InnoDB
Posted: Tue Nov 03, 2009 6:13 amMyISAM Limitations
* No foreign keys and cascading deletes/updates
* No transactional integrity (ACID compliance)
* No rollback abilities
* Row limit of 4,284,867,296 rows (232)
* Maximum of 64 indexes per row
InnoDB Limitations
* No full text indexing
* Cannot be compressed for fast, read-only
If none of those deal-breakers apply, then you can keep reviewing the pros and cons. Here’s some things to keep in mind:
Read to write ratio
If you find that the amount of writes climbs over 10-15%, you should consider InnoDB, but if it climbs over 20%, you will get a big performance gain by moving to InnoDB.
This all stems from how the storage engines handle locking. MyISAM uses table-level locking. That means that if you run a query against the table, the whole table will be locked. The only exception is that you can insert a row if there is an active read lock on the table, and the table is not fragmented (called concurrent insertion). So if a user is running a really slow select on a table, your updates or deletes will have to wait until the select is completely finished. This delay can reduce the overall efficiency of your application drastically if your MySQL server is busy and over 20% of your queries are writes.
InnoDB uses a row-level locking scheme, which means that waiting on locks is greatly reduced. When MySQL wants to lock something for a query in an InnoDB table, only the row that needs to be accessed is actually locked. For example, if you run a select that examines rows 5-20 in a table, but another user wants to write to row 24 at the same time, both queries will be executed simultaneously. Of course, transactions will change this behavior a bit, but I’ll leave that out for simplicity’s sake.
Available hardware
InnoDB requires additional hardware in order to function as efficiently as MyISAM. This means that you will need additional CPU power and RAM to get good performance out of InnoDB tables. MyISAM tables can also be compressed with myisampack so that they can handle queries quickly (at the expense of being read-only).
Table growth
MyISAM (by default) is limited to 232 rows, and this can be a pain when you reach the limit. You can increase it manually when you reach the limit, but this causes the row pointer to become gigantic, and this reduces performance. InnoDB tables have no limits, and they can grow over 2GB even on systems that don’t allow files to grow over 2GB. InnoDB tables can also use raw partitions to store data, and this ends up being quite fast relative to simple ibdata files.
Isolation and integrity
When it comes to transactional integrity, InnoDB holds all the cards. You can adjust your isolation levels so that you see the exact data you want, even when data is changing from other transactions when your transaction runs. Transactions can have save points to which you can roll back if something does not work out. Also, if you have the need to run several queries at once that need to run uninterrupted, transactions are required for this functionality.
* No foreign keys and cascading deletes/updates
* No transactional integrity (ACID compliance)
* No rollback abilities
* Row limit of 4,284,867,296 rows (232)
* Maximum of 64 indexes per row
InnoDB Limitations
* No full text indexing
* Cannot be compressed for fast, read-only
If none of those deal-breakers apply, then you can keep reviewing the pros and cons. Here’s some things to keep in mind:
Read to write ratio
If you find that the amount of writes climbs over 10-15%, you should consider InnoDB, but if it climbs over 20%, you will get a big performance gain by moving to InnoDB.
This all stems from how the storage engines handle locking. MyISAM uses table-level locking. That means that if you run a query against the table, the whole table will be locked. The only exception is that you can insert a row if there is an active read lock on the table, and the table is not fragmented (called concurrent insertion). So if a user is running a really slow select on a table, your updates or deletes will have to wait until the select is completely finished. This delay can reduce the overall efficiency of your application drastically if your MySQL server is busy and over 20% of your queries are writes.
InnoDB uses a row-level locking scheme, which means that waiting on locks is greatly reduced. When MySQL wants to lock something for a query in an InnoDB table, only the row that needs to be accessed is actually locked. For example, if you run a select that examines rows 5-20 in a table, but another user wants to write to row 24 at the same time, both queries will be executed simultaneously. Of course, transactions will change this behavior a bit, but I’ll leave that out for simplicity’s sake.
Available hardware
InnoDB requires additional hardware in order to function as efficiently as MyISAM. This means that you will need additional CPU power and RAM to get good performance out of InnoDB tables. MyISAM tables can also be compressed with myisampack so that they can handle queries quickly (at the expense of being read-only).
Table growth
MyISAM (by default) is limited to 232 rows, and this can be a pain when you reach the limit. You can increase it manually when you reach the limit, but this causes the row pointer to become gigantic, and this reduces performance. InnoDB tables have no limits, and they can grow over 2GB even on systems that don’t allow files to grow over 2GB. InnoDB tables can also use raw partitions to store data, and this ends up being quite fast relative to simple ibdata files.
Isolation and integrity
When it comes to transactional integrity, InnoDB holds all the cards. You can adjust your isolation levels so that you see the exact data you want, even when data is changing from other transactions when your transaction runs. Transactions can have save points to which you can roll back if something does not work out. Also, if you have the need to run several queries at once that need to run uninterrupted, transactions are required for this functionality.