MySQL Bugs: #5804: Free datafile space in "SHOW INNODB STATUS"?

Bug #5804	Free datafile space in "SHOW INNODB STATUS"?
Submitted:	29 Sep 2004 16:51	Modified:	13 May 2010 16:04
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.0.21	OS:	Linux (Debian GNU/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hello

I have a 100MB file specified in innodb_data_file_path.
"SHOW INNODB STATUS" seems not to tell me to which amount this file is already been filled.
Or does it and I just don't see it? If so then this bug is about giving this information a better description :-)

What I think of is:
 --------------------------------
 TABLE SPACES
 --------------------------------
  99MB of 100MB in /mnt/sda10/mysql-innodb/tablespace1.innodb
  40MB of 100MB in /mnt/sda10/mysql-innodb/tablespace2.innodb:autoextend

bye,

-christian-

How to repeat:
SHOW INNODB STATUS;

Suggested fix:
...

Hi!

SHOW TABLE STATUS does tell the free space.

Regards,

Heikki

There is a list with datafiles in information_schema.

There are two things still missing there:
- Free space 
- Files for space 0 (might be more than one, Bug #69323)

If tablespace 0 (system tablespace) consists of multiple files it is not possible to see the amount of data used per file.

The DATA_FREE column from I_S.tables does show the amount of free space for the complete tablespace. But if you have a few tables in space 0 and a few in their own space than it can get complicated to get the correct info.

mysql 5.6.14 > SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE './test/%';
+-------+--------------------------+
| SPACE | PATH                     |
+-------+--------------------------+
|     8 | ./test/t1.ibd            |
|    11 | ./test/accounts#P#p0.ibd |
|    12 | ./test/accounts#P#p1.ibd |
|    13 | ./test/accounts#P#p2.ibd |
|    14 | ./test/accounts#P#p3.ibd |
|    15 | ./test/accounts#P#p4.ibd |
+-------+--------------------------+
6 rows in set (0.01 sec)

mysql 5.6.14 > SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE SPACE=0;
Empty set (0.01 sec)