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

Details

Type: Bug
Status: Closed
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

Gliffy Diagrams

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

Hide

Permalink

VAROQUI Stephane added a comment - 2014-11-14 17:54 - edited

show profiles;
+----------+------------+-------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                     |
+----------+------------+-------------------------------------------------------------------------------------------+
|        1 | 0.00027697 | SELECT DISTINCT `hostname_max` FROM `global_query_review_test`                            |
|        2 | 0.02410047 | SELECT DISTINCT `hostname_max` FROM `global_query_review_test` FORCE INDEX (hostname_max) |
+----------+------------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Show

VAROQUI Stephane added a comment - 2014-11-14 17:54 - edited show profiles; +----------+------------+-------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------------------------------------------------------+ | 1 | 0.00027697 | SELECT DISTINCT `hostname_max` FROM `global_query_review_test` | | 2 | 0.02410047 | SELECT DISTINCT `hostname_max` FROM `global_query_review_test` FORCE INDEX (hostname_max) | +----------+------------+-------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

9 older comments

Hide

Permalink

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?

Show

Hide

Permalink

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

Show

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

Hide

Permalink

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?

Show

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?

Hide

Permalink

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~~.

Show

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 .

Hide

Permalink

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).

Show

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).

Anemometer stop working after upgrade to from 5.6 to 10.0

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile