MySQL Bugs: #36943: InnoDB ibdata storage does not clean

Bug #36943	InnoDB ibdata storage does not clean
Submitted:	25 May 2008 15:46	Modified:	26 May 2008 18:11
Reporter:	Aviv Levy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18-nt via TCP/IP	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, storage

Description:
I have a question. i dont know if it is a bug or i am missing something that should be done, but, i have a filling that never mind what i do, the file ibdata1 never cleans up. is there something i am missing and should do? is it a bug? i fills day by day my server and even when i deleted a user which has 800MB data, it didnt go down.

Thanks,
Aviv.

How to repeat:
1. create db.
2. fill it with data. 
3. see "ibdata1" growing.
4. delete the data.
5. watch ibdata file. has it gotten smaller?

It is 'not a bug'! It is expected behaviour! The tablespace grows when there is need for it, but it will not 'shrink' - there will be empty space inside the tablespace when data are deleted - but the file system cannot see that; it sees only the database file. That empty space will be used again (if possible) when new data are added. This behaviour it (with variations) same as what 'old and big' RDBMS like Oracle, DB2, MaxDB etc. do!

The reason is that those systems *does not* use the file system to allocate storage for databases and tables. What mysql does depends on the ENGINE and the settings (MyISAM and FALCON use the the file system to allocate disk space for storage objects: MyISAM for both tables and databases, FALCON for databases only - InnoDB (with default settings) does not).

I think we will see still more systems and engines that will use the file systems more or less (because file systems are improving - compare FAT with NTFS or EXTFS with EXT3FS/Reiser/XFS). But still the file systems is subject to fragmentation, may not support natively various types of indexing used by database systems and it is not controllable where to store (For instance with MyISAM a table data file (.MYD) and a related index file (MYI) may get stored on very different positions on disk. Letting the RDBMS/engine control how data are store inside the tablespace will to some extent 'isolate' file system issues from databases. I think the compromise 'a tablespace per database' is what we will see most for the future as wiht FALCON (and alsso a modern 3rd party engine like primebase XT).

InnoDB is here an 'old-fashioned system' that per default will use a single tablespace for everything. If the tablespace should *shrink* when deleting data then the complete tablespace would have to be rewritten. Note however: with InnoDB you have an option to configure 'file per table' option. Refer to:
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html.
Also you can purchase tools for InnoDB analysis and maintenaince from Oracle (who now owns InnoDB) that are not included with the MySQL server. A 'cheap solution' is to dump everything, disable InnoDB, delete all InnoDB files, re-enable InnoBD again and import.

Peter
(not a mysql person)

BTW: you should update that crap old server version!  Latest 5.0 is free/COMMUNITY version is 5.0.51b.

Thank you for the report.

Like Peter already correctly described this is not a bug.