Multiple database connection

Question

Hi, I'm trying to understand the best way to get the connection to my databases.

At the moment I've got a method wich parses the url (depending on the url called the application has to connect to a different database, like customer1.xxx.com will connect to the customer1 database) and calls

ActiveRecord::Base.establish_connection(conn_string)

where conn_string contains the name of the database.

This method (set_db) is called with a

before_filter :set_db

in my Application controller, so basicly for each request I get, the url is parsed and the application try to do an establish_connection.

I was wondering if I can have a connection pool somewhere....do you have any suggestion about that? Is it better to have a Singleton which keep all the connections made and gives back the right one?

Thanks! Roberto

Answer 1

Well it is not really covering my problem.

I have multiple databasse for the same basecode, this means that depending on the url my application has to work in a database (one for each customer I have, with exactly the same structure).

The application is working properly, but checking the "set_db" method, I saw that for each request the application parse the url and call an establish_connection. I think this is slowing down my app...am I right? What's exactly the behaviour of establish_connection if the connection is already established?

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.