ProGet log deletion query takes 26 hours

[jerome.jolidon_1453]

Hello all,

We had a small issue in production today that forced us to restart the container: the ancient log deletion query ran over 26 hours, with 15% cpu used on the DB instance.

The service eventually collapsed into unresponsiveness due to database DTU throttling when the second identical query started and the workers started using the service intensively (around 9:45 local time).

The culprit is this query:

WITH LM AS (
        SELECT RN = ROW_NUMBER() OVER (PARTITION BY [Message_Level]
                                           ORDER BY [LogMessage_Id] DESC)
          FROM [LogMessages]
    )
    DELETE
      FROM LM
     WHERE RN > 1000

The deployment is the container image v6.0.19 on Azure with an Azure SQL server instance.

Here are some screenshots of the app and db performance logs:
DTU during outage

DB query stats

DTU at the end of outage

App perfs during outage

Run logs for this query

[jerome.jolidon_1453]

Could this be related to this issue? https://inedo.myjetbrains.com/youtrack/issue/PG-2189

[atripp]

Hi @jerome-jolidon_1453 ,

Looks like you found the place where that was fixed; that query is a weak point when there's intensive database logging. About the only time that happens is when the server is overloaded with a traffic spike and starts running into database connection errors. So those just pile up.

It could also happen if some one left on Feed-level logging (Admin > Advanced setting, we do not recommend it).

Cheers,
Alana

[jerome.jolidon_1453]

I checked, we don't have feed-level logging enabled.

Very well then, I'll push to have our instance upgraded quickly so we don't run into it in the future.

Thanks for the confirmation.

Sincerely,
Jérôme Jolidon

[summercurrants_4154]

@jerome-jolidon_1453 said in ProGet log deletion query takes 26 hours:

Could this be related to this issue?foodlehttps://inedo.myjetbrains.com/youtrack/issue/PG-2189

Ok, that is clear now. Thank you for your answer.