ACS - Slow transformation causes a bottleneck of uncommitted transactions on the DB

Description

A slow transformation with the Transform Service causes a series of uncommitted transactions within the DB until the rendition is returned.
This is a problem that occurs using Share and could cause the db connections pool to run out when there is
an high load on the system.

Supporting evidence

Steps to reproduce

  1. Deploy ACS 6.2.2 with the docker-compose file available here: https://github.com/Alfresco/acs-deployment/blob/master/docker-compose/6.2.N-docker-compose.yml

  2. Login within Share and upload the .HTML document attached to this case (see testFile.html under FTP)

  3. Now check the existing db connections open within the database (in this case i'm using PostgreSQL, perform a query on the "pg_stat_activity" table to get this information), no "idle_in_transaction" connections at this stage

  4. Now click on the document within Share to open the document detail page (to see the full preview)

Expected Behaviour
The document detail page shows the document preview and no "idle_in_transaction" connections are left within the database

Observed Behaviour
The document detail page does not display the document preview and a query is left in the database with "idle_in_transaction" status (see attachment "uncommittedTransaction.png").
Any subsequent refresh of the page will result in more queries left open within the database, quickly filling the db connection pool. (see attachment "openConnections.png").
Same behaviour occurs with any operation on the document while Share waits for the rendition.
The query left in "idle_in_transaction" status is the following:

Additional findings
The database connections are kept in this uncommitted status until the transformation service provides a result, and this is a problem because for example by default the LibreOffice timeout is set to 20 minutes.
In my local environment this sample document always hit the LibreOffice timeout limit (probably due to a LibreOffice limitation), causing the connections to wait for 20 minutes in an uncommitted state, which is a big problem for the customer when the load on the system is high.

Workaround
Currently the only tested workaround for this is to lower the LibreOffice timeout limit within the all-in-one transformer using the LIBREOFFICE_TIMEOUT property (https://github.com/Alfresco/alfresco-transform-core/blob/master/docs/external-engine-configuration.md#core-aio).

Environment

None

Testcase ID

None

Activity

Show:
Preeti Nirwal
February 4, 2021, 10:44 PM

Thanks for the update. From my understanding, DTE should meet their transformation requirement.

Michael Wallach
February 4, 2021, 11:54 PM

We have the file causing issue but I’m not sure how to test in order to create LO ticket. I think running LO from command line with offending file might offer some guidance. Other thing to consider is there may be LO options that enable this file to be transformed properly but we have/have had specific LO parameters we use that are not configurable

Preeti Nirwal
February 5, 2021, 12:16 AM

Thanks . On a general note, not related to this customer, it will be a good idea to submit the bug while it’s fresh on our radars.

Another general note, its usually not wise to hold your breath for a vendor to fix a bug submitted through the open source community. It’s safe to assume most vendors will be slow. Our account team should set this expectation for this customer.

Michael Wallach
February 5, 2021, 1:27 AM

I tested what soffice is running. You can see soffice eventually passes off to convert which is ImageMagick and it seems to hang here

Not sure how that helps though

Michael Wallach
February 5, 2021, 7:47 PM

I have created LibreOffice defect against this issue:
https://bugs.documentfoundation.org/show_bug.cgi?id=140177

Assignee

Unassigned

Reporter

Damiano Mondardo

Labels

None

ACT Numbers

01021877

Security Issue

None

Patch Attached

None

Premier Customer

None

Prioritization Score

None

Delivery Team

None

Build Location

None

Cloud or Enterprise

None

Bug Priority

Category 1

Work Funnel End

None

Escalated By

CSO

Dependent Version/s

None

Regression Since

None

Code Branch

None

Fix versions

Affects versions