Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
8.0.18-9, 5.6.47-87.0, 5.7.29-32, 8.0.19-10
-
None
Description
When using MySQL Event Scheduler, a memory leak was detected. In production environment in 72 days, with one task of the scheduler, memory leak is about 30 GB.
The problem is identified in versions 8.0.18-9 and 8.0.19-10
The attachment contains a docker image to reproduse the problem.
Docker image contains:
- Mysql 8.0.18-9
- New Db
CREATE DATABASE serverStatus CHARACTER SET utf8mb4;
- 100 empty events
create event
updateReplicationHeartbeatEvent[0-99]
on schedule every 1 second starts current_timestamp
on completion preserve
enable
do
begin- empty event
end
- empty event
Reproduse instructions
1) Collect and launch the docker image
docker-compose up --build
2) View the initial state of memory
docker exec -it mysql mysql -u root -proot -e "SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, SUM(current_alloc/1024/1024) AS current_allocMb FROM sys.x`$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;"
Result out:
mysql: [Warning] Using a password on the command line interface can be insecure.
------------------------------------------+
code_area | current_allocMb |
------------------------------------------+
memory/innodb | 593.12500196 |
memory/performance_schema | 275.17948918 |
memory/mysys | 8.53232957 |
memory/sql | 3.96931836 |
memory/temptable | 1.00000000 |
memory/mysqld_openssl | 0.13145828 |
memory/mysqlx | 0.00335693 |
memory/myisam | 0.00066376 |
memory/csv | 0.00008392 |
memory/blackhole | 0.00008392 |
memory/vio | 0.00001526 |
------------------------------------------+
Remember or write value memory/sql = 3.96931836 MB
3) Enable Event Scheduler
docker exec -it mysql mysql -u root -proot -e "SET GLOBAL event_scheduler = ON;"
4) After 35 min see memory usage
docker exec -it mysql mysql -u root -proot -e "SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, SUM(current_alloc/1024/1024) AS current_allocMb FROM sys.x`$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;"
Result out:
mysql: [Warning] Using a password on the command line interface can be insecure.
------------------------------------------+
code_area | current_allocMb |
------------------------------------------+
memory/sql | 892.38242528 |
memory/innodb | 593.60049155 |
memory/performance_schema | 275.61708074 |
memory/mysys | 8.61237717 |
memory/temptable | 1.00000000 |
memory/mysqld_openssl | 0.13147354 |
memory/mysqlx | 0.00335693 |
memory/myisam | 0.00066376 |
memory/csv | 0.00008392 |
memory/blackhole | 0.00008392 |
memory/vio | 0.00001526 |
------------------------------------------+
memory/sql allocated 892 MB memory
5) Detail
docker exec -it mysql mysql -u root -proot -e "select event_name,current_alloc/1024/1024 from sys.x`$memory_global_by_current_bytes where event_name like 'memory/sql%' order by 2 desc limit 10;"
Result out:
mysql: [Warning] Using a password on the command line interface can be insecure.
---------------------------------------------------------+
event_name | current_alloc/1024/1024 |
---------------------------------------------------------+
memory/sql/thd::main_mem_root | 887.88725948 |
memory/sql/TABLE | 2.10419273 |
memory/sql/TABLE_SHARE::mem_root | 0.90688324 |
memory/sql/dd::String_type | 0.73744202 |
memory/sql/errmsgs | 0.31315327 |
memory/sql/plugin_mem_root | 0.29540253 |
memory/sql/tz_storage | 0.03125763 |
memory/sql/Event_basic::mem_root | 0.02517700 |
memory/sql/String::value | 0.01572418 |
memory/sql/NET::buff | 0.01563168 |
---------------------------------------------------------+
6) Disable Event Scheduler
docker exec -it mysql mysql -u root -proot -e "SET GLOBAL event_scheduler = OFF;"
7) See memory usage
docker exec -it mysql mysql -u root -proot -e "SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, SUM(current_alloc/1024/1024) AS current_allocMb FROM sys.x`$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;"
mysql: [Warning] Using a password on the command line interface can be insecure.
------------------------------------------+
code_area | current_allocMb |
------------------------------------------+
memory/innodb | 593.60026267 |
memory/performance_schema | 275.61708074 |
memory/mysys | 8.61241532 |
memory/sql | 4.66029164 |
memory/temptable | 1.00000000 |
memory/mysqld_openssl | 0.13147354 |
memory/mysqlx | 0.00335693 |
memory/myisam | 0.00066376 |
memory/csv | 0.00008392 |
memory/blackhole | 0.00008392 |
memory/vio | 0.00001526 |
------------------------------------------+
memory/sql allocated 4 MB memory
Attachments
Issue Links
- relates to
-
PS-6800 Uncontrollable memory allocation with TRIGGERS, PROCEDURES and FUNCTIONS
-
- On Hold
-