[MDEV-16934] Query with very large IN clause lists runs slowly. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2.15, 10.3.8
Fix Version/s: 10.2.18, 10.3.10
Component/s: Optimizer
Labels:
- optimizer
Environment:
RHEL 7.5

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`

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

MDEV-16934 Issue 20927 timing results.pdf
21 kB
2018-09-12 23:07

Issue Links

causes

MDEV-17486 eq_range_index_dives_limit option/variable is not documented

Closed

relates to

MDEV-17425 Improve range optimizer to stop evaluating cost using index dive when reaching the cost of any competing EQU_REF range

Open

Query with very large IN clause lists runs slowly.

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration