[MDEV-10321] MariaDB doesn't use the expected indexes with TokuDB Created: 2016-07-01  Updated: 2017-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - TokuDB
Affects Version/s: 5.5.38, 10.0, 10.1
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Osiris Support Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: TokuDB
Environment:

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

Attachments: File my.cnf     File real.my.cnf     File toku_problem1_database.sql    

 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

 Comments   
Comment by Elena Stepanova [ 2016-07-02 ]

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

Comment by Osiris Support [ 2016-07-04 ]

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

Comment by Elena Stepanova [ 2016-07-09 ]

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)?

Comment by Osiris Support [ 2016-07-15 ]

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

Comment by Elena Stepanova [ 2016-07-16 ]

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).

Comment by Osiris Support [ 2017-04-27 ]

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

Generated at Thu Feb 08 07:41:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.