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....@gmail.com> 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@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
