Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
The problem is repeatable starting from 10.0 with help of the Memory_used status variable, but the excessive memory consumption probably also takes place in 5.5 (which does not have Memory_used).
This script:
DROP PROCEDURE IF EXISTS p1; |
DELIMITER $$
|
CREATE PROCEDURE p1() |
BEGIN
|
DECLARE i INT DEFAULT 1; |
WHILE i <= 500000
|
DO
|
BEGIN |
DECLARE cur CURSOR FOR SELECT 1 FROM DUAL; |
IF (i % 100000 = 0) THEN |
SHOW STATUS LIKE 'Memory_used'; |
END IF; |
END; |
SET i=i+1; |
END WHILE; |
END; |
$$
|
DELIMITER ;
|
CALL p1;
|
produced the following output:
+---------------+---------+
|
| Variable_name | Value |
|
+---------------+---------+
|
| Memory_used | 7391264 |
|
+---------------+---------+
|
1 row in set (2.09 sec)
|
|
|
+---------------+----------+
|
| Variable_name | Value |
|
+---------------+----------+
|
| Memory_used | 14459048 |
|
+---------------+----------+
|
1 row in set (4.19 sec)
|
|
|
+---------------+----------+
|
| Variable_name | Value |
|
+---------------+----------+
|
| Memory_used | 21746936 |
|
+---------------+----------+
|
1 row in set (6.21 sec)
|
|
|
+---------------+----------+
|
| Variable_name | Value |
|
+---------------+----------+
|
| Memory_used | 29132648 |
|
+---------------+----------+
|
1 row in set (8.24 sec)
|
|
|
+---------------+----------+
|
| Variable_name | Value |
|
+---------------+----------+
|
| Memory_used | 36045544 |
|
+---------------+----------+
|
1 row in set (10.25 sec)
|
The thread leaks some memory at every loop iteration.
If I now comment out the line with cursor:
-- DECLARE cur CURSOR FOR SELECT 1 FROM DUAL; |
and rerun the script, the amount of thread memory reported permanently stays near 64K and does not grow through iterations.
The problem resides in this code:
bool sp_rcontext::push_cursor(THD *thd, sp_lex_keeper *lex_keeper) |
{
|
/* |
We should create cursors in the callers arena, as
|
it could be (and usually is) used in several instructions.
|
*/
|
sp_cursor *c= new (callers_arena->mem_root) sp_cursor(thd, lex_keeper); |
...
|
}
|
At the the push_cursor() call time, callers_area points to THD::main_mem_root, which is freed only after the SP instructions loop.
Cursors should be created at some different memory root.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
This problem seems to be fixed in the upstream. In MySQL-8.0, sp_cursor is allocated in the system heap (rather than in the routine memory root) and is freed in sp_inst_cpop::execute().
The relevant upstream change:
BUG#16857395 - EXCESSIVE MEMORY USAGE AFTER REPEATED TRIGGER
EXCEPTION HANDLERS
As this script demonstrates, the original thread memory grows from 87K to 175K after around 685 iterations:
DELIMITER $$BEGINWHILE i <= 5000DO$$DELIMITER ;CALL p1;ERROR 1644 (45000): Memory leak detected: i=685 mem_used_old=87544 mem_used_cur=175096