atman atta Tue Aug 23 16:17:52 -0400 2011

Subject: Using activerecord with multiple DB shards/partitions

Hi Everyone,

I am looking to re-purpose my existing active record to connect to different DB shards. I was thinking that I could use set_connections where each key maps onto a seperate DB shard connection.

I am willing to accept the risk of poor performance right now by sequentially accessing. The important thing is to get this working and optimizing it later (async queries, etc). I will probably be using memcache to lookup shards, however I need a basic scan first (User Id will be a UUID so I can guarantee it is unique)

I was wondering how I could do something like:

$cfg = ActiveRecord\Config::instance();
$cfg->set_model_directory(BASEPATH . '/Persistence/models');
$cfg->set_connections(
array('user_shard_1' => 'mysql://user:/user_schema_!',
'user_shard_2' => 'mysql://user:/user_schema_2' )
);

Then be able to loop over all connections and fetching results one at a time:

// Use the PHP ActiveRecord models to access data, however execute it against each connection
// sequentially.
foreach (User::connections() as $conn) {
$conn->find_by_user_id($id);
}

Thank you,


Clay vanSchalkwijk Wed Sep 14 00:10:27 -0400 2011

I looked over the code and it would be pretty substantial to support sharding. Have you looked into a proxy to handle the shard aspects of your database like http://spockproxy.sourceforge.net/?

Daniel Lowrey Wed Sep 21 14:28:54 -0400 2011

atman,

I posted something along these lines a few months back -- it may shed some light on what you're looking to accomplish:

http://www.phpactiverecord.org/boards/4/topics/765-optionally-selecting-connection-on-a-per-query-basis

(1-2/2)