MySQL Bugs: #93361: memory/performance_schema/table

Bug #93361	memory/performance_schema/table_handles have momery leak!
Submitted:	27 Nov 2018 11:21	Modified:	24 May 2019 12:15
Reporter:	dakun li	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.17	OS:	CentOS
Assigned to:		CPU Architecture:	x86
Tags:	performance_scheme memory leak table_handles

Description:
We have a problem about performance_scheme.

mysql version:
5.7.15-log Source distribution

my.cnf:
performance-schema-instrument = 'memory/%=COUNTED'
performance_schema_max_digest_length = 4096 
performance_schema_max_sql_text_length = 4096
performance_schema_max_table_instances = 40000
performance_schema_digests_size = 40000
performance-schema = ON

table_open_cache_instances = 64
open_files_limit=8192
table_open_cache =10000
innodb_open_files=10000
open_files_limit=10000
open-files-limit = 10000

innodb_buffer_pool set to 40G,This is ok.but "memory/performance_schema/table_handles" take 9G as follow.we set table_open_cache=10000. 
mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 5;
+-----------------------------------------+---------------+
| event_name                              | current_alloc |
+-----------------------------------------+---------------+
| memory/innodb/buf_buf_pool              | 40.96 GiB     |
| memory/performance_schema/table_handles | 9.06 GiB      |<-----
| memory/innodb/hash0hash                 | 1.17 GiB      |
| memory/innodb/ha_innodb                 | 1.15 GiB      |
| memory/innodb/os0event                  | 1.01 GiB      |
+-----------------------------------------+---------------+

But query this table is empty;

mysql> select * from performance_schema.table_handles;
Empty set (0.05 sec)

So I not know why so much memory can be allocate. The mysqld rss is "53G",It so easy  
 OOM,and we have twice "OOM". 

How to repeat:
When set table_open_cache to max:52w ,the  memory/performance_schema/table_handles can up to 4.5G. othen ,I don't know why memory can up to 9G?

Hi,

Thank you for your bug report. However, I do not believe that this is a bug.

The problem lies in the following settings:

performance_schema_max_table_instances = 40000
performance_schema_digests_size = 40000
table_open_cache =10000
innodb_open_files=10000
open_files_limit=10000

Hence, what you should do, is to reduce the above settings to 10 % of their current settings. After that you should run MySQL server for several hours or one day and collect the same memory data again. I am sure that you will see much less memory usage then 9 Gb. Each of the performance_schema objects takes some memory for itself, frequently not negligible, so when you multiply the above numbers you get a very large memory usage.

Also, you have evidently set InnoDB buffer pool size to be close to the total RAM available on your computer. There are many other parts of MySQL that use the memory. Please, do not set buffer pool size to more then 70 % of the memory that you are dedicated to the server. Also, check out other memory settings , like the values that are allocated for each connection.

This is all explained in our Reference Manual.

+-------------------------------------------+---------------+---------------+-------------------+
| event_name                                | current_count | current_alloc | current_avg_alloc |
+-------------------------------------------+---------------+---------------+-------------------+
| memory/performance_schema/table_handles   |          1024 | 9.06 GiB      | 9.06 MiB          |
| memory/performance_schema/mutex_instances |            11 | 1.38 MiB      | 128.00 KiB        |
| memory/performance_schema/scalable_buffer |          1051 | 98.77 KiB     | 96 bytes          |
+-------------------------------------------+---------------+---------------+-------------------+

+----------------------------------------+----------+
| Variable_name                          | Value    |
+----------------------------------------+----------+
| big_tables                             | OFF      |
| innodb_file_per_table                  | ON       |
| innodb_ft_aux_table                    |          |
| innodb_ft_server_stopword_table        |          |
| innodb_ft_user_stopword_table          |          |
| innodb_table_locks                     | ON       |
| innodb_undo_tablespaces                | 0        |
| lower_case_table_names                 | 0        |
| max_heap_table_size                    | 16777216 |
| max_tmp_tables                         | 32       |
| old_alter_table                        | OFF      |
| performance_schema_max_table_handles   | -1       |
| performance_schema_max_table_instances | -1       |
| performance_schema_max_table_lock_stat | -1       |
| table_definition_cache                 | 1400     |
| table_open_cache                       | 2000     |
| table_open_cache_instances             | 16       |
| tmp_table_size                         | 16777216 |
| updatable_views_with_limit             | YES      |
+----------------------------------------+----------+

mysql> select count(*) from performance_schema.table_handles;
+----------+
| count(*) |
+----------+
|       17 |
+----------+
1 row in set (0.03 sec)

请问一下，为什么memory/performance_schema/table_handles使用了9.06G的内存呢？

Hi,

Please, follow the instructions from my last comment (including restarting server) and then query the memory usage.

the have two problem:
1,query the table is empty,but the table rows display 1048576 rows.Why, how can i get this rows info ?
mysql> show table status like 'table_handles'\G
*************************** 1. row ***************************
           Name: table_handles
         Engine: PERFORMANCE_SCHEMA
        Version: 10
     Row_format: Dynamic
           Rows: 1048576   <-----
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> select count(*) from table_handles;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.05 sec)

2,on our server,the memory up to 9G  in one minute.what performace can cause this :

Hi,

First of all, regarding number of rows etc, that is how Performance_Tables function.

Second and more important, please reply to our previous questions. Hence,  follow the instructions from my last comment  about changing some values (including restarting server) and then query the memory usage.

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".

Any updates on this ? We are facing the same problem with memory/performance_schema/table_handles.

select * from sys.memory_global_by_current_bytes where event_name = 'memory/performance_schema/table_handles' ORDER BY current_alloc desc LIMIT 10;
+-----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name                              | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/performance_schema/table_handles |          1024 | 9.06 GiB      | 9.06 MiB          |       1024 | 9.06 GiB   | 9.06 MiB       |
+-----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
1 row in set (0.02 sec)

[sys]>select count(*) from performance_schema.table_handles;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

[sys]>select @@version;
+------------+
| @@version  |
+------------+
| 5.7.20-log |
+------------+
1 row in set (0.00 sec)

Hi,

We have asked questions to which we have got no answer. Hence, we can't proceed.

we met the same situation as well. Coincidentally, the memory of memory/performance_schema/table_handles is always 9.06 GiB. It's weird.

+--------------------------------------------------------------------------------+---------------+-------------+
| event_name                                                                     | current_alloc | high_alloc  |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/performance_schema/table_handles                                        | 9.06 GiB      | 9.06 GiB    |

>show global variables like '%open%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| have_openssl               | YES   |
| innodb_open_files          | 6144  |
| open_files_limit           | 68192 |
| table_open_cache           | 6144  |
| table_open_cache_instances | 16    |
+----------------------------+-------+

| performance_schema_digests_size                          | 10000 |
| performance_schema_max_table_instances                   | -1    |