Converting MyISAM to InnoDB. Beneficial? Consequences?

Question

We're running a social networking site that logs every member's action (including visiting other member's pages); this involves a lot of writes to the db. These actions are stored in a MyISAM table and since something is starting to tax the CPU, my first thought was that it's the table locking of MyISAM that is causing this stress on the CPU.

• There are only reads and writes, no updates to this table. I think the balance between reads and writes is about 50/50 for this table, would InnoDB therefore be a better option?
• If I want to change the table to InnoDB and we don't use foreign key constraints, transactions or fulltext indexes - do I need to worry about anything?

Faizabad

https://mcmap.net/q/53494/-myisam-versus-innodb-closed – Sitton

This is not a duplicate of the above, as it concerns migration rather than the benefits per se. – Gunderson

You can also use a mixture of tables, keeping MyISAM for read-heavy tables and InnoDB for the logs. However, I personally would not use MyISAM for anything much today (only fulltext searchbait really). – Jeweljeweler

Answer 1

Notwithstanding any benefits / drawbacks of its use, which are discussed in other threads ( MyISAM versus InnoDB ), migration is a nontrivial process.

Consider

• Functionally testing all components which talk to the database if possible - difference engines have different semantics
• Running as much performance testing as you can - some things may improve, others may be much worse. A well-known example is SELECT COUNT(*) on a large table.
• Checking that all your code will handle deadlocks gracefully - you can get them without explicit use of transactions
• Estimate how much space usage you'll get by converting - test this in a non-production environment.

You will doubtless need to change things in a large software platform; this is ok, but seeing as you (hopefully) have a lot of auto-test coverage, change should be acceptable.

PS: If "Something is starting to tax the CPU", then you should a) Find out what, in a non-production environment, b) Try various options to reduce it, in a non-production environment. You should not blindly start doing major things like changing database engines when you haven't fully analysed the problem.

All performance testing should be done in a non-production environment, with production-like data and on production-grade hardware. Otherwise it is difficult to interpret results correctly.

Gunderson

Thanks alot for this info. Could you elaborate on the "different semantics"? – Faizabad

Answer 2

With regards to other potential migration problems:

1) Space - InnoDB tables often require more disk space, though the Barracuda file format for new versions of InnoDB have narrowed the difference. You can get a sense for this by converting a recent backup of the tables and comparing the size. Use "show table status" to compare the data length.

2) Full text search - only on MyISAM

3) GIS/Spatial datatypes - only on MyISAM

On performance, as the other answers and the referenced answer indicate, it depends on your workload. MyISAM is much faster for full table scans. InnoDB tends to be much faster for highly concurrent access. InnoDB can also be much faster if your lookups are based on the primary key.

Another performance issue is that MyISAM can always keep a row count, since it only does table level locking. So, if you're frequently trying to get the row count for a very large table, it may be much slower with InnoDB. Search the Internet if you need a workaround for this, as I've seen several proposed.

Depending on the size of the table(s), you may also need to update your MySQL config file. At the very least, you may want to shift bytes from key_buffer to innodb_buffer_pool_size. You won't get a fair comparison if you leave the database as being optimized for MyISAM. Read up on all the innodb_* configuration properties.

Enidenigma

Answer 3

I think it's quite possible that switching to InnoDB would improve performance, but In my experience, you can't really be sure until you try it. If I were you, I would set up a test environment on the same server, convert to InnoDB and run a benchmark.

Shoshone

Answer 4

From my experience, MyISAM tables are only useful for text indexing where you need good performance with searches on big text, but you still don't need a full fledged search engine like Solr or ElasticSearch.

If you want to switch to InnoDB but want to keep indexing your text in a MyISAM table, I suggest you take a look at this: http://blog.lavoie.sl/2013/05/converting-myisam-to-innodb-keeping-fulltext.html

Also: InnoDB supports live atomic backups using innobackupex from Percona. This is godsent when dealing with production servers.

Sophy