Multiple database connection

Are the databases on the same server?

I have a application where some of the model objects are from one database and others are from a different database. I override the table_name function to specify the database. Won't work if they are different servers but will work for different databases in the same server.

class xx < ActiveRecord.base

def self.table_name
  "otherdatabase.table"
end

It also looks like database pooling may be on the way for an upcomming version of rails.

What's New In Edge Rails

I'm not a Ruby programmer, but generally speaking a connection pool is a good idea. You can make that connection pool a singleton and hand out/take back connections. The pool can check after taking back a connection whether everything is still in order.

You should have a distinct life cycle and ownership for each connection so two threads don't use a connection at the same time.

Be careful with temporary tables because they belong to one database session. We've had a bug where in high load situations we encountered weird errors. Finally we remembered that we were using temporary tables in our application. In the high load situation database connections broke down and were automatically regenerated by the connection object without the application code knowing it (we had implemented the reconnect, but forgot that it was a problem in such a situation). The temporary table we wanted to read had disappeared.

First regarding the comment left on your initial post : this has nothing to do with ruby. it has everything to do with the design of rails.

with that said, you could probably hijack the ActiveRecord::Base behaviour to keep a hash of connections indexed by a key (in your case the key would be the user name) then intercept establish_connection to check in the pool if the connection is open already

that would require that you reopen ActiveRecord base,which makes your change dependant on AR internals.

I'm not going to cover your question on pooling. However, i'm sure that pooling will help to improve things a lot. I also have a installation where each client has it's own database and codebase is shared among them.

Method to parse url is implemented outside Rails in Apache Rewrite configuration because multiple hosts can be mapped to single customer. Also i use customer "key" to access cached files on webserver disk. Rewrite configuration looks something like this:

RewriteMap accounts prg:domain_mapper.rb
RewriteMap lowercase int:tolower

RewriteCond %{HTTP_HOST} ^(.*)$
RewriteCond ${accounts:${lowercase:%1}} ^(.+)$
RewriteRule . - [E=ACCOUNT:%1]
RequestHeader set Customer-Key %{ACCOUNT}e

And customer key will map directly to database name which will be connected in before_filter method.

So far, switching a database connection on Rails side is not an performance issue. Problems are in MySQL configuration and we're facing problems where there are too many databases and too many requests cause too many open databases in MySQL.

I'm sure you've been considering migrating issues. It wasn't a problem in the beginning with < 2000 database schemas. Now there's > 15K customer databases (and growing) so we're merging them back into small number of sharded databases.