[MDEV-7118] Anemometer stop working after upgrade to from 5.6 to 10.0 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.14
Fix Version/s: 10.0.15
Component/s: Optimizer
Labels:
None

Description

analyze table hostname_max

explain SELECT DISTINCT `hostname_max` FROM `global_query_review_history`;

+------+-------------+-----------------------------+-------+---------------+--------------+---------+------+---------+-------------+

| id   | select_type | table                       | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |

+------+-------------+-----------------------------+-------+---------------+--------------+---------+------+---------+-------------+

|    1 | SIMPLE      | global_query_review_history | index | NULL          | hostname_max | 218     | NULL | 3167247 | Using index |

+------+-------------+-----------------------------+-------+---------------+--------------+---------+------+---------+-------------+

1 row in set (0.01 sec)

work around is :

create index hostname_max1 on global_query_review_history (hostname_max);

[11/14/14 4:19:25 PM] Nicolas Payart: slow_query_log=# explain SELECT DISTINCT `hostname_max` FROM `global_query_review_history`;

+------+-------------+-----------------------------+-------+---------------+---------------+---------+------+------+--------------------------+

| id   | select_type | table                       | type  | possible_keys | key           | key_len | ref  | rows | Extra                    |

+------+-------------+-----------------------------+-------+---------------+---------------+---------+------+------+--------------------------+

|    1 | SIMPLE      | global_query_review_history | range | NULL          | hostname_max1 | 194     | NULL |   27 | Using index for group-by |

+------+-------------+-----------------------------+-------+---------------+---------------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

was ok on MySQL 5.6 (without index hostname_max1)

slow_query_log=# show table status like 'global_query_review_history'\G

*************************** 1. row ***************************

           Name: global_query_review_history

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 3167374

 Avg_row_length: 1091

    Data_length: 3458203648

Max_data_length: 0

   Index_length: 335953920

      Data_free: 17825792

 Auto_increment: NULL

    Create_time: 2014-11-14 16:18:52

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

CREATE TABLE `global_query_review_history` (

  `hostname_max` varchar(64) NOT NULL,

  `db_max` varchar(64) DEFAULT NULL,

  `checksum` bigint(20) unsigned NOT NULL,

  `sample` longtext NOT NULL,

  `ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `ts_cnt` float DEFAULT NULL,

  `Query_time_sum` float DEFAULT NULL,

  `Query_time_min` float DEFAULT NULL,

  `Query_time_max` float DEFAULT NULL,

  `Query_time_pct_95` float DEFAULT NULL,

  `Query_time_stddev` float DEFAULT NULL,

  `Query_time_median` float DEFAULT NULL,

  `Lock_time_sum` float DEFAULT NULL,

  `Lock_time_min` float DEFAULT NULL,

  `Lock_time_max` float DEFAULT NULL,

  `Lock_time_pct_95` float DEFAULT NULL,

  `Lock_time_stddev` float DEFAULT NULL,

  `Lock_time_median` float DEFAULT NULL,

  `Rows_sent_sum` float DEFAULT NULL,

  `Rows_sent_min` float DEFAULT NULL,

  `Rows_sent_max` float DEFAULT NULL,

  `Rows_sent_pct_95` float DEFAULT NULL,

  `Rows_sent_stddev` float DEFAULT NULL,

  `Rows_sent_median` float DEFAULT NULL,

  `Rows_examined_sum` float DEFAULT NULL,

  `Rows_examined_min` float DEFAULT NULL,

  `Rows_examined_max` float DEFAULT NULL,

  `Rows_examined_pct_95` float DEFAULT NULL,

  `Rows_examined_stddev` float DEFAULT NULL,

  `Rows_examined_median` float DEFAULT NULL,

  `Rows_affected_sum` float DEFAULT NULL,

  `Rows_affected_min` float DEFAULT NULL,

  `Rows_affected_max` float DEFAULT NULL,

  `Rows_affected_pct_95` float DEFAULT NULL,

  `Rows_affected_stddev` float DEFAULT NULL,

  `Rows_affected_median` float DEFAULT NULL,

  `Rows_read_sum` float DEFAULT NULL,

  `Rows_read_min` float DEFAULT NULL,

  `Rows_read_max` float DEFAULT NULL,

  `Rows_read_pct_95` float DEFAULT NULL,

  `Rows_read_stddev` float DEFAULT NULL,

  `Rows_read_median` float DEFAULT NULL,

  `Merge_passes_sum` float DEFAULT NULL,

  `Merge_passes_min` float DEFAULT NULL,

  `Merge_passes_max` float DEFAULT NULL,

  `Merge_passes_pct_95` float DEFAULT NULL,

  `Merge_passes_stddev` float DEFAULT NULL,

  `Merge_passes_median` float DEFAULT NULL,

  `InnoDB_IO_r_ops_min` float DEFAULT NULL,

  `InnoDB_IO_r_ops_max` float DEFAULT NULL,

  `InnoDB_IO_r_ops_pct_95` float DEFAULT NULL,

  `InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL,

  `InnoDB_IO_r_bytes_stddev` float DEFAULT NULL,

  `InnoDB_IO_r_bytes_median` float DEFAULT NULL,

  `InnoDB_IO_r_wait_min` float DEFAULT NULL,

  `InnoDB_IO_r_wait_max` float DEFAULT NULL,

  `InnoDB_IO_r_wait_pct_95` float DEFAULT NULL,

  `InnoDB_IO_r_ops_stddev` float DEFAULT NULL,

  `InnoDB_IO_r_ops_median` float DEFAULT NULL,

  `InnoDB_IO_r_bytes_min` float DEFAULT NULL,

  `InnoDB_IO_r_bytes_max` float DEFAULT NULL,

  `InnoDB_IO_r_wait_stddev` float DEFAULT NULL,

  `InnoDB_IO_r_wait_median` float DEFAULT NULL,

  `InnoDB_rec_lock_wait_min` float DEFAULT NULL,

  `InnoDB_rec_lock_wait_max` float DEFAULT NULL,

  `InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL,

  `InnoDB_rec_lock_wait_stddev` float DEFAULT NULL,

  `InnoDB_rec_lock_wait_median` float DEFAULT NULL,

  `InnoDB_queue_wait_min` float DEFAULT NULL,

  `InnoDB_queue_wait_max` float DEFAULT NULL,

  `InnoDB_queue_wait_pct_95` float DEFAULT NULL,

  `InnoDB_queue_wait_stddev` float DEFAULT NULL,

  `InnoDB_queue_wait_median` float DEFAULT NULL,

  `InnoDB_pages_distinct_min` float DEFAULT NULL,

  `InnoDB_pages_distinct_max` float DEFAULT NULL,

  `InnoDB_pages_distinct_pct_95` float DEFAULT NULL,

  `InnoDB_pages_distinct_stddev` float DEFAULT NULL,

  `InnoDB_pages_distinct_median` float DEFAULT NULL,

  `QC_Hit_cnt` float DEFAULT NULL,

  `QC_Hit_sum` float DEFAULT NULL,

  `Full_scan_cnt` float DEFAULT NULL,

  `Full_scan_sum` float DEFAULT NULL,

  `Full_join_cnt` float DEFAULT NULL,

  `Full_join_sum` float DEFAULT NULL,

  `Tmp_table_cnt` float DEFAULT NULL,

  `Tmp_table_sum` float DEFAULT NULL,

  `Filesort_cnt` float DEFAULT NULL,

  `Filesort_sum` float DEFAULT NULL,

  `Tmp_table_on_disk_cnt` float DEFAULT NULL,

  `Tmp_table_on_disk_sum` float DEFAULT NULL,

  `Filesort_on_disk_cnt` float DEFAULT NULL,

  `Filesort_on_disk_sum` float DEFAULT NULL,

  `Bytes_sum` float DEFAULT NULL,

  `Bytes_min` float DEFAULT NULL,

  `Bytes_max` float DEFAULT NULL,

  `Bytes_pct_95` float DEFAULT NULL,

  `Bytes_stddev` float DEFAULT NULL,

  `Bytes_median` float DEFAULT NULL,

  UNIQUE KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`),

  KEY `ts_min` (`ts_min`),

  KEY `checksum` (`checksum`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Please backport ~~MDEV-6657~~ as soon as possible in 10.0

Attachments

Issue Links

relates to

MDEV-4120 UNIQUE indexes should not be considered for loose index scan

Closed

Activity

Ascending order - Click to sort in descending order

View 10 older comments

Sergei Petrunia added a comment - 2014-11-18 13:14 - edited

If I have a

  UNIQUE KEY `col1` (`col1`,`col2`,`col3`,`col4`)

and run a query:

explain select distinct col1,col2,col3,col4 from tpk1;

I see that Loose Scan is considered, despite that the index is unique. The cost
numbers prevent it from being chosen. The optimizer choses:

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | tpk1  | index | NULL          | col1 | 20      | NULL | 9990 | Using index |

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

If I adjust the cost numbers in so that loose index scan is chosen, it is used:

+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |

+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

|    1 | SIMPLE      | tpk1  | range | NULL          | col1 | 20      | NULL | 9991 | Using index for group-by |

+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

The same goes for "SELECT DISTINCT pk_part1, ... pk_partN". Perhaps, this is what ~~MDEV-4120~~ was trying to prevent?

VAROQUI Stephane added a comment - 2014-11-18 13:44 - edited

The cost of lose scan should be evaluated cost * (cardinality at the key_len /records) * cost_loose_scan_complexity /cost_index_scan_complexity

VAROQUI Stephane added a comment - 2014-11-18 13:44 - edited The cost of lose scan should be evaluated cost * (cardinality at the key_len /records) * cost_loose_scan_complexity /cost_index_scan_complexity

Sergei Petrunia added a comment - 2014-11-18 14:10

[stephane], your formula is not clear.

"cost of lose scan"= ... * cost_lose_scan_complexity ...

Is this formula recursive? if not, what is cost_lose_scan_complexity and how is it different from cost of loose scan?

Sergei Petrunia added a comment - 2014-11-18 14:10 [stephane] , your formula is not clear. "cost of lose scan"= ... * cost_lose_scan_complexity ... Is this formula recursive? if not, what is cost_lose_scan_complexity and how is it different from cost of loose scan?

Sergei Petrunia added a comment - 2014-11-18 14:11

Viable options so far

Undo the fix for ~~MDEV-4120~~ completely
Change the fix for ~~MDEV-4120~~ to disable Loose Index Scan only when doing "SELECT DISTINCT unique_key_col1 ,... unique_key_colN".

I am not sure if the second one is meaningful. I was not able to come up with a SELECT DISTINCT statement where cost of loose scan is smaller than the cost of index scan. Too bad we don't know the original testcase for ~~MDEV-4120~~.

Sergei Petrunia added a comment - 2014-11-18 14:11 Viable options so far Undo the fix for MDEV-4120 completely Change the fix for MDEV-4120 to disable Loose Index Scan only when doing "SELECT DISTINCT unique_key_col1 ,... unique_key_colN". I am not sure if the second one is meaningful. I was not able to come up with a SELECT DISTINCT statement where cost of loose scan is smaller than the cost of index scan. Too bad we don't know the original testcase for MDEV-4120 .

Sergei Petrunia added a comment - 2014-11-19 16:26

Implemented the second option. Fix pushed into 10.0 tree.

While debugging, also saw that Loose Index Scan cost formulas are poor - it can chose to use Loose Index Scan with group sizes as low as 4 rows, which seems to be wrong (at least, in some cases).

Sergei Petrunia added a comment - 2014-11-19 16:26 Implemented the second option. Fix pushed into 10.0 tree. While debugging, also saw that Loose Index Scan cost formulas are poor - it can chose to use Loose Index Scan with group sizes as low as 4 rows, which seems to be wrong (at least, in some cases).

MariaDB Server

Anemometer stop working after upgrade to from 5.6 to 10.0

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration