Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4271

optimizer picks wrong index for tokudb tables having a hot created index

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 5.7.17-12
    • Fix Version/s: 5.7.20-18, 5.6.38-83.0
    • Component/s: TokuDB
    • Labels:
      None

      Description

      Hello,

      I know you guys have been working hard on the logical row count issues with toku/PerconaFT, but unfortunately I believe the latest fix has uncovered or introduced another bug.

      When a table is created and populated with data, the optimizer works fine. It even works fine after a 'hard' rebuild (alter table engine=tokudb), but it does not work fine when the index is created hot a. la. create index foo on bar (f1, f2).

      I've created a simple test case that I will attach but here are some details:

      CREATE TABLE test.test (
      `t1` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `t2` int(10) unsigned NOT NULL,
      `t3` int(10) unsigned NOT NULL,
      `t4` int(10) unsigned NOT NULL,
      PRIMARY KEY (`t1`)
      ) ENGINE=TokuDB CHARSET=latin1;

      ...populate with 10k rows...

      create index i1 on test.test (t2, t3);
      create index i2 on test.test (t2, t4);

      an explain of a select where t2 = const and t4 = (good selectivity) const will always pick the i1 index, even though i1 doesn't have t4 and i2 was the best candidate.

      This holds through an optimize, analyze (standard) and analyze (recount). The only thing that will fix it is alter table test engine=tokudb, which of course is not hot and unfortunately means that hot index creation is no longer useful as any index you just hot-created will appear to the optimizer to be the best one for every query on that table.

      I've confirmed this behavior does not happen on:

      5.5.40-MariaDB-36.1
      5.7.14-7

      but does happen on:

      5.7.17-12

      I'm also fairly certain it did not happen on 5.7.17-11, but do not have it handy ATM. If you need me to get the data I can.

        Smart Checklist

          Attachments

          1. test.sql
            2 kB
          2. test.sql on 5.7.17-11.txt
            12 kB
          3. test.sql on 5.7.17-12.txt
            12 kB

            Activity

              People

              • Assignee:
                jun.yuan Jun Yuan
                Reporter:
                george.lorch George Lorch
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: