[MDEV-29413] Overzealous optimizer in 10.6.9? Created: 2022-08-30  Updated: 2022-09-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6.9
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: Falko Modler Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:
  • Official docker image
  • Hibernate ORM 5.6.9.Final within Quarkus 2.10.3.Final (but also with latest Quarkus 2.12.0.Final)


 Description   

When updating from 10.6.8 to 10.6.9, I started getting transaction timeouts (> 2 mins) for SELECTS on really small datasets that were fairly swift before in 10.6.8.
The affected queries are also popping up in the slow query log in 10.6.9.

One example is this (slightly obfuscated) query that is run in a Quarkus unit test (which also means that the dataset is totally equal to 10.6.8):

    select
 
        someapplic0_.`id` as id1_9_0_,
 
        someapplic0_.`changed_by_id` as changed_7_9_0_,
 
        someapplic0_.`comment` as comment2_9_0_,
 
        someapplic0_.`date_time` as date_tim3_9_0_,
 
        someapplic0_.`main_document_updated` as main_doc4_9_0_,
 
        someapplic0_.`other_document_updated` as other_do5_9_0_,
 
        someapplic0_.`reason_id` as reason_i8_9_0_,
 
        someapplic0_.`action_id` as action_i9_9_0_,
 
        someapplic0_.`comment_visible_for_ca` as comment_6_9_0_,
 
        someapplic0_.`some_application_id` as some_ap10_9_0_,
 
        someapplic0_.`new_state_id` as new_sta11_9_0_,
 
        someapplic0_.`old_state_id` as old_sta12_9_0_,
 
        user1_.`id` as id1_22_1_,
 
        user1_.`account_id` as account_8_22_1_,
 
        user1_.`authority_id` as authorit9_22_1_,
 
        user1_.`e_mail` as e_mail2_22_1_,
 
        user1_.`first_name` as first_na3_22_1_,
 
        user1_.`full_name` as full_nam4_22_1_,
 
        user1_.`last_name` as last_nam5_22_1_,
 
        user1_.`marked_for_deletion` as marked_f6_22_1_,
 
        user1_.`phone_number` as phone_nu7_22_1_,
 
        account2_.`id` as id1_0_2_,
 
        account2_.`deactivation_reason_id` as deactiva7_0_2_,
 
        account2_.`idp_id` as idp_id2_0_2_,
 
        account2_.`is_internal_temporary_state` as is_inter3_0_2_,
 
        account2_.`last_state_change` as last_sta4_0_2_,
 
        account2_.`roles_display_order` as roles_di5_0_2_,
 
        account2_.`state_id` as state_id8_0_2_,
 
        account2_.`user_name` as user_nam6_0_2_,
 
        confirming3_.`id` as id1_5_3_,
 
        confirming3_.`address_city` as address_2_5_3_,
 
        confirming3_.`address_postal_code` as address_3_5_3_,
 
        confirming3_.`address_street` as address_4_5_3_,
 
        confirming3_.`address_street_number` as address_5_5_3_,
 
        confirming3_.`address_display_name` as address_6_5_3_,
 
        confirming3_.`covered_professions_display_name` as covered_7_5_3_,
 
        confirming3_.`de_mail` as de_mail8_5_3_,
 
        confirming3_.`default_admin_id` as default17_5_3_,
 
        confirming3_.`e_mail` as e_mail9_5_3_,
 
        confirming3_.`key` as key10_5_3_,
 
        confirming3_.`marked_for_deletion` as marked_11_5_3_,
 
        confirming3_.`name` as name12_5_3_,
 
        confirming3_.`organizational_unit_one` as organiz13_5_3_,
 
        confirming3_.`organizational_unit_two` as organiz14_5_3_,
 
        confirming3_.`phone_number` as phone_n15_5_3_,
 
        confirming3_.`representative_name` as represe16_5_3_,
 
        confirming3_.`state_of_service_id` as state_o18_5_3_,
 
        catalogval4_.`id` as id2_3_4_,
 
        catalogval4_.`display_name` as display_3_3_4_,
 
        catalogval4_.`key` as key4_3_4_,
 
        catalogval4_.`order_value` as order_va5_3_4_,
 
        catalogval4_.`catalog_type` as catalog_1_3_4_,
 
        someaction5_.`id` as id2_3_5_,
 
        someaction5_.`display_name` as display_3_3_5_,
 
        someaction5_.`key` as key4_3_5_,
 
        someaction5_.`order_value` as order_va5_3_5_,
 
        someapplic6_.`id` as id1_7_6_,
 
        someapplic6_.`address_city` as address_2_7_6_,
 
        someapplic6_.`address_postal_code` as address_3_7_6_,
 
        someapplic6_.`address_street` as address_4_7_6_,
 
        someapplic6_.`address_street_number` as address_5_7_6_,
 
        someapplic6_.`applied_at` as applied_6_7_6_,
 
        someapplic6_.`birth_date` as birth_da7_7_6_,
 
        someapplic6_.`birth_name` as birth_na8_7_6_,
 
        someapplic6_.`birth_place` as birth_pl9_7_6_,
 
        someapplic6_.`confirming_authority_id` as confirm24_7_6_,
 
        someapplic6_.`country_id` as country25_7_6_,
 
        someapplic6_.`de_mail` as de_mail10_7_6_,
 
        someapplic6_.`e_mail` as e_mail11_7_6_,
 
        someapplic6_.`egbr_key` as egbr_ke12_7_6_,
 
        someapplic6_.`first_name` as first_n13_7_6_,
 
        someapplic6_.`full_name` as full_na14_7_6_,
 
        someapplic6_.`key` as key15_7_6_,
 
        someapplic6_.`last_log_entry_id` as last_lo26_7_6_,
 
        someapplic6_.`last_name` as last_na16_7_6_,
 
        someapplic6_.`other_document_id` as other_d27_7_6_,
 
        someapplic6_.`payment_id` as payment28_7_6_,
 
        someapplic6_.`payment_state_id` as payment29_7_6_,
 
        someapplic6_.`phone_number` as phone_n17_7_6_,
 
        someapplic6_.`print_line1` as print_l18_7_6_,
 
        someapplic6_.`print_line2` as print_l19_7_6_,
 
        someapplic6_.`process_key` as process20_7_6_,
 
        someapplic6_.`profession_id` as profess30_7_6_,
 
        someapplic6_.`profession_certificate_id` as profess31_7_6_,
 
        someapplic6_.`selected_tsp_id` as selecte32_7_6_,
 
        someapplic6_.`state_id` as state_i33_7_6_,
 
        someapplic6_.`state_of_profession_id` as state_o34_7_6_,
 
        someapplic6_.`telematik_key` as telemat21_7_6_,
 
        someapplic6_.`training_facility` as trainin22_7_6_,
 
        someapplic6_.`year_of_certificate` as year_of23_7_6_,
 
        confirming7_.`id` as id1_5_7_,
 
        confirming7_.`address_city` as address_2_5_7_,
 
        confirming7_.`address_postal_code` as address_3_5_7_,
 
        confirming7_.`address_street` as address_4_5_7_,
 
        confirming7_.`address_street_number` as address_5_5_7_,
 
        confirming7_.`address_display_name` as address_6_5_7_,
 
        confirming7_.`covered_professions_display_name` as covered_7_5_7_,
 
        confirming7_.`de_mail` as de_mail8_5_7_,
 
        confirming7_.`default_admin_id` as default17_5_7_,
 
        confirming7_.`e_mail` as e_mail9_5_7_,
 
        confirming7_.`key` as key10_5_7_,
 
        confirming7_.`marked_for_deletion` as marked_11_5_7_,
 
        confirming7_.`name` as name12_5_7_,
 
        confirming7_.`organizational_unit_one` as organiz13_5_7_,
 
        confirming7_.`organizational_unit_two` as organiz14_5_7_,
 
        confirming7_.`phone_number` as phone_n15_5_7_,
 
        confirming7_.`representative_name` as represe16_5_7_,
 
        confirming7_.`state_of_service_id` as state_o18_5_7_,
 
        country8_.`id` as id2_3_8_,
 
        country8_.`display_name` as display_3_3_8_,
 
        country8_.`key` as key4_3_8_,
 
        country8_.`order_value` as order_va5_3_8_,
 
        someapplic9_.`id` as id1_9_9_,
 
        someapplic9_.`changed_by_id` as changed_7_9_9_,
 
        someapplic9_.`comment` as comment2_9_9_,
 
        someapplic9_.`date_time` as date_tim3_9_9_,
 
        someapplic9_.`main_document_updated` as main_doc4_9_9_,
 
        someapplic9_.`other_document_updated` as other_do5_9_9_,
 
        someapplic9_.`reason_id` as reason_i8_9_9_,
 
        someapplic9_.`action_id` as action_i9_9_9_,
 
        someapplic9_.`comment_visible_for_ca` as comment_6_9_9_,
 
        someapplic9_.`some_application_id` as some_ap10_9_9_,
 
        someapplic9_.`new_state_id` as new_sta11_9_9_,
 
        someapplic9_.`old_state_id` as old_sta12_9_9_,
 
        paymentsta10_.`id` as id2_3_10_,
 
        paymentsta10_.`display_name` as display_3_3_10_,
 
        paymentsta10_.`key` as key4_3_10_,
 
        paymentsta10_.`order_value` as order_va5_3_10_,
 
        healthcare11_.`id` as id2_3_11_,
 
        healthcare11_.`display_name` as display_3_3_11_,
 
        healthcare11_.`key` as key4_3_11_,
 
        healthcare11_.`order_value` as order_va5_3_11_,
 
        tsp12_.`id` as id2_3_12_,
 
        tsp12_.`display_name` as display_3_3_12_,
 
        tsp12_.`key` as key4_3_12_,
 
        tsp12_.`order_value` as order_va5_3_12_,
 
        someapplic13_.`id` as id2_3_13_,
 
        someapplic13_.`display_name` as display_3_3_13_,
 
        someapplic13_.`key` as key4_3_13_,
 
        someapplic13_.`order_value` as order_va5_3_13_,
 
        federalsta14_.`id` as id2_3_14_,
 
        federalsta14_.`display_name` as display_3_3_14_,
 
        federalsta14_.`key` as key4_3_14_,
 
        federalsta14_.`order_value` as order_va5_3_14_,
 
        someapplic15_.`id` as id2_3_15_,
 
        someapplic15_.`display_name` as display_3_3_15_,
 
        someapplic15_.`key` as key4_3_15_,
 
        someapplic15_.`order_value` as order_va5_3_15_,
 
        someapplic16_.`id` as id2_3_16_,
 
        someapplic16_.`display_name` as display_3_3_16_,
 
        someapplic16_.`key` as key4_3_16_,
 
        someapplic16_.`order_value` as order_va5_3_16_ 
    from
 
        `some_application_log_entry` someapplic0_ 
    left outer join
 
        `user` user1_ 
            on someapplic0_.`changed_by_id`=user1_.`id` 
    left outer join
 
        `account` account2_ 
            on user1_.`account_id`=account2_.`id` 
    left outer join
 
        `confirming_authority` confirming3_ 
            on user1_.`authority_id`=confirming3_.`id` 
    left outer join
 
        `catalog_value` catalogval4_ 
            on someapplic0_.`reason_id`=catalogval4_.`id` 
    inner join
 
        `catalog_value` someaction5_ 
            on someapplic0_.`action_id`=someaction5_.`id` 
    inner join
 
        `some_application` someapplic6_ 
            on someapplic0_.`some_application_id`=someapplic6_.`id` 
    inner join
 
        `confirming_authority` confirming7_ 
            on someapplic6_.`confirming_authority_id`=confirming7_.`id` 
    inner join
 
        `catalog_value` country8_ 
            on someapplic6_.`country_id`=country8_.`id` 
    left outer join
 
        `some_application_log_entry` someapplic9_ 
            on someapplic6_.`last_log_entry_id`=someapplic9_.`id` 
    inner join
 
        `catalog_value` paymentsta10_ 
            on someapplic6_.`payment_state_id`=paymentsta10_.`id` 
    inner join
 
        `catalog_value` healthcare11_ 
            on someapplic6_.`profession_id`=healthcare11_.`id` 
    inner join
 
        `catalog_value` tsp12_ 
            on someapplic6_.`selected_tsp_id`=tsp12_.`id` 
    inner join
 
        `catalog_value` someapplic13_ 
            on someapplic6_.`state_id`=someapplic13_.`id` 
    inner join
 
        `catalog_value` federalsta14_ 
            on someapplic6_.`state_of_profession_id`=federalsta14_.`id` 
    inner join
 
        `catalog_value` someapplic15_ 
            on someapplic0_.`new_state_id`=someapplic15_.`id` 
    inner join
 
        `catalog_value` someapplic16_ 
            on someapplic0_.`old_state_id`=someapplic16_.`id` 
    where
 
        someapplic0_.`id` in (
 
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
 
        )

So, there are many joins (most of them targeting the same catalog_value table), but the tables are pretty simple and don't contain more than ~100 rows.

After finding this blog post I was able to bring back 10.6.8 reponse times by setting optimizer_search_depth = 6 but since that's a pretty low value compared to the default of 62 and we are only talking about a patch release here, I was wondering whether this is an unwanted side-effect of some optimizer changes in 10.6.9?

PS: I tried to find meaningful optimizer trace data but I failed thus far. So any hints are appreciated.

 Comments   
Comment by Falko Modler [ 2022-09-28 ]

FTR: no change in 10.6.10

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