Show InnoDB table overhead (free space)

[garas]

Currently, phpMyAdmin shows - for InnoDB and some value for MyISAM in Overhead column of table list.

phpMyAdmin could fetch free space of InnoDB and display it in Overhead column.

Like for MyISAM tables, if table has overhead, phpMyAdmin could show link to OPTIMIZE to rebuild table and reclaim disk space.

Table data, index and free space can be retrieved by running

SELECT `TABLE_SCHEMA`, `TABLE_NAME`, `DATA_LENGTH`, `INDEX_LENGTH`, `DATA_FREE`
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = 'dbname'

• phpMyAdmin version: 4.7.9
• Database: MySQL 5.6.41-log
• innodb_file_per_table=ON

[bahl24]

@garas Refer to the following post about clearing overhead in InnoDB
https://dba.stackexchange.com/questions/24773/mysql-innodb-does-daily-optimization-improve-performance
It is not same as MyISAM, if you can post a way on how to clear in InnoDB, I will be happy to help.

[garas]

I agree that reclaiming few percents overhead is overkill, it won't improve anything and sometimes MySQL reports same overhead after OPTIMIZE.

But it might be useful when overhead is large (50% or more) and it can reclaim much space.

I've reported this issue because I've overlooked that some tables had high overhead because I couldn't see that in phpMyAdmin. After I've optimized tables, they shrank 4GB to 800MB.

Maybe display overhead if it is over some (configurable) percentage, so users would be suggested only when there is real improvement possibility.

[bahl24]

Ok, let me work on it. But optimise won't work with innodb tables.

[garas]

It works with innodb_file_per_table=ON

[williamdes]

with innodb_file_per_table=OFF Effective shows a negative value using percona 5.5.61-38.13 server

[achantinura]

The fix should be easily be done by removing the IF function from the sql here https://github.com/phpmyadmin/phpmyadmin/blob/master/libraries/classes/DatabaseInterface.php#L937

[github-actions]

This issue has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.