19
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....
- 43.1k
13
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)::...
- 64.1k
7
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 ...
- 25k
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....
- 43.1k
5
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 ...
- 535
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 ...
- 166
4
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 ...
- 4,638
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 ...
- 5,723
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 (...
- 8,540
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:
...
- 548
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 ...
- 399
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 ...
- 9,245
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 ...
- 31
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....
- 10.3k
3
votes
Accepted
MongoDB --- Failed global initialization: Failed to open "/var/log/mongodb/mongod-config.log"
So, I have figured out my own question.
Firstly, the issue was that since I was using a different port 27019 for the config servers, I needed to open that port from the firewall.
Second, I changed ...
- 131
3
votes
MongoDB --- Failed global initialization: Failed to open "/var/log/mongodb/mongod-config.log"
I also faced the same problem while trying to start mongodb service in my Debian9 64bit server. I could see the below information while checking status.
:~# systemctl status mongod.service
● ...
- 150
3
votes
Accepted
What is the meaning of "Monotonically Changing Shard Keys"?
Monotonic here is used in the strict mathematical sense. A monotonically changing key is such that every key value is not less than (monotonically increasing) or not greater than (monotonically ...
- 26.6k
3
votes
Vertical / Horizontal Scaling Disk Size MySQL or MariaDB
What you're looking for is typically called shared-data architecture, where multiple database nodes access the same set of database files (a database) distributed across the nodes, and the database ...
- 26.6k
3
votes
How to implement High Availability for all nodes in a sharded database?
There's no difference in HA solutions when you break up a large database into multiple smaller ones. Either Availability Groups or Failover Cluster Instances will work.
AGs have the benefit that you ...
- 47.3k
3
votes
How many shards is Facebook's user table partitioned across?
across hundreds of applications in production
That's the key in the phrasing. Meta has hundreds of applications, not only Facebook. The users table in Facebook is not likely sharded that much (if at ...
- 38.6k
2
votes
mongodb could not find host matching read preference { mode: \"primary\" } for set?
I had the same issue, but my problem was that before calling sh.addShard() I needed to login to the new shard server (not mongos), and call the following command:
rs.initiate({ _id : "...
- 175
2
votes
Accepted
mongo shard balancer not working
First, restart the mongos process on mongosHost3, then recheck the locks as you did in the question.
If the same lock (make sure it is the same and not a new lock by the same process - check the ts ...
- 9,245
2
votes
Accepted
Not able to access a database on shard mongodb cluster
Try db.repairDatabase() which fixes inconsistency across database.
Check mongod and mongos logs for results.
- 56
2
votes
Accepted
MongoDB - Clarification on choosing randomly distributed shard key
While it's hard to say for certain without full context, I'm assuming its referring to the need to keep the working set in memory.
A randomly distributed shard key would distribute the workload ...
- 353
2
votes
Mariadb sharding?
I think the answer to both of your question could be MaxScale. MariaDB MaxScale is a database proxy which supports connection pooling, load balancing, automatic failover, query routing, read-write ...
- 6,451
2
votes
MongoDB: enable sharding automatically
No it is not possible to automatically shard the collection. This is what you have to do per collection.
Ref:
https://docs.mongodb.com/manual/reference/command/shardCollection/
https://docs.mongodb....
- 13.6k
2
votes
One of three Mongo Shard node is running with very high CPU LOAD/USAGE
Because in at normal situation all traffic (read, write) goes to the primary node, it is the busiest node at replica set. Secondaries just replicate changes (update, insert, delete) and not responding ...
- 5,723
2
votes
Accepted
ProxySQL equivalent of MaxScale schemarouter
You can find the answer here: Can proxysql have multiple listener? (Google Groups).
In short: ProxySQL's query rules support routing by schemaname.
For simplicity, let's assume you have 3 distinct ...
- 86
2
votes
Accepted
mongodb - different keyfiles for different replica sets?
The keyfile is used to secure intra-cluster communication, so all components of a sharded cluster (mongod and mongos) must use the same keyfile.
The documentation you referenced is for a single ...
- 10.3k
2
votes
Accepted
What is the behavior of null/empty value in compound shard key?
In MongoDB 'null' is just another value so two documents that have the same values for field1 (some non-null) and same value for field2 (null) have the same value for the shard key and will always be ...
- 650
Only top scored, non community-wiki answers of a minimum length are eligible