bradrobertson/apartment — pull request #29 by BDQ (2012-03-26 12:48:25+00:00)
Title: Add support for ActiveRecord's connection pool

This adds support for using the ActiveRecord connection pool when connecting to multiple databases for both mysql2 and postgres. This has no impact when using postgres schemas.

This is a sizable enough change so I want to outline some points about it.

1) Justification for using the connection pool:

1.a) Currently apartment creates a new connection for each request, totally by-passing Rails built-in connection pool. To highlight this, the output below is taken from a (freshly restarted) MySQL server, before and after 1,000 requests to a test Rails app using apartment:

#### Before (no connection pool)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> show processlist;
+------+------+-----------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------+--------+---------+------+-------+------------------+
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 1001 | root | localhost | app1 | Sleep | 62 | | NULL |
| 1002 | root | localhost | app2 | Sleep | 62 | | NULL |
| 1004 | root | localhost | app2 | Sleep | 62 | | NULL |
+------+------+-----------+--------+---------+------+-------+------------------+

As you can see the "Id" column shows that 1,000 db connections were created.

#### After (with connection pool)

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+-----------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-----------------------+---------+------+-------+------------------+
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 2 | root | localhost | multi_db_master | Sleep | 291 | | NULL |
| 3 | root | localhost | multi_db_master | Sleep | 290 | | NULL |
| 4 | root | localhost | multi_db_master | Sleep | 290 | | NULL |
| 5 | root | localhost | app1 | Sleep | 291 | | NULL |
| 6 | root | localhost | app1 | Sleep | 291 | | NULL |
| 7 | root | localhost | app1 | Sleep | 291 | | NULL |
| 8 | root | localhost | app2 | Sleep | 291 | | NULL |
| 9 | root | localhost | app2 | Sleep | 291 | | NULL |
| 10 | root | localhost | app2 | Sleep | 291 | | NULL |
+----+------+-----------+-----------------------+---------+------+-------+------------------+

And after this change you can see it's maintaining x connections for each database, with no churn on connection Id's.

1.b) It's also significantly faster, here's the before and after numbers:

#### Before (no connection pool)

Transactions:	1000 hits
Availability:	100.00 %
Elapsed time:	66.39 secs
Data transferred:	25.02 MB
Response time:	0.66 secs
Transaction rate:	15.06 trans/sec
Throughput:	0.38 MB/sec
Concurrency:	9.91
Successful transactions: 1000
Failed transactions:	0
Longest transaction:	1.13
Shortest transaction:	0.14

#### After (with connection pool)

Transactions:	1000 hits
Availability:	100.00 %
Elapsed time:	25.04 secs
Data transferred:	19.96 MB
Response time:	0.25 secs
Transaction rate:	39.94 trans/sec
Throughput:	0.80 MB/sec
Concurrency:	9.95
Successful transactions: 1000
Failed transactions:	0
Longest transaction:	0.38
Shortest transaction:	0.06

As you can see it dropped the time required for 1,000 requests from 10 concurrent clients (against 3 unicorn workers) from 66 seconds to 25 seconds.

Here's the siege command used to generate these tests:

siege -r 100 -c 10 -b -f localurls.txt

The urls file contains just two separate domains.

2) The implementation defines a dummy ActiveRecord model for each database and forces that to be used as the "key" for the connection pool within Rails.

This basically means that each separate database will now maintain it's own pool of connections to the database Note: This might require some custom database server configuration for large number of databases + connections, but we have a version of this running in production with 2,500 mysql databases.

3) I also needed the change the behavior of Apartment::Database.current_database (for abstract_adapter) as it was interrogating the AR:Base.connection for the db name, we now store this as an app configuration value to make it more independent of what ActiveRecord is doing.

4) Some existing specs had to be refactored to get them to run successfully (together) with the addition of my new specs.

Let me know if you have any questions / concerns.