Bug #86482 innodb leaks memory, performance_schema file_instances #sql-ib3129987-252773.ibd

• Submitted: 27 May 2017 7:54
• Modified: 19 Oct 2017 17:49
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Performance Schema
• Severity: S1 (Critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any
• Tags: file_instances, Leak, memory leak

[27 May 2017 7:54] Shane Bester

Description:
With many DDL on innodb tables,  memory leaks endlessly with the following outputs showing increasing values:

select count(*) from performance_schema.file_instances 
  where file_name like '%#sql-ib%';

select CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_global_by_event_name 
where event_name="memory/performance_schema/file_instances";

Example contents of leaked instances:

mysql> select * from performance_schema.file_instances where file_name like '%#sql-ib%' limit 5;
+-------------------------------+--------------------------------------+------------+
| FILE_NAME                     | EVENT_NAME                           | OPEN_COUNT |
+-------------------------------+--------------------------------------+------------+
| #sql-ib3129987-2527738320.ibd | wait/io/file/innodb/innodb_data_file |          2 |
| #sql-ib3129988-2527738322.ibd | wait/io/file/innodb/innodb_data_file |          2 |
| #sql-ib3129989-2527738324.ibd | wait/io/file/innodb/innodb_data_file |          2 |
| #sql-ib3130930-2527739892.ibd | wait/io/file/innodb/innodb_data_file |          2 |
| #sql-ib3126697-2527732836.ibd | wait/io/file/innodb/innodb_data_file |          2 |
+-----------------------------------------------+----------------------+------------+
5 rows in set (0.00 sec)

How to repeat:
Have to make the testcase private for now..

[27 May 2017 7:59] MySQL Verification Team

Testcase starts like:

+-----------+------------------------+--------------------+--------------+-----------------------+-----------------+
| iteration | when                   | duration(HH:mm:ss) | total memory | file_instances memory | ibtmp instances |
+-----------+------------------------+--------------------+--------------+-----------------------+-----------------+
|         1 | 2017-05-27 09:55:48.49 | 00:00:23           |    150509064 |               2883584 |             500 |
+-----------+------------------------+--------------------+--------------+-----------------------+-----------------+
1 row in set (23.20 sec)

After a short while we see :

+-----------+------------------------+--------------------+--------------+-----------------------+-----------------+
| iteration | when                   | duration(HH:mm:ss) | total memory | file_instances memory | ibtmp instances |
+-----------+------------------------+--------------------+--------------+-----------------------+-----------------+
|         8 | 2017-05-27 09:58:47.97 | 00:03:22           |    153392744 |               5767168 |            4000 |
+-----------+------------------------+--------------------+--------------+-----------------------+-----------------+
1 row in set (3 min 22.68 sec)

[27 May 2017 8:17] MySQL Verification Team

Maybe the autosized performance_schema_max_file_instances is not optimal?

[27 May 2017 8:23] MySQL Verification Team

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-memory-model.html

"As the Performance Schema collects data, memory is allocated in the corresponding buffer. The buffer size is unbounded, and may grow with the load."

So, the default setup allows unbounded memory growth?  That's not very kind..

[14 Jun 2017 19:37] MySQL Verification Team

similar issue for replication slaves who process many relay logs...

mysql> select count(*) from performance_schema.file_instances where file_name like '%relay%' ;
+----------+
| count(*) |
+----------+
|     9153 |
+----------+
1 row in set (0.03 sec)

[14 Jul 2017 4:57] MySQL Verification Team

workaround is to set the my.cnf variable performance_schema_max_file_instances to a reasonable number such as 10000 or 20000 or so (if you're seeing it going too high).

[19 Oct 2017 17:49] Paul DuBois

Posted by developer:
 
Fixed in 5.7.21, 8.0.4, 9.0.0.

The Performance Schema could leak memory due to nondeletion of file
instances created for ALTER TABLE operations that used the table-copy
algorithm.