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

SP with a cursor inside a loop wastes THD memory aggressively

    Details

      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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: