[MDEV-10536] Loose index scan regression Created: 2016-08-11  Updated: 2016-08-31  Resolved: 2016-08-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.26
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mikhail Avdienko Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Debian amd64


 Description   

After upgrade from 10.0.20 to 10.0.26 some queries become many times slower because of optimizer.
Table definition:

CREATE TABLE `versioned_params` (
 
  `object_id` int(10) unsigned NOT NULL,
 
  `param_id` int(10) unsigned NOT NULL,
 
  `time_id` int(10) unsigned NOT NULL,
 
  `counter` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `device_time` datetime(3) NOT NULL,
 
  `value` mediumblob,
 
  PRIMARY KEY (`object_id`,`time_id`,`param_id`,`counter`),
 
  KEY `second_idx` (`object_id`,`param_id`,`device_time`,`time_id`)
 
) ENGINE=TokuDB

Query:

EXPLAIN SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;

Result on 10.0.20:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE versioned_params range PRIMARY,second_idx second_idx 15 NULL 1 Using where; Using index for group-by

Result on 10.0.26:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE versioned_params range PRIMARY,second_idx second_idx 4 NULL 8957720 Using where; Using index


 Comments   
Comment by Mikhail Avdienko [ 2016-08-11 ]

ANALYZE TABLE has never been executed on 10.0.20, so cardinality is unknown
SHOW INDEX on 10.0.20

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality
versioned_params 0 PRIMARY 1 object_id A NULL
versioned_params 0 PRIMARY 2 time_id A NULL
versioned_params 0 PRIMARY 3 param_id A NULL
versioned_params 0 PRIMARY 4 counter A 24300823203
versioned_params 1 second_idx 1 object_id A NULL
versioned_params 1 second_idx 2 param_id A NULL
versioned_params 1 second_idx 3 device_time A NULL
versioned_params 1 second_idx 4 time_id A NULL

SHOW INDEX on 10.0.26

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality
versioned_params 0 PRIMARY 1 object_id A 360774
versioned_params 0 PRIMARY 2 time_id A 398454726
versioned_params 0 PRIMARY 3 param_id A 4861147661
versioned_params 0 PRIMARY 4 counter A 24305738307
versioned_params 1 second_idx 1 object_id A 388531
versioned_params 1 second_idx 2 param_id A 9916661
versioned_params 1 second_idx 3 device_time A 24305738307
versioned_params 1 second_idx 4 time_id A 24305738307
Comment by Phil Sweeney [ 2016-08-14 ]

whitewind Check out https://jira.mariadb.org/browse/MDEV-10503 in case it is related. I had similar problems until I did an analyze across my TokuDB tables to fix cardinality and row counts.

Comment by Elena Stepanova [ 2016-08-14 ]

Isn't the issue described here an opposite of MDEV-10503?
There, the problem is that cardinality is always 0, and it can create all kinds of trouble; here, cardinality is actually calculated on the recent version, but performance drops.

Although, is it the right cardinality? Does the table really have over 24 bln rows?

Comment by Mikhail Avdienko [ 2016-08-15 ]

Indeed, it may be opposite of MDEV-10503.
The table really has 24 bln rows, but first columns of indices has only approximate cardinality because of finite tokudb_analyze_time. Actually there are about 4k unique object_id's and about 160k unique combinations of object_id and param_id

Comment by Elena Stepanova [ 2016-08-15 ]

whitewind,
So, how slower has it actually become with the new plan? And did the query return identical correct results on both versions?

While the first plan claims there would be only one row to examine, it is obviously not so.

Comment by Mikhail Avdienko [ 2016-08-15 ] 

SELECT SQL_NO_CACHE object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;

10.0.20:

  • First run: 23 rows in set (0.53 sec)
  • Second run:23 rows in set (0.00 sec)

10.0.26:

  • First run: 23 rows in set (8.49 sec)
  • Second run: 23 rows in set (6.73 sec)
Comment by Elena Stepanova [ 2016-08-26 ]

whitewind,

Did you run ANALYZE on 10.0.26?
With my artificial data I'm also getting the same different plans on 10.0.20 vs 10.0.26, but after ANALYZE 10.0.26 gets back to the same plan with 'Using where; Using index for group-by". But my table is much slower (500K rows), so there is no distinct difference in execution time, thus I can't confirm that performance gets back to normal after ANALYZE:

MariaDB [test]> select count(*) from versioned_params;
 
+----------+
 
| count(*) |
 
+----------+
 
|   543200 |
 
+----------+
 
1 row in set (1.65 sec)

10.0.20

 
MariaDB [test]> EXPLAIN SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
| id   | select_type | table            | type  | possible_keys      | key        | key_len | ref  | rows | Extra                                 |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
|    1 | SIMPLE      | versioned_params | range | PRIMARY,second_idx | second_idx | 15      | NULL |    1 | Using where; Using index for group-by |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
1 row in set (0.25 sec)
 
 
 
MariaDB [test]> SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
....
 
935 rows in set (0.23 sec)


MariaDB [test]> analyze table versioned_params;
 
+-----------------------+---------+----------+----------+
 
| Table                 | Op      | Msg_type | Msg_text |
 
+-----------------------+---------+----------+----------+
 
| test.versioned_params | analyze | status   | OK       |
 
+-----------------------+---------+----------+----------+
 
1 row in set (9.02 sec)


MariaDB [test]> EXPLAIN SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
| id   | select_type | table            | type  | possible_keys      | key        | key_len | ref  | rows | Extra                                 |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
|    1 | SIMPLE      | versioned_params | range | PRIMARY,second_idx | second_idx | 15      | NULL |  664 | Using where; Using index for group-by |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
....
 
935 rows in set (0.02 sec)

10.0.26

 
MariaDB [test]> EXPLAIN SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+-------+--------------------------+
 
| id   | select_type | table            | type  | possible_keys      | key        | key_len | ref  | rows  | Extra                    |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+-------+--------------------------+
 
|    1 | SIMPLE      | versioned_params | range | PRIMARY,second_idx | second_idx | 4       | NULL | 36416 | Using where; Using index |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+-------+--------------------------+
 
1 row in set (0.13 sec)
 
 
 
MariaDB [test]> SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
....
 
935 rows in set (0.04 sec)


MariaDB [test]> analyze table versioned_params;
 
+-----------------------+---------+----------+----------+
 
| Table                 | Op      | Msg_type | Msg_text |
 
+-----------------------+---------+----------+----------+
 
| test.versioned_params | analyze | status   | OK       |
 
+-----------------------+---------+----------+----------+
 
1 row in set (8.54 sec)


MariaDB [test]> EXPLAIN SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
| id   | select_type | table            | type  | possible_keys      | key        | key_len | ref  | rows | Extra                                 |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
|    1 | SIMPLE      | versioned_params | range | PRIMARY,second_idx | second_idx | 15      | NULL | 2286 | Using where; Using index for group-by |
 
+------+-------------+------------------+-------+--------------------+------------+---------+------+------+---------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT object_id, param_id, MAX(device_time) AS device_time FROM versioned_params WHERE 1 AND object_id IN (0,1782) AND device_time < '2016-08-08 16:00:00.000' GROUP BY object_id, param_id;
 
 
 
935 rows in set (0.03 sec)

Comment by Mikhail Avdienko [ 2016-08-30 ]

Well, after running ANALYZE for 5 hours it got back to the plan 'Using index for group-by' with following cardinalities:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality
versioned_params 0 PRIMARY 1 object_id A 39798
versioned_params 0 PRIMARY 2 time_id A 378101114
versioned_params 0 PRIMARY 3 param_id A 4159112263
versioned_params 0 PRIMARY 4 counter A 24954673582
versioned_params 1 second_idx 1 object_id A 41533
versioned_params 1 second_idx 2 param_id A 1175720
versioned_params 1 second_idx 3 device_time A 24954673582
versioned_params 1 second_idx 4 time_id A 24954673582

So, I guess it's not a bug. Sorry for inconvenience

Comment by Elena Stepanova [ 2016-08-30 ]

Yes, it's real pain that in order to have an optimal plan we have to run ANALYZE, which on big tables is barely possible, but I guess there isn't much that can be done about it right now.

Comment by Mikhail Avdienko [ 2016-08-31 ]

As a suggestion: could SELECT DISTINCT on first column of the index automatically update index cardinality?

Comment by Elena Stepanova [ 2016-08-31 ]

psergey, see the question above.

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