Substantial difference between database size under MySQL and actual size on disk

Question

When I check the size of my databases under MySQL I get this:

MariaDB [(none)]> SELECT table_schema "Data Base Name",  sum( data_length + index_length ) / 1024 /  1024 "Data Base Size in MB",  sum( data_free )/ 1024 / 1024 "Free Space in MB"  FROM information_schema.TABLES  GROUP BY table_schema;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| alfresco           |         245.75000000 |      34.00000000 |
| drupal             |         892.15625000 |     216.00000000 |
+--------------------+----------------------+------------------+

When I check the size on the disk I get this:

$ sudo du -h --max-depth=1 /var/lib/mysql/
317M    /var/lib/mysql/alfresco
1.4G    /var/lib/mysql/drupal

If I combine both used and free space given by Maria DB and compare it with disk figures I have the following:

alfresco: DB=279MB  DISK=317MB (+14%)
drupal: DB=1100MB   DISK=1433MB (+30%)

Q: Is it normal to have that much overhead on the disk / Is there anything I can do to reduce it?

FYI I thought running a mysql optimize would help (using that command), it did reduce the size of the databases, but didn't change the size of files on the disk.

Additional info:

server:             ubuntu server 10.04 LTS
DB server:          MariaDB
DB engine:          InnoDB v10 (for all tables)
Table collation:    utf8_general_ci
Nb Drupal tables:   416  (0.80MB overhead per table)
Nb Alfresco tables: 84   (0.45MB overhead per table)

Answer 1

If you are using InnoDB tables, the size of your ibdata files will grow over time. So, if you issue DELETE statement, your database size will reduce, but the ibdata file will remain the same (not reduce).

If you are not using innodb_file_per_table option, the only way to reclaim the space is by dumping the database and restoring from the dumpfile.

However, if you are using innodb_file_per_table, you can issue an

ALTER TABLE foo ENGINE=InnoDB;

on tables that grow too large to reclaim the disk space.