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

max_session_mem_used should kill session if query used more memory

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.2.21
    • Fix Version/s: N/A
    • Component/s: Variables
    • Labels:
      None

      Description

      Customer reported that with max_session_mem_used setting, query isn't killed automatically when it's memory usage exceeded it's configured value.

      MariaDB [test]> set global max_session_mem_used =819200;
      MariaDB [test]> create table t (id bigint not null auto_increment, ts datetime, primary key(id,ts)) engine=InnoDB;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]> insert into t(ts) values(now());
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [test]> insert into t(ts) values(now());
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [test]> replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t
      Query OK, 262144 rows affected (20.28 sec)
      Records: 262144  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> set global max_session_mem_used =819200;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t17, t t18;
      

      processlist

      +-----+-------------+-----------+------+---------+------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------+-----------+----------+-------------+---------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+------+
      | ID  | USER        | HOST      | DB   | COMMAND | TIME | STATE                    | INFO                                                                                                                                                    | TIME_MS   | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                                                                                                               | TID  |
      +-----+-------------+-----------+------+---------+------+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------+-----------+----------+-------------+---------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+------+
      | 242 | root        | localhost | test | Query   |   13 | Sending data             | replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t t16, t t17, t t18 | 13407.087 |     0 |         0 |    0.000 |     8963224 |             0 |      864 | replace into t(ts) select now() from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15,t t16, t t17, t t18 | 2723 |
      

      On the other hand, it wasn't even started with error.

      SET SESSION max_session_mem_used = 8192;'
      SELECT * FROM FROM information_schema.processlist'.
      -------------------------------------------------------------------------------------------------------------------------------------------------------
      The MariaDB server is running with the --max-thread-mem-used=8192 option so it cannot execute this statement
      -------------------------------------------------------------------------------------------------------------------------------------------------------
      

      So, when is max_session_mem_used estimated and when should it be terminated?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              allen.lee@mariadb.com Allen Lee
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.