Shardset support

[hendrikswan]

Hi,

We've implemented a mosql setup at our company, and it would've worked great if it supported shardsets.

At the moment we're running a nightly job to migrate the whole DB over, but it sometimes fails. Our data is also growing at such a pace that we can't keep on doing this, so we'll have to stop using mosql until its got shardset support.

I know the readme explicitly states that it's not supported, but I'm creating the issue nonetheless so that we can keep an eye on it in case somebody picks it up.

[hendrikswan]

I'm keen to pick this issue up and do the work to support shardsets, but would need some guidance.

[nelhage]

The high-level picture is simple: You will need to discover each of the backing shards, and tail them individually, tracking state separately.

The thorny issue comes around chunk migrations by mongos. You'll want to double-check how those end up represented in the oplog, but I'm pretty sure you'll see an insert on the destination shard, followed later by a deletion on the source shard. A naive implementation that tails every oplog would thus follow the delete, even though the document still exists in the sharded cluster as a whole.

I have a few thoughts for how to fix this, but no solution I really like.

[hendrikswan]

Thanks for the great explanation! I'm still keen to hear your thoughts on how to fix it, even though you aren't happy with any of them. Maybe it kicks something lose in my mind..

[nelhage]

One option is, for each record in postgres, to keep track (in an additional column, managed entirely by MoSQL) of which shards it exists in. An insert appends to that set (if the record already exists), and a delete removes; If the set ever becomes empty, you can delete the record.

One option is to, on delete, consul the mongoc possibly by way of the mongos' APIs) to determine whether the shard that's being deleted from is the canonical location of the record, and ignore the delete if not. This has some problems where the mongoc represents the *current mapping, but you're tailing the oplog potentially some distance in the past, and it's possible there have been additional mongoc updates since then.

One option is to hope that the oplog contains enough information to detect this situation directly. I haven't looked into whether that's true. Even if it is you need to handle the case where mosql exits mid-migration, so that on resumption it won't see the past -- i.e. you'll need to reflect all state into the database table mosql uses to store its state.

I think that's basically all the ideas I've had. I'll update this issue if I recall / think of more.

[ebroder]

I think this may actually be comparatively straightforward.

There are oplog entries for chunk migrations (a bunch of insertions on the destination shard, and a bunch of deletions on the source shard). I haven't tested this directly, but I'm told that there's a "fromMigration" flag on all of those oplog entries. For cases where you're consolidating a sharded cluster into a single postgres server, it should be sufficient to just skip oplog entries with fromMigration.

I haven't actually tested this, though, so your mileage may vary, etc.

[hendrikswan]

Oooh, it would be cool if it's that simple!

On Thu, Jun 26, 2014 at 9:34 PM, Evan Broder notifications@github.com wrote:

I think this may actually be comparatively straightforward.

There are oplog entries for chunk migrations (a bunch of insertions on the destination shard, and a bunch of deletions on the source shard). I haven't tested this directly, but I'm told that there's a "fromMigration" flag on all of those oplog entries. For cases where you're consolidating a sharded cluster into a single postgres server, it should be sufficient to just skip oplog entries with fromMigration.

I haven't actually tested this, though, so your mileage may vary, etc.

— Reply to this email directly or view it on GitHub https://github.com/stripe/mosql/issues/43#issuecomment-47269533.

Hendrik Swanepoel email: hendrik.swanepoel@gmail.com mobile: 0829279906

[apocolipse]

+1 for this feature

[michael-erasmus]

I recently found these two posts which might be helpful:

https://www.mongodb.com/blog/post/tailing-mongodb-oplog-sharded-clusters

https://www.mongodb.com/blog/post/pitfalls-and-workarounds-for-tailing-the-oplog-on-a-mongodb-sharded-cluster

It has more details around the using the 'fromMigration' flag and details a general approach to follow. I'm also interested in this feature, but not for sending data directly to Postgres.

As I understand it, mosql uses mongoriver for tailling the oplog, so support for sharded collections could be built into mongoriver, no?

[ghost]

Any news on this? @hendrikswan

[hendrikswan]

Sorry @davidssonarnor, but unfortunately in the end we ran an ETL on a timer. All our mongo collections were time stamped, so we moved new and updated records over based on queries.

[ghost]

@hendrikswan ok I understand did you use mosql or just roll out your own?