← Back to team overview

maria-discuss team mailing list archive

Re: TokuDB performance hit after 10.0.25

 

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@xxxxxxxxx <mailto:dirty.ren@xxxxxxxxx>> 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@xxxxxxx
    <mailto:r@xxxxxxx>> 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@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp


Follow ups

References