Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.6.9
-
None
-
- 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.