Bug #95504 memory/performance_schema/table_handles has been allocated 9.06 GiB memory
Submitted: 23 May 2019 16:58 Modified: 28 May 2019 13:29
Reporter: Young Chen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.20-log OS:CentOS (CentOS (CentOS release 6.5 (Final)))
Assigned to: CPU Architecture:x86 (x86 (Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz))

[23 May 2019 16:58] Young Chen
Description:
Hi,
We are facing a problem that table_handles has been allocated 9.06 GiB memory while there is no row can be found from the table table_handles.
The problem is  on our production master database and I don't want to restart the instance... 
However, I have no idea how to repeat it, could you please advise what kind of operation will cause this problem and how to fix it ? thanks~

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

>select * from sys.memory_global_by_current_bytes where event_name = 'memory/performance_schema/table_handles';
+-----------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| 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)

>show table status like 'table_handles'\G
*************************** 1. row ***************************
           Name: table_handles
         Engine: PERFORMANCE_SCHEMA
        Version: 10
     Row_format: Dynamic
           Rows: 1048576 --->These rows can not be find from table_handles
 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)

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

+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| performance_schema_max_table_instances | -1    |
+----------------------------------------+-------+
+---------------------------------+-------+
| performance_schema_digests_size | 10000 |
+---------------------------------+-------+
+------------------+-------+
| table_open_cache | 5000  |
+------------------+-------+
+-------------------+-------+
| innodb_open_files | 4000  |
+-------------------+-------+
+------------------+--------+
| open_files_limit | 200000 |
+------------------+--------+

How to repeat:
However, I have no idea how to repeat it, could you please advise what kind of operation will cause this problem and how to fix it ? thanks~
[23 May 2019 17:03] Young Chen
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| performance_schema_max_table_handles | -1    |
+--------------------------------------+-------+
[23 May 2019 17:09] Young Chen
SHOW STATUS LIKE 'perf%';
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Performance_schema_accounts_lost              | 0           |
| Performance_schema_cond_classes_lost          | 0           |
| Performance_schema_cond_instances_lost        | 0           |
| Performance_schema_digest_lost                | 45067519899 |
| Performance_schema_file_classes_lost          | 0           |
| Performance_schema_file_handles_lost          | 0           |
| Performance_schema_file_instances_lost        | 0           |
| Performance_schema_hosts_lost                 | 0           |
| Performance_schema_index_stat_lost            | 0           |
| Performance_schema_locker_lost                | 0           |
| Performance_schema_memory_classes_lost        | 0           |
| Performance_schema_metadata_lock_lost         | 0           |
| Performance_schema_mutex_classes_lost         | 0           |
| Performance_schema_mutex_instances_lost       | 0           |
| Performance_schema_nested_statement_lost      | 0           |
| Performance_schema_prepared_statements_lost   | 0           |
| Performance_schema_program_lost               | 0           |
| Performance_schema_rwlock_classes_lost        | 0           |
| Performance_schema_rwlock_instances_lost      | 0           |
| Performance_schema_session_connect_attrs_lost | 0           |
| Performance_schema_socket_classes_lost        | 0           |
| Performance_schema_socket_instances_lost      | 0           |
| Performance_schema_stage_classes_lost         | 0           |
| Performance_schema_statement_classes_lost     | 0           |
| Performance_schema_table_handles_lost         | 19220914729 |
| Performance_schema_table_instances_lost       | 0           |
| Performance_schema_table_lock_stat_lost       | 0           |
| Performance_schema_thread_classes_lost        | 0           |
| Performance_schema_thread_instances_lost      | 0           |
| Performance_schema_users_lost                 | 0           |
+-----------------------------------------------+-------------+
30 rows in set (0.00 sec)
[23 May 2019 17:31] MySQL Verification Team
Kindly post output of:
 SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

If the memory is accounted for there, you'll have your answer.
[24 May 2019 8:20] MySQL Verification Team
Please see comment of Shane. Thanks.
[24 May 2019 11:37] Young Chen
Thanks for your quick response! Kindly advise why performance_schema.memory is 10497908408 or how can I tune it?

Attachment: performance_status.log (application/octet-stream, text), 22.59 KiB.

[24 May 2019 11:55] MySQL Verification Team
| performance_schema | (pfs_table).size                      | 9280        |
| performance_schema | (pfs_table).count                     | 1048576     |
| performance_schema | (pfs_table).memory                    | 9730785280  |

what does your my.cnf look like?
[24 May 2019 12:03] MySQL Verification Team
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-memory-model.html
[24 May 2019 12:13] MySQL Verification Team
Your version is affected by:

https://bugs.mysql.com/bug.php?id=86482

Does it look possible?
[24 May 2019 12:20] MySQL Verification Team
that my.cnf isn't so bad. i cant see how 9G is allocated from that alone. hence something odd goes on....
[24 May 2019 13:00] Young Chen
Emm... I guess it's not the same problem....
Please see my output below:

>select count(*) from performance_schema.file_instances  where file_name like '%#sql-ib%';
+----------+
| count(*) |
+----------+
|      404 |
+----------+
1 row in set (0.00 sec)

>select * from performance_schema.memory_summary_global_by_event_name  where event_name="memory/performance_schema/file_instances";
+------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| EVENT_NAME                               | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED |
+------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| memory/performance_schema/file_instances |           1 |          0 |                   2883584 |                        0 |              0 |                  1 |               1 |                        0 |                      2883584 |                   2883584 |
+------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
1 row in set (0.02 sec)

>select * from performance_schema.file_instances where file_name like '%#sql-ib%' limit 5;
+----------------------------------------------------------------+--------------------------------------+------------+
| FILE_NAME                                                      | EVENT_NAME                           | OPEN_COUNT |
+----------------------------------------------------------------+--------------------------------------+------------+
| /data2/ctob_data/data/ctob/#sql-ib826-1839488924.ibd           | wait/io/file/innodb/innodb_data_file |          2 |
| /data2/ctob_data/data/ctob_khz_stat/#sql-ib1588-1839489516.ibd | wait/io/file/innodb/innodb_data_file |          2 |
| /data2/ctob_data/data/ctob/#sql-ib1150-1839489132.ibd          | wait/io/file/innodb/innodb_data_file |          2 |
| /data2/ctob_data/data/ctob_khz_stat/#sql-ib1372-1839489518.ibd | wait/io/file/innodb/innodb_data_file |          2 |
| /data2/ctob_data/data/ctob_khz_stat/#sql-ib1373-1839489520.ibd | wait/io/file/innodb/innodb_data_file |          2 |
+----------------------------------------------------------------+--------------------------------------+------------+
5 rows in set (0.00 sec)
[24 May 2019 13:04] Young Chen
select count(*) from performance_schema.file_instances;
+----------+
| count(*) |
+----------+
|     2668 |
+----------+
1 row in set (0.00 sec)
[28 May 2019 13:31] MySQL Verification Team
Hi Mr. Chen,

Thank you for your bug report.

I agree with you that you are not affected by the mentioned bug.

We have examined all your data. There is no bug.

What you need is tuning of your configuration, so that less memory is used.

However, that is not a bug.
[28 May 2019 13:32] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.