[MDEV-16584] SP with a cursor inside a loop wastes THD memory aggressively - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.3.8, 10.4.0
Component/s: Stored routines
Labels:
- upstream-fixed

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

MDEV-16595 SP with a CONTINUE HANDLER inside a loop wastes THD memory aggressively

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-06-26 05:18

Alexander Barkov made changes - 2018-06-26 05:19

Field	Original Value	New Value
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: {code:sql} 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; {code} produced the following output: {noformat} +---------------+---------+ \| 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) {noformat} The thread leaks some memory at every loop iteration. If I now comment out the line with cursor: {code:sql} -- DECLARE cur CURSOR FOR SELECT 1 FROM DUAL; {code} and return the script, the amount of thread memory reported permanently stays near 64K and does not grow through iterations. The problem reside in this code: {code:cpp} 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); ... } {code} 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.	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: {code:sql} 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; {code} produced the following output: {noformat} +---------------+---------+ \| 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) {noformat} The thread leaks some memory at every loop iteration. If I now comment out the line with cursor: {code:sql} -- DECLARE cur CURSOR FOR SELECT 1 FROM DUAL; {code} and rerun the script, the amount of thread memory reported permanently stays near 64K and does not grow through iterations. The problem reside in this code: {code:cpp} 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); ... } {code} 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.

Alexander Barkov made changes - 2018-06-26 05:19

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:

{code:sql}
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;
{code}
produced the following output:
{noformat}
+---------------+---------+
| 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)
{noformat}

The thread leaks some memory at every loop iteration.

If I now comment out the line with cursor:
{code:sql}
      -- DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
{code}
and rerun the script, the amount of thread memory reported permanently stays near 64K and does not grow through iterations.

The problem reside in this code:
{code:cpp}
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);
  ...
}
{code}
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.

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:

{code:sql}
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;
{code}
produced the following output:
{noformat}
+---------------+---------+
| 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)
{noformat}

The thread leaks some memory at every loop iteration.

If I now comment out the line with cursor:
{code:sql}
      -- DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
{code}
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:
{code:cpp}
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);
  ...
}
{code}
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.

Alexander Barkov made changes - 2018-06-26 05:20

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:

{code:sql}
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;
{code}
produced the following output:
{noformat}
+---------------+---------+
| 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)
{noformat}

The thread leaks some memory at every loop iteration.

If I now comment out the line with cursor:
{code:sql}
      -- DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
{code}
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:
{code:cpp}
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);
  ...
}
{code}
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.

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:

{code:sql}
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;
{code}
produced the following output:
{noformat}
+---------------+---------+
| 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)
{noformat}

The thread leaks some memory at every loop iteration.

If I now comment out the line with cursor:
{code:sql}
      -- DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
{code}
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:
{code:cpp}
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);
  ...
}
{code}
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.

Alexander Barkov added a comment - 2018-06-26 08:24

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

Alexander Barkov added a comment - 2018-06-26 08:24 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

Alexander Barkov made changes - 2018-06-26 08:55

Labels

upstream

Alexander Barkov made changes - 2018-06-26 08:59

Labels

upstream

upstream-fixed

Alexander Barkov added a comment - 2018-06-26 09:01 - edited

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

Alexander Barkov added a comment - 2018-06-26 09:01 - edited 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

Alexander Barkov made changes - 2018-06-26 10:44

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2018-06-26 10:44

Assignee	Alexander Barkov [ bar ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2018-06-27 07:46

OK to push

Oleksandr Byelkin added a comment - 2018-06-27 07:46 OK to push

Oleksandr Byelkin made changes - 2018-06-27 07:46

Assignee	Oleksandr Byelkin [ sanja ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2018-06-27 08:54

Fix Version/s		10.3.8 [ 23113 ]
Fix Version/s		10.4.0 [ 23115 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Alexander Barkov made changes - 2018-06-27 09:01

Link

This issue relates to ~~MDEV-16595~~ [ ~~MDEV-16595~~ ]

Julien Fritsch made changes - 2018-07-02 16:45

Assignee

Alexander Barkov [ bar ]

Julien Fritsch [ julien.fritsch ]

Julien Fritsch made changes - 2018-07-02 17:11

Assignee

Julien Fritsch [ julien.fritsch ]

Alexander Barkov [ bar ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88058 ]

MariaDB v4 [ 154570 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-06-26 05:18

Updated:: 2018-07-02 17:11

Resolved:: 2018-06-27 08:54

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration