Improve database query performance for large Octopus installations

[michaelnoonan]

Over time we are finding cases where certain queries in Octopus are sub-optimal for certain situations. The vast majority of Octopus installations have smaller numbers of projects/environments/machines etc. We have noticed large variations in how the data volumes scale with different customers. Some have lots of projects, but not much of anything else. Some have lots of machines, but very few projects. Still others have really really long deployment processes and lots of variables, but very few projects. It has been difficult to apply a one-size-fits-all approach to query performance.

We are planning to simulate many of these different scenarios and see if there are some obvious low-hanging-fruit-style-optimizations which will benefit everyone.

Getting involved

If you have some database statistics you can share, or query optimizations which would help - please share them here or get in touch privately with our support team so we can incorporate them into the product.

[crimean-celica]

Hello

I have some suggestions for your database.

First. You don't use foreign keys. As a result, I can see some inconsistency in our database (deployments without releases). And FK (like other DRI methods) help anyone who is trying to work with a database a lot. Another side of using FK is - you can use (for example) left instead inner joins to change execution plan of complicated queries. To prevent any inconveniences, you can use "cascade" modifiers for these FK. So, I vote for FK.

Second. As I can see, you use "row_number" function in dashboards' views. Such practice makes your dashboard's queries slow and resources consuming. For my regret, this can't be optimized without significant changes for the data model. But I can offer a workaround. I've added tables per each dashboard view and triggers to keep these tables up-to-date. As result, I got small additional expences for adding/modifying rows into Deployment, ServerTask and Release tables but the cheapest dashboard's queries.

Third. Do you have any data retention policy? Maybe you may offer to keep (for example) N last deployments per each project/environment/tenant? This can solve #2 without workarounds. But such policies can be unacceptable.

If you interested please inform me how can I upload scripts.

[crimean-celica]

One more thing. You definitely should make UpdateDeploymentHistory "a bit easier". For example, you may use "rowversion" columns to provide partial merge instead of a full merge operation. p.s. You have to use MIN_ACTIVE_ROWVERSION function when you decide to use "rowversion" columns.

[michaelnoonan]

Relates to this issue where we are collating Index recommendations from the wild: https://github.com/OctopusDeploy/Issues/issues/2767

[MarkSiedle]

fyi, here's some index performance recommendations from a DBA for consideration: https://help.octopus.com/t/missing-item-indexes/22587/5

[michaelnoonan]

I'm going to close this issue now. We have a different and hopefully better way of dealing with performance issues over the long-term. Please reach out to support@octopus.com if you are experiencing performance problems including any supporting information which will assist our investigation. Thanks!

[octoreleasebot]

Thanks! https://octopus.com/company/roadmap/why-was-this-closed

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. If you think you've found a related issue, please contact our support team so we can triage your issue, and make sure it's handled appropriately.