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

Optimizer flag rowid_filter leads to long query

Details

    Description

      We noticed a slow query:

      SELECT /* ApiQueryExtLinksUsage::run */ el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501 /* ebd959f27d0c6fd989d1561b40ae8a3b db1107 enwiki 24s */
      

      This query involves the following tables:

      *************************** 1. row ***************************
             Table: pagelinks
      Create Table: CREATE TABLE `pagelinks` (
        `pl_from` int(8) unsigned NOT NULL DEFAULT 0,
        `pl_namespace` int(11) NOT NULL DEFAULT 0,
        `pl_title` varbinary(255) NOT NULL DEFAULT '',
        `pl_from_namespace` int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (`pl_from`,`pl_namespace`,`pl_title`),
        KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
        KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
      ) ENGINE=InnoDB DEFAULT CHARSET=binary
      *************************** 1. row ***************************
             Table: page
      Create Table: CREATE TABLE `page` (
        `page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `page_namespace` int(11) NOT NULL DEFAULT 0,
        `page_title` varbinary(255) NOT NULL DEFAULT '',
        `page_restrictions` tinyblob NOT NULL,
        `page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT 0,
        `page_is_new` tinyint(1) unsigned NOT NULL DEFAULT 0,
        `page_random` double unsigned NOT NULL DEFAULT 0,
        `page_touched` varbinary(14) NOT NULL DEFAULT '',
        `page_links_updated` varbinary(14) DEFAULT NULL,
        `page_latest` int(8) unsigned NOT NULL DEFAULT 0,
        `page_len` int(8) unsigned NOT NULL DEFAULT 0,
        `page_content_model` varbinary(32) DEFAULT NULL,
        `page_lang` varbinary(35) DEFAULT NULL,
        PRIMARY KEY (`page_id`),
        UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
        KEY `page_random` (`page_random`),
        KEY `page_len` (`page_len`),
        KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
      ) ENGINE=InnoDB AUTO_INCREMENT=63170169 DEFAULT CHARSET=binary
      

      The optimizer flags we had were:

      root@db1107.eqiad.wmnet[enwiki]> select @@optimizer_switch;
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | @@optimizer_switch                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      The following query has this execution plan:

      +------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
      | id   | select_type | table         | type         | possible_keys                                 | key                  | key_len | ref                          | rows     | Extra                                           |
      +------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
      |    1 | SIMPLE      | externallinks | range|filter | el_from,el_index,el_index_60,el_from_index_60 | el_index|el_index_60 | 62|62   | NULL                         | 553 (0%) | Using where; Using filesort; Using rowid filter |
      |    1 | SIMPLE      | page          | eq_ref       | PRIMARY                                       | PRIMARY              | 4       | enwiki.externallinks.el_from | 1        |                                                 |
      +------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
      2 rows in set (0.00 sec)
      

      That query was taking around 25 seconds.

      By turning off rowid_filter, the query doesn't change its query plan, but the execution time takes 0.01 to run:

      root@db1107.eqiad.wmnet[enwiki]> set session optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on';
      Query OK, 0 rows affected (0.00 sec)
       
      <query snip>
      501 rows in set (0.00 sec)
       
       
      root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryExtLinksUsage::run */ el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501 /* ebd959f27d0c6fd989d1561b40ae8a3b db1107 enwiki 24s */;
      +------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
      | id   | select_type | table         | type   | possible_keys                                 | key      | key_len | ref                          | rows | Extra                       |
      +------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
      |    1 | SIMPLE      | externallinks | range  | el_from,el_index,el_index_60,el_from_index_60 | el_index | 62      | NULL                         | 553  | Using where; Using filesort |
      |    1 | SIMPLE      | page          | eq_ref | PRIMARY                                       | PRIMARY  | 4       | enwiki.externallinks.el_from | 1    |                             |
      +------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
      2 rows in set (0.00 sec)
      
      

      Looks like that rowid_filter=ON is making the query super slow.

      This is a full optimizer trace with rowid_filter=ON:

      root@db1107.eqiad.wmnet[enwiki]> select * from information_schema.optimizer_trace\G
      *************************** 1. row ***************************
                                  QUERY: SELECT  el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501
                                  TRACE: {
        "steps": [
          {
            "join_preparation": {
              "select_id": 1,
              "steps": [
                {
                  "expanded_query": "select externallinks.el_index_60 AS el_index_60,externallinks.el_id AS el_id,`page`.page_id AS page_id,`page`.page_namespace AS page_namespace,`page`.page_title AS page_title,externallinks.el_to AS el_to from `page` join externallinks where `page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' order by externallinks.el_index_60,externallinks.el_id limit 501"
                }
              ]
            }
          },
          {
            "join_optimization": {
              "select_id": 1,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "`page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
                      }
                    ]
                  }
                },
                {
                  "table_dependencies": [
                    {
                      "table": "page",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": []
                    },
                    {
                      "table": "externallinks",
                      "row_may_be_null": false,
                      "map_bit": 1,
                      "depends_on_map_bits": []
                    }
                  ]
                },
                {
                  "ref_optimizer_key_uses": [
                    {
                      "table": "page",
                      "field": "page_id",
                      "equals": "externallinks.el_from",
                      "null_rejecting": false
                    },
                    {
                      "table": "page",
                      "field": "page_id",
                      "equals": "externallinks.el_from",
                      "null_rejecting": false
                    },
                    {
                      "table": "externallinks",
                      "field": "el_from",
                      "equals": "`page`.page_id",
                      "null_rejecting": false
                    },
                    {
                      "table": "externallinks",
                      "field": "el_from",
                      "equals": "`page`.page_id",
                      "null_rejecting": false
                    },
                    {
                      "table": "externallinks",
                      "field": "el_from",
                      "equals": "`page`.page_id",
                      "null_rejecting": false
                    }
                  ]
                },
                {
                  "rows_estimation": [
                    {
                      "table": "page",
                      "table_scan": {
                        "rows": 42067094,
                        "cost": 405050
                      }
                    },
                    {
                      "table": "externallinks",
                      "range_analysis": {
                        "table_scan": {
                          "rows": 138944656,
                          "cost": 3.03e7
                        },
                        "potential_range_indexes": [
                          {
                            "index": "PRIMARY",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "el_from",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "el_to",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "el_index",
                            "usable": true,
                            "key_parts": ["el_index"]
                          },
                          {
                            "index": "el_index_60",
                            "usable": true,
                            "key_parts": ["el_index_60", "el_id"]
                          },
                          {
                            "index": "el_from_index_60",
                            "usable": false,
                            "cause": "not applicable"
                          }
                        ],
                        "setup_range_conditions": [],
                        "group_index_range": {
                          "chosen": false,
                          "cause": "not single_table"
                        },
                        "analyzing_range_alternatives": {
                          "range_scan_alternatives": [
                            {
                              "index": "el_index",
                              "ranges": [
                                "(http://edu.utexas.cs.www./) <= (el_index) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                              ],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 553,
                              "cost": 696.95,
                              "chosen": true
                            },
                            {
                              "index": "el_index_60",
                              "ranges": [
                                "(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                              ],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 553,
                              "cost": 697.97,
                              "chosen": false,
                              "cause": "cost"
                            }
                          ],
                          "analyzing_roworder_intersect": {
                            "cause": "too few roworder scans"
                          },
                          "analyzing_index_merge_union": []
                        },
                        "chosen_range_access_summary": {
                          "range_access_plan": {
                            "type": "range_scan",
                            "index": "el_index",
                            "rows": 553,
                            "ranges": [
                              "(http://edu.utexas.cs.www./) <= (el_index) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                            ]
                          },
                          "rows_for_plan": 553,
                          "cost_for_plan": 696.95,
                          "chosen": true
                        }
                      }
                    },
                    {
                      "selectivity_for_indexes": [
                        {
                          "index_name": "el_index",
                          "selectivity_from_index": 4e-6
                        },
                        {
                          "index_name": "el_index_60",
                          "selectivity_from_index": 4e-6
                        }
                      ],
                      "selectivity_for_columns": [],
                      "cond_selectivity": 2e-11
                    }
                  ]
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "externallinks",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "range",
                            "resulting_rows": 0.0022,
                            "cost": 250.93,
                            "chosen": true,
                            "use_tmp_table": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "range",
                          "records": 0.0022,
                          "cost": 250.93,
                          "uses_join_buffering": false,
                          "filter_used": true
                        }
                      },
                      "rows_for_plan": 0.0022,
                      "cost_for_plan": 250.93,
                      "rest_of_plan": [
                        {
                          "plan_prefix": ["externallinks"],
                          "table": "page",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "eq_ref",
                                "index": "PRIMARY",
                                "rows": 1,
                                "cost": 0.0022,
                                "chosen": true
                              },
                              {
                                "type": "scan",
                                "chosen": false,
                                "cause": "cost"
                              }
                            ],
                            "chosen_access_method": {
                              "type": "eq_ref",
                              "records": 1,
                              "cost": 0.0022,
                              "uses_join_buffering": false,
                              "filter_used": false
                            }
                          },
                          "rows_for_plan": 0.0022,
                          "cost_for_plan": 250.94,
                          "estimated_join_cardinality": 0.0022
                        }
                      ]
                    },
                    {
                      "plan_prefix": [],
                      "table": "page",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 4.21e7,
                            "cost": 405050,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 4.21e7,
                          "cost": 405050,
                          "uses_join_buffering": false,
                          "filter_used": false
                        }
                      },
                      "rows_for_plan": 4.21e7,
                      "cost_for_plan": 8.82e6,
                      "pruned_by_cost": true
                    }
                  ]
                },
                {
                  "best_join_order": ["externallinks", "page"]
                },
                {
                  "table": "externallinks",
                  "range_analysis": {
                    "table_scan": {
                      "rows": 138944656,
                      "cost": 2e308
                    },
                    "potential_range_indexes": [
                      {
                        "index": "PRIMARY",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_from",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_to",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_index",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_index_60",
                        "usable": true,
                        "key_parts": ["el_index_60", "el_id"]
                      },
                      {
                        "index": "el_from_index_60",
                        "usable": false,
                        "cause": "not applicable"
                      }
                    ],
                    "setup_range_conditions": [],
                    "analyzing_range_alternatives": {
                      "range_scan_alternatives": [
                        {
                          "index": "el_index_60",
                          "ranges": [
                            "(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                          ],
                          "rowid_ordered": false,
                          "using_mrr": false,
                          "index_only": false,
                          "rows": 553,
                          "cost": 697.97,
                          "chosen": true
                        }
                      ]
                    },
                    "chosen_range_access_summary": {
                      "range_access_plan": {
                        "type": "range_scan",
                        "index": "el_index_60",
                        "rows": 553,
                        "ranges": [
                          "(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                        ]
                      },
                      "rows_for_plan": 553,
                      "cost_for_plan": 697.97,
                      "chosen": true
                    }
                  }
                },
                {
                  "attaching_conditions_to_tables": {
                    "original_condition": "`page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'",
                    "attached_conditions_computation": [],
                    "attached_conditions_summary": [
                      {
                        "table": "externallinks",
                        "attached": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'"
                      },
                      {
                        "table": "page",
                        "attached": null
                      }
                    ]
                  }
                },
                {
                  "reconsidering_access_paths_for_index_ordering": {
                    "clause": "ORDER BY",
                    "fanout": 1,
                    "read_time": 250.93,
                    "table": "externallinks",
                    "rows_estimation": 553,
                    "possible_keys": [
                      {
                        "index": "PRIMARY",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_from",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_to",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_index",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_index_60",
                        "can_resolve_order": true,
                        "updated_limit": 138944656,
                        "range_scan_time": 697.97,
                        "index_scan_time": 697.97,
                        "usable": false,
                        "cause": "cost"
                      },
                      {
                        "index": "el_from_index_60",
                        "can_resolve_order": false,
                        "cause": "order can not be resolved by key"
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "join_execution": {
              "select_id": 1,
              "steps": []
            }
          }
        ]
      }
      MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
                INSUFFICIENT_PRIVILEGES: 0
      

      Attachments

        Issue Links

          Activity

            In our environment, 10.4.13 did fix the issue.
            The original reported query being slow with this flag ON is no longer slow after upgrading to 10.4.13
            Per: https://jira.mariadb.org/projects/MDEV/versions/24223 it does include the fix for this.

            marostegui Manuel Arostegui added a comment - In our environment, 10.4.13 did fix the issue. The original reported query being slow with this flag ON is no longer slow after upgrading to 10.4.13 Per: https://jira.mariadb.org/projects/MDEV/versions/24223 it does include the fix for this.
            paulmourer paul added a comment - - edited

            I experienced the slowness related to this flag on a magento 2 database before upgrading to 10.4.13 version (confirmed by disabling in my.cnf and slowness disappeared). I upgraded to 10.4.13 and removed the disabling of this flag as this issue said it was resolved, however I experienced the same slowness. Disabling the flag again resolved the issue. maria DB 10.4.13 / Arch Linux. query time went from over 30s to < 1s with the flag disabled. I do not believe this issue is fully resolved.

            Edit: recorded actual time for queries (this was a count query so returns 1 line):

            with flag on (default): 1 row in set (3 min 24.590 sec)
            with flag off: 1 row in set (0.519 sec)

            Only change between the above 2 tests was adding the following to `/etc/my.cnf`

             [mysqld]
            optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on"
            

            paulmourer paul added a comment - - edited I experienced the slowness related to this flag on a magento 2 database before upgrading to 10.4.13 version (confirmed by disabling in my.cnf and slowness disappeared). I upgraded to 10.4.13 and removed the disabling of this flag as this issue said it was resolved, however I experienced the same slowness. Disabling the flag again resolved the issue. maria DB 10.4.13 / Arch Linux. query time went from over 30s to < 1s with the flag disabled. I do not believe this issue is fully resolved. Edit: recorded actual time for queries (this was a count query so returns 1 line): with flag on (default): 1 row in set (3 min 24.590 sec) with flag off: 1 row in set (0.519 sec) Only change between the above 2 tests was adding the following to `/etc/my.cnf` [mysqld] optimizer_switch="index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on"

            paulmourer, would it be possible for you to share the query that causes it and its EXPLAIN (for the slow execution, when ran with rowid_filter=on) ?

            psergei Sergei Petrunia added a comment - paulmourer , would it be possible for you to share the query that causes it and its EXPLAIN (for the slow execution, when ran with rowid_filter=on) ?
            paulmourer paul added a comment - - edited

            psergey, This is with the rowid_filter on. in both of these queries i have scrubbed the database name and substituted DB_NAME

            explain SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`  INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0  LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = 1) AND (`e`.`attribute_set_id` = '4');
            +------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+
            | id   | select_type | table             | type       | possible_keys                                                                                                                          | key                                                                         | key_len | ref                           | rows       | Extra                           |
            +------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+
            |    1 | SIMPLE      | e                 | ref        | PRIMARY,CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID                                                                                        | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID                                     | 2       | const                         | 20808      | Using index                     |
            |    1 | SIMPLE      | at_status         | ref|filter | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID|CATALOG_PRODUCT_ENTITY_INT_STORE_ID | 2|2     | const                         | 78010 (0%) | Using where; Using rowid filter |
            |    1 | SIMPLE      | at_status_default | eq_ref     | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID                  | 8       | DB_NAME.e.entity_id,const,const | 1          | Using where                     |
            +------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+
            3 rows in set (0.002 sec)
            

            this is without the rowid filter:

            explain SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`  INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0  LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = 1) AND (`e`.`attribute_set_id` = '4');
            +------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+
            | id   | select_type | table             | type   | possible_keys                                                                                                                          | key                                                        | key_len | ref                           | rows  | Extra       |
            +------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+
            |    1 | SIMPLE      | e                 | ref    | PRIMARY,CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID                                                                                        | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID                    | 2       | const                         | 20808 | Using index |
            |    1 | SIMPLE      | at_status         | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | DB_NAME.e.entity_id,const,const | 1     |             |
            |    1 | SIMPLE      | at_status_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | DB_NAME.e.entity_id,const,const | 1     | Using where |
            +------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+
            3 rows in set (0.001 sec)
            
            

            paulmourer paul added a comment - - edited psergey , This is with the rowid_filter on. in both of these queries i have scrubbed the database name and substituted DB_NAME explain SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = 1) AND (`e`.`attribute_set_id` = '4'); +------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+ | 1 | SIMPLE | e | ref | PRIMARY,CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | 2 | const | 20808 | Using index | | 1 | SIMPLE | at_status | ref|filter | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID|CATALOG_PRODUCT_ENTITY_INT_STORE_ID | 2|2 | const | 78010 (0%) | Using where; Using rowid filter | | 1 | SIMPLE | at_status_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | DB_NAME.e.entity_id,const,const | 1 | Using where | +------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+ 3 rows in set (0.002 sec) this is without the rowid filter: explain SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = 1) AND (`e`.`attribute_set_id` = '4'); +------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+ | 1 | SIMPLE | e | ref | PRIMARY,CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | 2 | const | 20808 | Using index | | 1 | SIMPLE | at_status | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | DB_NAME.e.entity_id,const,const | 1 | | | 1 | SIMPLE | at_status_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | DB_NAME.e.entity_id,const,const | 1 | Using where | +------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+ 3 rows in set (0.001 sec)
            igor Igor Babaev added a comment -

            Hi Paul,

            Your problem is definitely not the same as of bug MDEV-21794. Could you please submit a new bug report in which you'll repeat what you said here and you'll confirm that you have such indexes
            CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID (2 columns)
            CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID (1 column)
            CATALOG_PRODUCT_ENTITY_INT_STORE_ID (1 column)
            and you'll specify over which columns they are.

            Could you also provide us with statistics on these indexes (run SHOW INDEXES FROM catalog_product_entity_int) ?

            igor Igor Babaev added a comment - Hi Paul, Your problem is definitely not the same as of bug MDEV-21794 . Could you please submit a new bug report in which you'll repeat what you said here and you'll confirm that you have such indexes CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID (2 columns) CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID (1 column) CATALOG_PRODUCT_ENTITY_INT_STORE_ID (1 column) and you'll specify over which columns they are. Could you also provide us with statistics on these indexes (run SHOW INDEXES FROM catalog_product_entity_int) ?

            People

              psergei Sergei Petrunia
              marostegui Manuel Arostegui
              Votes:
              0 Vote for this issue
              Watchers:
              9 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.