MySQL Bugs: #69755: file_instances leaks with innodb_file_per

Bug #69755	file_instances leaks with innodb_file_per_table
Submitted:	16 Jul 2013 12:58	Modified:	17 Jul 2013 17:38
Reporter:	Ralf Wiebicke	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.5.31-0ubuntu0.12.04.2	OS:	Linux (Linux xxx 3.5.0-34-generic #55~precise1-Ubuntu SMP Fri Jun 7 16:25:50 UTC 2013 x86_64 x86_64 )
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
When I do the following three statements:

create table zack ( name varchar(80))
alter table zack  add name2 varchar(80)
drop table zack

then in performance_schema.file_instances two lines increase their OPEN_COUNT by one:

/var/lib/mysql/xxx/zack.ibd,wait/io/file/innodb/innodb_data_file

and

/var/lib/mysql/xxx/#sql2-337b-9.ibd,wait/io/file/innodb/innodb_data_file

I can do this again and again, and the value of OPEN_COUNT increases more and more.

When checking file descriptors using the lsof of the Linux operation system, I cannot see any corresponding file descriptors.

This problem happens only with innodb_file_per_table=ON and innodb_file_format=Barracuda. With innodb_file_per_table=OFF and innodb_file_format=Antelope no leaks are observed.

In any case, SHOW global STATUS LIKE 'perf%' shows zeros only.

How to repeat:
create table zack ( name varchar(80))
alter table zack  add name2 varchar(80)
drop table zack

The increasing values of OPEN_COUNT do happen in coincidence with a significant  slow down of our projects test suite. With innodb_file_per_table=OFF and innodb_file_format=Antelope no such slow down is observed.

The difference that you see is due to the fact that with the options set, ALTER TABLE is performed by means of a separate temporary table that requires to be opened in order to be used.

Hope that this explains the behavior.