Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29413

Overzealous optimizer in 10.6.9?

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6.9
    • Fix Version/s: 10.6
    • Component/s: Optimizer
    • 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.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            famod Falko Modler
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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