Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10321

MariaDB doesn't use the expected indexes with TokuDB

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 5.5.38, 10.0(EOL), 10.1(EOL)
    • N/A
    • Ubuntu 14.04.4 LTS (GNU/Linux 4.2.0-27-generic x86_64)
      MariaDB version : 10.0.23-MariaDB
      tokudb_version: 5.6.26-74.0

    Description

      Hello,
      We sometime face problems with poorly chosen index for queries on TokuDB tables.
      It can lead to important performance problems.
      This issue does not happen on recent Percona Server versions (we tried Percona 5.6.29 or 5.7.11).

      Please find attached a dataset.
      It consists in 4 tables, including toku_problem_myisam and toku_problem_toku, which are the same tables with a different engine (MyISAM for one and TokuDB for the other). The two other tables are in MyISAM

      We first execute, using the MyISAM table (toku_problem_myisam)

      EXPLAIN
      SELECT *
      FROM (
      	SELECT dt psdate,d,h,so,mo,anneeo, code_elt_sql,type_elt, dn
      	FROM
      	 toku_problem_tempologie
      	INNER JOIN toku_problem_topo
      	WHERE LENGTH(dn) IN (39) AND dn REGEXP '0118151|0118152|0118153|0118154|0118155|0118156' AND dn LIKE '0000001,0106486%' AND type_elt ='cellule' AND (dt BETWEEN '2015-06-02 00:00:00' AND '2015-06-16 23:59:59')
      ) AS sys
      LEFT JOIN toku_problem_myisam ON ((sys.d = toku_problem_myisam.date_debut_mesure AND sys.h = toku_problem_myisam.heure_debut_mesure) AND (sys.code_elt_sql = toku_problem_myisam.ni))
      GROUP BY
      LEFT(sys.dn,39),3;
      

      +------+-------------+-------------------------+------+-----------------------------+----------+---------+------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------------------------------+
      | id   | select_type | table                   | type | possible_keys               | key      | key_len | ref                                                                                                                          | rows | Extra                                                  |
      +------+-------------+-------------------------+------+-----------------------------+----------+---------+------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------------------------------+
      |    1 | SIMPLE      | toku_problem_topo       | ref  | dn,type_elt                 | type_elt | 33      | const                                                                                                                        |  215 | Using index condition; Using temporary; Using filesort |
      |    1 | SIMPLE      | toku_problem_tempologie | ALL  | PRIMARY                     | NULL     | NULL    | NULL                                                                                                                         | 4417 | Using where; Using join buffer (flat, BNL join)        |
      |    1 | SIMPLE      | toku_problem_myisam     | ref  | PRIMARY,ni,query,agg_sql_ne | query    | 14      | toku_problem1.toku_problem_topo.code_elt_sql,toku_problem1.toku_problem_tempologie.d,toku_problem1.toku_problem_tempologie.h |    1 | Using where                                            |
      +------+-------------+-------------------------+------+-----------------------------+----------+---------+------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------------------------------+
      

      But when we execute the same query, using the TokuDB table (toku_problem_tokudb), it uses the PRIMARY index instead of the expected "query" index.

      EXPLAIN
      SELECT *
      FROM
      (
      	SELECT dt psdate,d,h,so,mo,anneeo,code_elt_sql,type_elt,dn
      	FROM
      	 toku_problem_tempologie
      	INNER JOIN toku_problem_topo
      	WHERE LENGTH(dn) IN (39) AND dn REGEXP '0118151|0118152|0118153|0118154|0118155|0118156' AND dn LIKE '0000001,0106486%' AND type_elt ='cellule' AND (dt BETWEEN '2015-06-02 00:00:00' AND '2015-06-16 23:59:59')
      ) AS sys
      LEFT JOIN toku_problem_toku ON ((sys.d = toku_problem_toku.`date_debut_mesure` AND sys.h = toku_problem_toku.`heure_debut_mesure`) AND (sys.code_elt_sql = toku_problem_toku.ni))
      GROUP BY
      LEFT(sys.dn,39),3;
      

      +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
      | id   | select_type | table                   | type | possible_keys               | key      | key_len | ref                                     | rows | Extra                                                  |
      +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
      |    1 | SIMPLE      | toku_problem_topo       | ref  | dn,type_elt                 | type_elt | 33      | const                                   |  215 | Using index condition; Using temporary; Using filesort |
      |    1 | SIMPLE      | toku_problem_tempologie | ALL  | PRIMARY                     | NULL     | NULL    | NULL                                    | 4417 | Using where; Using join buffer (flat, BNL join)        |
      |    1 | SIMPLE      | toku_problem_toku       | ref  | PRIMARY,ni,query,agg_sql_ne | PRIMARY  | 3       | toku_problem1.toku_problem_tempologie.d |  100 | Using where                                            |
      +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
      

      We faced this problem with different versions of MariaDB and TokuDB.
      We also faced it several times with different requests, this dataset is just a simplified example.

      Please let us know if you need more information.

      Thank you

      Attachments

        1. my.cnf
          5 kB
        2. real.my.cnf
          5 kB
        3. toku_problem1_database.sql
          2.79 MB

        Activity

          Could you please attach your cnf file(s)? I'm getting very different plans, including the query with the MyISAM table.

          elenst Elena Stepanova added a comment - Could you please attach your cnf file(s)? I'm getting very different plans, including the query with the MyISAM table.

          Hello,

          I am sorry but I forgot one step, we run an analyze on every table after importing the dataset, which actually gives a different explain in both cases.

          Just after importing the dataset, without running analyze we get :

          +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
          | id   | select_type | table                   | type | possible_keys               | key      | key_len | ref                                     | rows | Extra                                                  |
          +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
          |    1 | SIMPLE      | toku_problem_topo       | ref  | dn,type_elt                 | type_elt | 33      | const                                   |  215 | Using index condition; Using temporary; Using filesort |
          |    1 | SIMPLE      | toku_problem_tempologie | ALL  | PRIMARY                     | NULL     | NULL    | NULL                                    | 4417 | Using where; Using join buffer (flat, BNL join)        |
          |    1 | SIMPLE      | toku_problem_myisam     | ref  | PRIMARY,ni,query,agg_sql_ne | ni       | 3       | toku_problem1.toku_problem_tempologie.d |    1 | Using where                                            |
          +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
          

          +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
          | id   | select_type | table                   | type | possible_keys               | key      | key_len | ref                                     | rows | Extra                                                  |
          +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
          |    1 | SIMPLE      | toku_problem_topo       | ref  | dn,type_elt                 | type_elt | 33      | const                                   |  215 | Using index condition; Using temporary; Using filesort |
          |    1 | SIMPLE      | toku_problem_tempologie | ALL  | PRIMARY                     | NULL     | NULL    | NULL                                    | 4417 | Using where; Using join buffer (flat, BNL join)        |
          |    1 | SIMPLE      | toku_problem_toku       | ref  | PRIMARY,ni,query,agg_sql_ne | PRIMARY  | 3       | toku_problem1.toku_problem_tempologie.d |    1 | Using where                                            |
          +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+
          

          And after running these ANALYZE :

          ANALYZE TABLE toku_problem_myisam;
          ANALYZE TABLE toku_problem_tempologie;
          ANALYZE TABLE toku_problem_toku;
          ANALYZE TABLE toku_problem_topo;
          

          We get:

          +------+-------------+-------------------------+------+-----------------------------+-------+---------+------------------------------------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+
          | id   | select_type | table                   | type | possible_keys               | key   | key_len | ref                                                                                                                          | rows  | Extra                                           |
          +------+-------------+-------------------------+------+-----------------------------+-------+---------+------------------------------------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+
          |    1 | SIMPLE      | toku_problem_tempologie | ALL  | PRIMARY                     | NULL  | NULL    | NULL                                                                                                                         |  4417 | Using where; Using temporary; Using filesort    |
          |    1 | SIMPLE      | toku_problem_topo       | ALL  | dn,type_elt                 | NULL  | NULL    | NULL                                                                                                                         | 21562 | Using where; Using join buffer (flat, BNL join) |
          |    1 | SIMPLE      | toku_problem_myisam     | ref  | PRIMARY,ni,query,agg_sql_ne | query | 14      | toku_problem1.toku_problem_topo.code_elt_sql,toku_problem1.toku_problem_tempologie.d,toku_problem1.toku_problem_tempologie.h |     1 | Using where                                     |
          +------+-------------+-------------------------+------+-----------------------------+-------+---------+------------------------------------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+
          

          +------+-------------+-------------------------+------+-----------------------------+---------+---------+-----------------------------------------+-------+-------------------------------------------------+
          | id   | select_type | table                   | type | possible_keys               | key     | key_len | ref                                     | rows  | Extra                                           |
          +------+-------------+-------------------------+------+-----------------------------+---------+---------+-----------------------------------------+-------+-------------------------------------------------+
          |    1 | SIMPLE      | toku_problem_tempologie | ALL  | PRIMARY                     | NULL    | NULL    | NULL                                    |  4417 | Using where; Using temporary; Using filesort    |
          |    1 | SIMPLE      | toku_problem_topo       | ALL  | dn,type_elt                 | NULL    | NULL    | NULL                                    | 21562 | Using where; Using join buffer (flat, BNL join) |
          |    1 | SIMPLE      | toku_problem_toku       | ref  | PRIMARY,ni,query,agg_sql_ne | PRIMARY | 3       | toku_problem1.toku_problem_tempologie.d |   100 | Using where                                     |
          +------+-------------+-------------------------+------+-----------------------------+---------+---------+-----------------------------------------+-------+-------------------------------------------------+
          

          Anyways in both cases we do not expect PRIMARY to be the chosen index for the table toku_problem_toku.

          Please find attached the my.cnf file we used my.cnf

          osiris-support Osiris Support added a comment - Hello, I am sorry but I forgot one step, we run an analyze on every table after importing the dataset, which actually gives a different explain in both cases. Just after importing the dataset, without running analyze we get : +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+ | 1 | SIMPLE | toku_problem_topo | ref | dn,type_elt | type_elt | 33 | const | 215 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | toku_problem_tempologie | ALL | PRIMARY | NULL | NULL | NULL | 4417 | Using where; Using join buffer (flat, BNL join ) | | 1 | SIMPLE | toku_problem_myisam | ref | PRIMARY,ni,query,agg_sql_ne | ni | 3 | toku_problem1.toku_problem_tempologie.d | 1 | Using where | +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+ +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+ | 1 | SIMPLE | toku_problem_topo | ref | dn,type_elt | type_elt | 33 | const | 215 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | toku_problem_tempologie | ALL | PRIMARY | NULL | NULL | NULL | 4417 | Using where; Using join buffer (flat, BNL join ) | | 1 | SIMPLE | toku_problem_toku | ref | PRIMARY,ni,query,agg_sql_ne | PRIMARY | 3 | toku_problem1.toku_problem_tempologie.d | 1 | Using where | +------+-------------+-------------------------+------+-----------------------------+----------+---------+-----------------------------------------+------+--------------------------------------------------------+ And after running these ANALYZE : ANALYZE TABLE toku_problem_myisam; ANALYZE TABLE toku_problem_tempologie; ANALYZE TABLE toku_problem_toku; ANALYZE TABLE toku_problem_topo; We get: +------+-------------+-------------------------+------+-----------------------------+-------+---------+------------------------------------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------------+------+-----------------------------+-------+---------+------------------------------------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+ | 1 | SIMPLE | toku_problem_tempologie | ALL | PRIMARY | NULL | NULL | NULL | 4417 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | toku_problem_topo | ALL | dn,type_elt | NULL | NULL | NULL | 21562 | Using where; Using join buffer (flat, BNL join ) | | 1 | SIMPLE | toku_problem_myisam | ref | PRIMARY,ni,query,agg_sql_ne | query | 14 | toku_problem1.toku_problem_topo.code_elt_sql,toku_problem1.toku_problem_tempologie.d,toku_problem1.toku_problem_tempologie.h | 1 | Using where | +------+-------------+-------------------------+------+-----------------------------+-------+---------+------------------------------------------------------------------------------------------------------------------------------+-------+-------------------------------------------------+ +------+-------------+-------------------------+------+-----------------------------+---------+---------+-----------------------------------------+-------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------------+------+-----------------------------+---------+---------+-----------------------------------------+-------+-------------------------------------------------+ | 1 | SIMPLE | toku_problem_tempologie | ALL | PRIMARY | NULL | NULL | NULL | 4417 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | toku_problem_topo | ALL | dn,type_elt | NULL | NULL | NULL | 21562 | Using where; Using join buffer (flat, BNL join ) | | 1 | SIMPLE | toku_problem_toku | ref | PRIMARY,ni,query,agg_sql_ne | PRIMARY | 3 | toku_problem1.toku_problem_tempologie.d | 100 | Using where | +------+-------------+-------------------------+------+-----------------------------+---------+---------+-----------------------------------------+-------+-------------------------------------------------+ Anyways in both cases we do not expect PRIMARY to be the chosen index for the table toku_problem_toku. Please find attached the my.cnf file we used my.cnf

          osiris-support,

          It cannot be the config file that you use for MariaDB 5.5/10.0. It contains options that no longer exist, so the server wouldn't even start with it.

          Also, importantly for this issue, it does not contain any TokuDB configuration at all. Could you please find the right cnf file(s)?

          elenst Elena Stepanova added a comment - osiris-support , It cannot be the config file that you use for MariaDB 5.5/10.0. It contains options that no longer exist, so the server wouldn't even start with it. Also, importantly for this issue, it does not contain any TokuDB configuration at all. Could you please find the right cnf file(s)?

          Hello,
          My apologies, I got confused indeed, please find the used my.cnf for "10.0.23-MariaDB" here : real.my.cnf
          We indeed have no specific TokuDB configuration, we use the default configuration for this test
          Regards

          osiris-support Osiris Support added a comment - Hello, My apologies, I got confused indeed, please find the used my.cnf for "10.0.23-MariaDB" here : real.my.cnf We indeed have no specific TokuDB configuration, we use the default configuration for this test Regards
          elenst Elena Stepanova added a comment - - edited

          Thanks.

          I'm getting somewhat different plans, but the part about query vs PRIMARY is reproducible (as of current post-10.0.26 tree), and I can also confirm that with the forced use of query index SELECT works faster on my machine.

          10.2 is also affected.

          psergey,
          I've set the tentative fix version to 10.1, please feel free to modify it as you see fit (and reassign the issue if necessary).

          elenst Elena Stepanova added a comment - - edited Thanks. I'm getting somewhat different plans, but the part about query vs PRIMARY is reproducible (as of current post-10.0.26 tree), and I can also confirm that with the forced use of query index SELECT works faster on my machine. 10.2 is also affected. psergey , I've set the tentative fix version to 10.1, please feel free to modify it as you see fit (and reassign the issue if necessary).

          Hello,
          I was wondering if there was any update on this issue, as it can have important consequences on server performances.
          Regards

          osiris-support Osiris Support added a comment - Hello, I was wondering if there was any update on this issue, as it can have important consequences on server performances. Regards

          People

            psergei Sergei Petrunia
            osiris-support Osiris Support
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.