MySQL use of DiskSpace is quite predictable.
The information_schema can quickly give away how much space is used by both storage engines. However, it is far better to configure InnoDB with innodb_file_per_table. That way, you can micromanage the diskspace of individual InnoDB tables. If you do not have innodb_file_per_table, the ibdata1 will grow and NEVER, EVER SHRINK.
I wrote nice articles about Cleaning Up InnoDB once and for all.
As for MyISAM, you need to periodically run one of the following:
OPTIMIZE TABLE myisam-tablename
;
ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename
;
These will compress MyISAM so that there are not unused data and index pages in the MyISAM table components (.MYD and .MYI)
You can manually monitor the disk space usage with this query:
SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;
This will tell how much space is occupied by data and index for engine storage engine. Notice at the end of the query the clause: (SELECT 3 pw). Change the number to generate the report in different units
(SELECT 0 pw) for Bytes
(SELECT 1 pw) for KiloBytes
(SELECT 2 pw) for MegaBytes
(SELECT 3 pw) for GigaBytes
(SELECT 4 pw) for TeraBytes
(SELECT 5 pw) for PetaBytes (Write me if you every get numbers this high)
UPDATE 2012-05-26 22:29 EDT
When it comes to InnoDB, if you use innodb_file_per_table, you may find that there is a difference between the file size of the .ibd
file and the sum of data_length + index_length.
For an InnoDB table mydb.mytable
, here is the comparison you should make:
- Get the size of file by running
ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print %5}'
- Get the size of the table from the information schemna's point of view:
SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';
- If the
filesize > (data_length+index_length) * 1.1
, then you should drag the table like this: ALTER TABLE mydb.mytable ENGINE=InnoDB;
This will make a temp table, copy only real data pages and index pages into the temp table, delete the original, and rename the temp table back to mydb.mytable. Instant table compression with one command. Please plan all table compressions during off hours.
Best Answer
I would go with MyISAM over InnoDB for three(3) major reasons
Reason # 1 : MyISAM storage engine provides simpler mechanisms for improving read performance
MyISAM tables are comprised of just three files
You can tune MyISAM tables for faster reads with one simple change to the table's row format.
For example, Pages 71-73 of the book MySQL Database Design and Tuning recommends doing this to a MyISAM table:
This will convert all VARCHARs to CHARs internally. Ths will also make the MyISAM table about 50% larger but have increased read performance because the string length is more rigidly checked with VARCHAR whereas a CHAR's length is fixed.
As an alternative you could run myisampack on a MyISAM table to reduce the MyISAM table size and create a new read-only format.
These are one-time operations you can perform on any MyISAM table. Changing the row format on an InnoDB table is somewhat useless due to its clustered index layout (explained in Reason # 3)
Reason # 2 : MyISAM Caching is Simpler
MyISAM only caches index pages. InnoDB caches data and index pages. You can thus configure the MyISAM key cache smaller that an InnoDB Buffer Pool.
Reason # 3 : InnoDB carries too much baggage for protecting data
InnoDB features crash recovery on startup even if nothing is wrong (precautionary)
InnoDB always performs MVCC against rows of data. That creates extra housecleaning for a house that never get messy from INSERTs, UPDATEs, or DELETEs.
InnoDB performs double index lookups all the time, particularly when using non-UNIQUE indexes. The reason this happens is due to InnoDB's internal rowid index which is closely linked to the clustered index.
You could bypass having InnoDB startup by having the following in /etc/my.cnf
MySQL will thus restart much quicker. You could also delete /var/lib/mysql/ibdata1, /var/lib/mysql/ib_logfile0, and /var/lib/mysql/ib_logfile1.
CAVEAT
If the data is small enough, mount /var/lib/mysql on a RAM disk. Then, all bets are off for comparing MyISAM to InnoDB.