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) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S1 (Critical)
Version:5.7 OS:Any
Assigned to: 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] Shane Bester
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] Shane Bester
Maybe the autosized performance_schema_max_file_instances is not optimal?
[27 May 2017 8:23] Shane Bester
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] Shane Bester
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] Shane Bester
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.