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)
Best Answer
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 anon tables that grow too large to reclaim the disk space.