Elastic Microservices, Rigid Databases ? Connection exhaustion?

Elastic Microservices, Rigid Databases ? Connection exhaustion?

Bridge the Scale Gap with AlloyDB Managed Connection Pooling & Multiplexing strategy

Authors : Adarsha Kuthuru and Kumar Ramamurthy

In the high-stakes world of enterprise applications, database connection management is a critical factor for both performance and stability. As you scale out microservices, each with its own connection pool, your database can quickly become overwhelmed by a “thundering herd” of connections.

Enter the powerful combination of HikariCP (or your application-side pooler) and AlloyDB Managed Connection Pooling (your database-side pooler). This “double pooling” strategy is a best practice for high-scale environments, offering the best of both worlds: lightning-fast connection acquisition for your application and robust protection for your AlloyDB instance.

The Problem: Too Many Connections!

Imagine a fleet of 50 Java microservice instances, each running with a typical HikariCP configuration of 10–20 connections. That’s potentially 500–1000 open TCP connections hitting your database. Even with a powerful database like AlloyDB, this many concurrent connections can lead to:

• CPU Exhaustion: Too much time spent on connection negotiation.
• Memory Pressure: Each connection consumes database server memory.
• “Too Many Connections” Errors: Database reaching its hard limit.

The Solution: The “Double Pool” Advantage

This strategy helps application and database connection management be highly efficient.

• HikariCP: Sits at your application layer. Its job is to hand off requests to the application as quickly as possible. It keeps a small, warm pool of connections to the AlloyDB pooler ready at all times. When your application needs a database connection, HikariCP instantly provides one from its pool, eliminating the latency of establishing a new TCP/TLS handshake.
• AlloyDB Managed Connection Pooling: Sits directly in front of your AlloyDB instance. Its job is to protect the database itself. It takes requests from multiple application-side poolers (or even direct connections) and multiplexes them onto a much smaller, optimized pool of “real” connections to the AlloyDB backend. This ensures the database never sees more connections than it can efficiently handle.

The Relay Race:

1. Your Spring Boot application requests a database connection from its DataSource (powered by HikariCP).
2. HikariCP immediately hands over an existing, “warm” TCP connection to the AlloyDB pooler.
3. The AlloyDB Managed Connection Pooler (configured for Transaction Mode for optimal scaling) receives the SQL query.
4. It then assigns this transaction to one of its available “backend” connections to the actual AlloyDB PostgreSQL engine.
5. As soon as the transaction COMMITs (or ROLLBACKs), the AlloyDB pooler releases that backend connection, making it immediately available for another transaction — even if HikariCP on the application side considers its connection to the pooler still “open.”

Best Practices for Optimal Configuration

To maximize the benefits of this “double pool” strategy for your enterprise applications (e.g., those handling high transaction volumes), follow these guidelines:

1. Configure AlloyDB Managed Pool in Transaction Mode (Default)

This is crucial for scaling. In transaction mode, the backend database connection is returned to the pool immediately after a transaction commits or rolls back. This allows a small number of real database connections to serve a very large number of application requests.

Important Note: Transaction mode means your application should not rely on session-specific SET commands or temporary tables that need to persist across multiple transactions, as these will be reset.

2. Size Your Pools Judiciously

HikariCP (spring.datasource.hikari.maximum-pool-size): Keep this small, typically 5–10 connections per application instance. Since Hikari is connected to the robust AlloyDB pooler, it doesn’t need to be large.

Generally, client connection pool libraries have good defaults. However, to get the best performance out of your client connection pool, you might need to configure the pool size. A good start point is to use the following formula:

connections = (vCPU count * 2) + 1

AlloyDB Managed Pooler (max_pool_size): Set this based on your AlloyDB instance’s vCPU count. A common starting point is 15–20 connections per vCPU. You can fine-tune this with performance testing.

3. Harmonize Timeouts

Ensure your HikariCP maxLifetime is slightly shorter than the AlloyDB pooler’s server_lifetime_timeout (or server_idle_timeout if you’re not using server_lifetime_timeout). This allows Hikari to gracefully retire connections before the AlloyDB pooler forcibly closes them, preventing “Connection reset by peer” errors on the application side.

Spring Boot Example Configuration

Here’s how you might configure your application.properties (or application.yml) for a Spring Boot application connecting to AlloyDB through its managed connection pooler:

Properties

# — — AlloyDB Managed Connection Pooler Configuration — -

# The default port for the AlloyDB Managed Pooler is 6432

spring.datasource.url=jdbc:postgresql://<ALLOYDB_POOLER_IP_ADDRESS>:6432/<DATABASE_NAME>

spring.datasource.username=<DATABASE_USER>

spring.datasource.password=<DATABASE_PASSWORD>

spring.datasource.driver-class-name=org.postgresql.Driver

# — — HikariCP Configuration (Application-Side) — -

spring.datasource.hikari.pool-name=AlloyDBHikariPool

# Keep this small, e.g., 5–10 connections per application instance

spring.datasource.hikari.maximum-pool-size=10

# Maximum time a connection is allowed to sit idle in the pool (e.g., 30 seconds)

spring.datasource.hikari.idle-timeout=30000

# Maximum lifetime of a connection in the pool (e.g., 5 minutes).

# Ensure this is slightly LESS than the AlloyDB pooler’s server_lifetime_timeout or server_idle_timeout.

spring.datasource.hikari.max-lifetime=300000 # 5 minutes

# Maximum time to wait for a connection from the pool (e.g., 5 seconds)

spring.datasource.hikari.connection-timeout=5000

# Test connection validity before returning to pool

spring.datasource.hikari.connection-test-query=SELECT 1

Replace <ALLOYDB_POOLER_IP_ADDRESS>, <DATABASE_NAME>, <DATABASE_USER>, and <DATABASE_PASSWORD> with your specific AlloyDB instance details.

Conclusion

By strategically implementing both HikariCP and AlloyDB Managed Connection Pooling, you create a robust, high-performance, and scalable data access layer. Your applications get blazing-fast connection access, and your AlloyDB database remains protected and efficient, ready to handle even the most demanding enterprise workloads.

To get started, you’ll need to enable the feature in your instance. You can find the full setup guide in the official AlloyDB Managed Connection Pooling documentation.

Elastic Microservices, Rigid Databases ? Connection exhaustion? was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Source Credit: https://medium.com/google-cloud/elastic-microservices-rigid-databases-connection-exhaustion-8cdc558f212a?source=rss—-e52cf94d98af—4

About the Author

Deven Goratela

Administrator

Visit Website View All Posts