Multiple DB connections in Rails/ActiveRecord

You might often want to connect to different databases using ActiveRecord. Here’s how you do it:


#DB definitions:

class DatabaseCurrent < ActiveRecord::Base
  self.abstract_class = true
  establish_connection settings['database']
end

class DatabaseOld < ActiveRecord::Base
  self.abstract_class = true
  establish_connection settings['database2']
end

#Model definitions (current):

class Video < DatabaseCurrent
  belongs_to :user

  set_table_name :videos
end

class Photo < DatabaseCurrent
  belongs_to :user

  set_table_name :photos
end

class User < DatabaseCurrent
  has_many :videos
  has_many :photos

  set_table_name :user
end

class Tag < DatabaseCurrent
  set_table_name :tag
end

#Model definitions (old):

class FileDB < DatabaseOld
  set_table_name :file
end

Pretty easy. The only thing to note is that you should set_table_name, otherwise, AR chokes up. Also, often you want to directly play directly with SQL. Unfortunately, ActiveRecord::Base.execute doesn’t work any longer (coz it doesn’t have a connection), but you can do it this way:


DatabaseCurrent.connection.execute("SQL")

That’s the tutorial for the day!

RubyNetBeans

I highly recommend people to switch to RubyNetBeans (the Netbeans support in Ruby) for people who prefer an IDE-heavy approach to development. I still use Textmate, but the integrated webserver support, shell script automation, and esp. the new debugger (which is still slow), is really useful.

JRuby/Glassfish Investigation

I’ve been keeping an eye on the JRuby scene for a while now and I recently went about benchmarking again. JRuby is interesting because it has the potential to bring robust Java deployment and tools to the Ruby world.

I made a simple widgets application as outlined here and installed JRuby/Glassfish as outlined here (both great articles at AD TechFL) and then proceeded to benchmark:

Glassfish/Jruby on httperf:

Elena:~/Applications/jruby/lib vishnu$ time httperf --client=0/1 --server=localhost --port=8080 --uri=/jruby-demo/widgets/list --send-buffer=4096 --recv-buffer=16384 --num-conns=480 --rate=60
httperf --client=0/1 --server=localhost --port=8080 --uri=/jruby-demo/widgets/list --rate=60 --send-buffer=4096 --recv-buffer=16384 --num-conns=480 --num-calls=1
httperf: warning: open file limit > FD_SETSIZE; limiting max. # of open files to FD_SETSIZE
Maximum connect burst length: 2

Total: connections 480 requests 480 replies 480 test-duration 7.999 s

Connection rate: 60.0 conn/s (16.7 ms/conn, <=7 concurrent connections)
Connection time [ms]: min 1.4 avg 24.6 max 251.3 median 15.5 stddev 35.7
Connection time [ms]: connect 0.1
Connection length [replies/conn]: 1.000

Request rate: 60.0 req/s (16.7 ms/req)
Request size [B]: 83.0

Reply rate [replies/s]: min 60.0 avg 60.0 max 60.0 stddev 0.0 (1 samples)
Reply time [ms]: response 24.5 transfer 0.0
Reply size [B]: header 360.0 content 1077.0 footer 0.0 (total 1437.0)
Reply status: 1xx=0 2xx=438 3xx=0 4xx=0 5xx=42

CPU time [s]: user 1.42 system 5.79 (user 17.7% system 72.4% total 90.1%)
Net I/O: 89.1 KB/s (0.7*10^6 bps)

Errors: total 0 client-timo 0 socket-timo 0 connrefused 0 connreset 0
Errors: fd-unavail 0 addrunavail 0 ftab-full 0 other 0

real    0m8.008s
user    0m1.418s
sys     0m5.799s

Glassfish/Jruby on ab:

Elena:~/Applications/jruby/lib vishnu$ ab -n400 http://localhost:8080/jruby-demo/widgets/show/1
This is ApacheBench, Version 1.3d  apache-1.3
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/

Benchmarking localhost (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Finished 400 requests
Server Software:        Sun
Server Hostname:        localhost
Server Port:            8080

Document Path:          /jruby-demo/widgets/show/1
Document Length:        611 bytes

Concurrency Level:      1
Time taken for tests:   8.551 seconds
Complete requests:      400
Failed requests:        0
Broken pipe errors:     0
Total transferred:      397600 bytes
HTML transferred:       244400 bytes
Requests per second:    46.78 [#/sec] (mean)
Time per request:       21.38 [ms] (mean)
Time per request:       21.38 [ms] (mean, across all concurrent requests)
Transfer rate:          46.50 [Kbytes/sec] received

Connnection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0     0    0.0      0     0
Processing:    13    21   52.6     16  1019
Waiting:       13    21   52.6     16  1019
Total:         13    21   52.6     16  1019

Percentage of the requests served within a certain time (ms)
  50%     16
  66%     17
  75%     17
  80%     17
  90%     18
  95%     20
  98%     37
  99%    116
 100%   1019 (last request)

Let’s compare. Here’s how a single Mongrel/C does with httperf:

Elena:~/Applications/jruby/lib vishnu$ time httperf --client=0/1 --server=localhost --port=3000 --uri=/widgets/list --send-buffer=4096 --recv-buffer=16384 --num-conns=480 --rate=30
httperf --client=0/1 --server=localhost --port=3000 --uri=/widgets/list --rate=30 --send-buffer=4096 --recv-buffer=16384 --num-conns=480 --num-calls=1
httperf: warning: open file limit > FD_SETSIZE; limiting max. # of open files to FD_SETSIZE
Maximum connect burst length: 3

Total: connections 480 requests 480 replies 480 test-duration 16.009 s

Connection rate: 30.0 conn/s (33.4 ms/conn, <=18 concurrent connections)
Connection time [ms]: min 9.0 avg 56.4 max 607.6 median 9.5 stddev 99.4
Connection time [ms]: connect 0.2
Connection length [replies/conn]: 1.000

Request rate: 30.0 req/s (33.4 ms/req)
Request size [B]: 72.0

Reply rate [replies/s]: min 29.4 avg 29.8 max 30.0 stddev 0.3 (3 samples)
Reply time [ms]: response 55.7 transfer 0.5
Reply size [B]: header 278.0 content 1011.0 footer 0.0 (total 1289.0)
Reply status: 1xx=0 2xx=480 3xx=0 4xx=0 5xx=0

CPU time [s]: user 3.91 system 11.35 (user 24.4% system 70.9% total 95.3%)
Net I/O: 39.9 KB/s (0.3*10^6 bps)

Errors: total 0 client-timo 0 socket-timo 0 connrefused 0 connreset 0
Errors: fd-unavail 0 addrunavail 0 ftab-full 0 other 0

real    0m16.017s
user    0m3.912s
sys     0m11.354s

And Mongrel/C with ab:

Elena:~/Applications/jruby/lib vishnu$ ab -n400 http://localhost:3000/widgets/list
This is ApacheBench, Version 1.3d  apache-1.3
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-2002 The Apache Software Foundation, http://www.apache.org/

Benchmarking localhost (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Finished 400 requests
Server Software:        Mongrel
Server Hostname:        localhost
Server Port:            3000

Document Path:          /widgets/list
Document Length:        1011 bytes

Concurrency Level:      1
Time taken for tests:   7.974 seconds
Complete requests:      400
Failed requests:        0
Broken pipe errors:     0
Total transferred:      515600 bytes
HTML transferred:       404400 bytes
Requests per second:    50.16 [#/sec] (mean)
Time per request:       19.93 [ms] (mean)
Time per request:       19.93 [ms] (mean, across all concurrent requests)
Transfer rate:          64.66 [Kbytes/sec] received

Connnection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0     0    0.0      0     0
Processing:     9    19   63.4     11  1167
Waiting:        9    19   63.4     11  1167
Total:          9    19   63.4     11  1167

Percentage of the requests served within a certain time (ms)
  50%     11
  66%     12
  75%     12
  80%     12
  90%     13
  95%     15
  98%    113
  99%    149
 100%   1167 (last request)

Observations

  • Advantage: For the same number of connections, Glassfish/Jruby versus a single instance Mongrel/C has twice the reply rate in half the benchmark time.
  • Disadvantage: Request latency is more for Glassfish/Jruby. (around 5ms av. more)
  • Disadvantage: JRuby Ruby/Rails support is not perfect but very good.
  • Advantage: Much simpler to set up. Copy the war file to the server and deployment is done, autodeployment is also possible w/o restarting Glassfish.
  • Advantage: Much simpler to install and get going on the server, just requires a JDK, glassfish installed anywhere, etc.
  • Advantage: Runs on Java, so Java deployment experience will do.
  • Disadvantage: Does not support C library extensions for Ruby, question: how many of them are there & useful (memcached?) ?
  • Advantage: Supports Java libraries kind of easily, again question: how many of them are there, useful and relevant?
  • Question: This benchmark is versus a single mongrel, how does cluster vs. cluster compare?
  • Question: Is glassfish clustering easy? Observation: mongrel clustering is cumbersome at present. Maybe swiftiply will help there with dynamic addition of mongrels?