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. Finally, the mysqld process was killed by Oom-killer.
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.00 sec)
3. prepare tables
sysbench --threads=50 --events=4000000 --time=600 --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=2000000 oltp_read_write.lua prepare
# It generates a tablespace in size of 561MB.
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.00 sec)
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;
# The size of the tablespace has been compressed to 360MB.
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 | 3388992 |
+-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+
1 rows in set (0.03 sec)
show variables like '%pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
show status like '%pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 180802 4:34:23 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 5420 |
| Innodb_buffer_pool_bytes_data | 66936832 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 105998 |
| Innodb_buffer_pool_pages_free | 4096 |
| Innodb_buffer_pool_pages_misc | 18446744073709550291 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 52310 |
| Innodb_buffer_pool_read_ahead_evicted | 59 |
| Innodb_buffer_pool_read_requests | 6995514 |
| Innodb_buffer_pool_reads | 11774 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 4549582 |
+---------------------------------------+--------------------------------------------------+
innodb_buffer_pool_size = 134217728
Innodb_buffer_pool_pages_total = 134217728/16/1024 = 8192
But, there are (38686-16390=22296) buf page descriptors in use.
5.
sysbench --threads=50 --events=4000000 --time=600 --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=2000000 oltp_read_write.lua run
# run 600 secends
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 | 54758 | 27947 | 4075272 |
+-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+
1 row in set (0.57 sec)
=================================
The key to the problem lies in the above fourth steps. If i test 1,2,3,5 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. Finally, the mysqld process was killed by Oom-killer. 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.00 sec) 3. prepare tables sysbench --threads=50 --events=4000000 --time=600 --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=2000000 oltp_read_write.lua prepare # It generates a tablespace in size of 561MB. 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.00 sec) 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; # The size of the tablespace has been compressed to 360MB. 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 | 3388992 | +-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+ 1 rows in set (0.03 sec) show variables like '%pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 134217728 | +-------------------------------------+----------------+ show status like '%pool%'; +---------------------------------------+--------------------------------------------------+ | Variable_name | Value | +---------------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_dump_status | Dumping of buffer pool not started | | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 180802 4:34:23 | | Innodb_buffer_pool_resize_status | | | Innodb_buffer_pool_pages_data | 5420 | | Innodb_buffer_pool_bytes_data | 66936832 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 105998 | | Innodb_buffer_pool_pages_free | 4096 | | Innodb_buffer_pool_pages_misc | 18446744073709550291 | | Innodb_buffer_pool_pages_total | 8192 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 52310 | | Innodb_buffer_pool_read_ahead_evicted | 59 | | Innodb_buffer_pool_read_requests | 6995514 | | Innodb_buffer_pool_reads | 11774 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 4549582 | +---------------------------------------+--------------------------------------------------+ innodb_buffer_pool_size = 134217728 Innodb_buffer_pool_pages_total = 134217728/16/1024 = 8192 But, there are (38686-16390=22296) buf page descriptors in use. 5. sysbench --threads=50 --events=4000000 --time=600 --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=2000000 oltp_read_write.lua run # run 600 secends 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 | 54758 | 27947 | 4075272 | +-----------+-----------------------------------+-----------------------+-------------+------------+------------------------------+ 1 row in set (0.57 sec) ================================= The key to the problem lies in the above fourth steps. If i test 1,2,3,5 steps, there is no memory leakage.