Suggestions for speeding up start-up of Flyway CLI?

BillPP

Hi everyone,

I have environment where we have lots of databases (several hundred) and I'm trying to get Flyway command line to run as fast as possible. Currently Flyway takes about 2.3 seconds to get to the Flyway by Redgate log line. The process is running in a loop in a PowerShell script which gets all the databases, then substitutes the database name in to the arguments for Flyway. 

Here is the snippet of my script which executes flyway

foreach($customer in $customers)
{
    $status = "Migrating system $($counter) of $($customers.Count). $($customer.Database) for customer $($customer.Id)."
    Write-Host $status
    try
    {
        flyway migrate -n -skipCheckForUpdate -url="jdbc:sqlserver://$sqlServer;encrypt=false;databaseName=$($customer.Database)" -user=REDACTED -password=REDACTED -configFiles="$flywaydirectory\flyway.toml" -workingDirectory="$flywaydirectory"
    }
    catch [Exception]
    {
        Write-Error $_
        $erroredCustomers.Add($customer)
    }
    $counter++
}

Below are the logs for an iteration of the loop. My log on line 1 runs immediately before running flyway -migrate. I have ruled out that this line of logging is taking any of the time and the gap starts from the line where flyway is executed.

2024-07-09T12:12:33.1337432Z Migrating database 1 of 4. REDACTED for customer REDACTED.
2024-07-09T12:12:35.4456415Z Flyway Community Edition 10.8.1 by Redgate
2024-07-09T12:12:35.4456764Z 
2024-07-09T12:12:35.4457380Z See release notes here: https://rd.gt/416ObMi
2024-07-09T12:12:35.8156190Z Database: jdbc:sqlserver://REDACTED;connectRetryInterval=10;connectRetryCount=1;maxResultBuffer=-1;sendTemporalDataTypesAsStringForBulkCopy=true;delayLoadingLobs=true;useFmtOnly=false;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;datetimeParameterType=datetime2;sendTimeAsDatetime=true;replication=false;trustStoreType=JKS;trustServerCertificate=true;TransparentNetworkIPResolution=true;iPAddressPreference=IPv4First;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=30;lockTimeout=-1;lastUpdateCount=true;prepareMethod=prepexec;encrypt=false;disableStatementPooling=true;databaseName=REDACTED;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Microsoft SQL Server 15.0)
2024-07-09T12:12:36.0651181Z Successfully validated 64 migrations (execution time 00:00.212s)
2024-07-09T12:12:36.0812096Z Current version of schema [dbo]: 171.02
2024-07-09T12:12:36.0816099Z WARNING: outOfOrder mode is active. Migration of schema [dbo] may not be reproducible.
2024-07-09T12:12:36.0881274Z Schema [dbo] is up to date. No migration necessary.
2024-07-09T12:12:36.0957447Z 
2024-07-09T12:12:36.0958135Z You are not signed in to Flyway, to sign in please run auth
2024-07-09T12:12:37.3269333Z Migrating system 2 of 4. REDACTED for customer REDACTED.
...

So it looks like the delay is in the start-up of Flyway. As you can see I've added the -skipCheckForUpdate argument. I have also added the environment variable (at server level) called REDGATE_DISABLE_TELEMETRY with the value true to see if that helps but as you can see from the log it is still ~2.3 seconds. The guts of the process where it is checking migrations and executing individual migrations is quite fast.

Anyone have any suggestions for other methods which might speed things up? A single multi-tenant database is the ultimate goal but not there yet!

Peter_Laws

Hello BillPP,

Thank you for your question. You've already done what I'd recommend that applies to the stage that you've referenced. There are potentially small savings in the execution time, but they have trade-offs and the gains are meagre, as you've said this part is fairly quick.

For example there is an implicit validation check built into migrations (https://documentation.red-gate.com/flyway/flyway-cli-and-api/configuration/parameters/flyway/validate-on-migrate) however based on your example, that only cost 00:00.212s, so I'd argue that the risk of disabling it outweighs the tiny gain.

AlistairW

To be honest - there is a fair amount of setup to run a java program and so I think you may be at the limits of that (the DB itself also takes time - in my experiments using a SQLite DB shaved 1 second off my 2.6 second timing but that is academic !).
I can think of two possibilities:
- If you need it to do 1 job as fast as possible you could create your own (java) application using the API but I don't know how much that will really improve matters for the effort involved.
- You could parallelize the jobs - flyway keeps it's records in the database itself so you should be able to update all of them as fast as you can spin up instances of Flyway. Each instance of flyway is just reading migrations and applying the given configuration. I had a look for any examples of this but drew a blank I'm afraid.  

BillPP

Thank you both for your answers and thoughts on this. Much appreciated. I think I will look at parallelisation of flyway execution next.