Calculating the InnoDB free space

Recently someone asked my if it's possible to find the total free space within InnoDB. I thought this would be very easy as the INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we could just use SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES couldn't we?
 
So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
 
The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
 
If innodb_file_per_table was always disabled then this query probably reports the correct free space:

SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;

This is because all tables will share 1 tablespace.
 
If innodb_file_per_table was always enabled (new default for 5.6!) then this would report the free space:

SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB';

This is because eache table will have it's own tablespace.
 
But how could we combine these two?
 
To give this a try I create a MySQL 5.6.10 sandbox with 4 tables of which only 2 have their own tablespace.
 
My first try is to use my udf_fileexists UDF:
 

mysql> SELECT TABLE_NAME,DATA_FREE,
    -> udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) AS ibd_file 
    -> FROM information_schema.tables WHERE ENGINE='InnoDB';
+----------------------+-----------+----------+
| TABLE_NAME           | DATA_FREE | ibd_file |
+----------------------+-----------+----------+
| t1                   |   4194304 |        1 |
| t2                   |   4194304 |        1 |
| t3                   |  66060288 |        0 |
| t4                   |  66060288 |        0 |
| innodb_index_stats   |         0 |        1 |
| innodb_table_stats   |         0 |        1 |
| slave_master_info    |         0 |        1 |
| slave_relay_log_info |         0 |        1 |
| slave_worker_info    |         0 |        1 |
+----------------------+-----------+----------+
9 rows in set (0.02 sec)

mysql> SELECT (SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE 
    -> NOT udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) 
    -> AND ENGINE='InnoDB' LIMIT 1) + (SELECT SUM(DATA_FREE) 
    -> FROM INFORMATION_SCHEMA.TABLES WHERE 
    -> udf_fileexists(CONCAT(TABLE_SCHEMA,'/',TABLE_NAME,'.ibd')) 
    -> AND ENGINE='InnoDB') AS TOTAL_DATA_FREE;
+-----------------+
| TOTAL_DATA_FREE |
+-----------------+
|        74448896 |
+-----------------+
1 row in set (0.03 sec)

 
So that works, but it requires loading a UDF. Luckily it's also possible to only use INFORMATION_SCHEMA by using the INNODB_SYS_TABLES table. This works only for 5.6 and newer.
 

mysql> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.DATA_FREE,ST.SPACE,SD.PATH 
    -> FROM INFORMATION_SCHEMA.TABLES T LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST 
    -> ON ST.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_DATAFILES SD ON SD.SPACE=ST.SPACE WHERE T.ENGINE='InnoDB';
+---------------------+----------------------+-----------+-------+----------------------------------+
| TABLE_SCHEMA        | TABLE_NAME           | DATA_FREE | SPACE | PATH                             |
+---------------------+----------------------+-----------+-------+----------------------------------+
| mysql               | innodb_table_stats   |         0 |     1 | ./mysql/innodb_table_stats.ibd   |
| mysql               | innodb_index_stats   |         0 |     2 | ./mysql/innodb_index_stats.ibd   |
| mysql               | slave_relay_log_info |         0 |     3 | ./mysql/slave_relay_log_info.ibd |
| mysql               | slave_master_info    |         0 |     4 | ./mysql/slave_master_info.ibd    |
| mysql               | slave_worker_info    |         0 |     5 | ./mysql/slave_worker_info.ibd    |
| innodbfreespacetest | t1                   |   4194304 |     6 | ./innodbfreespacetest/t1.ibd     |
| innodbfreespacetest | t2                   |   4194304 |     7 | ./innodbfreespacetest/t2.ibd     |
| innodbfreespacetest | t3                   |  66060288 |     0 | NULL                             |
| innodbfreespacetest | t4                   |  66060288 |     0 | NULL                             |
+---------------------+----------------------+-----------+-------+----------------------------------+
9 rows in set (0.02 sec)

mysql> SELECT (SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST ON 
    -> ST.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) WHERE SPACE<>0) + 
    -> (SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES ST 
    -> ON ST.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> WHERE SPACE=0 LIMIT 1) AS INNODB_FREE_GLOBAL;
+--------------------+
| INNODB_FREE_GLOBAL |
+--------------------+
|           74448896 |
+--------------------+
1 row in set (0.04 sec)

Calculating the free space can be useful if you don't have autoextend enabled on your datafiles (requires innodb_file_per_table to be disabled). It can also be useful to calculate how much space could be freed by running OPTIMIZE TABLE (requires innodb_file_per_table to be enabled).
 
Conclusion:
If you're on 5.6 you can use INFORMATION_SCHEMA and for 5.5 and earlier you have to use a UDF or have (a script) to peek in the MySQL datadir.