MySQL Bugs: #87265: High memory consumption by Mysql when performance schema is enabled

Bug #87265	High memory consumption by Mysql when performance schema is enabled
Submitted:	1 Aug 2017 2:59	Modified:	8 Sep 2017 12:01
Reporter:	Shrinivasa rao	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S2 (Serious)
Version:	5.7.14	OS:	Red Hat (7.3)
Assigned to:		CPU Architecture:	Any
Tags:	Memory, performance_schema

Description:
Hello Team,

We were observing high memory consumption when performance schema is enabled along with partitioned tables in database. We have 100 tables each with 1000 partitions.
Mysql gets auto restarted due to out of memory. After memory profiling through valgrind we suspected performance_schema to be the culprit. Post disabling performance_schema, memory consumption was normal.

Details:

Mysql version: 5.7.14, 5.7.18
OS: REDHAT 7.3 64 Bit
Server: Physical

Valgrind output:

99.96% (4,428,559B) (heap allocation functions) malloc/new/new[], --alloc-fns, etc.
->98.02% (4,342,712B) 0x127B34C: pfs_malloc(PFS_builtin_memory_class*, unsigned long, int) (pfs_global.cc:59)
| ->32.32% (1,432,000B) 0x1279154: init_events_statements_history_long(unsigned long) (pfs_events_statements.cc:66)
| | ->32.32% (1,432,000B) 0x128A584: initialize_performance_schema(PFS_global_param*) (pfs_server.cc:126)
| |   ->32.32% (1,432,000B) 0x7A8374: mysqld_main(int, char**) (mysqld.cc:4428)
| |     ->32.32% (1,432,000B) 0x62DBB33: (below main) (in /usr/lib64/libc-2.17.so)
| |
| ->23.11% (1,024,000B) 0x1279242: init_events_statements_history_long(unsigned long) (pfs_events_statements.cc:82)
| | ->23.11% (1,024,000B) 0x128A584: initialize_performance_schema(PFS_global_param*) (pfs_server.cc:126)
| |   ->23.11% (1,024,000B) 0x7A8374: mysqld_main(int, char**) (mysqld.cc:4428)
| |     ->23.11% (1,024,000B) 0x62DBB33: (below main) (in /usr/lib64/libc-2.17.so)
| |
| ->23.11% (1,024,000B) 0x127927A: init_events_statements_history_long(unsigned long) (pfs_events_statements.cc:99)
| | ->23.11% (1,024,000B) 0x128A584: initialize_performance_schema(PFS_global_param*) (pfs_server.cc:126)
| |   ->23.11% (1,024,000B) 0x7A8374: mysqld_main(int, char**) (mysqld.cc:4428)
| |     ->23.11% (1,024,000B) 0x62DBB33: (below main) (in /usr/lib64/libc-2.17.so)

#####################################################

Please let us know if any more inputs required.

How to repeat:
Create 100 table with 1000 partitions each. Enable performance schema. Memory consumption increases drastically when these tables are accessed. It keeps on increasing till OOM is invoked.

I reported this myself:
https://bugs.mysql.com/bug.php?id=69490

It is the 'max_connections' setting that matter most. A buffer for each possible connection will be reserved at server startup. I also think this is fixed in 8.x so that it will only allocate the buffer when a connection is created (or something like that)

-- Peter
-- not a MySQL/Oracle person

.. but your problem looks different though.

Please try 5.7.19 where this is fixed.
Bug 25080442 : PARTITIONED TABLES USE MORE MEMORY IN 5.7 THAN 5.6

Also in your my.cnf,  please set this :

performance_schema_max_file_instances=10000

And also send us a few outputs of SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
when memory is rising..

Hey Shane,

We have currently disabled performance schema. If this is a known issue and is fixed in 5.7.19, then we will test and update.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".