[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

Activity

Ascending order - Click to sort in descending order

Juan created issue - 2018-08-09 23:46

Juan made changes - 2018-08-10 00:47

Field	Original Value	New Value
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 I will supply an abbreviated data set tomorrow. The query is a union that contains two IN clauses with over 1000 items and two others with over 68,000 items in each. 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) {noformat} 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 {noformat} Here is the MySQL 5.6 plan: {noformat} [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 {noformat} the MariaDb 10.2.15 plan: {noformat} [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: {noformat} ...and the extended MariaDB 10.3.8 plan with warnings: {noformat} [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` {noformat}	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) {noformat} 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 {noformat} Here is the MySQL 5.6 plan: {noformat} [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 {noformat} the MariaDb 10.2.15 plan: {noformat} [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: {noformat} ...and the extended MariaDB 10.3.8 plan with warnings: {noformat} [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` {noformat}

Igor Babaev (Inactive) made changes - 2018-08-10 01:31

Assignee

Igor Babaev [ igor ]

Juan made changes - 2018-08-10 16:39

Affects Version/s		10.3.8 [ 23113 ]
Affects Version/s	10.3.6 [ 23003 ]

Igor Babaev (Inactive) made changes - 2018-08-11 02:58

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2018-08-13 07:54

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]

Juan made changes - 2018-09-12 23:07

Attachment

MDEV-16934 Issue 20927 timing results.pdf [ 46415 ]

Igor Babaev (Inactive) made changes - 2018-09-13 20:52

Fix Version/s		10.2.18 [ 23112 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Igor Babaev (Inactive) made changes - 2018-09-13 20:53

Fix Version/s

10.3.10 [ 23140 ]

Sergei Petrunia made changes - 2018-10-12 11:02

Link

This issue relates to MDEV-17425 [ MDEV-17425 ]

Elena Stepanova made changes - 2018-10-17 20:50

Link

This issue causes ~~MDEV-17486~~ [ ~~MDEV-17486~~ ]

Rob Schwyzer (Inactive) made changes - 2021-06-24 20:24

Labels

optimizer

ServiceNow optimizer

Rob Schwyzer (Inactive) made changes - 2021-07-02 19:57

Labels

ServiceNow optimizer

76qDvLB8Gju6Hs7nk3VY3EX42G795W5z optimizer

Sergei Golubchik made changes - 2021-08-13 22:33

Labels

76qDvLB8Gju6Hs7nk3VY3EX42G795W5z optimizer

optimizer

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88822 ]

MariaDB v4 [ 154773 ]

Jira Automation (IT) made changes - 2024-07-04 07:23

Zendesk Related Tickets		201658 188004
Zendesk active tickets		201658

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Juan

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2018-08-09 23:46

Updated:: 2024-09-06 10:00

Resolved:: 2018-09-13 20:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration