1

This is a follow up question from Tridion content services has alot of open sessions with database. Original question was about dropped spans and unclosed Hibernate sessions. But the data comes out of the same investigation so I decided to create a separate question.

When we looked into OpenTelemetry traces for Tridion content service we saw a massive amount of database queries. The highest number we have seen is 5604 db calls and the request takes up to 45 seconds to complete. I have generated a histogram and I can provide the most executed queries. OpenTelemetry logs every single database statement and I can see all of them in Jaeger but without actual values (only placeholders). When I checked the most common queries in that request I saw that those were for CUSTOM_META and KEYWORD_RELATIONS table. The first time I stumbled on that trace I suspected that there might be @OneToMany annotations with FetchType.EAGER as this could explain such a massive amount of queries.

I put here the analysis of the two traces.

First trace

Total db statements: 2808

Response time: 18 seconds

Most common db statements in that request

2488 statements (88.6%) : select custommeta0_.PUBLICATION_ID as PUBLICAT9_10_0_, custommeta0_.NAMESPACE_ID as NAMESPAC8_10_0_, custommeta0_.ITEM_ID as ITEM_ID4_10_0_, custommeta0_.CUSTOM_META_VALUE_ID as CUSTOM_M1_10_0_, custommeta0_.CUSTOM_META_VALUE_ID as CUSTOM_M1_10_1_, custommeta0_.KEY_DATE_VALUE as KEY_DATE2_10_1_, custommeta0_.KEY_FLOAT_VALUE as KEY_FLOA3_10_1_, custommeta0_.ITEM_ID as ITEM_ID4_10_1_, custommeta0_.ITEM_TYPE as ITEM_TYP5_10_1_, custommeta0_.KEY_NAME as KEY_NAME6_10_1_, custommeta0_.KEY_TYPE as KEY_TYPE7_10_1_, custommeta0_.NAMESPACE_ID as NAMESPAC8_10_1_, custommeta0_.PUBLICATION_ID as PUBLICAT9_10_1_, custommeta0_.KEY_STRING_LOB_VALUE as KEY_STR10_10_1_, custommeta0_.KEY_STRING_VALUE as KEY_STR11_10_1_ from CUSTOM_META custommeta0_ where custommeta0_.PUBLICATION_ID=? and custommeta0_.NAMESPACE_ID=? and custommeta0_.ITEM_ID=?

320 statements (11.4%) : select componentm0_.PUBLICATION_ID as PUBLICAT1_13_, componentm0_.NAMESPACE_ID as NAMESPAC2_13_, componentm0_.ITEM_REFERENCE_ID as ITEM_REF3_13_, componentm0_1_.CREATION_DATE as CREATION4_13_, componentm0_1_.INITIAL_PUBLICATION_DATE as INITIAL_5_13_, componentm0_1_.ITEM_SELECTOR as ITEM_SEL6_13_, componentm0_1_.ITEM_TYPE as ITEM_TYP7_13_, componentm0_1_.LAST_PUBLISHED_DATE as LAST_PUB8_13_, componentm0_1_.MAJOR_VERSION as MAJOR_VE9_13_, componentm0_1_.MINOR_VERSION as MINOR_V10_13_, componentm0_1_.MODIFICATION_DATE as MODIFIC11_13_, componentm0_1_.OWNING_PUBLICATION_ID as OWNING_12_13_, componentm0_1_.TITLE as TITLE13_13_, componentm0_1_.TRUSTEE as TRUSTEE14_13_, componentm0_.IS_MULTIMEDIA as IS_MULTI1_4_, componentm0_.SCHEMA_ID as SCHEMA_I2_4_ from COMPONENT componentm0_ inner join ITEMS componentm0_1_ on componentm0_.PUBLICATION_ID=componentm0_1_.PUBLICATION_ID and componentm0_.NAMESPACE_ID=componentm0_1_.NAMESPACE_ID and componentm0_.ITEM_REFERENCE_ID=componentm0_1_.ITEM_REFERENCE_ID cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ where componentm0_.NAMESPACE_ID=? and componentm0_.PUBLICATION_ID=? and componentm0_.NAMESPACE_ID=relatedkey1_.NAMESPACE_ID and componentm0_.PUBLICATION_ID=relatedkey1_.PUBLICATION_ID and componentm0_.ITEM_REFERENCE_ID=relatedkey1_.ITEM_REFERENCE_ID and relatedkey1_.KEYWORD_ID=? and relatedkey1_.TAXONOMY_ID=?

Second trace

Total db statements: 5604

Response time: 45 seconds

Most common db statements in that request

1547 statements (27.6%) : select custommeta0_.PUBLICATION_ID as PUBLICAT9_10_0_, custommeta0_.NAMESPACE_ID as NAMESPAC8_10_0_, custommeta0_.ITEM_ID as ITEM_ID4_10_0_, custommeta0_.CUSTOM_META_VALUE_ID as CUSTOM_M1_10_0_, custommeta0_.CUSTOM_META_VALUE_ID as CUSTOM_M1_10_1_, custommeta0_.KEY_DATE_VALUE as KEY_DATE2_10_1_, custommeta0_.KEY_FLOAT_VALUE as KEY_FLOA3_10_1_, custommeta0_.ITEM_ID as ITEM_ID4_10_1_, custommeta0_.ITEM_TYPE as ITEM_TYP5_10_1_, custommeta0_.KEY_NAME as KEY_NAME6_10_1_, custommeta0_.KEY_TYPE as KEY_TYPE7_10_1_, custommeta0_.NAMESPACE_ID as NAMESPAC8_10_1_, custommeta0_.PUBLICATION_ID as PUBLICAT9_10_1_, custommeta0_.KEY_STRING_LOB_VALUE as KEY_STR10_10_1_, custommeta0_.KEY_STRING_VALUE as KEY_STR11_10_1_ from CUSTOM_META custommeta0_ where custommeta0_.PUBLICATION_ID=? and custommeta0_.NAMESPACE_ID=? and custommeta0_.ITEM_ID=?

995 statements (17.8%) : select keywordrel0_.NODE as NODE2_15_0_, keywordrel0_.KEYWORD_TO as KEYWORD_1_15_0_, keywordrel0_.KEYWORD_TO as KEYWORD_1_15_1_, keywordrel0_.NODE as NODE2_15_1_ from KEYWORD_RELATIONS keywordrel0_ where keywordrel0_.NODE=?

897 statements (16.0%) : select keyword0_.NODE_ID as NODE_ID2_26_, keyword0_.FACET_ISABSTRACT as FACET_IS3_26_, keyword0_.FACET_DEPTH as FACET_DE4_26_, keyword0_.FACET_HASCHILDREN as FACET_HA5_26_, keyword0_.FACET_ID as FACET_ID6_26_, keyword0_.FACET_ITEMTYPE as FACET_IT7_26_, keyword0_.FACET_KEY as FACET_KE8_26_, keyword0_.FACET_LEFT as FACET_LE9_26_, keyword0_.NAMESPACE_ID as NAMESPA10_26_, keyword0_.FACET_ISNAVIGABLE as FACET_I11_26_, keyword0_.FACET_PARENT as FACET_P12_26_, keyword0_.PUBLICATION_ID as PUBLICA13_26_, keyword0_.FACET_RIGHT as FACET_R14_26_, keyword0_.TAXONOMY_ID as TAXONOM15_26_, keyword0_.TOTAL_RELATEDITEMS as TOTAL_R16_26_, keyword0_.FACET_DESCRIPTION as FACET_D17_26_, keyword0_.FACET_NAME as FACET_N18_26_, keyword0_.FACET_ISUSEDFORIDENTIFICATION as FACET_I19_26_ from TAXFACETS keyword0_ where keyword0_.ITEM_SELECTOR=? and keyword0_.PUBLICATION_ID=? and keyword0_.NAMESPACE_ID=? and keyword0_.TAXONOMY_ID=? and keyword0_.FACET_ID=?

575 statements (10.3%) : select componentm0_.PUBLICATION_ID as PUBLICAT1_13_, componentm0_.NAMESPACE_ID as NAMESPAC2_13_, componentm0_.ITEM_REFERENCE_ID as ITEM_REF3_13_, componentm0_1_.CREATION_DATE as CREATION4_13_, componentm0_1_.INITIAL_PUBLICATION_DATE as INITIAL_5_13_, componentm0_1_.ITEM_SELECTOR as ITEM_SEL6_13_, componentm0_1_.ITEM_TYPE as ITEM_TYP7_13_, componentm0_1_.LAST_PUBLISHED_DATE as LAST_PUB8_13_, componentm0_1_.MAJOR_VERSION as MAJOR_VE9_13_, componentm0_1_.MINOR_VERSION as MINOR_V10_13_, componentm0_1_.MODIFICATION_DATE as MODIFIC11_13_, componentm0_1_.OWNING_PUBLICATION_ID as OWNING_12_13_, componentm0_1_.TITLE as TITLE13_13_, componentm0_1_.TRUSTEE as TRUSTEE14_13_, componentm0_.IS_MULTIMEDIA as IS_MULTI1_4_, componentm0_.SCHEMA_ID as SCHEMA_I2_4_ from COMPONENT componentm0_ inner join ITEMS componentm0_1_ on componentm0_.PUBLICATION_ID=componentm0_1_.PUBLICATION_ID and componentm0_.NAMESPACE_ID=componentm0_1_.NAMESPACE_ID and componentm0_.ITEM_REFERENCE_ID=componentm0_1_.ITEM_REFERENCE_ID cross join ITEM_CATEGORIES_AND_KEYWORDS relatedkey1_ where componentm0_.NAMESPACE_ID=? and componentm0_.PUBLICATION_ID=? and componentm0_.NAMESPACE_ID=relatedkey1_.NAMESPACE_ID and componentm0_.PUBLICATION_ID=relatedkey1_.PUBLICATION_ID and componentm0_.ITEM_REFERENCE_ID=relatedkey1_.ITEM_REFERENCE_ID and relatedkey1_.KEYWORD_ID=? and relatedkey1_.TAXONOMY_ID=?

563 statements (10.0%) : select custommeta0_.CUSTOM_META_VALUE_ID as CUSTOM_M1_10_, custommeta0_.KEY_DATE_VALUE as KEY_DATE2_10_, custommeta0_.KEY_FLOAT_VALUE as KEY_FLOA3_10_, custommeta0_.ITEM_ID as ITEM_ID4_10_, custommeta0_.ITEM_TYPE as ITEM_TYP5_10_, custommeta0_.KEY_NAME as KEY_NAME6_10_, custommeta0_.KEY_TYPE as KEY_TYPE7_10_, custommeta0_.NAMESPACE_ID as NAMESPAC8_10_, custommeta0_.PUBLICATION_ID as PUBLICAT9_10_, custommeta0_.KEY_STRING_LOB_VALUE as KEY_STR10_10_, custommeta0_.KEY_STRING_VALUE as KEY_STR11_10_ from CUSTOM_META custommeta0_ where custommeta0_.NAMESPACE_ID=? and custommeta0_.PUBLICATION_ID=? and custommeta0_.ITEM_ID=? and custommeta0_.ITEM_TYPE=?

I suspect that the top ones are caused by the @OneToMany annotation with FetchType.EAGER as this is a typical picture for that. With that fetch type Hibernate will fetch all related items even if they are not used. I am also attaching a screenshot of the first trace in Jaeger.

enter image description here

1
  • I suspect you may get better traction on this via Customer Support, as this is rather low-level for the regular Stack Exchange implementer crowd.
    – Nuno Linhares
    Jan 6 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.