Account usage query takes a long time

johnsimons commented 2 years ago

Team

[X] I've assigned a team label to this issue

Severity

1 instance is affected

Version

v2021.3 (Build 7670)

Latest Version

No response

What happened?

When I go to https://deploy.octopus.app/app#/Spaces-622/infrastructure/accounts/Accounts-163?activeTab=usage, I cannot load the usages

Reproduction

go to https://deploy.octopus.app/app#/Spaces-622/infrastructure/accounts/Accounts-163?activeTab=usage

Error and Stacktrace

https://seq.octopushq.com/#/events?signal=signal-289&filter=@Id%20%3D%20'event-9b44040f9ff508d9a38b288101000000'

More Information

SQL Error -2 - Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.\nUnknown error 258

Crow's nest says that this query has never run for 2020.3 on deploy

Workaround

No response

zentron commented 2 years ago

There might be a quick win here from using some of nevermores new json querying syntax

veochen-octopus commented 2 years ago

There's one query that fetches VariableSets that is slow. We're fairly certain the root cause is the 10k+ VariableSets the query returns and the large amount of JSON data they come with. Dropping the JSON column, the same query goes from 10min+ to a couple seconds. However the current design of our data layer doesn't allow for easily excluding columns. The usage finder class also doesn't have room for that type of customization. Further investigation/work required for the fix to be added.

veochen-octopus commented 2 years ago

No easy way around. Putting back for more investigation.

OctopusDeploy / Issues