Rails - May 2005 - A cautionary tale on multiple databases under a single Rails app

Here is a cautionary tale to help others avoid my folly!

I kept running into bizarre problems with my rails app. I run the latest
Rails with FastCGI, Oracle 10g and Apache 1.x. After moderate load the
server would throw up application errors. Looking in the logs I noticed
that my underlying Oracle DB was complaining that it had opened too many
processes and won''t open any more. Hmm...

So I looked at the process list and WOW, there''s like 100 oracle
processes representing connections to the DB with a parent ID of 1. Odd.
So I happily ''kill -9'' them, but the problem keeps recurring.
At this
point I begin to suspect FastCGI, so I cap the max processes at 5. Well
that''s great and all, but now I''m seeing Application Errors
because we
hit the limit of 5 so often.

I go back to the default FastCGI setup and begin killing some of those
processes. Magically the oracle connection processes disappear. Aha, so
FastCGI _is_ the culprit. I then spend the next 2 days tweaking FastCGI
to no avail.

Just as I begin composing an email to this list, I start to describe my
app and where it is non-standard... I use the subdomain to dynamically
swap DBs underneath the user. So I thought about it a bit and realized,
well there''s really only two subdomains (2 DBs) right now, and I
don''t
see us growing past 5 or so. So decided to try and swap the DB via
subdomain by installing multiple instances of rails with Apache just
routing to these static instances which have their DB set up in the
database.yaml normally.

It worked! No more wild free-roaming Oracle processes everywhere!

So, the moral of the story? Beware opening connections to the database
under FastCGI! Since the whole point of FastCGI is to hold open
long-lived resources across requests (like say DB connections), the
older, mo longer used connections were never removed. So every FastCGI
process started a new Oracle DB connection process for every request!
Ick!
Also, a second moral might be: Stick to one DB per rails app instance.
If you want the same codebase to run over multiple DBs, then set up
multiple virtual hosts in your web server and create multiple instances
of the app with the correct DB hard-coded.

Thanks,
Chris




_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails