Team,
we upgraded from alfresco 7.3 to 25.3, we able to see alfresco running but from database side actually it is more degraded performance than 7.3. People now complaining about performance and slowness more.
Our environment is on GCP ( 6 ACS, 2 SOLR search service ) , DB hosted on GCP. Using GCS buckets as storage.
All working fine till 7.3 but after upgrade we see outage because DB CPU spike randomly 99% and active connection reaches 200+ , user start raising INCs and complains.
I want to know below :
1) Is MS-SQL 2019 Enterprise fully compatible with Alfresco 25.3 ? Is there any open bug or ISSUE ?
2) We see below query specially randomly start db table index or occupy randomly CPU and Active Connections, we haven't schedule anything
"SELECT
node.id AS id,
node.version AS version,
node.store_id AS store_id,
node.uuid AS uuid,
node.type_qname_id AS type_qname_id,
node.locale_id AS locale_id,
node.acl_id AS acl_id,
txn.id AS txn_id,
txn.change_txn_id AS txn_change_id,
node.audit_creator AS audit_creator,
node.audit_created AS audit_created,
node.audit_modifier AS audit_modifier,
node.audit_modified AS audit_modified,
node.audit_accessed AS audit_accessed
FROM
alf_node node
JOIN
alf_transaction txn
ON
(txn.id = node.transaction_id)
WHERE
node.type_qname_id <> @P0
AND node.store_id = @p1
AND ( node.id IN (
SELECT
PROP.node_id
FROM
alf_node_properties PROP
WHERE
(@P2 = PROP.qname_id)
AND PROP.string_value = @P3 )
AND node.id IN (
SELECT
PROP.node_id
FROM
alf_node_properties PROP
WHERE
(@P4 = PROP.qname_id)
AND PROP.string_value = @P5 )
AND node.id IN (
SELECT
PROP.node_id
FROM
alf_node_properties PROP
WHERE
(@P6 = PROP.qname_id)
AND PROP.string_value = @P7 )
AND ( node.type_qname_id IN ( @P8,
@P9,
@P10,
@P11,
@P12,
@P13,
@P14,
@P15,
@P16,
@P17,
@P18,
@P19,
@P20 ) ) )"
===================================================
I want to know if anyone similar faced issue and fixed this. Please let us know.
Currently we are doing manual updating DB stats. But that is out of business hrs, but this random issue surprise our PRODUCTION daily.
This is not happening on UAT because amount of DATA is not even 20% on UAT.
Thanks
