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

Performance degradation with new default settings for optimizer in 10.10

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.10
    • Fix Version/s: 10.10
    • Component/s: Optimizer
    • Labels:
      None

      Description

      A performance degradation can be observed with new default setting for 'optimizer_prune_level' in 10.10 for the following simple query over the dbt3 tables:

      select l_orderkey, l_linenumber, l_tax from lineitem
        where l_orderkey in (select o_orderkey from orders
                               where o_custkey in
                                     (select c_custkey from customer
                                        where c_nationkey in
                                              (select n_nationkey from nation
                                                 where n_name='PERU'))
                                     and
                                    o_orderDATE between '1992-06-30' and '1992-12-31')
              and
              (l_partkey, l_suppkey) in
              (select p_partkey, s_suppkey from part, supplier
                 where p_retailprice between 901 and 1000 and
                       s_nationkey in (select n_nationkey from nation
                                         where n_name='PERU'));
      

      The query was run on a Release build of the latest 10.10 for the the database created with these mtr commands

      --disable_warnings
      DROP DATABASE IF EXISTS dbt3_s001;
      --enable_warnings
       
      CREATE DATABASE dbt3_s001;
       
      use dbt3_s001;
       
      --disable_query_log
      --disable_result_log
      --disable_warnings
      --source include/dbt3_s001.inc
      --enable_warnings
      --enable_result_log
      --enable_query_log
       
      create index i_n_name on nation(n_name);
      analyze table nation;
      

      The default setting for 'optimizer_prune_level' is 2 10.10 while before this version the default setting for this variable was 1.
      The performance degradation for the reported query can be observed for MyISAM. For InnoDB we don't see such degradation for this particular query.
      Here's what I got when running the above query for MyISAM (all time measurements were taken from ANALYZE stmt)

      1. set optimizer_prune_level=default
        1st run: "r_total_time_ms": 5.942242744
        2nd run: "r_total_time_ms": 5.795177193
        3rd run: "r_total_time_ms": 5.734443905
      2. set optimizer_prune_level=1
        1st run: "r_total_time_ms": 4.177749859
        2nd run: "r_total_time_ms": 3.995446738
        3rd run: "r_total_time_ms": 4.384639953
      3. set optimizer_prune_level=0
        1st run: "r_total_time_ms": 4.269302585
        2nd run: "r_total_time_ms": 3.914398888
        3rd run: "r_total_time_ms": 3.921989294
      

      With the above settings for optimizer_prune_level the optimizer choses the following execution plans:

      1. set optimizer_prune_level=default
       
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using index
      condition
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using index condition
      1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    2
      1       PRIMARY lineitem        ref     PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity  i_l_suppkey5dbt3_s001.supplier.s_suppkey    100     Using where
      1       PRIMARY part    eq_ref  PRIMARY PRIMARY 4       dbt3_s001.lineitem.l_partkey    1       Using where
      1       PRIMARY orders  eq_ref|filter   PRIMARY,i_o_orderdate,i_o_custkey       PRIMARY|i_o_orderdate   4|4     dbt3_s001.lineitem.l_orderkey       1 (7%)  Using where; Using rowid filter
      1       PRIMARY customer        eq_ref  PRIMARY,i_c_nationkey   PRIMARY 4       dbt3_s001.orders.o_custkey      1       Using where
       
      2. set optimizer_prune_level=1
       
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using index condition
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using index condition
      1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    2
      1       PRIMARY lineitem        ref     PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity  i_l_suppkey5dbt3_s001.supplier.s_suppkey    100     Using where
      1       PRIMARY orders  eq_ref|filter   PRIMARY,i_o_orderdate,i_o_custkey       PRIMARY|i_o_orderdate   4|4     dbt3_s001.lineitem.l_orderkey       1 (7%)  Using where; Using rowid filter
      1       PRIMARY part    eq_ref  PRIMARY PRIMARY 4       dbt3_s001.lineitem.l_partkey    1       Using where
      1       PRIMARY customer        eq_ref  PRIMARY,i_c_nationkey   PRIMARY 4       dbt3_s001.orders.o_custkey      1       Using where
       
      3. set optimizer_prune_level=0
       
       id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using index condition
      1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    2
      1       PRIMARY lineitem        ref     PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity  i_l_suppkey5dbt3_s001.supplier.s_suppkey    100
      1       PRIMARY orders  eq_ref|filter   PRIMARY,i_o_orderdate,i_o_custkey       PRIMARY|i_o_orderdate   4|4     dbt3_s001.lineitem.l_orderkey       1 (7%)  Using where; Using rowid filter
      1       PRIMARY customer        eq_ref  PRIMARY,i_c_nationkey   PRIMARY 4       dbt3_s001.orders.o_custkey      1       Using where
      1       PRIMARY nation  eq_ref|filter   PRIMARY,i_n_name        PRIMARY|i_n_name        4|26    dbt3_s001.customer.c_nationkey  1 (4%)  Using where; Using rowid filter
      1       PRIMARY part    ALL     PRIMARY NULL    NULL    NULL    200     Using where; Using join buffer (flat, BNL join) 
      

      We see that with different settings for optimizer_prune_level the optimizer chooses different join orders:
      1. set optimizer_prune_level=default
      nation, nation, supplier, lineitem, part, orders, customer
      2. set optimizer_prune_level=1
      nation, nation, supplier, lineitem, orders, part, customer
      3. set optimizer_prune_level=0
      nation, supplier, lineitem, orders, customer, nation, part

      For InnoDB I got

      1. set optimizer_prune_level=default
        1st run: "r_total_time_ms": 2.05392333
        2nd run: "r_total_time_ms": 2.169093801
        3rd run   "r_total_time_ms": 1.988759027
      2. set optimizer_prune_level=1
        1st run: "r_total_time_ms": 1.904479043
        2nd run: "r_total_time_ms": 2.059541392
        3rd run: "r_total_time_ms": 1.961143374
      3. set optimizer_prune_level=0
        1st run: "r_total_time_ms": 1.391450822
        2st run: "r_total_time_ms": 1.450423518
        3rd run: "r_total_time_ms": 1.565808954
      

      For InnoDB with the above settings for optimiser_prune_level the optimizer chooses the following execution plans:

      1. set optimizer_prune_level=default
       
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using where; Using index
      1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       Using index
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using where; Using index
      1       PRIMARY customer        ref     PRIMARY,i_c_nationkey   i_c_nationkey   5       dbt3_s001.nation.n_nationkey    1       Using index
      1       PRIMARY orders  ref     PRIMARY,i_o_orderdate,i_o_custkey       i_o_custkey     5       dbt3_s001.customer.c_custkey    1       Using where
      1       PRIMARY lineitem        ref     PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity  PRIMARY 4  dbt3_s001.orders.o_orderkey      1       Using where
      1       PRIMARY part    eq_ref  PRIMARY PRIMARY 4       dbt3_s001.lineitem.l_partkey    1       Using where
       
      2. set optimizer_prune_level=1
       
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using where; Using index
      1       PRIMARY supplier        ref     PRIMARY,i_s_nationkey   i_s_nationkey   5       dbt3_s001.nation.n_nationkey    1       Using index
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using where; Using index
      1       PRIMARY customer        ref     PRIMARY,i_c_nationkey   i_c_nationkey   5       dbt3_s001.nation.n_nationkey    1       Using index
      1       PRIMARY orders  ref     PRIMARY,i_o_orderdate,i_o_custkey       i_o_custkey     5       dbt3_s001.customer.c_custkey    1       Using where
      1       PRIMARY lineitem        ref     PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity  PRIMARY 4  dbt3_s001.orders.o_orderkey      1       Using where
      1       PRIMARY part    eq_ref  PRIMARY PRIMARY 4       dbt3_s001.lineitem.l_partkey    1       Using where
       
      3. set optimizer_prune_level=0
       
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using where; Using index
      1       PRIMARY customer        ref     PRIMARY,i_c_nationkey   i_c_nationkey   5       dbt3_s001.nation.n_nationkey    1       Using index
      1       PRIMARY orders  ref     PRIMARY,i_o_orderdate,i_o_custkey       i_o_custkey     5       dbt3_s001.customer.c_custkey    1       Using where
      1       PRIMARY lineitem        ref     PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity  PRIMARY 4  dbt3_s001.orders.o_orderkey      1       Using where
      1       PRIMARY part    eq_ref  PRIMARY PRIMARY 4       dbt3_s001.lineitem.l_partkey    1       Using where
      1       PRIMARY nation  ref     PRIMARY,i_n_name        i_n_name        26      const   1       Using where; Using index
      1       PRIMARY supplier        eq_ref  PRIMARY,i_s_nationkey   PRIMARY 4       dbt3_s001.lineitem.l_suppkey    1       Using where
      analyze format=json select l_orderkey, l_linenumber, l_tax from lineitem
      

      Here with different settings for optimizer_prune_level we see that the optimizer chooses such join orders:
      1. set optimizer_prune_level=default
      nation, supplier, nation, customer, orders, lineitem, part
      2. set optimizer_prune_level=1
      nation, supplier, nation, customer, orders, lineitem, part
      3. set optimizer_prune_level=0
      nation, customer, orders, lineitem, part, nation, supplier

      Here for Innodb we don't see that the execution plans for settings of optimizer_prune_level to 2 (default) and 1 differ. However setting of this variable to 0 gives us a better plan.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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