← Back to Upcase

Heroku, Redis, Resque, and MySql max_connection


(Alex Bush) #1

Hello fellow rubyists,

continuing working on my current Rails project(updating a legacy Rails/Wordpress mix to a decent iOS/Android JSON API server) I stumbled upon yet another pitfall I couldn’t predict… max_connection mysql error in resque jobs handled by a single worker.

The error looks like this:

Worker
6813a6ae-acb1-4299-9784-49048efe6001:2 on PHOTOS_QUEUE at about 10 hours ago Retry or Remove
Class
PhotoDownloader
Arguments
10461
2311
"https://fbcdn-sphotos-h-a.akamaihd.net/hphotos-ak-ash4/296216_195621207167014_6213639_n.jpg"
Exception
Mysql2::Error
Error
User 'b10656ee5a6bc3' has exceeded the 'max_user_connections' resource (current value: 30)

and it occurs in production when I queue about a 700 to 1000 jobs to the worker.

We have a mini CMS for admins of this API and the feature I was trying to implement is a csv file upload. Admins can upload a csv file with multiple entries instead of entering things manually through the form.
Upon submission of the csv file I parse it and create ActiveRecord objects and also I detach external image downloads(from an external url) to the resque worker(about 2 image per entry from the csv file) which ends up in 700 to 1200 image download jobs queued per csv file.
For image storage I use AWS S3 using Carrierwave.

But unfortunately as I mentioned above I had a max_connection mysql error which made me think that when the worker takes on a job to download an image it open a db connection but doesn’t close it when the job is done… am I correct? should I close it manually somehow? I did try to do it with no success.

I solved the problem by rethinking how I process this downloads. Now instead of detaching a job per image download I collect all the new entries from the csv parsing and then create a single job to download all the images… but it doesn’t seem right because something might fail and it’s hard to retry partially executed job… But it solved the max_connection issue because now I have only one open connection for single monstrous download job.

Am I doing it the right way? Was my initial approach better? If so how could I solve max_connection issue?

P.S. I can’t list all the links/documentation I read while I was solving it (just too many urls and open tabs :D) but I can assure you I went through everything I could find, especially on heroku but it seems nobody experienced quite the same problem.

Thanks.


(Ben Orenstein) #2

I’m afraid I don’t have a great answer for you, but it at least looks like you’re not alone in having this issue: http://stackoverflow.com/questions/16796705/mysql-connections-are-not-closing-in-rails-on-heroku

Given that others are seeing it, it’s possible it’s a bug. Hard to say for sure though.


(Alex Bush) #3

Thanks @benorenstein, at least now I know I haven’t done something stupid :slight_smile:

It’s a shame though, looks like the problem is this particular setup with unicorn and cleardb…