We've been getting errors the last few days on all our Heroku instances when re-indexing:
ERROR: index 'team_core': sql_connect: Unknown MySQL server host '[hostname]' (2) (DSN=[url]).
But when I get the email, I can connect to that database; and our app itself hasn't reported any problems.
What should I look for?
Thanks!
#1 Posted by Pat Allan Support staff on 24 Sep, 2012 05:02 PM
Hi Bill
Surprising that this has cropped up... what MySQL provider are you using? Amazon's RDS or something else? Have there been any changes to your setup with MySQL recently?
Pat
#2 Posted by Bill Kaufman on 24 Sep, 2012 05:44 PM
It's RDS, same URL we've been using for months. The schema changed quite a bit back on Tuesday, but nothing since then, and the errors only started on Saturday.
FYI, the Heroku app is keas-frontend-production, if that helps.
#3 Posted by Pat Allan Support staff on 24 Sep, 2012 08:29 PM
Hi Bill
I'm at a bit of a loss why this is happening, especially given it's not consistent. I was able to connect to your database via the Sphinx server you're on without any problems, and ran the indexing process fine.
How often are the errors cropping up? I wonder if it's worth raising the issue with Amazon and see if they're aware of any inconsistencies with RDS access lately?
Pat
#4 Posted by Bill Kaufman on 24 Sep, 2012 08:52 PM
We're seeing an error every hour or two. We're also indexing every 10 minutes, so it seems pretty intermittent.
But on the other hand, the app itself doesn't get errors, so I don't think it's Amazon.
(FYI, we're also getting errors from our test server, about as frequently, and just as inexplicable. Not sure if that info is useful to you...)
#5 Posted by Pat Allan Support staff on 24 Sep, 2012 09:50 PM
I'm not entirely sure, but I think Rails holds onto database connections between requests, so the odds of the app instantiating a new connection at a point when connectivity's flaky are relatively small. That said, I'm wondering if there's a pattern between high numbers of delta updates and the database issue - perhaps RDS has a relatively small number of concurrent database connections allowed.
Will hunt around and see what I can find...
Pat
#6 Posted by Pat Allan Support staff on 24 Sep, 2012 11:47 PM
Hi Bill
When you have a moment - what size RDS instance do you have, and how many web and worker dynos are you generally running? Having found some details on the connection limits, I doubt this is the cause, but I just want to confirm where things stand.
Thanks
Pat
#7 Posted by Bill Kaufman on 25 Sep, 2012 12:05 AM
The database is db.m1.xlarge (20GiB). We've got a total of 13 dynos:
RDS reports around 29 simultaneous connections., so that lines up.
#8 Posted by Tommy on 30 Sep, 2012 04:32 PM
Hi Pat,
Thanks again for your help with this. Have you discovered anything further? The problem is still occurring during indexing.
Another strange thing we noticed: It will often successfully complete one index and fail on the next - for instance (1AM PDT today):
using config file '/mnt/local/flying-sphinx/9930a99086accb7bd/sphinx.conf'...
indexing index 'team_core'...
ERROR: index 'team_core': sql_connect: Unknown MySQL server host 'prod.ct1vjcyxovqq.us-east-1.rds.amazonaws.com' (2) (DSN=mysql://gynxpFLh7:***@prod.ct1vjcyxovqq.us-east-1.rds.amazonaws.com...).
... indexing index 'user_core'...
collected 75137 docs, 3.1 MB
sorted 16.6 Mhits, 100.0% done
total 75137 docs, 3051509 bytes
total 13.323 sec, 229031 bytes/sec, 5639.42 docs/sec
Followed by 5AM today:
indexing index 'team_core'...
collected 7364 docs, 1.0 MB
sorted 5.2 Mhits, 100.0% done
total 7364 docs, 987407 bytes
total 3.921 sec, 251768 bytes/sec, 1877.66 docs/sec
indexing index 'team_delta'...
ERROR: index 'team_delta': sql_connect: Unknown MySQL server host 'prod.ct1vjcyxovqq.us-east-1.rds.amazonaws.com' (2) (DSN=mysql://gynxpFLh7:***@prod.ct1vjcyxovqq.us-east-1.rds.amazonaws.com...).
total 0 docs, 0 bytes
total 20.013 sec, 0 bytes/sec, 0.00 docs/sec
#9 Posted by Pat Allan Support staff on 30 Sep, 2012 06:47 PM
Hi Tommy
Beyond being almost certain you're not coming close to connection limits on the RDS instance, I'm not sure at all what the cause is. I've just posted to the AWS RDS forum, am hoping someone there can shed some light.
https://forums.aws.amazon.com/thread.jspa?threadID=105701
Pat
#10 Posted by Thomas Fisher on 03 Oct, 2012 03:54 PM
After thinking about this for a bit, I'm wondering if these could be intermittent DNS resolution problems?
I'm not sure if hard coding the IP to our RDS would alleviate them. Hard coding might cause problems with failover though. What do you think?
#11 Posted by Pat Allan Support staff on 08 Oct, 2012 11:35 AM
The IP of the flying-sphinx server in the access permissions? Or of your RDS server in the Sphinx configuration? I guess the former is perhaps worth a shot - but if the server address changes (which is a rare event, but could happen), then permissions will still work with the values you've already got in place.
It's a little annoying the AWS team never followed up on that thread properly... maybe they'll get to it, but given it's been almost a week, I'm not holding out much hope. I'll try and revive the thread soon.