From 858dadcdd1caadb5fa8cc13a02eb227098f39c3c Mon Sep 17 00:00:00 2001 From: Bond_009 Date: Fri, 14 Apr 2023 13:43:56 +0200 Subject: POC sql connection pool --- .../Data/BaseSqliteRepository.cs | 115 +++++++++++++++------ 1 file changed, 82 insertions(+), 33 deletions(-) (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index bc520b86e..859a3c746 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -4,7 +4,6 @@ using System; using System.Collections.Generic; -using System.Threading; using Jellyfin.Extensions; using Microsoft.Extensions.Logging; using SQLitePCL.pretty; @@ -27,9 +26,19 @@ namespace Emby.Server.Implementations.Data /// /// Gets or sets the path to the DB file. /// - /// Path to the DB file. protected string DbFilePath { get; set; } + /// + /// Gets or sets the number of write connections to create. + /// + /// Path to the DB file. + protected int WriteConnectionsCount { get; set; } = 1; + + /// + /// Gets or sets the number of read connections to create. + /// + protected int ReadConnectionsCount { get; set; } = 1; + /// /// Gets the logger. /// @@ -63,7 +72,7 @@ namespace Emby.Server.Implementations.Data /// /// Gets the locking mode. . /// - protected virtual string LockingMode => "EXCLUSIVE"; + protected virtual string LockingMode => "NORMAL"; /// /// Gets the journal mode. . @@ -88,7 +97,7 @@ namespace Emby.Server.Implementations.Data /// /// The temp store mode. /// - protected virtual TempStoreMode TempStore => TempStoreMode.Default; + protected virtual TempStoreMode TempStore => TempStoreMode.Memory; /// /// Gets the synchronous mode. @@ -101,63 +110,115 @@ namespace Emby.Server.Implementations.Data /// Gets or sets the write lock. /// /// The write lock. - protected SemaphoreSlim WriteLock { get; set; } = new SemaphoreSlim(1, 1); + protected ConnectionPool WriteConnections { get; set; } /// /// Gets or sets the write connection. /// /// The write connection. - protected SQLiteDatabaseConnection WriteConnection { get; set; } + protected ConnectionPool ReadConnections { get; set; } + + public virtual void Initialize() + { + WriteConnections = new ConnectionPool(WriteConnectionsCount, CreateWriteConnection); + ReadConnections = new ConnectionPool(ReadConnectionsCount, CreateReadConnection); + } protected ManagedConnection GetConnection(bool readOnly = false) { - WriteLock.Wait(); - if (WriteConnection is not null) + if (readOnly) { - return new ManagedConnection(WriteConnection, WriteLock); + return ReadConnections.GetConnection(); } - WriteConnection = SQLite3.Open( + return WriteConnections.GetConnection(); + } + + protected SQLiteDatabaseConnection CreateWriteConnection() + { + var writeConnection = SQLite3.Open( DbFilePath, DefaultConnectionFlags | ConnectionFlags.Create | ConnectionFlags.ReadWrite, null); if (CacheSize.HasValue) { - WriteConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); } if (!string.IsNullOrWhiteSpace(LockingMode)) { - WriteConnection.Execute("PRAGMA locking_mode=" + LockingMode); + writeConnection.Execute("PRAGMA locking_mode=" + LockingMode); } if (!string.IsNullOrWhiteSpace(JournalMode)) { - WriteConnection.Execute("PRAGMA journal_mode=" + JournalMode); + writeConnection.Execute("PRAGMA journal_mode=" + JournalMode); } if (JournalSizeLimit.HasValue) { - WriteConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); + writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); } if (Synchronous.HasValue) { - WriteConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); + writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); } if (PageSize.HasValue) { - WriteConnection.Execute("PRAGMA page_size=" + PageSize.Value); + writeConnection.Execute("PRAGMA page_size=" + PageSize.Value); } - WriteConnection.Execute("PRAGMA temp_store=" + (int)TempStore); + writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore); // Configuration and pragmas can affect VACUUM so it needs to be last. - WriteConnection.Execute("VACUUM"); + writeConnection.Execute("VACUUM"); - return new ManagedConnection(WriteConnection, WriteLock); + return writeConnection; + } + + protected SQLiteDatabaseConnection CreateReadConnection() + { + var writeConnection = SQLite3.Open( + DbFilePath, + DefaultConnectionFlags | ConnectionFlags.ReadOnly, + null); + + if (CacheSize.HasValue) + { + writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + } + + if (!string.IsNullOrWhiteSpace(LockingMode)) + { + writeConnection.Execute("PRAGMA locking_mode=" + LockingMode); + } + + if (!string.IsNullOrWhiteSpace(JournalMode)) + { + writeConnection.Execute("PRAGMA journal_mode=" + JournalMode); + } + + if (JournalSizeLimit.HasValue) + { + writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); + } + + if (Synchronous.HasValue) + { + writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); + } + + if (PageSize.HasValue) + { + writeConnection.Execute("PRAGMA page_size=" + PageSize.Value); + } + + writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore); + + return writeConnection; } public IStatement PrepareStatement(ManagedConnection connection, string sql) @@ -240,22 +301,10 @@ namespace Emby.Server.Implementations.Data if (dispose) { - WriteLock.Wait(); - try - { - WriteConnection?.Dispose(); - } - finally - { - WriteLock.Release(); - } - - WriteLock.Dispose(); + WriteConnections.Dispose(); + ReadConnections.Dispose(); } - WriteConnection = null; - WriteLock = null; - _disposed = true; } } -- cgit v1.2.3 From 33f97045f957179c907a22af96dac0261b3651d8 Mon Sep 17 00:00:00 2001 From: Bond_009 Date: Fri, 14 Apr 2023 21:38:12 +0200 Subject: Use BlockingCollection --- .../Data/BaseSqliteRepository.cs | 37 ++++++++----------- Emby.Server.Implementations/Data/ConnectionPool.cs | 42 ++++++++++------------ 2 files changed, 32 insertions(+), 47 deletions(-) (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index 859a3c746..ce0d03b2b 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -122,18 +122,17 @@ namespace Emby.Server.Implementations.Data { WriteConnections = new ConnectionPool(WriteConnectionsCount, CreateWriteConnection); ReadConnections = new ConnectionPool(ReadConnectionsCount, CreateReadConnection); - } - protected ManagedConnection GetConnection(bool readOnly = false) - { - if (readOnly) + // Configuration and pragmas can affect VACUUM so it needs to be last. + using (var connection = GetConnection(true)) { - return ReadConnections.GetConnection(); + connection.Execute("VACUUM"); } - - return WriteConnections.GetConnection(); } + protected ManagedConnection GetConnection(bool readOnly = false) + => readOnly ? ReadConnections.GetConnection() : WriteConnections.GetConnection(); + protected SQLiteDatabaseConnection CreateWriteConnection() { var writeConnection = SQLite3.Open( @@ -173,52 +172,44 @@ namespace Emby.Server.Implementations.Data writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore); - // Configuration and pragmas can affect VACUUM so it needs to be last. - writeConnection.Execute("VACUUM"); - return writeConnection; } protected SQLiteDatabaseConnection CreateReadConnection() { - var writeConnection = SQLite3.Open( + var connection = SQLite3.Open( DbFilePath, DefaultConnectionFlags | ConnectionFlags.ReadOnly, null); if (CacheSize.HasValue) { - writeConnection.Execute("PRAGMA cache_size=" + CacheSize.Value); + connection.Execute("PRAGMA cache_size=" + CacheSize.Value); } if (!string.IsNullOrWhiteSpace(LockingMode)) { - writeConnection.Execute("PRAGMA locking_mode=" + LockingMode); + connection.Execute("PRAGMA locking_mode=" + LockingMode); } if (!string.IsNullOrWhiteSpace(JournalMode)) { - writeConnection.Execute("PRAGMA journal_mode=" + JournalMode); + connection.Execute("PRAGMA journal_mode=" + JournalMode); } if (JournalSizeLimit.HasValue) { - writeConnection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); + connection.Execute("PRAGMA journal_size_limit=" + JournalSizeLimit.Value); } if (Synchronous.HasValue) { - writeConnection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); + connection.Execute("PRAGMA synchronous=" + (int)Synchronous.Value); } - if (PageSize.HasValue) - { - writeConnection.Execute("PRAGMA page_size=" + PageSize.Value); - } + connection.Execute("PRAGMA temp_store=" + (int)TempStore); - writeConnection.Execute("PRAGMA temp_store=" + (int)TempStore); - - return writeConnection; + return connection; } public IStatement PrepareStatement(ManagedConnection connection, string sql) diff --git a/Emby.Server.Implementations/Data/ConnectionPool.cs b/Emby.Server.Implementations/Data/ConnectionPool.cs index 86a125ba5..091a1b74f 100644 --- a/Emby.Server.Implementations/Data/ConnectionPool.cs +++ b/Emby.Server.Implementations/Data/ConnectionPool.cs @@ -2,44 +2,47 @@ using System; using System.Collections.Concurrent; -using System.Threading; using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data; public sealed class ConnectionPool : IDisposable { - private readonly int _count; - private readonly SemaphoreSlim _lock; - private readonly ConcurrentQueue _connections = new ConcurrentQueue(); + private readonly BlockingCollection _connections = new(); private bool _disposed; public ConnectionPool(int count, Func factory) { - _count = count; - _lock = new SemaphoreSlim(count, count); for (int i = 0; i < count; i++) { - _connections.Enqueue(factory.Invoke()); + _connections.Add(factory.Invoke()); } } public ManagedConnection GetConnection() { - _lock.Wait(); - if (!_connections.TryDequeue(out var connection)) + if (_disposed) { - _lock.Release(); - throw new InvalidOperationException(); + ThrowObjectDisposedException(); } - return new ManagedConnection(connection, this); + return new ManagedConnection(_connections.Take(), this); + + void ThrowObjectDisposedException() + { + throw new ObjectDisposedException(GetType().Name); + } } public void Return(SQLiteDatabaseConnection connection) { - _connections.Enqueue(connection); - _lock.Release(); + if (_disposed) + { + connection.Dispose(); + return; + } + + _connections.Add(connection); } public void Dispose() @@ -49,20 +52,11 @@ public sealed class ConnectionPool : IDisposable return; } - for (int i = 0; i < _count; i++) + foreach (var connection in _connections) { - _lock.Wait(); - if (!_connections.TryDequeue(out var connection)) - { - _lock.Release(); - throw new InvalidOperationException(); - } - connection.Dispose(); } - _lock.Dispose(); - _disposed = true; } } -- cgit v1.2.3 From 8dba3a44fd3f4771acb0cade2c91696179ad21d9 Mon Sep 17 00:00:00 2001 From: Bond_009 Date: Fri, 14 Apr 2023 22:43:14 +0200 Subject: Get write connection for vacuum --- Emby.Server.Implementations/Data/BaseSqliteRepository.cs | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'Emby.Server.Implementations/Data/BaseSqliteRepository.cs') diff --git a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs index ce0d03b2b..4b9ab53ae 100644 --- a/Emby.Server.Implementations/Data/BaseSqliteRepository.cs +++ b/Emby.Server.Implementations/Data/BaseSqliteRepository.cs @@ -124,7 +124,7 @@ namespace Emby.Server.Implementations.Data ReadConnections = new ConnectionPool(ReadConnectionsCount, CreateReadConnection); // Configuration and pragmas can affect VACUUM so it needs to be last. - using (var connection = GetConnection(true)) + using (var connection = GetConnection()) { connection.Execute("VACUUM"); } -- cgit v1.2.3