Database Connection Pool Planned Migration
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-tenant database environments, service relocation from one database instance to another often results in disconnect and logon storms due to immediate closure and reestablishment of connections, leading to significant dips in throughput and spikes in response times.
Innovation Solution
A system and method for planned migration of service connections using a data source with parameters for delay-for-planned-draining and planned-draining-period, allowing connections to remain active during service downtime and gradually migrate to a new instance upon service availability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If connections are closed immediately after service is stopped on source database instance, then service relocation can proceed, but disconnect storm and logon storm occur leading to throughput dip and response time spike
Solution Approach 1:
The system performs preliminary actions by maintaining connections in a pending state before actually closing them. When a service is stopped on the source instance, connections are not immediately closed but kept alive with a flag indicating they should be closed on next use. This preliminary maintenance of connections prevents the disconnect storm that would occur with immediate closure.
Solution Approach 2:
The system provides beforehand cushioning by using a connection pool that can buffer connection requests during service relocation. The connection pool absorbs the shock of service migration by maintaining a pool of connections that can be gradually transitioned from source to target instance, cushioning against the throughput dip and response time spike that would occur with immediate connection closure and reestablishment.
2Productivity
If connections are maintained during service relocation, then disconnect and logon storms are prevented, but service downtime increases
Solution Approach 1:
The system applies dynamics by making the connection state flexible rather than static. Connections transition through different states: active, pending closure, and closed. The pending closure state allows connections to be maintained temporarily during service relocation and then gracefully closed when appropriate. This dynamic state management enables the system to balance connection maintenance benefits against downtime costs, closing connections after a configurable timeout period rather than maintaining them indefinitely.
3Reliability
If connection pool reestablishes connections immediately after service comes back up, then service availability is restored, but logon storm occurs
Solution Approach 1:
The system uses periodic action by spreading connection reestablishment over time rather than doing it all at once. When the service comes back up on the target instance, the connection pool gradually reestablishes connections during a warmup period, configured by a percentage parameter that controls the rate of connection creation. This periodic, controlled reestablishment prevents the logon storm that would occur with immediate mass connection creation.
Data Source
AI summary
A system and method for a planned migration of service connections from a first database instance to a second database instance in a clustered database. In accordance with an embodiment, a data source and connection pool enable access by a software application to a service on the first database instance. The data source is associated with a parameter specifying a delay-for-planned-draining, and a parameter specifying a planned-draining-period, which are used to rebalance connections when relocating a service. When a service is stopped on the first database instance, the connection pool receives a “service down” event from a notification service, and maintains the connections up to a time corresponding to the delay-for-planned-draining. When a “service up” event is received, the connection pool spreads migration of the connections from the first to second database instance over the planned-draining-period.