Bug #91861 The buf_LRU_free_page function may leak some memory in a particular scenario
Submitted: 1 Aug 21:47 Modified: 24 Sep 12:46
Reporter: Xiaofeng Dong Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: compression, innodb, innodb_buffer_pool, Leak, Memory

[1 Aug 21:47] Xiaofeng Dong
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.
[6 Aug 12:49] Sinisa Milivojevic
Hi,

Thank you for your report.

To tell you the truth I do not see any memory leak in your report. The fact that InnoDB thread for page cleaning uses 4 Mb of memory is expected behaviour, especially after all those DDLs.

I would recommend you to abolish the usage of the thread cache. It will be soon deprecated and thread cache tends to keep lot's of memory tied to itself. Instead, you should use our Thread Pool plugin.

Regardless of that fact, I repeat that I do not see any memory leak in your report.
[7 Sep 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".