Lots of request on PostGres without any activity on Alfresco

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

Corentin

Hello

Following this post : https://hub.alfresco.com/t5/alfresco-content-services-forum/alfresco-millions-connection-to-postgres/m-p/306701#M24022

We check the postgres' logs. 

Just 1 min after the launch, I just display the login page of Alfresco dashboard (without connecting)
And this is the result.

cat postgres-data/log/postgresql-2021-05-21_152402.log |grep select | wc -l
5794

Near 6000 Select requests.
Is it normal ?

Morever, we see the following query 2 times separated by only 20 ms.

select
                    assoc.id                    as id,
                    parentNode.id               as parentNodeId,
                    parentNode.version          as parentNodeVersion,
                    parentStore.protocol        as parentNodeProtocol,
                    parentStore.identifier      as parentNodeIdentifier,
                    parentNode.uuid             as parentNodeUuid,
                    childNode.id                as childNodeId,
                    childNode.version           as childNodeVersion,
                    childStore.protocol         as childNodeProtocol,
                    childStore.identifier       as childNodeIdentifier,
                    childNode.uuid              as childNodeUuid,
                    assoc.type_qname_id         as type_qname_id,
                    assoc.child_node_name_crc   as child_node_name_crc,
                    assoc.child_node_name       as child_node_name,
                    assoc.qname_ns_id           as qname_ns_id,
                    assoc.qname_localname       as qname_localname,
                    assoc.is_primary            as is_primary,
                    assoc.assoc_index           as assoc_index


                from
                    alf_child_assoc assoc
                    join alf_node parentNode on (parentNode.id = assoc.parent_node_id)
                    join alf_store parentStore on (parentStore.id = parentNode.store_id)
                    join alf_node childNode on (childNode.id = assoc.child_node_id)
                    left join alf_store childStore on (childStore.id = childNode.store_id)
                where
                    parentNode.id = $1
                     and assoc.qname_crc = $2
                     and assoc.qname_ns_id = $3
                     and assoc.qname_localname = $4
                order by
                    assoc.assoc_index ASC,
                    assoc.id ASC

Is it normal too ?

At last, in pgAdmin we can see this, without any interaction with alfresco.
 

Our IT service is a little bit scared

Thanks
Corentin.

afaust

On first access to Share, you can expect various configurations / state to be loaded from the DB into memory for the Share UI or checks for potentially existing configuration to occur. Due to the separation of applications between UI and backend, most of persisted data is not loaded when Share starts up (because the backend may not be available yet), but only when the first user accesses the UI. The majority of data loaded in that stage would only be loaded once and should be cached afterwards, not requiring any more DB interactions.

As for the second aspect, Alfresco includes quite a number of background processes out of the box, which run on CRON schedules. These may perform various queries / DB calls without immediate user interaction. One of the more regular of such processes is the SOLR based indexing, which queries every 10-15 seconds for any new changes in Alfresco. Since Alfresco uses a pull and not a push approach to indexing, this means that there will always be those queries even if there was no user activity that actually changed data. Alfresco has been designed more with large-ish organiations in mind, where it is guaranteed that there will always be some changes - not the mostly-idle scenario where IT people might assume Alfresco not to have any activity when no user accesses it.

Is your IT service "scared" just because of the non-zero numbers, or because of actual, non-trivial load on the DB, i.e. CPU usage above single-digits of %?