Bug #80772 Excessive memory used in memory/innodb/os0file starting 5.7.8
| Submitted: | 17 Mar 2016 1:34 | Modified: | 25 May 2016 12:38 |
|---|---|---|---|
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.7.8+,8.0.0, 5.7.11, 5.7.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[17 Mar 2016 1:37]
Morgan Tocker
Only configuration setting changed from default: performance-schema-instrument='memory/%=COUNTED'
[17 Mar 2016 9:49]
MySQL Verification Team
Hello Morgan, Thank you for the report. Thanks, Umesh
[17 Mar 2016 9:50]
MySQL Verification Team
-- 5.7.7-rc mysql> select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+---------------------+------------+-------------+---------------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+---------------------+------------+-------------+---------------------+ | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | . | memory/innodb/os0file | 37 | 521.77 KiB | 14.10 KiB | 38 | 1.53 MiB | 41.10 KiB | -- 5.7.11 mysql> select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | memory/innodb/buf_buf_pool | 16 | 2.05 GiB | 131.06 MiB | 16 | 2.05 GiB | 131.06 MiB | | memory/innodb/os0file | 2799 | 681.06 MiB | 249.16 KiB | 2800 | 682.08 MiB | 249.45 KiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | -- 5.7.13 +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | memory/innodb/os0file | 2799 | 681.06 MiB | 249.16 KiB | 2800 | 682.08 MiB | 249.45 KiB | | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | -- 5.8.0 mysql> select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | memory/innodb/os0file | 2799 | 681.06 MiB | 249.16 KiB | 2800 | 682.08 MiB | 249.45 KiB | | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 32.01 MiB | 3.56 MiB | 9 | 32.01 MiB | 3.56 MiB |
[17 Mar 2016 22:29]
Sunny Bains
It is not a memory leak but a bad design by me :-). We will use a capped pool of pages, instead of a page per IO slot. These scratch pages are required for the transformations. The idea was to avoid malloc/free on every transformation.
[18 Mar 2016 12:14]
Morgan Tocker
Updated synopsis based on feedback from Sunny.
[29 Apr 2016 3:01]
Morgan Tocker
There is a partial workaround available for this issue. By setting io threads to a lower number, InnoDB will reduce the number of IO slots required. i.e.
innodb-read-io-threads=1
innodb-write-io-threads=1
I have verified that this works on 5.7.12 (where the impact of this bug is slightly higher than prior releases):
# default
mysql> select * from sys.memory_global_by_current_bytes where event_name = 'memory/innodb/os0file'\G
*************************** 1. row ***************************
event_name: memory/innodb/os0file
current_count: 2799
current_alloc: 681.06 MiB
current_avg_alloc: 249.16 KiB
high_count: 2800
high_alloc: 682.08 MiB
high_avg_alloc: 249.45 KiB
1 row in set (0.01 sec)
# innodb-read-io-threads=1
# innodb-write-io-threads=1
mysql> select * from sys.memory_global_by_current_bytes where event_name = 'memory/innodb/os0file'\G
*************************** 1. row ***************************
event_name: memory/innodb/os0file
current_count: 1263
current_alloc: 297.03 MiB
current_avg_alloc: 240.82 KiB
high_count: 1264
high_alloc: 298.05 MiB
high_avg_alloc: 241.45 KiB
1 row in set (0.01 sec)
This is a workaround that should not be used once the issue is resolved. There are potential benefits to using multiple threads for read/write.
[9 May 2016 14:08]
Daniel Price
Posted by developer: Fixed as of the upcoming 5.7.14 release, and here's the changelog entry: Memory was allocated to I/O slots unnecessarily. Thank you for the bug report.
[10 May 2016 13:47]
Daniel Price
Posted by developer: The changelog entry was revised as follows: Fixed as of the upcoming 5.7.14 release, and here's the changelog entry: Memory was allocated to I/O slots unnecessarily, causing an apparent memory leak.
[16 May 2016 11:06]
Daniel Price
Posted by developer: Fix was pushed to 5.7.13. The changelog entry was revised as follows: Fixed as of the upcoming 5.7.13 release, and here's the changelog entry: Memory was allocated to I/O slots unnecessarily, causing an apparent memory leak.
[18 Jun 2016 21:36]
Omer Barnir
Posted by developer: Reported version value updated to reflect release name change from 5.8 to 8.0
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.064 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: I've tracked down a memory leak using performance_schema, which seems to have been introduced in MySQL 5.7.8 - at the same time that transparent page compression was introduced. I first noticed it on 5.7.11 on my blog server, which is just a simple wordpress install - using defaults *except* turning pfs memory instrumentation on: mysql> select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | memory/innodb/os0file | 2799 | 681.06 MiB | 249.16 KiB | 2800 | 682.08 MiB | 249.45 KiB | | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | How to repeat: It can be repeated on various versions - except 5.7.7. Using more or less default config, and running query straight after startup: 5.7.7: mysql [localhost] {msandbox} ((none)) > select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+---------------------+------------+-------------+---------------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+---------------------+------------+-------------+---------------------+ | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | | memory/performance_schema/events_statements_history_long | 1 | 13.50 MiB | 13.50 MiB | 1 | 13.50 MiB | 13.50 MiB | .. | memory/innodb/os0file | 27 | 251.79 KiB | 9.33 KiB | 29 | 1.79 MiB | 63.30 KiB | 5.7.8: mysql [localhost] {msandbox} ((none)) > select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+---------------------+------------+-------------+----------------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+---------------------+------------+-------------+----------------------+ | memory/innodb/os0file | 2666 | 332.56 MiB | 127.74 KiB | 2667 | 333.58 MiB | 128.08 KiB | | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | 5.7.9: mysql [localhost] {msandbox} ((none)) > select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | memory/innodb/os0file | 2666 | 332.56 MiB | 127.74 KiB | 2667 | 333.58 MiB | 128.08 KiB | | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | 5.8.0: mysql [localhost] {msandbox} ((none)) > select * from sys.memory_global_by_current_bytes; +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +--------------------------------------------------------------------------------+---------------+---------------+-------------------+------------+-------------+----------------+ | memory/innodb/os0file | 2794 | 681.06 MiB | 249.61 KiB | 2795 | 682.08 MiB | 249.89 KiB | | memory/innodb/buf_buf_pool | 1 | 131.06 MiB | 131.06 MiB | 1 | 131.06 MiB | 131.06 MiB | | memory/innodb/log0log | 9 | 16.01 MiB | 1.78 MiB | 9 | 16.01 MiB | 1.78 MiB | Suggested fix: Make memory/innodb/os0file take less memory.