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
ISAM = Indexed Sequential Access Method and is essentially a flat file (for those DBAs who can remember, think Btrieve, or B-Tree). It's a very old technology - but don't let that put you off using it. Because it's a flat file (more on that later), it is not relational, and thus is not an RDBMS, and thus is more appropriate in some situations.
InnoDB is the full RDBMS like you are most likely familiar with. MyISAM can appear to be relational through another layer added on top that maintains your links, logic and referential integrity.
ISAM is brilliant if you have a LOT of records (like, 20 million), and the records are mostly stand-alone (i.e. you don't need to do lots of links to retrieve associated data). It relies VERY heavilly on indexes and if you don't have the right index, be prepared for very very long query times. Case in point: We had a Btrieve ISAM table with 20M+ records and to do a retrieve and filter data based on an accurate index was almost instant. Using the wrong index was literally 15 minutes.
InnoDB is great for if you have a lot of relational links. Table A references a field in Table B, which references Table C and D. InnoDB can fetch these records using all sorts of nice joining methods (hash joins, etc), whereas an ISAM database would have to run multiple sub-queries for every single row and match the records manually.
You will really have to do a course in databases if you want much more detail than that!