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

Defaults for 10.4: histogram size should be set

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.4.3
    • Component/s: Optimizer
    • Labels:
      None

      Description

      So after MDEV-15253 and co., the default settings in 10.4 are:

      mysql> show variables like 'use_%';
      +-----------------+------------------------+
      | Variable_name   | Value                  |
      +-----------------+------------------------+
      | use_stat_tables | PREFERABLY_FOR_QUERIES |
      +-----------------+------------------------+
      

      mysql> show variables like 'optimizer_us%';
      +-------------------------------------+-------+
      | Variable_name                       | Value |
      +-------------------------------------+-------+
      | optimizer_use_condition_selectivity | 4     |
      +-------------------------------------+-------+
      

      mysql> show variables like 'hist%';
      +----------------+----------------+
      | Variable_name  | Value          |
      +----------------+----------------+
      | histogram_size | 0              |
      | histogram_type | SINGLE_PREC_HB |
      +----------------+----------------+
      

      Create a table and fill with some data

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
      create table t1 (a int, b int);
      insert into t1 select A.a+100*B.a, A.a+1000*B.a from one_k A, one_k B;
      

      Then

      mysql> analyze table t1;
      +---------+---------+----------+----------+
      | Table   | Op      | Msg_type | Msg_text |
      +---------+---------+----------+----------+
      | test.t1 | analyze | status   | OK       |
      +---------+---------+----------+----------+
      1 row in set (0.03 sec)
      

      This is instant as it used to be. Good.

      mysql> analyze table t1 persistent for all;
      +---------+---------+----------+-----------------------------------------+
      | Table   | Op      | Msg_type | Msg_text                                |
      +---------+---------+----------+-----------------------------------------+
      | test.t1 | analyze | status   | Engine-independent statistics collected |
      | test.t1 | analyze | status   | OK                                      |
      +---------+---------+----------+-----------------------------------------+
      2 rows in set (11.92 sec)
      

      This collects EITS stats. It takes a while. Debugging, one can see that a Unique object is constructed and filled with data.
      But the histogram is not created, which is logical as the setting is that histogram_size=0.

      mysql> select * from mysql.column_stats;
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      | db_name | table_name | column_name | min_value | max_value | nulls_ratio | avg_length | avg_frequency | hist_size | hist_type | histogram |
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      | test    | t1         | a           | 0         | 100899    |      0.0000 |     4.0000 |        9.9108 |         0 | NULL      | NULL      |
      | test    | t1         | b           | 0         | 999999    |      0.0000 |     4.0000 |        1.0000 |         0 | NULL      | NULL      |
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      

      The point of this MDEV is that default histogram size/ type should specify a value which does cause histogram to be collected.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: