- In production environment only, slow responses seen for Content services endpoints when publishing just a few pages (several minutes)
http://{domain}/client/v2/content.svc http://{domain}/client/v4/content.svc/ExecuteQueryActionImport- Concurrently on the database server, a CPU spike nearing 100% can be seen for a few minutes after publishing. - When not publishing, endpoint response times average a few hundred milliseconds. - The most frequent queries during a publish transaction are the below. {PREPARED} SELECT DISTINCT ITEMMETA0_.PUBLICATION_ID AS COL_0_0_, ITEMMETA0_.ITEM_REFERENCE_ID AS COL_1_0_, ITEMMETA0_.ITEM_TYPE AS COL_2_0_, ITEMMETA0_.ITEM_REFERENCE_ID AS COL_3_0_ FROM ITEMS ITEMMETA0_ CROSS JOIN COMPONENT COMPONENTM1_ INNER JOIN ITEMS COMPONENTM1_1_ ON COMPONENTM1_.PUBLICATION_ID=COMPONENTM1_1_.PUBLICATION_ID AND COMPONENTM1_.NAMESPACE_ID=COMPONENTM1_1_.NAMESPACE_ID AND COMPONENTM1_.ITEM_REFERENCE_ID=COMPONENTM1_1_.ITEM_REFERENCE_ID CROSS JOIN CUSTOM_META CUSTOMMETA2_ CROSS JOIN CUSTOM_META CUSTOMMETA3_ WHERE ITEMMETA0_.PUBLICATION_ID=@P0 AND ITEMMETA0_.ITEM_REFERENCE_ID=COMPONENTM1_.ITEM_REFERENCE_ID AND ITEMMETA0_.PUBLICATION_ID=COMPONENTM1_.PUBLICATION_ID AND COMPONENTM1_.SCHEMA_ID=@P1 AND ITEMMETA0_.ITEM_TYPE=@P2 AND ITEMMETA0_.PUBLICATION_ID=CUSTOMMETA2_.PUBLICATION_ID AND ITEMMETA0_.ITEM_REFERENCE_ID=CUSTOMMETA2_.ITEM_ID AND ITEMMETA0_.ITEM_TYPE=CUSTOMMETA2_.ITEM_TYPE AND (CUSTOMMETA2_.ITEM_ID NOT IN (SELECT DISTINCT CUSTOMMETA4_.ITEM_ID FROM CUSTOM_META CUSTOMMETA4_ WHERE CUSTOMMETA4_.KEY_STRING_VALUE=@P3)) AND ITEMMETA0_.PUBLICATION_ID=CUSTOMMETA3_.PUBLICATION_ID AND ITEMMETA0_.ITEM_REFERENCE_ID=CUSTOMMETA3_.ITEM_ID AND ITEMMETA0_.ITEM_TYPE=CUSTOMMETA3_.ITEM_TYPE AND CUSTOMMETA3_.KEY_STRING_VALUE=@P4 ORDER BY ITEMMETA0_.ITEM_REFERENCE_ID ASC- Issue persists after Deployer had been hotfixed with CD_8.5.0.16778, and Content microservice hotfixed with CD_8.5.0.16778. - CD_8.5.0.14124 had been applied to the state store database, which is separate from the broker database. - Content service JVM args are -Xrs", "-Xms512m", "-Xmx5120m- Deployer service JVM args are -Xms512m", "-Xmx4096m- Published pages have various dynamic components embedded, but issue can be reproduced with simple pages or Fredhopper enabled content. - Database maintenance is being done regularly. - Class used to generate problem query uses the CUSTOM_META notEquals criteria. |
Issue resolved after applying hotfix CD_8.5.0.17761 to Deployer and Content service. No spikes were seen for either Content service endpoints or database server CPU when publishing afterwards. In hotfix, performance for the CustomMeta criteria was improved, as some unnecessary cross joins were removed and the query was migrated from an IN clause to an EXISTS clause. To download hotfix, see KB article Where can I find hotfixes for SDL Tridion Sites/Web? When publishing, frequent query appearing in database trace is now PREPARED} SELECT DISTINCT ITEMMETA0_.PUBLICATION_ID AS COL_0_0_, ITEMMETA0_.ITEM_REFERENCE_ID AS COL_1_0_, ITEMMETA0_.ITEM_TYPE AS COL_2_0_, ITEMMETA0_.ITEM_REFERENCE_ID AS COL_3_0_ FROM ITEMS ITEMMETA0_ INNER JOIN COMPONENT COMPONENTM1_ ON {##}={##} INNER JOIN ITEMS COMPONENTM1_1_ ON COMPONENTM1_.PUBLICATION_ID=COMPONENTM1_1_.PUBLICATION_ID AND COMPONENTM1_.NAMESPACE_ID=COMPONENTM1_1_.NAMESPACE_ID AND COMPONENTM1_.ITEM_REFERENCE_ID=COMPONENTM1_1_.ITEM_REFERENCE_ID INNER JOIN CUSTOM_META CUSTOMMETA2_ ON (ITEMMETA0_.NAMESPACE_ID=CUSTOMMETA2_.NAMESPACE_ID AND ITEMMETA0_.PUBLICATION_ID=CUSTOMMETA2_.PUBLICATION_ID AND ITEMMETA0_.ITEM_REFERENCE_ID=CUSTOMMETA2_.ITEM_ID AND ITEMMETA0_.ITEM_TYPE=CUSTOMMETA2_.ITEM_TYPE) INNER JOIN CUSTOM_META CUSTOMMETA3_ ON (ITEMMETA0_.NAMESPACE_ID=CUSTOMMETA3_.NAMESPACE_ID AND ITEMMETA0_.PUBLICATION_ID=CUSTOMMETA3_.PUBLICATION_ID AND ITEMMETA0_.ITEM_REFERENCE_ID=CUSTOMMETA3_.ITEM_ID AND ITEMMETA0_.ITEM_TYPE=CUSTOMMETA3_.ITEM_TYPE) WHERE (ITEMMETA0_.NAMESPACE_ID=COMPONENTM1_.NAMESPACE_ID AND ITEMMETA0_.ITEM_REFERENCE_ID=COMPONENTM1_.ITEM_REFERENCE_ID AND ITEMMETA0_.PUBLICATION_ID=COMPONENTM1_.PUBLICATION_ID) AND ITEMMETA0_.PUBLICATION_ID=@P0 AND COMPONENTM1_.SCHEMA_ID=@P1 AND ITEMMETA0_.ITEM_TYPE=@P2 AND NOT (EXISTS (SELECT CUSTOMMETA4_.ITEM_ID FROM CUSTOM_META CUSTOMMETA4_ WHERE CUSTOMMETA2_.NAMESPACE_ID=CUSTOMMETA4_.NAMESPACE_ID AND CUSTOMMETA2_.PUBLICATION_ID=CUSTOMMETA4_.PUBLICATION_ID AND CUSTOMMETA2_.ITEM_TYPE=CUSTOMMETA4_.ITEM_TYPE AND CUSTOMMETA2_.ITEM_ID=CUSTOMMETA4_.ITEM_ID AND CUSTOMMETA4_.KEY_STRING_VALUE=@P3)) AND (CUSTOMMETA3_.KEY_STRING_VALUE=@P4 OR CUSTOMMETA3_.KEY_STRING_VALUE=@P5 OR CUSTOMMETA3_.KEY_STRING_VALUE=@P6 OR CUSTOMMETA3_.KEY_STRING_VALUE=@P7 OR CUSTOMMETA3_.KEY_STRING_VALUE=@P8 OR CUSTOMMETA3_.KEY_STRING_VALUE=@P9) ORDER BY ITEMMETA0_.ITEM_REFERENCE_ID ASC |
A SDL Tridion Sites 9.1 version of the fix is expected to be included in a cumulative hotfix. It will be made available publicly then. |