FILE_INSTANCES #SQL-IB3129987-252773.IBD

Description:- When a Innodb table is altered numerous times
memory growth is observed for PFS file instances.

Analysis:- The temporary IBD file instance created during
ALTER table (using copy algorithm) is not deleted from PFS.
For every ALTER TABLE operation, a residue IBD file instance
is created which causes memory growth.
This problem is not restricted to Innodb tables alone but
also other SE which uses copy algorithm for ALTER TABLE
operation. Following is the order of file creation during
the ALTER operation which uses copy algorithm :-

a) A temporary FRM file (example: temp1.frm) is created and
   the corresponding file instance entry is made.
b) File t1.frm  is renamed to temp2.frm and the file
   instance entry in PFS is not updated.
c) File temp1.frm is renamed to t1.frm and the file instance
   entry in PFS is not updated.
d) File temp2.frm is deleted and the file instance entry is
   not updated since no corresponding entry is found in PFS.

Similar sequence is observed for MYI/MYD/IBD/.. files. PFS
file instances are not updated during the file rename
operation which in turn triggers memory growth. A RENAME
operation performed on a table will also trigger similar
issue. Even after the rename, FRM/IBD/MYI/MYD... files
instance names are not updated in the PFS.

Fix:- A new performance schema API "rename_file_close_wait"
is introduced to handle the rename of a file, which will
take care of adjusting the hash by name. The new API does
the following operations:
- lf_hash_search to find the old instrumented file.
- lf_hash_delete to remove the old name.
- fix the file name in PSI_file.
- lf_hash_insert to add the new name.