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

Details

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

Description

This problem is similar to ~~MDEV-16584~~, but now for condition handlers.
This script:

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  DECLARE i INT DEFAULT 1;

  WHILE i <= 500000

DO

    BEGIN

      DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x=1;

      IF (i % 100000 = 0) THEN

        SHOW STATUS LIKE 'Memory_used';

      END IF;

    END;

    SET i=i+1;

  END WHILE;

END;

$$

DELIMITER ;

CALL p1;

returns the following output:

+---------------+---------+

| Variable_name | Value   |

+---------------+---------+

| Memory_used   | 5664384 |

+---------------+---------+

1 row in set (2.36 sec)

+---------------+----------+

| Variable_name | Value    |

+---------------+----------+

| Memory_used   | 11264384 |

+---------------+----------+

1 row in set (4.67 sec)

+---------------+----------+

| Variable_name | Value    |

+---------------+----------+

| Memory_used   | 16864384 |

+---------------+----------+

1 row in set (6.96 sec)

+---------------+----------+

| Variable_name | Value    |

+---------------+----------+

| Memory_used   | 22464384 |

+---------------+----------+

1 row in set (9.26 sec)

+---------------+----------+

| Variable_name | Value    |

+---------------+----------+

| Memory_used   | 28064384 |

+---------------+----------+

1 row in set (11.59 sec)

Notice, thread memory usage grows through iterations.

Attachments

Issue Links

relates to

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

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2018-06-28 12:32 - edited

This script:

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 CONTINUE HANDLER FOR SQLSTATE '23000' SET @x=1;

      DECLARE CONTINUE HANDLER FOR SQLSTATE '23001' SET @x=1;

      DECLARE CONTINUE HANDLER FOR SQLSTATE '23002' SET @x=1;

      DECLARE CONTINUE HANDLER FOR SQLSTATE '23003' SET @x=1;

      IF (mem_used_cur >= mem_used_old * 1.1) 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;

returns with the following error:

ERROR 1644 (45000): Memory leak detected: i=118 mem_used_old=109224 mem_used_cur=125568

It should return without errors.

Alexander Barkov added a comment - 2018-06-28 12:32 - edited This script: 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 CONTINUE HANDLER FOR SQLSTATE '23000' SET @x=1; DECLARE CONTINUE HANDLER FOR SQLSTATE '23001' SET @x=1; DECLARE CONTINUE HANDLER FOR SQLSTATE '23002' SET @x=1; DECLARE CONTINUE HANDLER FOR SQLSTATE '23003' SET @x=1; IF (mem_used_cur >= mem_used_old * 1.1) 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; returns with the following error: ERROR 1644 (45000): Memory leak detected: i=118 mem_used_old=109224 mem_used_cur=125568 It should return without errors.

Alexander Barkov added a comment - 2018-07-30 10:46

The patch was pushed with a wrong MDEV in the comment:

commit 724a5105cba31fe48bd0a2754d074d8942b21153

Author: Alexander Barkov <bar@mariadb.com>

Date:   Thu Jun 28 16:55:42 2018 +0400

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

Alexander Barkov added a comment - 2018-07-30 10:46 The patch was pushed with a wrong MDEV in the comment: commit 724a5105cba31fe48bd0a2754d074d8942b21153 Author: Alexander Barkov <bar@mariadb.com> Date: Thu Jun 28 16:55:42 2018 +0400 MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-06-27 09:00

Updated:: 2018-07-30 10:46

Resolved:: 2018-06-28 12:56

Git Integration

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

MariaDB Server