0

We have recently been undertaking a purge of data from our databases, which has left some tables with huge amounts of free space (in some cases hunderd of GB's). So I have started a process of going around and optimizing those tables as and when I can to reclaim that space.

In MySQL 5.7 after the optimize completes, the DATA_FREE value in information_schema.tables is shrunk to reflect the fact that the space has been re-claimed and the DATA_LENGTH + INDEX_LENGTH (roughly) matches the size of the ibd file on the server.

However, I have also been upgrading to MySQL 8.0, and have just completed an optimize on here. But the information_schema.tables.data_free value still shows the same as before the optimize. The actual ibd file has shrunk, so I know it has worked. I'm trying to figure out:

  1. is this a new feature of MySQL 8 or a Bug
  2. how can I force information_schema.tables to update it's values to be correct.

I have tried looking through release notes and searching the web but can't find any reference to the change in the way table works.

I have also tried running analyze table, optimize table (again), restarting the databases etc, all to no avail.

Has anyone else come across this, or can shed some light on what is happening it would be appreciated.

1 Answer 1

0

Apparently, this is all to do with the new information_schema_stats_expiry which is set to 24 hours by default.

These values are now cached rather than updated live, so you either have to set information_schema_stats_expiry = 0 or wait for them to expire.

I can confirm, that after 24 hours, the DATA_FREE value updated itself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.