Here it goes 10.0.25 MariaDB [opperf]> show variables like '%version%'; +-------------------------+------------------+ | Variable_name | Value | +-------------------------+------------------+ | innodb_version | 5.6.29-76.2 | | protocol_version | 10 | | slave_type_conversions | | | tokudb_version | 5.6.26-74.0 | | version | 10.0.25-MariaDB | | version_comment | MariaDB Server | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_malloc_library | bundled jemalloc | +-------------------------+------------------+ 9 rows in set (0.09 sec) +------+-------------+------------------+-------+----------------------------+--------------------+---------+------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------------+-------+----------------------------+--------------------+---------+------+-------+----------------------------------------------+ | 1 | SIMPLE | service_perf_651 | range | PRIMARY,service_perf_1_idx | service_perf_1_idx | 16 | NULL | 17880 | Using where; Using temporary; Using filesort | +------+-------------+------------------+-------+----------------------------+--------------------+---------+------+-------+----------------------------------------------+ 10.0.31 MariaDB [opperf]> show variables like '%version%'; +-------------------------+------------------+ | Variable_name | Value | +-------------------------+------------------+ | innodb_version | 5.6.36-82.0 | | protocol_version | 10 | | slave_type_conversions | | | tokudb_version | 5.6.36-82.0 | | version | 10.0.31-MariaDB | | version_comment | MariaDB Server | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_malloc_library | bundled jemalloc | +-------------------------+------------------+ 9 rows in set (0.11 sec) +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+ | 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 | +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+ On Mon, Jul 24, 2017 at 10:53 AM, <Rhys.Campbell@swisscom.com> wrote:
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