[MDEV-16595] SP with a CONTINUE HANDLER inside a loop wastes THD memory aggressively Created: 2018-06-27  Updated: 2018-07-30  Resolved: 2018-06-28

Status: Closed
Project: MariaDB Server
Component/s: Stored routines
Affects Version/s: 10.0, 10.2.16, 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: None

Issue Links:
Relates
relates to MDEV-16584 SP with a cursor inside a loop wastes... Closed

 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.

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

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.

Comment by Alexander Barkov [ 2018-07-30 ]

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

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