Description:
Hi,
We are facing a problem that table_handles is allocated 9.06 GiB memory while these 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 ORDER BY current_alloc desc LIMIT 3;
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 11 | 95.37 MiB | 8.67 MiB | 11 | 95.37 MiB | 8.67 MiB |
| memory/performance_schema/threads | 11 | 9.97 MiB | 928.00 KiB | 11 | 9.97 MiB | 928.00 KiB |
| memory/performance_schema/events_statements_summary_by_digest.tokens | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
3 rows in set (0.03 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~
Description: Hi, We are facing a problem that table_handles is allocated 9.06 GiB memory while these 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 ORDER BY current_alloc desc LIMIT 3; +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 11 | 95.37 MiB | 8.67 MiB | 11 | 95.37 MiB | 8.67 MiB | | memory/performance_schema/threads | 11 | 9.97 MiB | 928.00 KiB | 11 | 9.97 MiB | 928.00 KiB | | memory/performance_schema/events_statements_summary_by_digest.tokens | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ 3 rows in set (0.03 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~