Skip to content

Optimize database connection pool settings for MySQL and PostgreSQL - #7645

Merged
chrislusf merged 1 commit into
masterfrom
optimize-postgres-connection-pool
Dec 7, 2025
Merged

chrislusf merged 1 commit into
masterfrom
optimize-postgres-connection-pool

Conversation

@chrislusf

@chrislusf chrislusf commented Dec 7, 2025 •

Copy link
Copy Markdown
Collaborator

Problem

Fixes #6887 - Users experiencing 'cannot assign requested address' errors when using PostgreSQL2 storage type under high load with large GET/POST requests.

Root Cause

The error is caused by ephemeral port exhaustion due to:

  1. Too many concurrent database connections (100 max)
  2. Connections never expiring ()
  3. High number of idle connections accumulating over time

Changes

Updated connection pool settings for all SQL database stores in filer.toml:

MySQL ([mysql] and [mysql2])

  • connection_max_idle: 2 → 10
  • connection_max_open: 100 → 50
  • connection_max_lifetime_seconds: 0 → 300 (5 minutes)

PostgreSQL ([postgres] and [postgres2])

  • connection_max_idle: 100 → 10
  • connection_max_open: 100 → 50
  • connection_max_lifetime_seconds: 0 → 300 (5 minutes)

Benefits

  1. Prevents ephemeral port exhaustion - Reducing max connections from 100 to 50 significantly reduces the number of TCP connections and ephemeral ports used
  2. Forces connection recycling - Setting lifetime to 300 seconds ensures connections are regularly refreshed, preventing stale connections and port buildup
  3. Reduces resource consumption - Keeping 10 idle connections instead of 100 reduces memory and connection overhead
  4. Improves stability under high load - More predictable connection behavior during heavy GET/POST operations

Testing Recommendations

Users should test with these new defaults and adjust based on their specific workload:

  • For very high throughput, may need to increase connection_max_open to 75-100
  • For connection poolers like PgBouncer, these defaults should work well
  • Monitor with: netstat -an | grep :5432 | wc -l to track active connections

Additional System Tuning

For production environments experiencing this issue, also recommend:

# /etc/sysctl.conf
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 30
net.ipv4.ip_local_port_range = 10000 65535

Summary by CodeRabbit

  • Chores
    • Optimized database connection pool configuration across MySQL and PostgreSQL instances to improve resource management, enhance connection lifecycle handling, and increase overall system stability.

✏️ Tip: You can customize this high-level summary in your review settings.

- Reduce connection_max_idle from 100 to 10 (PostgreSQL) and from 2 to 10 (MySQL)
- Reduce connection_max_open from 100 to 50 for all database stores
- Set connection_max_lifetime_seconds to 300 (5 minutes) to force connection recycling

This prevents 'cannot assign requested address' errors under high load by:
1. Limiting the number of concurrent connections to reduce ephemeral port usage
2. Forcing connection recycling to prevent stale connections and port exhaustion
3. Reducing idle connections to minimize resource consumption

Fixes #6887
@gemini-code-assist

Copy link
Copy Markdown
Contributor

Summary of Changes

Hello @chrislusf, I'm Gemini Code Assist1! I'm currently reviewing this pull request and will post my feedback shortly. In the meantime, here's a summary to help you and other reviewers quickly get up to speed!

This pull request refines the database connection pool settings for MySQL and PostgreSQL within the filer.toml configuration. The primary goal is to mitigate issues like ephemeral port exhaustion and enhance overall system stability, especially when the application experiences high load. By adjusting parameters such as maximum idle connections, maximum open connections, and connection lifetime, the changes ensure more efficient resource utilization and prevent the accumulation of stale or excessive database connections.

Highlights

  • Problem Resolution: This pull request addresses and fixes 'cannot assign requested address' errors, specifically ephemeral port exhaustion, which occurred when using PostgreSQL2 storage under high load with large GET/POST requests.
  • Connection Pool Optimization: Database connection pool settings in filer.toml have been optimized for both MySQL and PostgreSQL configurations to prevent resource exhaustion and improve stability.
  • MySQL Configuration Changes: For MySQL ([mysql] and [mysql2]), connection_max_idle increased from 2 to 10, connection_max_open decreased from 100 to 50, and connection_max_lifetime_seconds was set to 300 (5 minutes) from 0.
  • PostgreSQL Configuration Changes: For PostgreSQL ([postgres] and [postgres2]), connection_max_idle decreased from 100 to 10, connection_max_open decreased from 100 to 50, and connection_max_lifetime_seconds was set to 300 (5 minutes) from 0.
  • System Stability and Resource Management: The changes aim to prevent ephemeral port exhaustion, force connection recycling, reduce memory and connection overhead, and ultimately improve system stability under high load conditions.
Using Gemini Code Assist

The full guide for Gemini Code Assist can be found on our documentation page, here are some quick tips.

Invoking Gemini

You can request assistance from Gemini at any point by creating a comment using either /gemini <command> or @gemini-code-assist <command>. Below is a summary of the supported commands on the current page.

Feature Command Description
Code Review /gemini review Performs a code review for the current pull request in its current state.
Pull Request Summary /gemini summary Provides a summary of the current pull request in its current state.
Comment @gemini-code-assist Responds in comments when explicitly tagged, both in pull request comments and review comments.
Help /gemini help Displays a list of available commands.

Customization

To customize Gemini Code Assist for GitHub experience, repository maintainers can create a configuration file and/or provide a custom code review style guide (such as PEP-8 for Python) by creating and adding files to a .gemini/ folder in the base of the repository. Detailed instructions can be found here.

Limitations & Feedback

Gemini Code Assist may make mistakes. Please leave feedback on any instances where its feedback is incorrect or counter productive. You can react with 👍 and 👎 on @gemini-code-assist comments. If you're interested in giving your feedback about your experience with Gemini Code Assist for Github and other Google products, sign up here.

You can also get AI-powered code generation, chat, as well as code reviews directly in the IDE at no cost with the Gemini Code Assist IDE Extension.

Footnotes

  1. Review the Privacy Notices, Generative AI Prohibited Use Policy, Terms of Service, and learn how to configure Gemini Code Assist in GitHub here. Gemini can make mistakes, so double check it and use code with caution. ↩

@coderabbitai

coderabbitai Bot commented Dec 7, 2025 •

Copy link
Copy Markdown
Contributor

Caution

Review failed

The pull request is closed.

Walkthrough

Updated database connection pool configuration settings in the filer scaffold template across MySQL and PostgreSQL store sections, increasing idle connection limits, reducing maximum open connections, and introducing connection lifetime constraints.

Changes

Cohort / File(s) Change Summary
Connection pool configuration
weed/command/scaffold/filer.toml
Updated connection pool settings for [mysql], [mysql2], [postgres], and [postgres2] sections: connection_max_idle (2 → 10), connection_max_open (100 → 50), connection_max_lifetime_seconds (0 → 300)

Estimated code review effort

🎯 2 (Simple) | ⏱️ ~5 minutes

  • Verify that the new pool parameters align with intended connection management strategy for handling concurrent requests
  • Confirm the 300-second lifetime setting prevents long-lived stale connections while allowing sufficient request processing time

Poem

🐰✨ A rabbit hops through pools so grand,
With connections managed, perfectly planned!
Idle limits raised, open count measured just right,
Database stability shining so bright! 🌟

✨ Finishing touches
🧪 Generate unit tests (beta)
  • Create PR with unit tests
  • Post copyable unit tests in a comment
  • Commit unit tests in branch optimize-postgres-connection-pool

📜 Recent review details

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro

📥 Commits

Reviewing files that changed from the base of the PR and between dcc200f and 60366db.

📒 Files selected for processing (1)
  • weed/command/scaffold/filer.toml (4 hunks)

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

Comment @coderabbitai help to get the list of available commands and usage tips.

@chrislusf
chrislusf merged commit 5d53edb into master Dec 7, 2025
34 of 35 checks passed

@gemini-code-assist gemini-code-assist Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Code Review

This pull request optimizes the default database connection pool settings for MySQL and PostgreSQL to address ephemeral port exhaustion under high load. The changes reduce the maximum number of open connections, set a maximum connection lifetime to ensure recycling, and adjust the number of idle connections to more sensible defaults. These are excellent changes that will improve the stability and resource consumption of the filer. I've added a few suggestions to include comments in the filer.toml configuration file to explain these new defaults to users, which will help them with future tuning.

Comment on lines +66 to +68
connection_max_idle = 10
connection_max_open = 50
connection_max_lifetime_seconds = 300

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

medium

The new default values for the connection pool are much more sensible for production environments. To improve clarity for users who will be editing this configuration file, it would be beneficial to add inline comments explaining what each of these settings does. This will help them understand the impact of these settings and tune them for their specific needs.

connection_max_idle = 10  # Maximum number of connections in the idle connection pool.
connection_max_open = 50  # Maximum number of open connections to the database. Helps prevent ephemeral port exhaustion.
connection_max_lifetime_seconds = 300  # Maximum lifetime of a connection in seconds. Helps recycle connections.

Comment on lines +90 to +92
connection_max_idle = 10
connection_max_open = 50
connection_max_lifetime_seconds = 300

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

medium

The new default values for the connection pool are much more sensible for production environments. To improve clarity for users who will be editing this configuration file, it would be beneficial to add inline comments explaining what each of these settings does. This will help them understand the impact of these settings and tune them for their specific needs.

connection_max_idle = 10  # Maximum number of connections in the idle connection pool.
connection_max_open = 50  # Maximum number of open connections to the database. Helps prevent ephemeral port exhaustion.
connection_max_lifetime_seconds = 300  # Maximum lifetime of a connection in seconds. Helps recycle connections.

Comment on lines +120 to +122
connection_max_idle = 10
connection_max_open = 50
connection_max_lifetime_seconds = 300

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

medium

The new default values for the connection pool are much more sensible for production environments. To improve clarity for users who will be editing this configuration file, it would be beneficial to add inline comments explaining what each of these settings does. This will help them understand the impact of these settings and tune them for their specific needs.

connection_max_idle = 10  # Maximum number of connections in the idle connection pool.
connection_max_open = 50  # Maximum number of open connections to the database. Helps prevent ephemeral port exhaustion.
connection_max_lifetime_seconds = 300  # Maximum lifetime of a connection in seconds. Helps recycle connections.

Comment on lines +159 to +161
connection_max_idle = 10
connection_max_open = 50
connection_max_lifetime_seconds = 300

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

medium

The new default values for the connection pool are much more sensible for production environments. To improve clarity for users who will be editing this configuration file, it would be beneficial to add inline comments explaining what each of these settings does. This will help them understand the impact of these settings and tune them for their specific needs.

connection_max_idle = 10  # Maximum number of connections in the idle connection pool.
connection_max_open = 50  # Maximum number of open connections to the database. Helps prevent ephemeral port exhaustion.
connection_max_lifetime_seconds = 300  # Maximum lifetime of a connection in seconds. Helps recycle connections.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Filer use Postgresql2 storage type error with cannot assign requested address

1 participant