[MDEV-16584] SP with a cursor inside a loop wastes THD memory aggressively Created: 2018-06-26  Updated: 2018-07-02  Resolved: 2018-06-27

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.3.8, 10.4.0

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed

Issue Links:
Relates
relates to MDEV-16595 SP with a CONTINUE HANDLER inside a l... Closed

 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.

 Comments   
Comment by Alexander Barkov [ 2018-06-26 ]

As this script demonstrates, the original thread memory grows from 87K to 175K after around 685 iterations:

DROP PROCEDURE IF EXISTS p1;
 
DELIMITER $$
 
CREATE PROCEDURE p1()
 
BEGIN
 
  DECLARE mem_used_old BIGINT UNSIGNED DEFAULT
 
    (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
 
     WHERE VARIABLE_NAME='MEMORY_USED');
 
  DECLARE i INT DEFAULT 1;
 
  WHILE i <= 5000
 
  DO
 
    BEGIN
 
      DECLARE msg TEXT;
 
      DECLARE mem_used_cur BIGINT UNSIGNED DEFAULT
 
        (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
 
         WHERE VARIABLE_NAME='MEMORY_USED');
 
      DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
 
      IF (mem_used_cur >= mem_used_old * 2) THEN
 
        SHOW STATUS LIKE 'Memory_used';
 
        SET msg=CONCAT('Memory leak detected: i=', i, ' mem_used_old=',mem_used_old,' mem_used_cur=', mem_used_cur);
 
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=msg;
 
      END IF;
 
    END;
 
    SET i=i+1;
 
  END WHILE;
 
END;
 
$$
 
DELIMITER ;
 
CALL p1;
 
DROP PROCEDURE p1;


ERROR 1644 (45000): Memory leak detected: i=685 mem_used_old=87544 mem_used_cur=175096

Comment by Alexander Barkov [ 2018-06-26 ]

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

Comment by Oleksandr Byelkin [ 2018-06-27 ]

OK to push

Generated at Thu Feb 08 08:30:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.