From 0b5bbb528af08d950bc9887b5a1114888820688b Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Fri, 4 Sep 2026 18:57:54 +0200 Subject: Optimize database after running migrations --- .../Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs | 3 ++- .../Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs | 3 ++- 2 files changed, 4 insertions(+), 2 deletions(-) (limited to 'src') diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs index 27dbeaba6a..87d87e92b8 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs @@ -37,7 +37,8 @@ public interface IJellyfinDatabaseProvider void ConfigureConventions(ModelConfigurationBuilder configurationBuilder); /// - /// 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. /// /// The token to abort the operation. /// A representing the asynchronous operation. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index 8020fe1f93..dff834bfec 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -109,8 +109,9 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider 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 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!"); } -- cgit v1.2.3 From 5465e0c69423506d6836f2a6b8aacfce8cb0501e Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 5 Sep 2026 07:34:38 +0200 Subject: Optimize the database on startup instead of before shutdown --- .../Migrations/JellyfinMigrationService.cs | 6 ++--- Jellyfin.Server/Program.cs | 30 ++++++++-------------- Jellyfin.Server/ServerSetupApp/StartupActivity.cs | 2 +- .../IJellyfinDatabaseProvider.cs | 2 +- .../SqliteDatabaseProvider.cs | 14 ++-------- 5 files changed, 16 insertions(+), 38 deletions(-) (limited to 'src') diff --git a/Jellyfin.Server/Migrations/JellyfinMigrationService.cs b/Jellyfin.Server/Migrations/JellyfinMigrationService.cs index 5ef039a843..6ecbe6a95e 100644 --- a/Jellyfin.Server/Migrations/JellyfinMigrationService.cs +++ b/Jellyfin.Server/Migrations/JellyfinMigrationService.cs @@ -188,8 +188,8 @@ internal class JellyfinMigrationService /// /// The stage to migrate. /// The service provider handed to the migrations. - /// A value indicating whether at least one migration has been applied. - public async Task MigrateStepAsync(JellyfinMigrationStageTypes stage, IServiceProvider? serviceProvider) + /// A representing the asynchronous operation. + public async Task MigrateStepAsync(JellyfinMigrationStageTypes stage, IServiceProvider? serviceProvider) { var logger = _startupLogger.With(_loggerFactory.CreateLogger()).BeginGroup($"Migrate stage {stage}."); ICollection migrationStage = (Migrations.FirstOrDefault(e => e.Stage == stage) as ICollection) ?? []; @@ -303,8 +303,6 @@ internal class JellyfinMigrationService completedMigrations++; } - - return completedMigrations > 0; } } diff --git a/Jellyfin.Server/Program.cs b/Jellyfin.Server/Program.cs index 2341af47c1..2391c86641 100644 --- a/Jellyfin.Server/Program.cs +++ b/Jellyfin.Server/Program.cs @@ -61,7 +61,6 @@ namespace Jellyfin.Server private static ILogger _logger = NullLogger.Instance; private static bool _restartOnShutdown; private static IStartupLogger? _migrationLogger; - private static bool _optimizeDatabaseAfterMigration; private static string? _restoreFromBackup; /// @@ -210,15 +209,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. - _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.CoreInitialisation, appHost.ServiceProvider).ConfigureAwait(false); + await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.CoreInitialisation, appHost.ServiceProvider).ConfigureAwait(false); SetupServer.ReportActivity(StartupActivity.InitializingServices); await appHost.InitializeServices(startupConfig).ConfigureAwait(false); _appHost = appHost; - _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.AppInitialisation, appHost.ServiceProvider).ConfigureAwait(false); + await jellyfinMigrationService.MigrateStepAsync(JellyfinMigrationStageTypes.AppInitialisation, appHost.ServiceProvider).ConfigureAwait(false); await jellyfinMigrationService.CleanupSystemAfterMigration(_logger).ConfigureAwait(false); - await OptimizeDatabaseAfterMigrationAsync(appHost.ServiceProvider).ConfigureAwait(false); + await OptimizeDatabaseAsync(appHost.ServiceProvider).ConfigureAwait(false); try { configurationCompleted = true; @@ -273,11 +272,8 @@ 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"); - var databaseProvider = appHost.ServiceProvider.GetRequiredService(); - using var shutdownSource = new CancellationTokenSource(); - shutdownSource.CancelAfter((int)TimeSpan.FromSeconds(60).TotalMicroseconds); + using var shutdownSource = new CancellationTokenSource(TimeSpan.FromSeconds(60)); await databaseProvider.RunShutdownTask(shutdownSource.Token).ConfigureAwait(false); } @@ -316,7 +312,7 @@ namespace Jellyfin.Server var jellyfinMigrationService = ActivatorUtilities.CreateInstance(startupService); await jellyfinMigrationService.CheckFirstTimeRunOrMigration(appPaths, startupOptions).ConfigureAwait(false); - _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(Migrations.Stages.JellyfinMigrationStageTypes.PreInitialisation, startupService).ConfigureAwait(false); + await jellyfinMigrationService.MigrateStepAsync(Migrations.Stages.JellyfinMigrationStageTypes.PreInitialisation, startupService).ConfigureAwait(false); } /// @@ -331,30 +327,24 @@ namespace Jellyfin.Server public static async Task ApplyCoreMigrationsAsync(IServiceProvider serviceProvider, Migrations.Stages.JellyfinMigrationStageTypes jellyfinMigrationStage) { var jellyfinMigrationService = ActivatorUtilities.CreateInstance(serviceProvider, _migrationLogger!); - _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(jellyfinMigrationStage, serviceProvider).ConfigureAwait(false); + await jellyfinMigrationService.MigrateStepAsync(jellyfinMigrationStage, serviceProvider).ConfigureAwait(false); } - private static async Task OptimizeDatabaseAfterMigrationAsync(IServiceProvider serviceProvider) + private static async Task OptimizeDatabaseAsync(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"); + _logger.LogInformation("Vacuuming and analyzing the database... This might take a while"); try { + // Deliberately untimed: incomplete statistics are worse than a slow start. var databaseProvider = serviceProvider.GetRequiredService(); 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"); + _logger.LogError(ex, "Error while optimizing the database"); } } diff --git a/Jellyfin.Server/ServerSetupApp/StartupActivity.cs b/Jellyfin.Server/ServerSetupApp/StartupActivity.cs index abfc5bc8ba..5fc485bd48 100644 --- a/Jellyfin.Server/ServerSetupApp/StartupActivity.cs +++ b/Jellyfin.Server/ServerSetupApp/StartupActivity.cs @@ -27,7 +27,7 @@ public static class StartupActivity /// Bringing up core services and plugins. public const string InitializingServices = "Initializing services"; - /// Refreshing the database statistics after migrations have run. + /// Refreshing the database query planner statistics. public const string OptimizingDatabase = "Optimizing database"; /// Running the final startup tasks. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs index 87d87e92b8..a7b2aa493b 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs @@ -38,7 +38,7 @@ public interface IJellyfinDatabaseProvider /// /// 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. + /// planner statistics. Also runs on startup once all migrations have been applied. /// /// The token to abort the operation. /// A representing the asynchronous operation. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index dff834bfec..6103ecd992 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -124,21 +124,11 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } /// - public async Task RunShutdownTask(CancellationToken cancellationToken) + public Task RunShutdownTask(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); - } - SqliteConnection.ClearAllPools(); + return Task.CompletedTask; } /// -- cgit v1.2.3 From 74ce774effac4d70728e231e6de7c1c5d82a2685 Mon Sep 17 00:00:00 2001 From: Shadowghost Date: Sat, 5 Sep 2026 17:33:47 +0200 Subject: Revert to doing after migration and on shutdown --- .../Migrations/JellyfinMigrationService.cs | 6 ++- Jellyfin.Server/Program.cs | 30 ++++++++++----- Jellyfin.Server/ServerSetupApp/StartupActivity.cs | 2 +- .../IJellyfinDatabaseProvider.cs | 5 ++- .../SqliteDatabaseProvider.cs | 45 +++++++++++++++------- 5 files changed, 60 insertions(+), 28 deletions(-) (limited to 'src') diff --git a/Jellyfin.Server/Migrations/JellyfinMigrationService.cs b/Jellyfin.Server/Migrations/JellyfinMigrationService.cs index 6ecbe6a95e..5ef039a843 100644 --- a/Jellyfin.Server/Migrations/JellyfinMigrationService.cs +++ b/Jellyfin.Server/Migrations/JellyfinMigrationService.cs @@ -188,8 +188,8 @@ internal class JellyfinMigrationService /// /// The stage to migrate. /// The service provider handed to the migrations. - /// A representing the asynchronous operation. - public async Task MigrateStepAsync(JellyfinMigrationStageTypes stage, IServiceProvider? serviceProvider) + /// A value indicating whether at least one migration has been applied. + public async Task MigrateStepAsync(JellyfinMigrationStageTypes stage, IServiceProvider? serviceProvider) { var logger = _startupLogger.With(_loggerFactory.CreateLogger()).BeginGroup($"Migrate stage {stage}."); ICollection migrationStage = (Migrations.FirstOrDefault(e => e.Stage == stage) as ICollection) ?? []; @@ -303,6 +303,8 @@ internal class JellyfinMigrationService completedMigrations++; } + + return completedMigrations > 0; } } diff --git a/Jellyfin.Server/Program.cs b/Jellyfin.Server/Program.cs index 2391c86641..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? _migrationLogger; + private static bool _optimizeDatabaseAfterMigration; private static string? _restoreFromBackup; /// @@ -209,15 +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 OptimizeDatabaseAsync(appHost.ServiceProvider).ConfigureAwait(false); + await OptimizeDatabaseAfterMigrationAsync(appHost.ServiceProvider).ConfigureAwait(false); try { configurationCompleted = true; @@ -272,9 +273,11 @@ namespace Jellyfin.Server // Don't throw additional exception if startup failed. if (appHost.ServiceProvider is not null) { + _logger.LogInformation("Optimizing the database... This might take a while"); + + // Deliberately untimed: a truncated optimization leaves the statistics incomplete. var databaseProvider = appHost.ServiceProvider.GetRequiredService(); - using var shutdownSource = new CancellationTokenSource(TimeSpan.FromSeconds(60)); - await databaseProvider.RunShutdownTask(shutdownSource.Token).ConfigureAwait(false); + await databaseProvider.RunShutdownTask(CancellationToken.None).ConfigureAwait(false); } _appHost = null; @@ -312,7 +315,7 @@ namespace Jellyfin.Server var jellyfinMigrationService = ActivatorUtilities.CreateInstance(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); } /// @@ -327,13 +330,20 @@ namespace Jellyfin.Server public static async Task ApplyCoreMigrationsAsync(IServiceProvider serviceProvider, Migrations.Stages.JellyfinMigrationStageTypes jellyfinMigrationStage) { var jellyfinMigrationService = ActivatorUtilities.CreateInstance(serviceProvider, _migrationLogger!); - await jellyfinMigrationService.MigrateStepAsync(jellyfinMigrationStage, serviceProvider).ConfigureAwait(false); + _optimizeDatabaseAfterMigration |= await jellyfinMigrationService.MigrateStepAsync(jellyfinMigrationStage, serviceProvider).ConfigureAwait(false); } - private static async Task OptimizeDatabaseAsync(IServiceProvider serviceProvider) + 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("Vacuuming and analyzing the database... This might take a while"); + _logger.LogInformation("Migrations have been applied, optimizing the database... This might take a while"); try { @@ -344,7 +354,7 @@ namespace Jellyfin.Server catch (Exception ex) { // A missed optimization only costs performance, so never fail startup over this. - _logger.LogError(ex, "Error while optimizing the database"); + _logger.LogError(ex, "Error while optimizing the database after migration"); } } diff --git a/Jellyfin.Server/ServerSetupApp/StartupActivity.cs b/Jellyfin.Server/ServerSetupApp/StartupActivity.cs index 5fc485bd48..abfc5bc8ba 100644 --- a/Jellyfin.Server/ServerSetupApp/StartupActivity.cs +++ b/Jellyfin.Server/ServerSetupApp/StartupActivity.cs @@ -27,7 +27,7 @@ public static class StartupActivity /// Bringing up core services and plugins. public const string InitializingServices = "Initializing services"; - /// Refreshing the database query planner statistics. + /// Refreshing the database statistics after migrations have run. public const string OptimizingDatabase = "Optimizing database"; /// Running the final startup tasks. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs index a7b2aa493b..77abb45f2a 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Implementations/IJellyfinDatabaseProvider.cs @@ -38,14 +38,15 @@ public interface IJellyfinDatabaseProvider /// /// If supported this should run any periodic maintaince tasks, reclaiming unused space and refreshing the query - /// planner statistics. Also runs on startup once all migrations have been applied. + /// planner statistics. Also used after migrations have modified the database. /// /// The token to abort the operation. /// A representing the asynchronous operation. Task RunScheduledOptimisation(CancellationToken cancellationToken); /// - /// 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 . /// /// The token that will be used to abort the operation. /// A representing the asynchronous operation. diff --git a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs index 6103ecd992..f11cde7e48 100644 --- a/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs +++ b/src/Jellyfin.Database/Jellyfin.Database.Providers.Sqlite/SqliteDatabaseProvider.cs @@ -103,18 +103,9 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } /// - 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("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!"); - } + return OptimizeAsync(cancellationToken); } /// @@ -124,11 +115,39 @@ public sealed class SqliteDatabaseProvider : IJellyfinDatabaseProvider } /// - public Task RunShutdownTask(CancellationToken cancellationToken) + 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(); - return Task.CompletedTask; + } + + private async Task OptimizeAsync(CancellationToken cancellationToken) + { + if (DbContextFactory is null) + { + return; + } + + 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("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!"); + } } /// -- cgit v1.2.3