Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

migrated from serverfault.com Jan 25 '12 at 15:44

This question came from our site for system and network administrators.

    
All InnoDB tables? How many tables? –  gbn Jan 25 '12 at 16:13

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
why doesn't the size of the ibdata files reduces if the size of the database does? –  user359650 Jan 25 '12 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. –  Derek Downey Jan 25 '12 at 17:13
    
why didn't optimise table reclaim space? or is that just "tidy" not "shrink"? –  gbn Jan 25 '12 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. –  Derek Downey Jan 25 '12 at 19:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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