maria-discuss team mailing list archive

[Alessandro Ren <dirty.ren@xxxxxxxxx>]

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