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

Once cached, the query returns in 0s. The force index solved the problem: MariaDB 10.0.31 Force index:13 rows in set (2.31 sec) No force: 13 rows in set (12.97 sec) MariDB 10.0.25: Force index: 13 rows in set (1.46 sec) No force: 13 rows in set (1.70 sec) Explains per version follows: 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 | +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+ tks. On Mon, Jul 24, 2017 at 11:10 AM, Reinis Rozitis <r@roze.lv> wrote:

MariaDB 10.0.25 - 13 rows in set (1.67 sec)

...

MariaDB 10.0.31 - 13 rows in set (29.06 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 | +------+-------------+------------------+-------+----------- -----------------+---------+---------+------+------+-------- --------------------------------------+

This is typical when the MySQL query optimiser decides that it will be faster to use an index for sorting rather than selecting. Sometimes the query plans change because of versions or table/index statistics.

For testing purposes you can try to drop the "order by entry_time" part or add FORCE INDEX:

SELECT ... from service_perf_651 FORCE INDEX(service_perf_1_idx) WHERE ...

rr

Hi Alessandro! 10.0.31 should be affected by https://jira.percona.com/browse/TDB-35 , do you think it could be related to your issue? HTH, Jocelyn Fournier Founder M : +33 6 51 21 54 10 https://www.softizy.com Softizy - At your side to Optimize your PHP / MySQL applications Le 24/07/2017 à 17:41, Alessandro Ren a écrit :

Even if a change the order by and the query to include all 3 fields on the index, it still selects the PRIMARY key to query the table.

Do you think this is a bug?

Tks.

On Mon, Jul 24, 2017 at 11:34 AM, Alessandro Ren <dirty.ren@gmail.com <mailto:dirty.ren@gmail.com>> wrote:

Once cached, the query returns in 0s.

The force index solved the problem: MariaDB 10.0.31 Force index:13 rows in set (2.31 sec) No force: 13 rows in set (12.97 sec)

MariDB 10.0.25: Force index: 13 rows in set (1.46 sec) No force: 13 rows in set (1.70 sec)

Explains per version follows:

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

tks.

On Mon, Jul 24, 2017 at 11:10 AM, Reinis Rozitis <r@roze.lv <mailto:r@roze.lv>> wrote:

MariaDB 10.0.25 - 13 rows in set (1.67 sec) MariaDB 10.0.31 - 13 rows in set (29.06 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 | +------+-------------+------------------+-------+----------------------------+---------+---------+------+------+----------------------------------------------+

This is typical when the MySQL query optimiser decides that it will be faster to use an index for sorting rather than selecting. Sometimes the query plans change because of versions or table/index statistics.

For testing purposes you can try to drop the "order by entry_time" part or add FORCE INDEX:

SELECT ... from service_perf_651 FORCE INDEX(service_perf_1_idx) WHERE ...

rr

_______________________________________________ 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