| 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
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.