maria-discuss team mailing list archive

[Alessandro Ren <alessandro.ren@xxxxxxxxxxxxxx>]

  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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx] *On Behalf Of *Alessandro Ren
> *Sent:* 24 July 2017 15:29
> *To:* maria-discuss@xxxxxxxxxxxxxxxxxxx
> *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
>