Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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 Jan 11 '13 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 Jan 12 '13 at 1:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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