engine-compare ---simple concept

 

With InnoDB, either use autocommit=1, making each operation into a "transaction", or use BEGIN (START) and COMMIT to explicity bracket each set of statement(s) that make up a transaction. InnoDB can prevent many kinds of "deadlocks", and simply stall one transaction until it is safe to continue. Some deadlocks are easily detected after starting transactions, and lead to aborting one of the competing transactions; this ROLLBACK must be monitored for by the application. Still other situations can lead to hanging until a timeout (cf innodb_lock_wait_timeout, which defaults to 50 seconds).

MyISAM dates back to the '90s. It was designed to be simple and fast, while lacking important database features (ACID). InnoDB came along in the '00s. Most energy has gone into improving it. Today, InnoDB, (and Percona's Xtradb) are excellent engines. InnoDB recovers automatically after a crash. 

MyISAM's simplicity leads to very little overhead on disk. InnoDB's transactional semantics, MVCC, ROLLBACK, undo, etc, lead to a lot of disk overhead. 

MyISAM caches INDEX blocks in the "key_buffer". Data blocks are cached by the Operating system. InnoDB caches both data and indexes in the "buffer_pool". These lead to radically different tuning (see 70%). 

What is the key_buffer?MyISAM does two different things for caching. 
    ⚈  Index blocks (1KB each, BTree structured, from .MYI file) live in the "key buffer". 
    ⚈  Data block caching (from .MYD file) is left to the OS, so be sure to leave a bunch of free space for this. 
Caveat: Some flavors of OS always claim to be using over 90%, even when there is really lots of free space. 

SHOW GLOBAL STATUS LIKE 'Key%'; then calculate Key_read_requests / Key_reads If it is high (say, over 10), then the key_buffer is big enough.