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

Use true sampling to collect index statistics

    XMLWordPrintable

Details

    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • ROADMAP
    • Optimizer
    • None

    Description

      Problem description

      For very large tables, we sample very small fraction of table to collect histograms. But EITS statistics collection still reads O(table_size) data:

      1. Column statistics: It does a full table scan and "flips a coin" for each record to see if it should contribute to the histogram.

      2. Index statistics: It does a full index scan to compute index statistics n_distinct(index_prefix) for each prefix. Note that this computation ignores tuples with NULLs.

      The Goal: collect all kinds of statistics with sampling

      1. Column statistics

      There are two options:

      A. Reuse innodb's index sampling. Sample the clustered PK but look at the column values also.

      B. Reuse the knowledge from MDEV-39491 about how to start a read in different parts of the index and so get a record sample from different parts of the index.

      One could get into the questions like "if we sample pages and use all data on the page for the sample, we get a larger more data for the same amount of data read from disk. On the other hand, getting co-located data makes the sample non-random. Especially so when the data is of variable-size, so one page will give you one "long" record or many "short" ones.
      Maybe we can just use one record from every page read? That's not the top efficiency but would still be much better than the current approach of reading everything...

      2. Index statistics

      InnoDB can collect index statistics with "genuine sampling", that is, it can read a few index pages and come up with an estimate.
      After MDEV-19574, the estimate is/will treat NULL values properly.

      Attachments

        Activity

          People

            Unassigned Unassigned
            psergei Sergei Petrunia
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:

              Time Tracking

                Estimated:
                Original Estimate - 30d
                30d
                Remaining:
                Remaining Estimate - 30d
                30d
                Logged:
                Time Spent - Not Specified
                Not Specified

                Git Integration

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