Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4(EOL)
-
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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
So after {noformat} mysql> show variables like 'use_%'; +-----------------+------------------------+ | Variable_name | Value | +-----------------+------------------------+ | use_stat_tables | PREFERABLY_FOR_QUERIES | +-----------------+------------------------+ {noformat} {noformat} mysql> show variables like 'optimizer_us%'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | optimizer_use_condition_selectivity | 4 | +-------------------------------------+-------+ {noformat} {noformat} mysql> show variables like 'hist%'; +----------------+----------------+ | Variable_name | Value | +----------------+----------------+ | histogram_size | 0 | | histogram_type | SINGLE_PREC_HB | +----------------+----------------+ {noformat} Create a table and fill with some data {code:sql} 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; {code} Then {noformat} mysql> analyze table t1; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.03 sec) {noformat} This is instant as it used to be. Good. {noformat} 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) {noformat} This collects EITS stats. Debugging, one can see it is constructing a Unique object and filling it with data. But the histogram is not collected, as we've had histogram_size=0 : {noformat} 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 | +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+ {noformat} |
So after {noformat} mysql> show variables like 'use_%'; +-----------------+------------------------+ | Variable_name | Value | +-----------------+------------------------+ | use_stat_tables | PREFERABLY_FOR_QUERIES | +-----------------+------------------------+ {noformat} {noformat} mysql> show variables like 'optimizer_us%'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | optimizer_use_condition_selectivity | 4 | +-------------------------------------+-------+ {noformat} {noformat} mysql> show variables like 'hist%'; +----------------+----------------+ | Variable_name | Value | +----------------+----------------+ | histogram_size | 0 | | histogram_type | SINGLE_PREC_HB | +----------------+----------------+ {noformat} Create a table and fill with some data {code:sql} 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; {code} Then {noformat} mysql> analyze table t1; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.03 sec) {noformat} This is instant as it used to be. Good. {noformat} 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) {noformat} 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. {noformat} 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 | +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+ {noformat} The point of this MDEV is that default histogram size/ type should specify a value which does cause histogram to be collected. |
| Link |
This issue relates to |
| Assignee | Sergei Petrunia [ psergey ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 10.4.3 [ 23230 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 92648 ] | MariaDB v4 [ 155719 ] |