maria-discuss team mailing list archive

[Vicențiu Ciorbaru <vicentiu@xxxxxxxxxxx>]

Hi Alessandro,

Can you by any chance provide the query plan for the previous version of
MariaDB? That can potentially help in diagnosing the problem. I haven't yet
looked into the issue, but it's good if we can eliminate the query
optimiser in case it's providing a different query plan.

Vicențiu

On Mon, 24 Jul 2017 at 16:29 Alessandro Ren <dirty.ren@xxxxxxxxx> wrote:

>
> Hello,
>
> I've noticed a great performance hit after I upgraded my MariaDB install
> to 10.0.28, 10.0.29, 10.0.30 and 10.0.31.
> I even tried upgrading to MariaDB 10.2.7 and had the same problem. Bellow
> de details.
>
> MariaDB 10.0.25 - 13 rows in set (1.67 sec)
> MariaDB 10.0.31 - 13 rows in set (29.06 sec)
>
> The query:
>
> SELECT metric_id,date_format(entry_time, '%m:%Y') as date_group,
> unix_timestamp(entry_time) as entry_time, entry_time as datetime,
> avg(perf_value) as perf_value, warning, critical, baseline, lower_limit,
> upper_limit from service_perf_651 where service_id='56551' and
> metric_id='90183701' and entry_time>='2016-07-24 09:41:42' and
> entry_time<='2017-07-24 09:41:42' and ( (date_format(entry_time,'%w')=0 and
> ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H') < 24)))
> or (date_format(entry_time,'%w')=1 and ((date_format(entry_time,'%H')>=0
> and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=2
> and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24)
> )) or (date_format(entry_time,'%w')=3 and ((date_format(entry_time,'%H')>=0
> and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=4
> and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24)
> )) or (date_format(entry_time,'%w')=5 and ((date_format(entry_time,'%H')>=0
> and date_format(entry_time,'%H')<24) )) or (date_format(entry_time,'%w')=6
> and ((date_format(entry_time,'%H')>=0 and date_format(entry_time,'%H')<24)
> )) ) group by date_group order by entry_time
>
> Explain query:
>
>
> +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+
> | id   | select_type | table            | type  | possible_keys
>    | key     | key_len | ref  | rows | Extra
>          |
>
> +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+
> |    1 | SIMPLE      | service_perf_651 | range |
> PRIMARY,service_perf_1_idx | PRIMARY | 16      | NULL |    1 | Using where;
> Using temporary; Using filesort |
>
> +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+
> 1 row in set (0.06 sec)
>
>
> The table:
>
> CREATE TABLE `service_perf_651` (
>   `entry_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
>   `service_id` int(11) unsigned NOT NULL DEFAULT '0',
>   `metric_id` int(11) unsigned NOT NULL DEFAULT '0',
>   `perf_value` float(13,3) DEFAULT NULL,
>   `warning` float(13,3) DEFAULT NULL,
>   `critical` float(13,3) DEFAULT NULL,
>   `baseline` float(13,3) DEFAULT NULL,
>   `lower_limit` float(13,3) DEFAULT NULL,
>   `upper_limit` float(13,3) DEFAULT NULL,
>   `reserved0` float(13,3) DEFAULT NULL,
>   `reserved1` float(13,3) DEFAULT NULL,
>   `reserved2` float(13,3) DEFAULT NULL,
>   PRIMARY KEY (`entry_time`,`service_id`,`metric_id`),
>   KEY `service_perf_1_idx` (`service_id`,`metric_id`,`entry_time`)
> ) ENGINE=TokuDB DEFAULT CHARSET=utf8 `compression`='tokudb_snappy'
>
> The size:
>
> 32497415 records
>
>
>   Any idea what could be wrong? I even tried everything above on a
> different HW where the databse fit in memory in TokuDB, with the same
> performance hit.
>
>    Tks for the help
>
>    Alessandro Ren
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>