Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4
-
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
- relates to
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
-
- Closed
-