Mysql – Choosing between MyISAM & InnoDB – two servers with related tables

innodblinuxmyisamMySQLperformance

I don't want to make a mistake.

I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.

I read here and there that InnoDB should be preferred now. MySQL is version 5.1.63.

Context: Two MySQL servers, serv1 and serv2.

serv1: one table with unique id. Call this table daddy.
serv2: one table child on which id's are the same as daddy's.

I have to update (and only this) my fields contained in daddy with the fields of child, where id's are the same. Indexes are on the columns that are important for the query.

So, to max performance out, should I stick with MyISAM or convert to InnoDB? I can still do it as the database is empty.

In my case, every hundredth of a second is worth a million $ 😀 (pure joke of course)

May you enlighten me?

Best Answer

"MyISAM is faster than InnoDB" -- This is an old wives tale. It used to be somewhat correct; now it is somewhat incorrect.

SELECTing during UPDATEing -- MyISAM is usually fast enough so this is not an issue, but InnoDB will usually not block at all. Go with InnoDB.

Power failure -- InnoDB automatically recovers.

You are talking about two "servers" -- Do you mean two different computers? And two differen instances of mysqld? There is nothing to have them talk to each other. (OK there are Replication and Federation, but it did not sound like you are into those.)

If you really meant "database" or "table" on a single MySQL instance, then so state. Tables (even in different databases) can be JOINed for most operations.

REASON #1

You only have 150MiB of MyISAM tables (70MiB Data, 80 MiB Indexes)

REASON #2

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

REASON #3

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run of the the following to physically shrink the InnoDB table mydb.mytable:
- OPTIMIZE TABLE mydb.mytable;
- ALTER TABLE mydb.mytable ENGINE=InnoDB;

All this can be avoid with MyISAM

REASON #4

InnoDB protects individual rows by performing MVCC for transaction control. The overhead generated for reads in a day would probably be greater than 150 MiB.

I can probably name 2 or 3 more reasons, but let's cut to the chase: Is there anything that can improve performance for MyISAM in your case? Why, yes there is.

Your said the following

The table is about 260k rows in size, with 28 fields which for the most part is varchars and ints

If you have many varchars, there is something you can do to increase read/write performance. For any MyISAM table mydb.mytable: run this command:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

What will this do, this will treat all VARCHARs as CHARs. Every row will be the exact same length. This will increase disk space 80%-100%. In your case, let's assume it doubles your 150 MiB MyISAM table to 300 MiB. Where is the benefit? Your MyISAM table can now be read/written anywhere from 20% - 30% faster without changing anything else I learned that from pages 72,73 from MySQL Database Design and Tuning.

I have written about this in the past:

Which is faster, InnoDB or MyISAM? (May 03, 2012)
Which mysql storage engine to choose? (May 02, 2012)
Best of MyISAM and InnoDB (Sep 20, 2011)
Which DBMS is good for super-fast reads and a simple data structure? (Aug 12, 2011)
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (May 10, 2011)

Mysql – Choosing MyISAM over InnoDB for these project requirements; and long term options

InnoDB and MyISAM each have their strengths and weaknesses.

May 03, 2012 : Which is faster, InnoDB or MyISAM?
Sep 20, 2011 : Best of MyISAM and InnoDB

If you have enough RAM, I would choose InnoDB because it caches data and index pages in the Buffer Pool. MyISAM only caches index pages in the Key Cache.

MyISAM tables experience full table locks for each INSERT, UPDATE, and DELETE. MyISAM tables always require disk access for data.

InnoDB tables always incur disk I/O in the following areas:

Double Write Buffer : Changes are posted in ibdata1 to avoid OS caching
Insert Buffer : Changes to Secondary (non-Unique) Indexes as posted in ibdata1
Data and Indexes
- With innodb_file_per_table = 0, changes are written to ibdata1
- With innodb_file_per_table = 1, changes are written to .ibd tablespace file. Read I/O against ibdata1 still necessary to crosscheck table metadata

SUMMARY

In an environment with the following:

heavy writes
heavy reads
tons of RAM
heavy connections

I would always choose InnoDB. Please check out my other post about InnoDB over MyISAM : When to switch from MyISAM to InnoDB?

When would I every choose MyISAM?

Under the following scenario

Using MySQL Replication
Master with all InnoDB
Slave with all tables converted to MyISAM
ALTER TABLE ... ROW_FORMAT=Fixed for all tables on the Slave

Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT-Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR is treated as CHAR this shortening access time for data retrieval.

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

On the other hand, InnoDB has to interact with multiple files (ibdata1, serveral read/write threads upon the .ibd of the InnoDB table).