This is repeatable and it's still the same once data is cached?

 

Which version is the explain from? I guess it's from 10.0.31. Can you include the explain from the other version(s)?

 

 

The DATE_FORMAT function part of the query…

 

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

 

This asks for all days and all hours from 0-23 (excluding 24). You could simplify this a lot. Might help the parser out.

 

 

Rhys

 

 

 

From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Alessandro Ren
Sent: 24 July 2017 15:29
To: maria-discuss@lists.launchpad.net
Subject: [Maria-discuss] TokuDB performance hit after 10.0.25

  

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