Differences between INNODB abd MYISAM Database
- InnoDB uses row locking, MyISAM uses table locking.
- InnoDB is slower when it comes to SELECT
- InnoDB enforces referential integrit
- InnoDB allows transactions
- MyISAM allows full text search Indexes where as InnoDB does not allows that
- You will find better crash recovery in InnoDB.
| MyISAM | Innodb |
|---|---|
| Not *ACID compliant and non-transactional | *ACID compliant and hence fully transactional with ROLLBACK and COMMIT and support for Foreign Keys |
| MySQL 5.0 Default Engine | Rackspace Cloud Default Engine |
| Offers Compression | Offers Compression |
| Requires full repair/rebuild of indexes/tables | Auto recovery from crash via replay of logs |
| Changed Db pages written to disk instantly | Dirty pages converted from random to sequential before commit and flush to disk |
| No ordering in storage of data | Row data stored in pages in PK order |
| Table level locking | Row level locking |
MYISAM
ReplyDeleteThe main mechanism used is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;
This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.
InnoDB
The main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed. To size your InnoDB Buffer Pool, run the following query:
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;
This will give the Recommended Setting for the size of the InnoDB Buffer Pool (innodb_buffer_pool_size) given your current data set.
Don't forget to resize the InnoDB Log Files (ib_logfile0 and ib_logfile1). MySQL Source Code places a cap of the combined sizes of all InnoDB Log Files must be < 4G (4096M). For the sake of simplicity, given just two log files, here is how you can size them:
Step 1) Add innodb_log_file_size=NNN to /etc/my.cnf (NNN should be 25% of innodb_buffer_pool_size or 2047M, whichever is smaller)
Step 2) service mysql stop
Step 3) rm /var/log/mysql/ib_logfile[01]
Step 4) service mysql start (ib_logfile0 and ib_logfile1 are recreated)
CAVEAT
At the End of both queries is a an Inline Query (SELECT 2 PowerOf1024) B
(SELECT 0 PowerOf1024) gives the Setting in Bytes
(SELECT 1 PowerOf1024) gives the Setting in Kilobytes
(SELECT 2 PowerOf1024) gives the Setting in Megabytes
(SELECT 3 PowerOf1024) gives the Setting in Gigabytes
No powers less that 0 or greater than 3 is accepted
EPILOGUE
There is no substitute for common sense. If you have limited memory, a mixture of storage engines, or a combination thereof, you will have to adjust for different scenarios.
If you have 2GB RAM and 16GB of InnoDB, allocate 512M as innodb_buffer_pool.
If you have 2GB RAM and 4GB of MyISAM Indexes, allocate 512M as key_buffer_size.
If you have 2GB RAM and 4GB of MyISAM Indexes and 16GB InnoDB, allocate 512M as key_buffer_size and 512M as innodb_buffer_pool_size.
You can backup InnoDB tables just by taking a snapshot of the filesystem. Backing up MyISAM requires using mysqldump and is not guaranteed to be consistent (e.g. if you insert into a parent and a child table, you might find only the child table's row in your backup).
ReplyDeleteBasically, if you have another copy of the data and are only caching it in MySQL e.g. to allow a standard means of accessing it from a PHP website, then MyISAM is fine (i.e. it's better than a flat CSV file or a logfile for querying and concurrent access). If the database is the actual "master copy" of the data, if you are doing INSERT and UPDATE using real data from users, then it is foolish to use anything other than InnoDB, at any sort of scale MyISAM is unreliable and hard to manage, you'll be doing myisamchk half the time, negating any performance gains.