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

New optimizer defaults and ANALYZE TABLE

    XMLWordPrintable

Details

    Description

      We have MDEV-15253, which changes optimizer defaults to include using the histograms and their selectivity:

      optimizer_use_condition_selectivity=4
      use_stat_tables=PREFERABLY
      

      One of the effects of the new settings is that statement like

      ANALYZE TABLE t1
      

      after MDEV-15253 will start to collect EITS stats.

      This was enabled in MTR and was instrumental in finding a lot of bugs related to EITS (Good).

      However, it is not appropriate for production uses: If ANALYZE TABLE t1 collects EITS stats, it takes much more time (my measurement: 10x time the full table scan). This is BAD.

      Possible ways out:

      Solution 1: Make ANALYZE TABLE t1 not collect EITS stats

      • We will need to rollback all of the changes to .result files in MDEV-15253.
      • EITS will have few test coverage.

      Solution 2: Make ANALYZE TABLE t1 collect EITS stats for MTR but not users.

      • Let use_stat_tables=preferably remain what it currently is: ANALYZE TABLE t1 collects EITS stats. MTR will run with this setting.
      • Introduce another value of use_stat_tables=preferably_for_reads (name is tentative). This will be the default for the users. It will mean that ANALYZE TABLE t1 does not collect EITS stats.

      (One may argue that this is bad as MTR will run in an environment that's not like the users have. On the other hand, MTR will run with predictable statistical data. MTR used to run with sampled, non-predictable stats which made `rows` column and query plans unstable)

      Solution 3:

      Wait until Vicentiu and Teodor are done with EITS-via-sampling.
      This is bad as it creates a dependency between these two tasks.
      We do not want to push the defaults change late in the release cycle.

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.