[MDEV-16934] Query with very large IN clause lists runs slowly. Created: 2018-08-09  Updated: 2021-09-30  Resolved: 2018-09-13

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.15, 10.3.8
Fix Version/s: 10.2.18, 10.3.10

Type: Bug Priority: Critical
Reporter: Juan Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: optimizer
Environment:

RHEL 7.5

Attachments: PDF File MDEV-16934 Issue 20927 timing results.pdf    
Issue Links:
Problem/Incident
causes MDEV-17486 eq_range_index_dives_limit option/var... Closed
Relates
relates to MDEV-17425 Improve range optimizer to stop evalu... Open

 Description   

Customer filed a support issue concerned that migrating from MySQL 5.6 to MariaDB 10.2.15 was causing a query that ran in 7 seconds in the old environment to run in ~300s in MariaDB. Testing side-by-side confirmed the difference in performance.

The specific query and data-set are included in the referenced support case, and will be linked in an internal comment, and an abbreviated query is also included in comments. The full query is a union that contains two IN clauses with over 1000 items and two others with over 68,000 items in each, totaling 138,180 items listed in IN clauses.

Testing revealed that MariaDB 10.2.15 runs approximately 40x longer than MySQL 5.6 to return the results, and 10.3.8 runs 20x longer than MySQL 5.6 in the same operation.

The following information is tested on MariaDB 10.2.15

Increasing max_heap_table_size, tmp_table_size, and key_buffer_size substantially (to 128M each) had no noticeable effect on performance, and toggling every available optimization switch resulted in variances of no more than 12% in execution speed.

Changing default_tmp_storage_engine to MEMORY and ISAM had no significant effect on performance either.

The following optimization switches were toggled and resulted in no meaningful performance change (all in MariaDb 10.2.15)

condition_pushdown_for_derived=off  717 sec
 
derived_merge=off  719 sec
 
derived_with_keys=off  713 sec
 
engine_condition_pushdown=on  738 sec
 
exists_to_in=off  735 sec
 
extended_keys=off  711 sec
 
firstmatch=off  712 sec
 
index_condition_pushdown=off  728 sec
 
index_merge=off  745 sec
 
index_merge_intersection=off  721 sec
 
index_merge_sort_intersection=on  731 sec
 
index_merge_sort_union=off  737 sec
 
index_merge_union=off  755 sec
 
in_to_exists=off  734 sec
 
join_cache_bka=off  749 sec
 
join_cache_hashed=off  740 sec
 
join_cache_incremental=off  713 sec
 
loosescan=off  745 sec
 
materialization=off  737 sec
 
mrr=on  718 sec
 
mrr_cost_based=on  812 sec
 
mrr_sort_keys=on  777 sec
 
optimize_join_buffer_size=on  749 sec
 
orderby_uses_equalities=off  809 sec
 
outer_join_with_cache=off  728 sec
 
partial_match_rowid_merge=off  736 sec
 
partial_match_table_scan=off  725 sec
 
semijoin=off  753 sec
 
semijoin_with_cache=off  809 sec
 
subquery_cache=off  795 sec
 
table_elimination=off  733 sec

Here is the MySQL 5.6 plan:

[root@MySQL-5-6-34-216-202-163-RD ~]# echo "explain $(cat 20927_00_myQ.sql)" | mysql d20927
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: PRIMARY
 
        table: <derived2>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 15232
 
        Extra: NULL
 
*************************** 2. row ***************************
 
           id: 2
 
  select_type: DERIVED
 
        table: MVWorkLogEntry
 
         type: range
 
possible_keys: StoreID,mPlanID,ClassName,mPlanStoreClassStatus
 
          key: mPlanID
 
      key_len: 4
 
          ref: NULL
 
         rows: 8704
 
        Extra: Using index condition; Using where
 
*************************** 3. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: MVWorkLogEntry
 
         type: range
 
possible_keys: PRIMARY,StoreID,mPlanID,mPlanStoreClassStatus
 
          key: mPlanID
 
      key_len: 4
 
          ref: NULL
 
         rows: 8704
 
        Extra: Using index condition; Using where; Using temporary
 
*************************** 4. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: MVPendingWorkLogEntry
 
         type: eq_ref
 
possible_keys: PRIMARY
 
          key: PRIMARY
 
      key_len: 4
 
          ref: d20927.MVWorkLogEntry.ID
 
         rows: 1
 
        Extra: Using where
 
*************************** 5. row ***************************
 
           id: NULL
 
  select_type: UNION RESULT
 
        table: <union2,3>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: NULL
 
        Extra: Using temporary

the MariaDb 10.2.15 plan:

[root@Maria-54-203-18-168-RD ~]# echo "explain $(cat 20927_10_myQ2.sql)" | mysql d20927
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: PRIMARY
 
        table: <derived2>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 3968
 
        Extra:
 
*************************** 2. row ***************************
 
           id: 2
 
  select_type: DERIVED
 
        table: MVWorkLogEntry
 
         type: range
 
possible_keys: StoreID,mPlanID,ClassName,mPlanStoreClassStatus
 
          key: mPlanID
 
      key_len: 4
 
          ref: NULL
 
         rows: 2267
 
        Extra: Using index condition; Using where
 
*************************** 3. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: MVWorkLogEntry
 
         type: range
 
possible_keys: PRIMARY,StoreID,mPlanID,mPlanStoreClassStatus
 
          key: mPlanID
 
      key_len: 4
 
          ref: NULL
 
         rows: 2267
 
        Extra: Using index condition; Using where; Using temporary
 
*************************** 4. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: MVPendingWorkLogEntry
 
         type: eq_ref
 
possible_keys: PRIMARY,Closed
 
          key: PRIMARY
 
      key_len: 4
 
          ref: d20927.MVWorkLogEntry.ID
 
         rows: 1
 
        Extra: Using where; Distinct
 
*************************** 5. row ***************************
 
           id: NULL
 
  select_type: UNION RESULT
 
        table: <union2,3>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: NULL
 
        Extra:

...and the extended MariaDB 10.3.8 plan with warnings:

[root@Maria-34-217-194-169-RD ~]# echo "explain extended $(cat 20927_10_myQ2.sql); show warnings;" | mysql d20927
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: PRIMARY
 
        table: <derived2>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 8
 
     filtered: 100.00
 
        Extra:
 
*************************** 2. row ***************************
 
           id: 2
 
  select_type: DERIVED
 
        table: <derived7>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 2
 
     filtered: 100.00
 
        Extra: Start temporary
 
*************************** 3. row ***************************
 
           id: 2
 
  select_type: DERIVED
 
        table: <derived5>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 2
 
     filtered: 100.00
 
        Extra: Using join buffer (flat, BNL join)
 
*************************** 4. row ***************************
 
           id: 2
 
  select_type: DERIVED
 
        table: MVWorkLogEntry
 
         type: ref
 
possible_keys: StoreID,mPlanID,ClassName,mPlanStoreClassStatus
 
          key: mPlanStoreClassStatus
 
      key_len: 10
 
          ref: tvc_0.75333,tvc_1.198963,const
 
         rows: 1
 
     filtered: 100.00
 
        Extra: Using index condition; End temporary
 
*************************** 5. row ***************************
 
           id: 7
 
  select_type: DERIVED
 
        table: NULL
 
         type: NULL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: NULL
 
     filtered: NULL
 
        Extra: No tables used
 
*************************** 6. row ***************************
 
           id: 5
 
  select_type: DERIVED
 
        table: NULL
 
         type: NULL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: NULL
 
     filtered: NULL
 
        Extra: No tables used
 
*************************** 7. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: <derived11>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 2
 
     filtered: 100.00
 
        Extra: Start temporary; Using temporary
 
*************************** 8. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: <derived9>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 2
 
     filtered: 100.00
 
        Extra: Using join buffer (flat, BNL join)
 
*************************** 9. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: MVWorkLogEntry
 
         type: ref
 
possible_keys: PRIMARY,StoreID,mPlanID,mPlanStoreClassStatus
 
          key: mPlanStoreClassStatus
 
      key_len: 8
 
          ref: tvc_0.75333,tvc_1.198963
 
         rows: 1
 
     filtered: 100.00
 
        Extra: Using index condition; End temporary
 
*************************** 10. row ***************************
 
           id: 3
 
  select_type: UNION
 
        table: MVPendingWorkLogEntry
 
         type: eq_ref
 
possible_keys: PRIMARY,Closed
 
          key: PRIMARY
 
      key_len: 4
 
          ref: d20927.MVWorkLogEntry.ID
 
         rows: 1
 
     filtered: 100.00
 
        Extra: Using where; Distinct
 
*************************** 11. row ***************************
 
           id: 11
 
  select_type: DERIVED
 
        table: NULL
 
         type: NULL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: NULL
 
     filtered: NULL
 
        Extra: No tables used
 
*************************** 12. row ***************************
 
           id: 9
 
  select_type: DERIVED
 
        table: NULL
 
         type: NULL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: NULL
 
     filtered: NULL
 
        Extra: No tables used
 
*************************** 13. row ***************************
 
           id: NULL
 
  select_type: UNION RESULT
 
        table: <union2,3>
 
         type: ALL
 
possible_keys: NULL
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: NULL
 
     filtered: NULL
 
        Extra:
 
Level	Code	Message
 
Note	1003	/* select#1 */ select `don`.`LogEntryID` AS `LogEntryID`,`don`.`ClassName` AS `ClassName`,`don`.`LastEdited` AS `LastEdited`,`don`.`RepID` AS `RepID`,`don`.`StoreID` AS `StoreID`,`don`.`mPlanID` AS `mPlanID`,`don`.`Status` AS `Status` from (/* select#2 */ select `d20927`.`MVWorkLogEntry`.`ID` AS `LogEntryID`,`d20927`.`MVWorkLogEntry`.`ClassName` AS `ClassName`,`d20927`.`MVWorkLogEntry`.`LastEdited` AS `LastEdited`,`d20927`.`MVWorkLogEntry`.`RepID` AS `RepID`,`d20927`.`MVWorkLogEntry`.`StoreID` AS `StoreID`,`d20927`.`MVWorkLogEntry`.`mPlanID` AS `mPlanID`,`d20927`.`MVWorkLogEntry`.`Status` AS `Status` from `d20927`.`MVWorkLogEntry` semi join ((values (75333),(75334),(75954),(76257),(76411),(76564),(76565),(76721),(76722),(76724),(76725),(76726),(76727),(76728),(76729),(76730),(76731),(76732),(76733
 
 
 
00),(77801),(77802),(77803),(77804),(77805),(77806),(77807),(77808),(77809),(77811),(77937),(77968),(77969)) `tvc_0`) semi join ((values (198963),(208459),(213146),(217375),(160344),(165363),(169957),(175520),(221764),(226415),(231647),(235837),(239769),(180975),(186246),(191059),(198979),(208477),(213162),(
 
 
 
8),(213145),(217374),(160343),(165362),(169956),(175519),(221763),(226414),(231646),(235836),(239768),(180974),(186245),(191058)) `tvc_1`) where `d20927`.`MVWorkLogEntry`.`ClassName` = 'MVWorkLogEntry' and `d20927`.`MVWorkLogEntry`.`mPlanID` = `tvc_0`.`75333` and `d20927`.`MVWorkLogEntry`.`StoreID` = `tvc_1`.`198963` union (/* select#3 */ select distinct `d20927`.`MVWorkLogEntry`.`ID` AS `LogEntryID`,`d20927`.`MVWorkLogEntry`.`ClassName` AS `ClassName`,`d20927`.`MVWorkLogEntry`.`LastEdited` AS `LastEdited`,`d20927`.`MVWorkLogEntry`.`RepID` AS `RepID`,`d20927`.`MVWorkLogEntry`.`StoreID` AS `StoreID`,`d20927`.`MVWorkLogEntry`.`mPlanID` AS `mPlanID`,`d20927`.`MVWorkLogEntry`.`Status` AS `Status` from `d20927`.`MVWorkLogEntry` semi join ((values (75333),(75334),(75954),(76257),(76411),(76564),(76565),(76721),(76722),
 
 
 
3),(77804),(77805),(77806),(77807),(77808),(77809),(77811),(77937),(77968),(77969)) `tvc_0`) semi join ((values (198963),(208459),(213146),(217375),(160344),(165363),(169957),(175520),(221764),(226415),(231
 
 
 
(226398),(231630),(235820),(239752),(180956),(186225),(191040),(198961),(208458),(213145),(217374),(160343),(165362),(169956),(175519),(221763),(226414),(231646),(235836),(239768),(180974),(186245),(191058)) `tvc_1`) straight_join `d20927`.`MVPendingWorkLogEntry` where `d20927`.`MVPendingWorkLogEntry`.`Closed` = 0 and `d20927`.`MVWorkLogEntry`.`mPlanID` = `tvc_0`.`75333` and `d20927`.`MVWorkLogEntry`.`StoreID` = `tvc_1`.`198963` and `d20927`.`MVPendingWorkLogEntry`.`ID` = `d20927`.`MVWorkLogEntry`.`ID` and `d20927`.`MVWorkLogEntry`.`Status` <> 'paused')) `don`



 Comments   
Comment by Sergei Petrunia [ 2018-08-17 ]

Review input: http://lists.askmonty.org/pipermail/commits/2018-August/012804.html

Comment by Marko Mäkelä [ 2018-08-24 ]

igor, the fix did not trivially merge to 10.3.
Please pull bb-10.3-marko, revert the top commit and fix the call chains so that the seq_init_param will be passed correctly.

Comment by Igor Babaev [ 2018-08-25 ]

Marko,
I changed the fix for MDEV- 16934, pushed the changes into 10.2. Then I cherry-picked the changes and pushed them into bb-10.3-marko.

Comment by Igor Babaev [ 2018-09-13 ]

A fix for this bug was pushed into 10.2

Generated at Thu Feb 08 08:32:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.