How do MyISAM and InnoDB Utilize HD Space?

Question

My MySQL server is running out of HD space fast. Most of my larger tables use the InnoDB engine (for no mission-critical reason). In an effort to avoid the dreaded 'drop database to recover innodb disk-space' response detailed here http://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table, I'd like to better understand how the two engines store data on the disk.

1. If I were to use MyISAM would I be able to more-easily recover disk space by removing rows? Is there a command I would have to run in addition to the delete/truncate/empty query?
2. Assuming #1 were true: Would it be possible/feasible to convert existing InnoDB tables to MyISAM and recover space that way?

Answer 1

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.

• Article1
• Article2

As for MyISAM, you need to periodically run the following:

OPTIMIZE TABLE <mysqisam-tablename>;
or
ALTER TABLE <mysqisam-tablename> ENGINE=MyISAM;
ANALYZE TABLE <mysqisam-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 TerraBytes
(SELECT 5 pw) for PetaBytes (Write me if you every get numbers this high)

Answer 2

MyISAM allocates to disk reflecting the actual space utilized. If you are not using foreign keys, transactions, or any other feature unique to InnoDB, you could convert to MyISAM easily. InnoDB is quicker for writing and MyISAM is quicker for reading. Ultimately, I would recommend researching the differences between the engines in detail before arbitrarily changing storage engines.

Here is a procedure I have used for freeing up InnoDB tablespace in the past:

http://serverfault.com/questions/140947/deleting-huge-chunks-of-data-from-mysql-innodb/140956#140956