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)
  • All InnoDB tables? How many tables?
    – gbn
    Commented Jan 25, 2012 at 16:13

1 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


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

  • why doesn't the size of the ibdata files reduces if the size of the database does?
    – Max
    Commented Jan 25, 2012 at 16:48
  • It's apparently a 'design decision' made by developers of innodb, briefly explained in the comments of this bug. A similar feature request was made quite a few years back, but nothing has been done about it. I don't agree with the design decision personally, but it is what it is. Commented Jan 25, 2012 at 17:13
  • why didn't optimise table reclaim space? or is that just "tidy" not "shrink"?
    – gbn
    Commented Jan 25, 2012 at 18:08
  • 1
    @gbn "For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index." src So yes, just a tidy. Same reason an AlTER TABLE foo ENGINE=InnoDB without innodb_file_per_table=1 won't shrink ibdata. Commented Jan 25, 2012 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.