Description:
When optimizing a large table, the total_data_length is reduced before the free_data_length in information_schema.tables.
The following is an example of what I saw while optimizing one table from a large database.
This is before the optimization.
mysql> SELECT IFNULL(SUM(DATA_LENGTH), 0) AS total_data_length, IFNULL(SUM(DATA_FREE), 0) AS free_data_length, IFNULL(SUM(INDEX_LENGTH), 0) AS index_length FROM information_schema.tables WHERE table_schema = 'f5em_extern';
+-------------------+------------------+--------------+
| total_data_length | free_data_length | index_length |
+-------------------+------------------+--------------+
| 7190250680 | 1643900420 | 4388337664 |
+-------------------+------------------+--------------+
I saw this during the optimization:
mysql> SELECT IFNULL(SUM(DATA_LENGTH), 0) AS total_data_length, IFNULL(SUM(DATA_FREE), 0) AS free_data_length, IFNULL(SUM(INDEX_LENGTH), 0) AS index_length FROM information_schema.tables WHERE table_schema = 'f5em_extern';
+-------------------+------------------+--------------+
| total_data_length | free_data_length | index_length |
+-------------------+------------------+--------------+
| 5962448936 | 1643900420 | 4388337664 |
+-------------------+------------------+--------------+
1 row in set (1.79 sec)
I saw this at the end of the optimization:
mysql> SELECT IFNULL(SUM(DATA_LENGTH), 0) AS total_data_length, IFNULL(SUM(DATA_FREE), 0) AS free_data_length, IFNULL(SUM(INDEX_LENGTH), 0) AS index_length FROM information_schema.tables WHERE table_schema = 'f5em_extern';
+-------------------+------------------+--------------+
| total_data_length | free_data_length | index_length |
+-------------------+------------------+--------------+
| 5962448936 | 416611940 | 3990305792 |
+-------------------+------------------+--------------+
1 row in set (0.05 sec)
Notice that total_data_length is reduced at some point before free_data_length is reduced. Is seems like these should be reduced at the same time. This gives the user an inaccurate view of what is currently happening with the database.
How to repeat:
Optimize a large table that has at least some free_data_length space.
Suggested fix:
The total_data_length and free_data_length should be updated together when a table optimization completes.
