18 votes
Accepted

Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant?

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer....
user avatar
  • 41.4k
12 votes
Accepted

mongodb shard chunk migration 500GB takes 13 days - Is this slow or normal?

Update: April 2018 This answer was correct at the time of the question, but things have moved on since then. Since version 3.4 parallelism has been introduced, and the ticket I referenced originally ...
user avatar
  • 9,075
12 votes
Accepted

MongoDB: co-locate the mongos process on application servers

Since there is already and answer submitted, and a useful and valid one at that, I do not want to distract from its own usefulness but there are indeed points to raise that go way beyond just a short ...
user avatar
  • 365
11 votes

Effectively handle 10-100 millions row table of unrelated data

Using a simple BRIN index TIAS. Here is a table exactly as you described, worst case 100 million rows with 1 million rows per SEGMENT_ID explain analyze CREATE TABLE foo AS SELECT (x::int%100)::...
user avatar
  • 56.2k
8 votes
Accepted

Mongo large chunks will not split

Lot to go through here, so I'll take it piece by piece, first off splitting: I thought this meant that when a chunk hits 64mb, it splits into two equal chunks both of size 32mb. That's what is ...
user avatar
  • 9,075
6 votes
Accepted

Are multiple databases sharding for read on docker as a good idea?

The gotcha with sharding is that the application has to know which shard to query. Generally, this is done by sharding on something like client. I'll adapt one of my old blog posts to use as my answer....
user avatar
  • 41.4k
5 votes
Accepted

How to activate shard balancer in sharded MongoDB

Basically you have a few misunderstandings here, the first being that the balancer is a load balancer. It is not - it simply looks to address imbalances in chunk counts on your shards. That can have ...
user avatar
  • 9,075
5 votes

MongoDB: co-locate the mongos process on application servers

Since best practice also suggest that "The appropriate number of mongos processes will depend on the nature of the application and deployment" I started to wonder whether our usage of mongos ...
user avatar
5 votes

Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant?

One further consideration I haven't yet seen in other answers. Having a design that allows for many tenants in a single database will give flexibility later. Should load/ scale out/ security/ geo ...
user avatar
5 votes
Accepted

What is the new JDBC 4.3 Connection Request feature with `beginRequest` & `endRequest` methods?

These new JDBC4.3 APIs beginRequest and endRequest have to do with connection management. It's really a hint provided by the connection pool to the driver that a connection has been checked out of the ...
user avatar
4 votes
Accepted

MongoDB presplitting chunks for compound shard key

Just a slight issue with how you are passing the $minKey values in, try this instead: db.adminCommand( { split : "mydb.mycollection" , middle : { "region" : "region1", "foo" : MinKey , "bar" : MinKey ...
user avatar
  • 9,075
4 votes
Accepted

Heavy load of one of shards in MongoDB

The problem explained As per your comment, your shard key is the _id field of the document. This field is monotonically increasing, basically like an incremented integer. Put simply, sharding works ...
user avatar
4 votes

Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant?

One practice that makes multi-tenant models much easier, even though it breaks normalization*, is to include a column on every table for the tenant. You could call it TenantID. That way every query ...
user avatar
4 votes
Accepted

MongoDB Cluster - Can't access member of shard with credentials

When you are using mongoS authentication is done against config servers admin database. When you connect directly to replica set, you are authenticating against replica set's admin database, where you ...
user avatar
  • 5,198
3 votes

How to Determine Chunk Distribution (data and number of docs) in a Sharded MongoDB Cluster?

db.collection.getShardDistribution() That is a pretty good command I use often for stuff like this. It will show you total chunks, average chunk size, document counts, all on a per shard basis. It ...
user avatar
  • 303
3 votes
Accepted

Mongodb sharded cluster backup user issue

Once you add a user to the individual shards, which you indicate you have done for MMS, you must then have valid credentials to connect for any purpose, including mongodump. Up until you added that ...
user avatar
  • 9,075
3 votes

Scaling out SQL Server and syncing data across multiple machines

Creating a Scale Out database at Internet scale is pretty huge step. You will face a lot of issues that are not critical on a single big database. From your notes I see that you understand some of the ...
user avatar
  • 13.8k
3 votes

Sharding Postgres Database

To the extent your bottleneck is in streaming realtime reads and writes, you may want to look into the open source PostgreSQL extension: pg_shard It shards and replicates your PostgreSQL tables for ...
user avatar
3 votes
Accepted

MongoDB : How to estimate max number of connections supported?

Each connection eats up about 1MB of RAM on the server side. That includes the connections to the config servers and the replica set members. Please keep in mind that those communicate with each other....
user avatar
3 votes

Sizing a MongoDB shard?

It's not an easy task to plan the dimensions of shards, and there is no fool or errorproof way of doing it. What I tend to do is to create a certain fraction of the expected data per shard as dummy ...
user avatar
3 votes
Accepted

Do SQL Sharding or Cluster help speed up slow queries, or just concurency?

Sharding requires a bunch of changes to the infrastructure, connections, etc. The product "Spider" makes most of that transparent. Sharding may improve scaling and concurrency for simple queries. ...
user avatar
  • 68.7k
3 votes
Accepted

mongodb could not find host matching read preference { mode: \"primary\" } for set?

I faced the same issue then looked into Mongos log file and it was saying can't authenticate to hostname as internal user, error: Authentication failed. Please see here: internal user authentication ...
user avatar
3 votes

mongodb could not find host matching read preference { mode: \"primary\" } for set?

I had this same issue, and found out that I simply had the name of the replica set wrong in the addShard command.
user avatar
3 votes

Horizontally scaling SQL Server, distributing the database with sharding

Is there material on how? Yes. You can read about it at https://msdn.microsoft.com/en-us/library/dn589797.aspx Is the process as tough as it seems? Sort of. It is time consuming in that it ...
user avatar
3 votes
Accepted

MongoDB Sharded Cluster: Balancing chunks not working, stuck at "step 2"

The definition of the steps referenced in a chunk migration are a little fluid, and can depend on the version you are running. However, based on what you have provided I suspect that the primary on ...
user avatar
  • 9,075
3 votes
Accepted

Multiple MongoDB databases (1 per client) and sharding - are they compatible?

Yes, they are compatible: if you have multiple shards, then different databases will be automatically allocated to different shards in a balanced way. The sharding system does distribute large ...
user avatar
3 votes
Accepted

mongo shard + maxSize

Most likely you are using a different shell version than the db version. That is, the output of db.version() (the server version) and version() (the shell version) should be the same. For example: ...
user avatar
  • 538
3 votes

how to partition the mysql table

I think you should analyze the scenario from a different perspective, because it looks quite likely that (a) continuing to work with the current structure of the table in question might end up being (...
user avatar
  • 8,220
3 votes

Effectively handle 10-100 millions row table of unrelated data

What are the common approaches to boost read/write performance of table with up to 100 millions of rows? Not running it on a phone? I mean, seriously 100s of millions of rows is not particularly ...
user avatar
  • 4,594
3 votes
Accepted

mongodb - mongos - query a mix of sharded and unsharded collections / databases?

A MongoDB sharded cluster deployment can contain collections that are either unsharded (the default when created) or sharded (based on your chosen shard key). Sharding a collection is (as at MongoDB 3....
user avatar
  • 9,805

Only top scored, non community-wiki answers of a minimum length are eligible