We have been experiencing a large number of timeouts when using Octopus.
Our DB is stored in Azure (CosmosDB)
As per recommendations, we have not applied any automatic performance suggestions from Azure to the database
Our Octopus server is onsite
We are using v2020.1.13
We have set retention policies to only keep the last 10 builds (in a bid to limit the amount of data being queried) - I’m not actually sure if this is working due to the various timeouts logged.
no other problems are reported in the diagnostics pages.
I initially thought that this was some sort of latency problem between our servers and Azure, but after talking to our networking guys, I’m told that there isn’t any connectivity problems.
I’ve uploaded the latest log file - if you search through it for the work ‘exception’ you will see various timeouts for different operations.
The link to the other problem seems to suggest a correlation with SQL in Azure being slow to the point of timing out. Apart from moving my database on-site, is there anything I can do to speed up Octopus? Does Octopus always go back to the database, or does it use any local caching that I can turn on to speed things up?
Thank you for contacting Octopus Support. I’m sorry you are having an issue with your SQL db timing out.
Is it possible that you’re using Azure SQL here, and not Cosmos? Cosmos is a NoSQL database, and not really suited to the transactional nature of Octopus’s backend.
We commonly see these slow times where we have a locally hosted octopus server, connecting to an Azure SQL database. Does this match the way your instance is configured?
I was running some tests yesterday, just simple queries from the Azure SQL database – things like select count(*) from [dbo].[deployments] (there are 36K records, which really is nothing). It took Azure over 15 seconds to service that request. Another query – select top 1000 from [dbo].[deployments] order by [created] asc – this took 45 seconds. In both cases the query diagnostic information seemed to indicate that the bulk of the work was happening on the server (not in the SQl Server management studio client) and that it was performing an index scan. This happens for all the tables with lots of data (such as [dbo].[events]). I’m now thinking it is the Azure SQL server itself rather than Octopus or a latency issue between our server and Azure.
I would assume than any necessary indexes would be added by the OctopusDB installer, so I don’t really want to second guess how the database is being used by the application and add any more in.
I was going to try to copy the database over to a local server to try the same speed tests – but I’m really struggling to be able to migrate a database away from Azure. There doesn’t seem to be any simple way to do this.
If I was to go into Octopus Manager and export my data, and create a new database on a local server, would I be re-import that data and point my octopus server to the new local instance instead and carry on without having to reconfigure?
You are correct about indexes. We do not recommend messing with the Octopus SQL db indexes as this will more often result in performance loss.
After you Export your data via Octopus Manager, you will need to reinstall Octopus first to set the new location for your SQL db, then Import the data via Octopus Manager once installed. I highly, highly recommend doing this in a test environment before making changes to your live environment.
Just in case, here is our documentation on this process:
Please let me know if you have any questions or run into any trouble.