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

[16 Jul 2013 12:58] Ralf Wiebicke
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
[16 Jul 2013 13:06] Ralf Wiebicke
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.
[17 Jul 2013 17:38] Sinisa Milivojevic
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.