Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.9.5
-
None
-
Linux version 4.9.0-6-amd64 (debian-kernel@lists.debian.org) (gcc version 6.3.0 20170516 (Debian 6.3.0-18+deb9u1) ) #1 SMP Debian 4.9.82-1+deb9u3 (2018-03-02)
ldd (Debian GLIBC 2.31-13+deb11u5) 2.31
Jemalloc
Description
Following migration from 10.6.x to 10.9.5
At some point different type of queries start taking hours to execute while they should be fast what ever theorical execution plan was chosen.
We have been observing massive amount of innodb row reads
One query is more relevant to dig into the issue because it involved only 2 tables one small (100K) and one large (300M).
HERE IS THE BAD PLAN that run for 3 hours before being killed
explain SELECT count(*) FROM `data_entity` AS `e` INNER JOIN `data_value` `sort` ON `e`.`uri` = `sort`.`entity_uri` AND `sort`.`predicate_uri` = "population" and `sort`.`date` = "2014-01-01" WHERE `e`.`type_uri` = "Town" AND `e`.`status` = 1;
|
+------+-------------+-------+------+----------------------------------+--------------------------+---------+-----------------------------------------------+------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+----------------------------------+--------------------------+---------+-----------------------------------------------+------+------------------------------------+
|
| 1 | SIMPLE | e | ref | PRIMARY,data_entity_type_uri_idx | data_entity_type_uri_idx | 767 | const | 1 | Using index condition; Using where |
|
| 1 | SIMPLE | sort | ref | entity_uri,dpne,pne,pve,ped | ped | 1538 | const,directory_figaro_immobilier.e.uri,const | 1 | Using where; Using index |
|
+------+-------------+-------+------+----------------------------------+--------------------------+---------+-----------------------------------------------+------+------------------------------------+
|
Note there is a ref access on small table using data_entity_type_uri_idx and join the big table using ped index with an other ref .
As one can see there is issue with statistics reporting one row for every part of the plan
Now we are fixing the statistics issue
ANALYZE TABLE data_entity PERSISTENT FOR ALL;
explain SELECT count(*) FROM `data_entity` AS `e` INNER JOIN `data_value` `sort` ON `e`.`uri` = `sort`.`entity_uri` AND `sort`.`predicate_uri` = "population" and `sort`.`date` = "2014-01-01" WHERE `e`.`type_uri` = "Town" AND `e`.`status` = 1;
|
+------+-------------+-------+------+----------------------------------+------+---------+-----------------------------------------------+-------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+----------------------------------+------+---------+-----------------------------------------------+-------+--------------------------+
|
| 1 | SIMPLE | e | ALL | PRIMARY,data_entity_type_uri_idx | NULL | NULL | NULL | 98035 | Using where |
|
| 1 | SIMPLE | sort | ref | entity_uri,dpne,pne,pve,ped | ped | 1538 | const,directory_figaro_immobilier.e.uri,const | 1 | Using where; Using index |
|
+------+-------------+-------+------+----------------------------------+------+---------+-----------------------------------------------+-------+--------------------------+
|
2 rows in set (0.001 sec)
|
 |
SELECT
|
-> `sort`.`value` AS `sortingValue`,
|
-> uri,
|
-> type_uri,
|
-> longitude,
|
-> latitude,
|
-> status
|
-> FROM
|
-> `data_entity` AS `e`
|
-> INNER JOIN `data_value` `sort` ON `e`.`uri` = `sort`.`entity_uri`
|
-> AND `sort`.`predicate_uri` = "population" and `sort`.`date` = "2014-01-01"
|
-> WHERE
|
-> `e`.`type_uri` = "Town"
|
-> AND `e`.`status` = 1
|
-> ORDER BY
|
-> `sort`.`numeric_value` DESC
|
-> LIMIT 5;
|
+--------------+-------------+----------+-----------+----------+--------+
|
| sortingValue | uri | type_uri | longitude | latitude | status |
|
+--------------+-------------+----------+-----------+----------+--------+
|
| 2243739 | ville-75056 | Town | 2.33828 | 48.8589 | 1 |
|
| 866644 | ville-13055 | Town | 5.37479 | 43.2977 | 1 |
|
| 514707 | ville-69123 | Town | 4.83287 | 45.7612 | 1 |
|
| 474246 | ville-31555 | Town | 1.43167 | 43.5964 | 1 |
|
| 347636 | ville-06088 | Town | 7.23827 | 43.712 | 1 |
|
+--------------+-------------+----------+-----------+----------+--------+
|
5 rows in set (0.370 sec)
|
From here we know we have a serious bug as the difference to FULL SCAN vs REF on 100K records can not lead to 3 hours difference in execution time.
But more problematic is that now that EITS statistics tables are in place i could not reproduce the issue
explain SELECT `sort`.`value` AS `sortingValue`, uri, type_uri, longitude, latitude, status FROM `data_entity` AS `e` force index(data_entity_type_uri_idx) STRAIGHT_JOIN `data_value` `sort` ON `e`.`uri` = `sort`.`entity_uri` AND `sort`.`predicate_uri` = "population" and `sort`.`date` = "2014-01-01" WHERE `e`.`type_uri` = "Town" AND `e`.`status` = 1 ORDER BY `sort`.`numeric_value` DESC LIMIT 5\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: e
|
type: ref
|
possible_keys: data_entity_type_uri_idx
|
key: data_entity_type_uri_idx
|
key_len: 767
|
ref: const
|
rows: 49463
|
Extra: Using index condition; Using where; Using temporary; Using filesort
|
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: sort
|
type: ref
|
possible_keys: entity_uri,dpne,pne,pve,ped
|
key: ped
|
key_len: 1538
|
ref: const,directory_figaro_immobilier.e.uri,const
|
rows: 1
|
Extra: Using index condition
|
2 rows in set (0.001 sec)
|
 |
SELECT `sort`.`value` AS `sortingValue`, uri, type_uri, longitude, latitude, status FROM `data_entity` AS `e` force index(data_entity_type_uri_idx) STRAIGHT_JOIN `data_value` `sort` ON `e`.`uri` = `sort`.`entity_uri` AND `sort`.`predicate_uri` = "population" and `sort`.`date` = "2014-01-01" WHERE `e`.`type_uri`
|
= "Town" AND `e`.`status` = 1 ORDER BY `sort`.`numeric_value` DESC LIMIT 5\G
|
*************************** 1. row ***************************
|
sortingValue: 2243739
|
uri: ville-75056
|
type_uri: Town
|
longitude: 2.33828
|
latitude: 48.8589
|
status: 1
|
*************************** 2. row ***************************
|
sortingValue: 866644
|
uri: ville-13055
|
type_uri: Town
|
longitude: 5.37479
|
latitude: 43.2977
|
status: 1
|
*************************** 3. row ***************************
|
sortingValue: 514707
|
uri: ville-69123
|
type_uri: Town
|
longitude: 4.83287
|
latitude: 45.7612
|
status: 1
|
*************************** 4. row ***************************
|
sortingValue: 474246
|
uri: ville-31555
|
type_uri: Town
|
longitude: 1.43167
|
latitude: 43.5964
|
status: 1
|
*************************** 5. row ***************************
|
sortingValue: 347636
|
uri: ville-06088
|
type_uri: Town
|
longitude: 7.23827
|
latitude: 43.712
|
status: 1
|
5 rows in set (0.348 sec)
|
|
delete from index_stats where table_name='data_entity;
|
delete from column_stats where table_name='data_entity';
|
delete from table_stats where table_name='data_entity';
|
Flush tables;
|
 |
 |
ANALYZE SELECT `sort`.`value` AS `sortingValue`, uri, type_uri, longitude, latitude, status FROM `data_entity` AS `e` force index(data_entity_type_uri_idx) STRAIGHT_JOIN `data_value` `sort` ON `e`.`uri` = `sort`.`entity_uri` AND `sort`.`predicate_uri` = "population" and `sort`.`date` = "2014-01-01" WHERE `e`.`type_uri` = "Town" AND `e`.`status` = 1 ORDER BY `sort`.`numeric_value` DESC LIMIT 5\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: e
|
type: ref
|
possible_keys: data_entity_type_uri_idx
|
key: data_entity_type_uri_idx
|
key_len: 767
|
ref: const
|
rows: 48621
|
r_rows: 34955.00
|
filtered: 100.00
|
r_filtered: 100.00
|
Extra: Using index condition; Using where; Using temporary; Using filesort
|
*************************** 2. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: sort
|
type: ref
|
possible_keys: entity_uri,dpne,pne,pve,ped
|
key: ped
|
key_len: 1538
|
ref: const,directory_figaro_immobilier.e.uri,const
|
rows: 1
|
r_rows: 1.00
|
filtered: 4.48
|
r_filtered: 100.00
|
Extra: Using index condition
|
2 rows in set (0.795 sec)
|
rows estimate on table e never get back to 1 as in the hurting plan and the query is alway fast now a RANGE is prefered over the REF without forcing and with no stats tables informations.
Glad to give more info if needed or spend. some time to try to reproduce if you get more ideas on what can lead to such issue .