Merge pull request #17786 from Shadowghost/optimize-db

Optimize database after migrations

6 files changed, 76 insertions, 27 deletions

diff --git a/Emby.Server.Implementations/ScheduledTasks/Tasks/OptimizeDatabaseTask.cs b/Emby.Server.Implementations/ScheduledTasks/Tasks/OptimizeDatabaseTask.cs
index 8d133dc074..687947616f 100644
--- a/Emby.Server.Implementations/ScheduledTasks/Tasks/OptimizeDatabaseTask.cs
+++ b/Emby.Server.Implementations/ScheduledTasks/Tasks/OptimizeDatabaseTask.cs
@@ -11,7 +11,7 @@ using Microsoft.Extensions.Logging;
 namespace Emby.Server.Implementations.ScheduledTasks.Tasks;
 
 /// <summary>
-/// Optimizes Jellyfin's database by issuing a VACUUM command.
+/// Optimizes Jellyfin's database by issuing VACUUM and ANALYZE commands.
 /// </summary>
 public class OptimizeDatabaseTask : IScheduledTask, IConfigurableScheduledTask
 {
@@ -82,7 +82,7 @@ public class OptimizeDatabaseTask : IScheduledTask, IConfigurableScheduledTask
             return;
         }
 
-        _logger.LogInformation("Optimizing and vacuuming jellyfin.db...");
+        _logger.LogInformation("Vacuuming and analyzing jellyfin.db...");
 
         try
         {
diff --git a/Jellyfin.Server/Migrations/JellyfinMigrationService.cs b/Jellyfin.Server/Migrations/JellyfinMigrationService.cs
index beafc3916f..5ef039a843 100644
--- a/Jellyfin.Server/Migrations/JellyfinMigrationService.cs
+++ b/Jellyfin.Server/Migrations/JellyfinMigrationService.cs
@@ -183,7 +183,13 @@ internal class JellyfinMigrationService
         }
     }
 
-    public async Task MigrateStepAsync(JellyfinMigrationStageTypes stage, IServiceProvider? serviceProvider)
+    /// <summary>
+    /// Runs all pending migrations of the requested stage.
+    /// </summary>
+    /// <param name="stage">The stage to migrate.</param>
+    /// <param name="serviceProvider">The service provider handed to the migrations.</param>
+    /// <returns>A value indicating whether at least one migration has been applied.</returns>
+    public async Task<bool> MigrateStepAsync(JellyfinMigrationStageTypes stage, IServiceProvider? serviceProvider)
     {
         var logger = _startupLogger.With(_loggerFactory.CreateLogger<JellyfinMigrationService>()).BeginGroup($"Migrate stage {stage}.");
         ICollection<CodeMigration> migrationStage = (Migrations.FirstOrDefault(e => e.Stage == stage) as ICollection<CodeMigration>) ?? [];
@@ -297,6 +303,8 @@ internal class JellyfinMigrationService
 
                 completedMigrations++;
             }
+
+            return completedMigrations > 0;
         }
     }
 
diff --git a/Jellyfin.Server/Program.cs b/Jellyfin.Server/Program.cs
index 12f92efb35..58861fc476 100644
--- a/Jellyfin.Server/Program.cs
+++ b/Jellyfin.Server/Program.cs
@@ -61,6 +61,7 @@ namespace Jellyfin.Server
         private static ILogger _logger = NullLogger.Instance;
         private static bool _restartOnShutdown;
         private static IStartupLogger<JellyfinMigrationService>? _migrationLogger;
+        private static bool _optimizeDatabaseAfterMigration;
         private static string? _restoreFromBackup;
 
         /// <summary>
@@ -209,14 +210,15 @@ namespace Jellyfin.Server
                 await jellyfinMigrationService.PrepareSystemForMigration(_logger).ConfigureAwait(false);
                 // "Preparing migrations" carries through the DB read; per-migration progress is reported
                 // as "Running migration X of Y" from inside the step once the pending set is known.
-                await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.CoreInitialisation, appHost.ServiceProvider).ConfigureAwait(false);
+                _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.CoreInitialisation, appHost.ServiceProvider).ConfigureAwait(false);
 
                 SetupServer.ReportActivity(StartupActivity.InitializingServices);
                 await appHost.InitializeServices(startupConfig).ConfigureAwait(false);
                 _appHost = appHost;
 
-                await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.AppInitialisation, appHost.ServiceProvider).ConfigureAwait(false);
+                _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.AppInitialisation, appHost.ServiceProvider).ConfigureAwait(false);
                 await jellyfinMigrationService.CleanupSystemAfterMigration(_logger).ConfigureAwait(false);
+                await OptimizeDatabaseAfterMigrationAsync(appHost.ServiceProvider).ConfigureAwait(false);
                 try
                 {
                     configurationCompleted = true;
@@ -271,12 +273,11 @@ namespace Jellyfin.Server
                 // Don't throw additional exception if startup failed.
                 if (appHost.ServiceProvider is not null)
                 {
-                    _logger.LogInformation("Running query planner optimizations in the database... This might take a while");
+                    _logger.LogInformation("Optimizing the database... This might take a while");
 
+                    // Deliberately untimed: a truncated optimization leaves the statistics incomplete.
                     var databaseProvider = appHost.ServiceProvider.GetRequiredService<IJellyfinDatabaseProvider>();
-                    using var shutdownSource = new CancellationTokenSource();
-                    shutdownSource.CancelAfter((int)TimeSpan.FromSeconds(60).TotalMicroseconds);
-                    await databaseProvider.RunShutdownTask(shutdownSource.Token).ConfigureAwait(false);
+                    await databaseProvider.RunShutdownTask(CancellationToken.None).ConfigureAwait(false);
                 }
 
                 _appHost = null;
@@ -314,7 +315,7 @@ namespace Jellyfin.Server
 
             var jellyfinMigrationService = ActivatorUtilities.CreateInstance<JellyfinMigrationService>(startupService);
             await jellyfinMigrationService.CheckFirstTimeRunOrMigration(appPaths, startupOptions).ConfigureAwait(false);
-            await jellyfinMigrationService.MigrateStepAsync(Migrations.Stages.JellyfinMigrationStageTypes.PreInitialisation, startupService).ConfigureAwait(false);
+            _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(Migrations.Stages.JellyfinMigrationStageTypes.PreInitialisation, startupService).ConfigureAwait(false);
         }
 
         /// <summary>
@@ -329,7 +330,32 @@ namespace Jellyfin.Server
         public static async Task ApplyCoreMigrationsAsync(IServiceProvider serviceProvider, Migrations.Stages.JellyfinMigrationStageTypes jellyfinMigrationStage)
         {
             var jellyfinMigrationService = ActivatorUtilities.CreateInstance<JellyfinMigrationService>(serviceProvider, _migrationLogger!);
-            await jellyfinMigrationService.MigrateStepAsync(jellyfinMigrationStage, serviceProvider).ConfigureAwait(false);
+            _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(jellyfinMigrationStage, serviceProvider).ConfigureAwait(false);
+        }
+
+        private static async Task OptimizeDatabaseAfterMigrationAsync(IServiceProvider serviceProvider)
+        {
+            if (!_optimizeDatabaseAfterMigration)
+            {
+                return;
+            }
+
+            // Reset first: a restart runs no migrations and must not optimize again.
+            _optimizeDatabaseAfterMigration = false;
+            SetupServer.ReportActivity(StartupActivity.OptimizingDatabase);
+            _logger.LogInformation("Migrations have been applied, optimizing the database... This might take a while");
+
+            try
+            {
+                // Deliberately untimed: incomplete statistics are worse than a slow start.
+                var databaseProvider = serviceProvider.GetRequiredService<IJellyfinDatabaseProvider>();
+                await databaseProvider.RunScheduledOptimisation(CancellationToken.None).ConfigureAwait(false);
+            }
+            catch (Exception ex)
+            {
+                // A missed optimization only costs performance, so never fail startup over this.
+                _logger.LogError(ex, "Error while optimizing the database after migration");
+            }
         }
 
         /// <summary>
diff --git a/Jellyfin.Server/ServerSetupApp/StartupActivity.cs b/Jellyfin.Server/ServerSetupApp/StartupActivity.cs
index 888cc617d4..abfc5bc8ba 100644
--- a/Jellyfin.Server/ServerSetupApp/StartupActivity.cs
+++ b/Jellyfin.Server/ServerSetupApp/StartupActivity.cs
@@ -27,6 +27,9 @@ public static class StartupActivity
     /// <summary>Bringing up core services and plugins.</summary>
     public const string InitializingServices = "Initializing services";
 
+    /// <summary>Refreshing the database statistics after migrations have run.</summary>
+    public const string OptimizingDatabase = "Optimizing database";
+
     /// <summary>Running the final startup tasks.</summary>
     public const string FinishingStartup = "Finishing startup";
 
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
index 27dbeaba6a..77abb45f2a 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs
@@ -37,14 +37,16 @@ public interface IJellyfinDatabaseProvider
     void ConfigureConventions(ModelConfigurationBuilder configurationBuilder);
 
     /// <summary>
-    /// If supported this should run any periodic maintaince tasks.
+    /// If supported this should run any periodic maintaince tasks, reclaiming unused space and refreshing the query
+    /// planner statistics. Also used after migrations have modified the database.
     /// </summary>
     /// <param name="cancellationToken">The token to abort the operation.</param>
     /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns>
     Task RunScheduledOptimisation(CancellationToken cancellationToken);
 
     /// <summary>
-    /// If supported this should perform any actions that are required on stopping the jellyfin server.
+    /// If supported this should perform any actions that are required on stopping the jellyfin server, including the
+    /// same maintenance as <see cref="RunScheduledOptimisation(CancellationToken)"/>.
     /// </summary>
     /// <param name="cancellationToken">The token that will be used to abort the operation.</param>
     /// <returns>A <see cref="Task"/> representing the asynchronous operation.</returns>
diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
index 8020fe1f93..f11cde7e48 100644
--- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
+++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs
@@ -103,17 +103,9 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
     }
 
     /// <inheritdoc/>
-    public async Task RunScheduledOptimisation(CancellationToken cancellationToken)
+    public Task RunScheduledOptimisation(CancellationToken cancellationToken)
     {
-        var context = await DbContextFactory!.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
-        await using (context.ConfigureAwait(false))
-        {
-            await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false);
-            await context.Database.ExecuteSqlRawAsync("PRAGMA optimize", cancellationToken).ConfigureAwait(false);
-            await context.Database.ExecuteSqlRawAsync("VACUUM", cancellationToken).ConfigureAwait(false);
-            await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false);
-            _logger.LogInformation("jellyfin.db optimized successfully!");
-        }
+        return OptimizeAsync(cancellationToken);
     }
 
     /// <inheritdoc/>
@@ -125,19 +117,37 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider
     /// <inheritdoc/>
     public async Task RunShutdownTask(CancellationToken cancellationToken)
     {
+        // Run before disposing the application
+        try
+        {
+            await OptimizeAsync(cancellationToken).ConfigureAwait(false);
+        }
+        catch (Exception ex)
+        {
+            // A missed optimization only costs performance, so never fail the shutdown over this.
+            _logger.LogError(ex, "Error while optimizing jellyfin.db");
+        }
+
+        SqliteConnection.ClearAllPools();
+    }
+
+    private async Task OptimizeAsync(CancellationToken cancellationToken)
+    {
         if (DbContextFactory is null)
         {
             return;
         }
 
-        // Run before disposing the application
         var context = await DbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
         await using (context.ConfigureAwait(false))
         {
-            await context.Database.ExecuteSqlRawAsync("PRAGMA optimize", cancellationToken).ConfigureAwait(false);
+            await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false);
+            await context.Database.ExecuteSqlRawAsync("VACUUM", cancellationToken).ConfigureAwait(false);
+            await context.Database.ExecuteSqlRawAsync("PRAGMA analysis_limit=0", cancellationToken).ConfigureAwait(false);
+            await context.Database.ExecuteSqlRawAsync("ANALYZE", cancellationToken).ConfigureAwait(false);
+            await context.Database.ExecuteSqlRawAsync("PRAGMA wal_checkpoint(TRUNCATE)", cancellationToken).ConfigureAwait(false);
+            _logger.LogInformation("jellyfin.db optimized successfully!");
         }
-
-        SqliteConnection.ClearAllPools();
     }
 
     /// <inheritdoc/>