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: | |
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
[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.