Calculating the InnoDB free space - part 2

This is part 2, you can find part 1 here.

So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
This is Bug #36312.

Example:

mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,
    -> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
    -> P.DATA_FREE AS P_DATA_FREE FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT JOIN INFORMATION_SCHEMA.PARTITIONS P ON P.TABLE_SCHEMA=T.TABLE_SCHEMA 
    -> AND P.TABLE_NAME=T.TABLE_NAME 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES IBT 
    -> ON IBT.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME) 
    -> OR IBT.NAME=CONCAT(T.TABLE_SCHEMA,'/',T.TABLE_NAME,'#P#',P.PARTITION_NAME) 
    -> LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_DATAFILES IBD 
    -> ON IBD.SPACE=IBT.SPACE WHERE ENGINE='InnoDB' ORDER BY T.TABLE_SCHEMA,T.TABLE_NAME;
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
| TABLE_NAME                 | PART | SPACE | PATH                              | T_DATA_FREE | P_DATA_FREE |
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
| innodbfreespacetest.t1     | NULL |     6 | ./innodbfreespacetest/t1.ibd      |     4194304 |     4194304 |
| innodbfreespacetest.t11    | p2   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p1   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p0   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p4   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t11    | p3   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t13    | NULL |    46 | ./innodbfreespacetest/t13.ibd     |     4194304 |     4194304 |
| innodbfreespacetest.t2     | NULL |     0 | NULL                              |    80740352 |    80740352 |
| innodbfreespacetest.t5     | p1   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p0   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p4   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p3   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t5     | p2   |     0 | NULL                              |   403701760 |    80740352 |
| innodbfreespacetest.t6     | p4   |    15 | ./innodbfreespacetest/t6#P#p4.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p3   |    14 | ./innodbfreespacetest/t6#P#p3.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p2   |    13 | ./innodbfreespacetest/t6#P#p2.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p1   |    12 | ./innodbfreespacetest/t6#P#p1.ibd |           0 |           0 |
| innodbfreespacetest.t6     | p0   |    11 | ./innodbfreespacetest/t6#P#p0.ibd |           0 |           0 |
| mysql.innodb_index_stats   | NULL |     2 | ./mysql/innodb_index_stats.ibd    |           0 |           0 |
| mysql.innodb_table_stats   | NULL |     1 | ./mysql/innodb_table_stats.ibd    |           0 |           0 |
| mysql.slave_master_info    | NULL |     4 | ./mysql/slave_master_info.ibd     |           0 |           0 |
| mysql.slave_relay_log_info | NULL |     3 | ./mysql/slave_relay_log_info.ibd  |           0 |           0 |
| mysql.slave_worker_info    | NULL |     5 | ./mysql/slave_worker_info.ibd     |           0 |           0 |
+----------------------------+------+-------+-----------------------------------+-------------+-------------+
23 rows in set (0.05 sec)

This example is on MySQL 5.6.10.
Tables t1 and t13 have their own tablespace.
Table t2 is in the system tablespace.
Tables t5 and t11 are partitioned and in the system tablespace, these tables show the real DATA_FREE multiplied by the number of partitions. The DATA_FREE for individual partitions is correct.
 
For some old 5.1 versions the DATA_FREE might be show in kilobytes instead of bytes. (and there is no column in which the measurement unit size is stored)