Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Tip: How to make Rails properly handle database failovers on AWS RDS #12

Closed
crohr opened this issue Sep 8, 2016 · 5 comments
Closed

Tip: How to make Rails properly handle database failovers on AWS RDS #12

crohr opened this issue Sep 8, 2016 · 5 comments

Comments

@crohr
Copy link
Owner

@crohr crohr commented Sep 8, 2016

Problem: a client had multiple rails apps pointing to a few RDS databases, in multi-AZ mode. However, after initiating a database failover, the apps would appear to be stuck trying to reach the old database master, instead of gracefully killing the stale connection, and reconnecting.

After a few tries and a lot of digging, here is the configuration for Rails' DATABASE_URL that appears to work for our multi-AZ PostgreSQL RDS instances:

DATABASE_URL=postgres://dbuser:dbpassword@rds-dbhost:5432/dbname?pool=5&encoding=unicode&reconnect=true&connect_timeout=2&keepalives_idle=30&keepalives_interval=10&keepalives_count=2&checkout_timeout=5&reaping_frequency=10

Or in a config/database.yml:

host: rds-dbhost
port: 5432
username: dbuser
password: dbpassword
database: dbname
pool: 5
encoding: unicode
reconnect: true
connect_timeout: 2
keepalives_idle: 30
keepalives_interval: 10
keepalives_count: 2
checkout_timeout: 5
reaping_frequency: 10

Note:

  • If you're not using DATABASE_URL, you can also use the same settings in your config/database.yml.
  • These settings are quite aggressive and should probably be tuned if your latency to reach your database is somewhat high.
  • Some are probably redundant/useless, or plain wrong, so let me know in the comments ;) But at least the failover is now happening in less than a minute.

Relevant documentation:

@crohr crohr added the published label Sep 8, 2016
@crohr crohr closed this Sep 8, 2016
@davidcpell
Copy link

@davidcpell davidcpell commented Sep 19, 2017

Came across this because I'm trying to figure out how to get ActiveRecord to reconnect after an ELB connection timeout: https://aws.amazon.com/blogs/aws/elb-idle-timeout-control/

This config doesn't seem to be helping for that use case. Have you ever run into that by any chance? I'm not sure how it differs from the failover case.

@SimonHausdorf
Copy link

@SimonHausdorf SimonHausdorf commented Apr 20, 2018

We tried this config but still getting a downtime on failover. Should this be without downtime?

@a2f0
Copy link

@a2f0 a2f0 commented Mar 17, 2019

Sorry to necrobump this issue but does reconnect actually do anything here? From what I can tell this is a MySQL specific configuration option.

@sasharevzin
Copy link

@sasharevzin sasharevzin commented Mar 15, 2020

Sorry to necrobump this issue but does reconnect actually do anything here? From what I can tell this is a MySQL specific configuration option.

@deepeeess I'm curious as well. Let me know if you found it out...

@a2f0
Copy link

@a2f0 a2f0 commented Mar 16, 2020

@sasharevzin I''m not sure if it was some other issue but this is what I ultimately ended up going with, which seems to have largely fixed whatever issue I was having. We use the DATABASE_URL method. Also, you might want to upgrade your postgres gem if it's old, IIRC I did that as well.

postgres://<db_username>:<db_password>@<fqdn>:5432/<db_name>?prepared_statements=false&reconnect=true&connect_timeout=2&keepalives_idle=2&keepalives_interval=1&keepalives_count=5&checkout_timeout=2&reaping_frequency=10

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
5 participants