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

MariaDB does not limit memory used for range optimization

    XMLWordPrintable

Details

    • 10.2.1-1, 10.2.1-2, 5.5.50, 10.1.15, 10.2.4-1, 10.1.21

    Description

      `Unlike MySQL 5.6 (that provides eq_range_index_dive_limit server variable) and MySQL 5.7 (that additionally provides range_optimizer_max_mem_size server variable) MariaDB 10.x does NOT limit memory used for range optimization in any way (neither directly nor indirectly).

      As a result, for some queries with huge list of value in the IN () list in the WHERE clause (having number of items comparable to the number of rows in the table) MariaDB 10.x does not only execute the query longer than MySQL 5.6 and 5.7 with default settings (as it spends more time on query optimization), but also uses a lot of memory in the process (see https://bugs.mysql.com/bug.php?id=78973 for some estimations and links). With many concurrent connections running this kind of queries we may easily end up with OOM condition for the mysqld process.

      Consider a table like this:

      create table t (id bigint not null auto_increment, ts datetime, primary key(id,ts)) engine=InnoDB;
      insert into t(ts) values(now());
      insert into t(ts) values(now());
      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;
      analyze table t;
      

      and a query like this:

      SELECT * FROM t WHERE id IN (10000,10001,10002, ... 54106,54107);
      

      Run the query on different versions of MySQL and MariaDB, compare execution times and memory used in the process. It may be easier to see the memory impact on versions that do not allow to monitor memory used per session or "state" by running many copies of the same query concurrently using mysqlslap, for example.

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              valerii Valerii Kravchuk
              Votes:
              2 Vote for this issue
              Watchers:
              10 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.