Description:
The MySQL manual (section "Flush syntax") clearly states in all versions since (at least) 5.1:
FLUSH TABLES
Closes all open tables, forces all tables in use to be closed, and flushes the query cache. ...
FLUSH TABLES tbl_name [, tbl_name] ...
With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with no names except that the server flushes only the named tables. ...
However, for an InnoDB table this is not true, the table is not closed.
This is relevant especially on XFS: On an XFS file system, the Linux kernel automatically pre-allocates further pages to a growing file, so the free disk space becomes less and less. Closing the file is the only documented way to reclaim those pre-allocated but still unused pages.
Similar, flushing the InnoDB pages and closing the file might be important if a LVM snapshot is used to take a backup.
How to repeat:
Run the MySQL server daemon under strace control and issue a "flush table DB.innotab": There will not be a close() system call.
Alternatively, on an XFS file system, grow an InnoDB table by a loop of insert statements.
In parallel, use "ls -l --block-size=K DB/innotab.ibd" and "du -k DB/innotab.ibd" to see the file size and the space used, notice the difference.
Then, issue a "flush table DB.innotab" and re-check: The difference remains, because the data file was not closed.
Suggested fix:
Make the manual come true and let InnoDB really close the data file.
As a minimum, let the statement issue a warning saying it is a no-op for InnoDB tables, and document that in the manual.
Description: The MySQL manual (section "Flush syntax") clearly states in all versions since (at least) 5.1: FLUSH TABLES Closes all open tables, forces all tables in use to be closed, and flushes the query cache. ... FLUSH TABLES tbl_name [, tbl_name] ... With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with no names except that the server flushes only the named tables. ... However, for an InnoDB table this is not true, the table is not closed. This is relevant especially on XFS: On an XFS file system, the Linux kernel automatically pre-allocates further pages to a growing file, so the free disk space becomes less and less. Closing the file is the only documented way to reclaim those pre-allocated but still unused pages. Similar, flushing the InnoDB pages and closing the file might be important if a LVM snapshot is used to take a backup. How to repeat: Run the MySQL server daemon under strace control and issue a "flush table DB.innotab": There will not be a close() system call. Alternatively, on an XFS file system, grow an InnoDB table by a loop of insert statements. In parallel, use "ls -l --block-size=K DB/innotab.ibd" and "du -k DB/innotab.ibd" to see the file size and the space used, notice the difference. Then, issue a "flush table DB.innotab" and re-check: The difference remains, because the data file was not closed. Suggested fix: Make the manual come true and let InnoDB really close the data file. As a minimum, let the statement issue a warning saying it is a no-op for InnoDB tables, and document that in the manual.