Description:
We have an instance running on mysql-5.7.22. However, we found that its physical memory and virtual memory are both growing slowly.
Through performance_shecma monitoring and code analysis, we suspect that the buf_page_alloc_descriptor called by the buf_LRU_free_page function might leak some memory。
sizeof(buf_page_t) + sizeof(ut_new_pfx_t) = 128 + 24 = 152
In performance_schema.memory_summary_by_thread_by_event_name,
select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED\G
*************************** 1. row ***************************
thread_id: 15
name: thread/innodb/page_cleaner_thread
EVENT_NAME: memory/innodb/buf0buf
COUNT_ALLOC: 805895
COUNT_FREE: 23884
CURRENT_NUMBER_OF_BYTES_USED: 118865672
(805895-23884)*152 = 118865672
How to repeat:
We can reproduce similar problems in the test environment:
1. start the instance, and it's cnf(In order to reproduce more easily, some options are modified.):
[mysqld]
transaction_isolation = READ-COMMITTED
max_allowed_packet = 16M
open_files_limit = 10240
thread_cache_size = 1536
thread_stack = 512K
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 64
query_cache_type = 0
query_cache_size = 0
innodb_buffer_pool_size = 128M
innodb_buffer_pool_instances = 1
innodb_lru_scan_depth = 4096
innodb_sort_buffer_size = 64M
innodb_log_buffer_size = 128M
innodb_purge_threads = 8
innodb_page_cleaners = 8
2. setup performance_schema
update performance_schema.setup_instruments set enabled = 'YES' where name like 'memory/%';
select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED;
Empty set (0.01 sec)
3. prepare tables
sysbench --threads=50 --events=1000000 --time=0 --thread-stack-size=512K --report-interval=2 --mysql-host=1.2.3.4 --mysql-port=56789 --mysql-user=user1 --mysql-password=passwd1 --mysql-db=test1 --tables=10 --table_size=1000000 oltp_read_write.lua prepare
4. alter tables
alter table test1.sbtest1 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest2 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest3 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest4 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest5 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest6 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest7 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest8 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest9 ROW_FORMAT=COMPRESSED;
alter table test1.sbtest10 ROW_FORMAT=COMPRESSED;
5.
select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED;
+-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+
| thread_id | name | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+
| 37 | thread/innodb/page_cleaner_thread | memory/innodb/buf0buf | 38686 | 16390 | 4459200 |
+-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+
1 rows in set (0.03 sec)
6.
sysbench --threads=50 --events=1000000 --time=0 --thread-stack-size=512K --report-interval=2 --mysql-host=1.2.3.4 --mysql-port=56789 --mysql-user=user1 --mysql-password=passwd1 --mysql-db=test1 --tables=10 --table_size=1000000 oltp_read_write.lua run
7.
select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED;
=================================
The key to the problem lies in the above fourth steps. If i test 1-3, 5-7 steps, there is no memory leakage.
Description: We have an instance running on mysql-5.7.22. However, we found that its physical memory and virtual memory are both growing slowly. Through performance_shecma monitoring and code analysis, we suspect that the buf_page_alloc_descriptor called by the buf_LRU_free_page function might leak some memory。 sizeof(buf_page_t) + sizeof(ut_new_pfx_t) = 128 + 24 = 152 In performance_schema.memory_summary_by_thread_by_event_name, select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED\G *************************** 1. row *************************** thread_id: 15 name: thread/innodb/page_cleaner_thread EVENT_NAME: memory/innodb/buf0buf COUNT_ALLOC: 805895 COUNT_FREE: 23884 CURRENT_NUMBER_OF_BYTES_USED: 118865672 (805895-23884)*152 = 118865672 How to repeat: We can reproduce similar problems in the test environment: 1. start the instance, and it's cnf(In order to reproduce more easily, some options are modified.): [mysqld] transaction_isolation = READ-COMMITTED max_allowed_packet = 16M open_files_limit = 10240 thread_cache_size = 1536 thread_stack = 512K table_open_cache = 4096 table_definition_cache = 4096 table_open_cache_instances = 64 query_cache_type = 0 query_cache_size = 0 innodb_buffer_pool_size = 128M innodb_buffer_pool_instances = 1 innodb_lru_scan_depth = 4096 innodb_sort_buffer_size = 64M innodb_log_buffer_size = 128M innodb_purge_threads = 8 innodb_page_cleaners = 8 2. setup performance_schema update performance_schema.setup_instruments set enabled = 'YES' where name like 'memory/%'; select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED; Empty set (0.01 sec) 3. prepare tables sysbench --threads=50 --events=1000000 --time=0 --thread-stack-size=512K --report-interval=2 --mysql-host=1.2.3.4 --mysql-port=56789 --mysql-user=user1 --mysql-password=passwd1 --mysql-db=test1 --tables=10 --table_size=1000000 oltp_read_write.lua prepare 4. alter tables alter table test1.sbtest1 ROW_FORMAT=COMPRESSED; alter table test1.sbtest2 ROW_FORMAT=COMPRESSED; alter table test1.sbtest3 ROW_FORMAT=COMPRESSED; alter table test1.sbtest4 ROW_FORMAT=COMPRESSED; alter table test1.sbtest5 ROW_FORMAT=COMPRESSED; alter table test1.sbtest6 ROW_FORMAT=COMPRESSED; alter table test1.sbtest7 ROW_FORMAT=COMPRESSED; alter table test1.sbtest8 ROW_FORMAT=COMPRESSED; alter table test1.sbtest9 ROW_FORMAT=COMPRESSED; alter table test1.sbtest10 ROW_FORMAT=COMPRESSED; 5. select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED; +-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+ | thread_id | name | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED | +-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+ | 37 | thread/innodb/page_cleaner_thread | memory/innodb/buf0buf | 38686 | 16390 | 4459200 | +-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+ 1 rows in set (0.03 sec) 6. sysbench --threads=50 --events=1000000 --time=0 --thread-stack-size=512K --report-interval=2 --mysql-host=1.2.3.4 --mysql-port=56789 --mysql-user=user1 --mysql-password=passwd1 --mysql-db=test1 --tables=10 --table_size=1000000 oltp_read_write.lua run 7. select a.thread_id, a.name, b.EVENT_NAME, b.COUNT_ALLOC, b.COUNT_FREE, b.CURRENT_NUMBER_OF_BYTES_USED from performance_schema.threads a, performance_schema.memory_summary_by_thread_by_event_name b where a.thread_id=b.thread_id and a.name='thread/innodb/page_cleaner_thread' and b.COUNT_ALLOC != b.COUNT_FREE order by b.CURRENT_NUMBER_OF_BYTES_USED; ================================= The key to the problem lies in the above fourth steps. If i test 1-3, 5-7 steps, there is no memory leakage.