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

Anemometer stop working after upgrade to from 5.6 to 10.0

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              stephane@skysql.com VAROQUI Stephane
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: