High DB Contention on T_FLOW_STATE Table Leading to Connection Exhaustion in IDSP

Products

• Symantec Identity Security Platform - IDSP (formerly VIP Authentication Hub)

Issue/Introduction

During peak loads or pod restarts, high contention is observed on the T_FLOW_STATE table. This contention results in database locks, causing sessions to hang and connection counts to spike to the maximum threshold. This eventually delays all authentication processes and impacts system availability.

Environment

IDSP (VIP Authentication Hub)

Cause

The T_FLOW_STATE table is responsible for maintaining the sign-in state for active sessions. During pod startup or scaling events, pods may briefly attempt to allocate a high number of connections. If the database pool size is misconfigured or exceeds the database's capacity to handle concurrent locks on the state table, performance degrades.

Resolution

1. Connection Pool Configuration

   
   While pods may initially peak at the maximum pool size (e.g., 200) during a start or restart event, they should stabilize quickly.
   
   - Recommendation: Based on standard sizing guidelines, set a maximum DB pool size of 100 per pod (1).
   - Note: This is a ceiling, not a constant usage requirement. Limiting the pool helps prevent the database from being overwhelmed during mass pod restarts.
   
   

2. Environment-Specific Sizing

   
   The IDSP Sizing Sheet should be used as a baseline reference only (2).
   
   - Perform load and performance testing in a Pre-Production environment that mirrors Production.
   - Monitor actual T_FLOW_STATE contention and connection usage during these tests.
   - Adjust the pod and DB pool sizes based on observed metrics before promoting changes to Production.
   
   

3. Database Memory & Resource Allocation

   
   Supporting 1,000 concurrent connections is well within the capabilities of a properly tuned MySQL instance and does not require excessive memory (e.g., 1TB).
   
   - Memory Estimation: It is standard to estimate between 2 MB and 10 MB of memory per connection, depending on your MySQL buffer settings.
   - Example: Even at 10 MB per connection, 1,000 connections would only require approximately 10 GB of memory.
   - Reference: Industry standards (such as AWS RDS) often use a calculation of 12.5 MB per connection for MySQL.
   - Support Note: If contention persists after adjusting pool sizes, review the InnoDB deadlock logs and ensure the T_FLOW_STATE table has been properly indexed according to the latest schema requirements (3). 

Additional Information

1. Sizing Systems
   
   
2. deployment sizing sheet
   
   
3. 17.7.2.4 Locking Reads