Design Document
Design Document: ChannelDbConnectionPool
Problem Statement
The current connection pool implementation is slow to open new connections and does not follow async best practices.
Connection opening is serialized, causing delays when multiple new connections are required simultaneously. This is done using a semaphore to rate limit connection creation. When multiple new connections are requested, they queue up waiting for the semaphore. Once acquired, the thread opens the connection and releases the semaphore, allowing the next thread to proceed. This approach was initially designed to prevent overwhelming the server but can lead to significant delays, especially in high-latency environments.
Async requests are also serialized through an additional queue. When an async request is made, it is added to a queue, and a reader thread processes these requests one by one. This method was chosen due to the lack of async APIs in native SNI, resulting in synchronous handling of async requests on a dedicated thread.
Design Goals
- Enable parallel connection opening.
- Minimize thread contention and synchronization overhead.
- Follow async best practices to reduce managed threadpool pressure and enable other components of the driver to modernize their async code.
Overview
The core of the design is the Channel data structure from the System.Threading.Channels library (available to .NET Framework as a nuget package) (Also see Stephen Toub's intro here). Channels are thread-safe, async-first queues that fit well for the connection pooling use case.
A single channel holds the idle connections managed by the pool. A channel reader reads idle connections out of the channel to vend them to SqlConnections. A channel writer writes connections back to the channel when they are returned to the pool.
Pool maintenance operations (warmup, pruning) are handled asynchronously as Tasks.
Transaction-enlisted connections are stored in a separate dictionary data structure, in the same manner as the WaitHandleDbConnectionPool implementation.
This design is based on the PoolingDataSource class from the npgsql driver. The npgsql implemenation is proven to be reliable and performant in real production workloads.
Why the Channel Data Structure is a Good Fit
-
Thread-Safety:
Channels are designed to facilitate thread-safe communication between producers (e.g., threads returning connections to the pool) and consumers (e.g., threads requesting connections). This eliminates the need for complex locking mechanisms, reducing the risk of race conditions and deadlocks.
-
Built-In Request Queueing:
Channels provide a succinct API to wait asynchronously if no connections are available at the time of the request.
-
Asynchronous Support:
Channels provide a robust async API surface, simplifying the async paths for the connection pool.
-
Performant:
Channels are fast and avoid extra allocations, making them suitable for high throughput applications: https://devblogs.microsoft.com/dotnet/an-introduction-to-system-threading-channels/#performance
Workflows
-
Warmup:
New connections are written to the tail of the idle channel by an async task.
-
Acquire Connection:
Idle connections are acquired from the head of the idle channel.
-
Release Connection:
Connections that are released to the pool are added to the tail of the idle channel.
-
Pruning:
Connections are pruned from the head of the idle channel.
Test Plan
The testing strategy is built on three pillars:
- Thorough unit and integration test coverage.
- Test coverage follows a typical hierarchy, with targeted integration tests built on a large foundation of unit tests.
- Tests run with the AppContextSwitch both on and off to prove functional parity between the new and old pool implementations.
- Performance testing
- Covers both pool implementations, proves performance improvements in the new implementation and guards against performance regressions.
- Opt-in release approach and community feedback.
- Customers and community members may choose to use the new pool implementation and provide feedback on its performance and reliability.
Performance Benchmarks
Note: All graphed results use managed SNI. See full results below for native SNI.
The channel based implementation shows significant performance improvements across frameworks and operating systems. In particular, interacting with a warm pool is much faster.
When interacting with a cold pool and connecting to an Azure database, performance is equivalent to the legacy implementation provided enough threads are made available in the managed threadpool. This requirement highlights a bottleneck present further down the stack when acquiring federated auth tokens.
Windows - .NET 8.0
Windows - .NET Framework 4.8.1
Linux - net8.0
Windows - net8.0 - AzureSQL - Default Azure Credential
Windows - .NET Framework 4.8.1 - AzureSQL - Default Azure Credential
Windows - NET 8.0 - Azure SQL - AccessToken
Discussed in #2612
Originally posted by mdaigle June 26, 2024
POC code: #3211
Work Plan
a. New pool scaffolding #3352
b. Add unit test project #3380
c. Add ChannelDbConnectionPool stub and unit tests. #3396
a. Get/Return pooled connections #3404
a. Implement ChannelDbConnectionPool.Clear #4194
a. Remove unnecessary inheritance structure from pool key class. #4235
b. Remove connection options inheritance #4237
c. Remove extra connection options parameters #4261
d. Connect timeout propagated through pool #4270
a. Pruning timer not disposed even after disposing all SqlConnection instances #1881
a. Add automatic pool size reduction (pruning) to ChannelDbConnectionPool #4304
a. Add Pool Idle Timeout to control connection pool #343
a. Add configurable idle connection timeout (ADO #39970) #4295
a. Implement pool shutdown for ChannelDbConnectionPool and harden WaitHandleDbConnectionPool shutdown #4302
a. Extract pool blocking-period error state into BlockingPeriodErrorState #4395
a. Add connection-creation rate limiting to ChannelDbConnectionPool #4396
a. Background pool warmup and replenishment for ChannelDbConnectionPool #4452
a. Refactor ForceNewConnection #4415
b. ChannelDbConnectionPool replace connection #4429
a. Cleanup | SqlInternalConnection properties #3743
b. Cleanup | Split out TransactedConnectionPool #3746
d. Test | Connection pool transaction tests #3805
e. ChannelDbConnectionPool transaction support #4487
a. Reclaim leaked connections in ChannelDbConnectionPool #4490 / Reclaim emancipated connections, including while callers are parked #4529
a. Emit pool metrics and traces, and fix Count semantics in ChannelDbConnectionPool #4504
b. Add pool tracing/metrics parity and surface pooled-open timeout cause #4505
i. Improve error reporting when an application fails to obtain a connection from the pool #3545
a. [Tests] Setup needed for Connection Pool reliability testing #3667
b. Expand connection pool benchmark coverage for pool implementation A/B testing #4459
App context switches
- UseConnectionPoolV2 - Legacy -Design Document
Design Document:
ChannelDbConnectionPoolProblem Statement
The current connection pool implementation is slow to open new connections and does not follow async best practices.
Connection opening is serialized, causing delays when multiple new connections are required simultaneously. This is done using a semaphore to rate limit connection creation. When multiple new connections are requested, they queue up waiting for the semaphore. Once acquired, the thread opens the connection and releases the semaphore, allowing the next thread to proceed. This approach was initially designed to prevent overwhelming the server but can lead to significant delays, especially in high-latency environments.
Async requests are also serialized through an additional queue. When an async request is made, it is added to a queue, and a reader thread processes these requests one by one. This method was chosen due to the lack of async APIs in native SNI, resulting in synchronous handling of async requests on a dedicated thread.
Design Goals
Overview
The core of the design is the Channel data structure from the System.Threading.Channels library (available to .NET Framework as a nuget package) (Also see Stephen Toub's intro here). Channels are thread-safe, async-first queues that fit well for the connection pooling use case.
A single channel holds the idle connections managed by the pool. A channel reader reads idle connections out of the channel to vend them to SqlConnections. A channel writer writes connections back to the channel when they are returned to the pool.
Pool maintenance operations (warmup, pruning) are handled asynchronously as Tasks.
Transaction-enlisted connections are stored in a separate dictionary data structure, in the same manner as the WaitHandleDbConnectionPool implementation.
This design is based on the PoolingDataSource class from the npgsql driver. The npgsql implemenation is proven to be reliable and performant in real production workloads.
Why the Channel Data Structure is a Good Fit
Thread-Safety:
Channels are designed to facilitate thread-safe communication between producers (e.g., threads returning connections to the pool) and consumers (e.g., threads requesting connections). This eliminates the need for complex locking mechanisms, reducing the risk of race conditions and deadlocks.
Built-In Request Queueing:
Channels provide a succinct API to wait asynchronously if no connections are available at the time of the request.
Asynchronous Support:
Channels provide a robust async API surface, simplifying the async paths for the connection pool.
Performant:
Channels are fast and avoid extra allocations, making them suitable for high throughput applications: https://devblogs.microsoft.com/dotnet/an-introduction-to-system-threading-channels/#performance
Workflows
Warmup:
New connections are written to the tail of the idle channel by an async task.
Acquire Connection:
Idle connections are acquired from the head of the idle channel.
Release Connection:
Connections that are released to the pool are added to the tail of the idle channel.
Pruning:
Connections are pruned from the head of the idle channel.
Test Plan
The testing strategy is built on three pillars:
Performance Benchmarks
Note: All graphed results use managed SNI. See full results below for native SNI.
The channel based implementation shows significant performance improvements across frameworks and operating systems. In particular, interacting with a warm pool is much faster.
When interacting with a cold pool and connecting to an Azure database, performance is equivalent to the legacy implementation provided enough threads are made available in the managed threadpool. This requirement highlights a bottleneck present further down the stack when acquiring federated auth tokens.
Windows - .NET 8.0
Windows - .NET Framework 4.8.1
Linux - net8.0
Windows - net8.0 - AzureSQL - Default Azure Credential
Windows - .NET Framework 4.8.1 - AzureSQL - Default Azure Credential
Windows - NET 8.0 - Azure SQL - AccessToken