1

We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it).

We are loading changes to this table in a nightly batch job using LOAD DATA INFILE.

MyISAM is more compact and efficient for a reference table. But this would be the ONLY MyISAM table we use.

The workload on the rest of the tables is typical web application except for 1 table which has a heavy write load and normal read load.

  • Question 1 - Is it worth the tradeoff of dedicating memory to the MyISAM cache for just this one table, when I know I need a reasonably amount of memory dedicated to the InnoDB cache for the other workload usecases?
  • Question 2 - When I run LOAD DATA INFILE 'from_nightly_batch' REPLACE INTO TABLE mytable, will MyISAM lock the table for reads? Will InnoDB? e.g. can I use the reference table while I do the nightly update in either engine?
Improve this question

1 Answer 1

Reset to default
2

Here is something to really think about

MyISAM only caches indexes, so there is always disk I/O. InnoDB caches data and indexes. If you could guarantee that every query you call against this one table can only be fetch necessary columns and all columns can be fetched from the index only (called a covering index)

Here are some nice links on Covering Indexes:

MyISAM can do shared reads. So can InnoDB.

Please remember that MyISAM inserts, updates, and deletes perform full tables locks each time (that can be the critical path for disk I/O in making InnoDB wait its turn in certain circumstances). If the table is ready-heavy, you may want to use a dedicated MyISAM key cache.

Heavy INSERTs from LOAD DATA INFILE will lock the MyISAM table. InnoDB can tolerate it better but check the behavior of LOAD DATA INFILE to see if it locks the table regardless of Storage Engine.

The tradeoff depends upon how often you write to the MyISAM table.

PLEASE DO NOT JOIN THE MyISAM TABLE TO ANY INNODB TABLE !!! The locking is clumsy and MyISAM changes cannot rollback.

Improve this answer
2
  • I found this excerpt from mysql docs, I presume it means InnoDB is unaffected by LOAD DATA INFILE: If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. Using this option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.
    – David Parks
    Commented Jan 11, 2013 at 3:54
  • I've sent about 3 days on trying to get innodb to take down 100GB reference table, but I'm not having any success. I'm feeling like the only useable option is myisam, not sure I can avoid the joins with myisam, the whole point of the reference table is to support typical join operations. But the data is read only, no need for transactions at least.
    – David Parks
    Commented Jan 12, 2013 at 1:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.