Repository navigation
Optimize database connection pool settings for MySQL and PostgreSQL - #7645
Conversation
- 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
Summary of ChangesHello @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 Highlights
Using Gemini Code AssistThe 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
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 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
|
|
Caution Review failedThe pull request is closed. WalkthroughUpdated 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
Estimated code review effort🎯 2 (Simple) | ⏱️ ~5 minutes
Poem
✨ Finishing touches🧪 Generate unit tests (beta)
📜 Recent review detailsConfiguration used: CodeRabbit UI Review profile: CHILL Plan: Pro 📒 Files selected for processing (1)
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. Comment |
There was a problem hiding this comment.
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.
| connection_max_idle = 10 | ||
| connection_max_open = 50 | ||
| connection_max_lifetime_seconds = 300 |
There was a problem hiding this comment.
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.
| connection_max_idle = 10 | ||
| connection_max_open = 50 | ||
| connection_max_lifetime_seconds = 300 |
There was a problem hiding this comment.
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.
| connection_max_idle = 10 | ||
| connection_max_open = 50 | ||
| connection_max_lifetime_seconds = 300 |
There was a problem hiding this comment.
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.
| connection_max_idle = 10 | ||
| connection_max_open = 50 | ||
| connection_max_lifetime_seconds = 300 |
There was a problem hiding this comment.
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.
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:
Changes
Updated connection pool settings for all SQL database stores in
filer.toml:MySQL (
[mysql]and[mysql2])connection_max_idle: 2 → 10connection_max_open: 100 → 50connection_max_lifetime_seconds: 0 → 300 (5 minutes)PostgreSQL (
[postgres]and[postgres2])connection_max_idle: 100 → 10connection_max_open: 100 → 50connection_max_lifetime_seconds: 0 → 300 (5 minutes)Benefits
Testing Recommendations
Users should test with these new defaults and adjust based on their specific workload:
connection_max_opento 75-100netstat -an | grep :5432 | wc -lto track active connectionsAdditional 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 65535Summary by CodeRabbit
✏️ Tip: You can customize this high-level summary in your review settings.