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 --- Emby.Server.Implementations/Data/ConnectionPool.cs | 68 ++++++++++++++++++++++ 1 file changed, 68 insertions(+) create mode 100644 Emby.Server.Implementations/Data/ConnectionPool.cs (limited to 'Emby.Server.Implementations/Data/ConnectionPool.cs') diff --git a/Emby.Server.Implementations/Data/ConnectionPool.cs b/Emby.Server.Implementations/Data/ConnectionPool.cs new file mode 100644 index 000000000..86a125ba5 --- /dev/null +++ b/Emby.Server.Implementations/Data/ConnectionPool.cs @@ -0,0 +1,68 @@ +#pragma warning disable CS1591 + +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 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()); + } + } + + public ManagedConnection GetConnection() + { + _lock.Wait(); + if (!_connections.TryDequeue(out var connection)) + { + _lock.Release(); + throw new InvalidOperationException(); + } + + return new ManagedConnection(connection, this); + } + + public void Return(SQLiteDatabaseConnection connection) + { + _connections.Enqueue(connection); + _lock.Release(); + } + + public void Dispose() + { + if (_disposed) + { + return; + } + + for (int i = 0; i < _count; i++) + { + _lock.Wait(); + if (!_connections.TryDequeue(out var connection)) + { + _lock.Release(); + throw new InvalidOperationException(); + } + + connection.Dispose(); + } + + _lock.Dispose(); + + _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/ConnectionPool.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 13152bf09dcadf8807e1240be7ca84662d2ed397 Mon Sep 17 00:00:00 2001 From: Bond_009 Date: Fri, 21 Apr 2023 14:05:27 +0200 Subject: Change number of read connections based on # of threads and add comments --- Emby.Server.Implementations/Data/ConnectionPool.cs | 19 +++++++++++++++++-- .../Data/SqliteItemRepository.cs | 2 +- 2 files changed, 18 insertions(+), 3 deletions(-) (limited to 'Emby.Server.Implementations/Data/ConnectionPool.cs') diff --git a/Emby.Server.Implementations/Data/ConnectionPool.cs b/Emby.Server.Implementations/Data/ConnectionPool.cs index 091a1b74f..6d28a5e43 100644 --- a/Emby.Server.Implementations/Data/ConnectionPool.cs +++ b/Emby.Server.Implementations/Data/ConnectionPool.cs @@ -1,16 +1,22 @@ -#pragma warning disable CS1591 - using System; using System.Collections.Concurrent; using SQLitePCL.pretty; namespace Emby.Server.Implementations.Data; +/// +/// A pool of SQLite Database connections. +/// public sealed class ConnectionPool : IDisposable { private readonly BlockingCollection _connections = new(); private bool _disposed; + /// + /// Initializes a new instance of the class. + /// + /// The number of database connection to create. + /// Factory function to create the database connections. public ConnectionPool(int count, Func factory) { for (int i = 0; i < count; i++) @@ -19,6 +25,10 @@ public sealed class ConnectionPool : IDisposable } } + /// + /// Gets a database connection from the pool if one is available, otherwise blocks. + /// + /// A database connection. public ManagedConnection GetConnection() { if (_disposed) @@ -34,6 +44,10 @@ public sealed class ConnectionPool : IDisposable } } + /// + /// Return a database connection to the pool. + /// + /// The database connection to return. public void Return(SQLiteDatabaseConnection connection) { if (_disposed) @@ -45,6 +59,7 @@ public sealed class ConnectionPool : IDisposable _connections.Add(connection); } + /// public void Dispose() { if (_disposed) diff --git a/Emby.Server.Implementations/Data/SqliteItemRepository.cs b/Emby.Server.Implementations/Data/SqliteItemRepository.cs index 33466c34b..22d485d33 100644 --- a/Emby.Server.Implementations/Data/SqliteItemRepository.cs +++ b/Emby.Server.Implementations/Data/SqliteItemRepository.cs @@ -336,7 +336,7 @@ namespace Emby.Server.Implementations.Data _jsonOptions = JsonDefaults.Options; DbFilePath = Path.Combine(_config.ApplicationPaths.DataPath, "library.db"); - ReadConnectionsCount = 5; + ReadConnectionsCount = Environment.ProcessorCount * 2; } /// -- cgit v1.2.3 From 0d67901e373ff8a2ab279d51c3c1f88adf68bfcf Mon Sep 17 00:00:00 2001 From: Bond_009 Date: Mon, 24 Apr 2023 13:08:46 +0200 Subject: Dispose BlockingCollection --- Emby.Server.Implementations/Data/ConnectionPool.cs | 2 ++ 1 file changed, 2 insertions(+) (limited to 'Emby.Server.Implementations/Data/ConnectionPool.cs') diff --git a/Emby.Server.Implementations/Data/ConnectionPool.cs b/Emby.Server.Implementations/Data/ConnectionPool.cs index 6d28a5e43..a671eb165 100644 --- a/Emby.Server.Implementations/Data/ConnectionPool.cs +++ b/Emby.Server.Implementations/Data/ConnectionPool.cs @@ -72,6 +72,8 @@ public sealed class ConnectionPool : IDisposable connection.Dispose(); } + _connections.Dispose(); + _disposed = true; } } -- cgit v1.2.3 From 8e1f0d53c1efe286628a4119b3c595f219513d23 Mon Sep 17 00:00:00 2001 From: Bond_009 Date: Thu, 4 May 2023 14:42:39 +0200 Subject: nameof instead of GetType().Name --- Emby.Server.Implementations/Data/ConnectionPool.cs | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'Emby.Server.Implementations/Data/ConnectionPool.cs') diff --git a/Emby.Server.Implementations/Data/ConnectionPool.cs b/Emby.Server.Implementations/Data/ConnectionPool.cs index a671eb165..5ea7e934f 100644 --- a/Emby.Server.Implementations/Data/ConnectionPool.cs +++ b/Emby.Server.Implementations/Data/ConnectionPool.cs @@ -38,9 +38,9 @@ public sealed class ConnectionPool : IDisposable return new ManagedConnection(_connections.Take(), this); - void ThrowObjectDisposedException() + static void ThrowObjectDisposedException() { - throw new ObjectDisposedException(GetType().Name); + throw new ObjectDisposedException(nameof(ConnectionPool)); } } -- cgit v1.2.3