MyISAM vs InnoDB for BI / batch query performance (ie, _NOT_ OLTP)

Question

Sure, for a transactional database InnoDB is a slam dunk. MyISAM doesn't support transactions or row-level locking.

But what if I want to do big nasty batch queries that touch hundreds of millions of rows?

Are there areas where MyISAM has relative advantage over InnoDB??

eg, one (minor) one that I know of ... "select count(*) from my_table;" MyISAM knows the answer to this instantly whereas InnoDB may take a minute or more to make up its mind.

--- Dave

Lizethlizette

Answer 1

MyISAM scales better with very large datasets. InnoDB outperforms MyISAM in many situations until it can't keep the indexes in memory, then performance drop drastically.

MyISAM also supports MERGE tables, which is sort of a "poor man's" sharding. You can add/remove very large sets of data instantaneously. For example, if you have 1 table per business quarter, you can create a merge table of the last 4 quarters, or a specific year, or any range you want. Rather than exporting, deleting and importing to shift data around, you can just redeclare the underlying MERGE table contents. No code change required since the name of the table doesn't change.

MyISAM is also better suited for logging, when you are only adding to a table. Like MERGE tables, you can easily swap out (rotate "logs") a table and/or copy it.

You can copy the DB files associated with a MyISAM table to another computer and just put them in the MySQL data directory and MySQL will automatically add them to the available tables. You can't do that with InnoDB, you need to export/import.

These are all specific cases, but I've taken advantage of each one a number of times.

Of course, with replication, you could use both. A table can be InnoDB on the master and MyISAM on the slave. The structure has to be the same, not the table type. Then you can get the best of both. The BLACKHOLE table type works this way.

Dimissory

"InnoDB on the master and MyISAM on the slave" --- exactly. This is my current setup. My thought experiment is InnoDB vs MyISAM for that slave that only services read-only queries that tend to be ad-hoc and touch lots of rows. I use MyISAM now, but I'm not sure if it buys me anything other than quick row counts. – Lizethlizette

MERGE tables are interesting. Those vs partitioned tables. Partitioned tables seem to enforce semantics more strictly, supporting unique keys across all partitions. The "looser" semantics of a "poor man's shard" might be more suited to some scenarios. I'll have to think about those trade-offs for a bit. – Lizethlizette

Answer 2

Here's a great article comparing various performance points http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ - you'll have to evaluate this from quite a few angles, including how you intend to write your queries and what your schema looks like. It's simply not a black and white question.

Gautious

That article is all about OLTP. Every graph has "# of connections" as the x-axis. For my scenarios, "# of connections" == 1. And all queries are ad-hoc. – Lizethlizette

Answer 3

According to this article, as of v5.6, InnoDB has been developed to the point where it is better in all scenarios. The author is probably a bit biased, but it clearly outlines which tech is seen as the future direction of the platform.

Lizethlizette