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

main.innodb_ext_key fails sporadically

Details

    Description

      In https://buildbot.mariadb.org/#/builders/27/builds/4241 (ubuntu-1604) I noted:

      main.innodb_ext_key 'innodb'             w4 [ fail ]
              Test ended at 2020-04-01 20:19:02
       
      CURRENT_TEST: main.innodb_ext_key
      --- /buildbot/ubuntu-1604/build/mysql-test/main/innodb_ext_key.result	2020-04-01 19:39:45.000000000 +0000
      +++ /buildbot/ubuntu-1604/build/mysql-test/main/innodb_ext_key.reject	2020-04-01 20:19:02.155337769 +0000
      @@ -1014,7 +1014,7 @@
       ORDER BY rev_timestamp ASC LIMIT 10;
       id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
       1	SIMPLE	t1	const	PRIMARY,name_title	name_title	261	const,const	1	
      -1	SIMPLE	t2	ref	page_timestamp	page_timestamp	4	const	10	Using where
      +1	SIMPLE	t2	ALL	page_timestamp	NULL	NULL	NULL	45	Using where; Using filesort
       1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.rev_text_id	1	
       set optimizer_switch='extended_keys=on';
       EXPLAIN 
      @@ -1023,7 +1023,7 @@
       ORDER BY rev_timestamp ASC LIMIT 10;
       id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
       1	SIMPLE	t1	const	PRIMARY,name_title	name_title	261	const,const	1	
      -1	SIMPLE	t2	ref	page_timestamp	page_timestamp	4	const	10	Using where
      +1	SIMPLE	t2	ALL	page_timestamp	NULL	NULL	NULL	45	Using where; Using filesort
       1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.rev_text_id	1	
       DROP TABLE t1,t2,t3;
       #
      

      I have not seen this before, I suspect it is a recent regression on the 10.5 branch.

      Attachments

        Issue Links

          Activity

            vladbogo Vlad Bogolin added a comment -

            otto The dockerfile that sets up the environment can be found here https://github.com/MariaDB/mariadb.org-tools/blob/master/buildbot.mariadb.org/dockerfiles/ubuntu-1604.dockerfile. The steps to reproduce would be: 1) build the image associated with the docker file (e.g. docker build -t ubuntu-1604 --file ubuntu-1604.dockerfile .); 2) then start a container (e.g. docker run --user root -it ubuntu-1604 bash). 3) Finally, just fetch the tarball that the builder fetches (see the "fetch_tarball" step). Unfortunately, only one month of history is kept due to space limitations so anything older than that I assume can only be reproduced by reverting git.

            vladbogo Vlad Bogolin added a comment - otto The dockerfile that sets up the environment can be found here https://github.com/MariaDB/mariadb.org-tools/blob/master/buildbot.mariadb.org/dockerfiles/ubuntu-1604.dockerfile . The steps to reproduce would be: 1) build the image associated with the docker file (e.g. docker build -t ubuntu-1604 --file ubuntu-1604.dockerfile .); 2) then start a container (e.g. docker run --user root -it ubuntu-1604 bash). 3) Finally, just fetch the tarball that the builder fetches (see the "fetch_tarball" step). Unfortunately, only one month of history is kept due to space limitations so anything older than that I assume can only be reproduced by reverting git.

            Well i checked today on buildbot
            https://buildbot.mariadb.org/#/builders/27/builds/5587/steps/8/logs/stdio
            and the test main.innodb_ext_key passes there, so looks like it does not fail reliably.

            So i saw the cost of the plan in the optimizer_trace for table t2

                          {
                            "table": "t2",
                            "range_analysis": {
                              "table_scan": {
                                "rows": 48,
                                "cost": 12.6
                              },
            

            "analyzing_range_alternatives": {
                                "range_scan_alternatives": [
                                  {
                                    "index": "page_timestamp",
                                    "ranges": ["(38928081) <= (rev_page) <= (38928081)"],
                                    "rowid_ordered": false,
                                    "using_mrr": false,
                                    "index_only": false,
                                    "rows": 10,
                                    "cost": 12.14841776,
                                    "chosen": true
                                  }
                                ],
            

            So the cost are 12.145 and 12.6 which is very close and due to the statistics from INNODB we may be getting different plans, so increasing the rows would be the best option here.

            After increasing the rows in table t2 (the ones that would not satify the range), I get

             "table": "t2",
                            "range_analysis": {
                              "table_scan": {
                                "rows": 56,
                                "cost": 14.2
                              },
            

            and

                              "analyzing_range_alternatives": {
                                "range_scan_alternatives": [
                                  {
                                    "index": "page_timestamp",
                                    "ranges": ["(38928081) <= (rev_page) <= (38928081)"],
                                    "rowid_ordered": false,
                                    "using_mrr": false,
                                    "index_only": false,
                                    "rows": 10,
                                    "cost": 12.14841776,
                                    "chosen": true
                                  }
                                ],
            

            varun Varun Gupta (Inactive) added a comment - Well i checked today on buildbot https://buildbot.mariadb.org/#/builders/27/builds/5587/steps/8/logs/stdio and the test main.innodb_ext_key passes there, so looks like it does not fail reliably. So i saw the cost of the plan in the optimizer_trace for table t2 { "table": "t2", "range_analysis": { "table_scan": { "rows": 48, "cost": 12.6 }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "page_timestamp", "ranges": ["(38928081) <= (rev_page) <= (38928081)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 10, "cost": 12.14841776, "chosen": true } ], So the cost are 12.145 and 12.6 which is very close and due to the statistics from INNODB we may be getting different plans, so increasing the rows would be the best option here. After increasing the rows in table t2 (the ones that would not satify the range), I get "table": "t2", "range_analysis": { "table_scan": { "rows": 56, "cost": 14.2 }, and "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "page_timestamp", "ranges": ["(38928081) <= (rev_page) <= (38928081)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 10, "cost": 12.14841776, "chosen": true } ],
            varun Varun Gupta (Inactive) added a comment - Patch https://github.com/MariaDB/server/commit/1f43c18735c809e9108bb48d61841c4419510134

            Btw, the test is currently not listed in mysql-test/unstable-tests. Not sure what has removed Otto's commit (a merge?)

            psergei Sergei Petrunia added a comment - Btw, the test is currently not listed in mysql-test/unstable-tests . Not sure what has removed Otto's commit (a merge?)

            Ok to push.

            psergei Sergei Petrunia added a comment - Ok to push.

            People

              varun Varun Gupta (Inactive)
              otto Otto Kekäläinen
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.