Re: [Maria-discuss] TokuDB performance hit after 10.0.25

24 Jul 2017

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

Re: [Maria-discuss] TokuDB performance hit after 10.0.25

Vicențiu Ciorbaru