Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16595

SP with a CONTINUE HANDLER inside a loop wastes THD memory aggressively

    XMLWordPrintable

Details

    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

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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