Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now
16

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. When you’re building an application for lots of clients, there’s two common ways to design the database(s): Option A: Put all clients in the same database ...


12

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 comment. So consider this "augmentation", which is hopefully valid but primarily in addition to what has already been said. The truth is to really consider "...


11

There is currently no built-in way to do this, so a small function is needed. For the purposes of this answer I have created a 2 shard cluster with ~1 million documents as per these instructions. Next up I used this function to examine those documents: AllChunkInfo = function(ns, est){ var chunks = db.getSiblingDB("config").chunks.find({"ns" : ns})....


10

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 has been closed. For more information I cover some of the details in this more recent answer. I will leave the rest of the answer as-is because it remains a ...


8

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 demonstrated here. Is that not correct? That's not quite how it works. If you have a 64MB chunk and you manually run a splitFind command, you will get (by ...


7

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)::int AS SEGMENT_ID FROM generate_series(1,100e6) AS gs(x); QUERY PLAN ...


6

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. When you’re building an application for lots of clients, there’s two common ways to design the database(s): Option A: Put all clients in the same database ...


5

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 actually appropriate I think this is a question that ultimately only you can answer, as the documentation refers to. One of the recommended strategies is to have ...


5

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 location demands later suggest a tenant should have a separate database it can be created by restoring the currect DB on the new instance. The other tenants' data ...


4

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 this way: documents are stored in chunks. Those chunks are spread over the cluster based on ranges of the shard key. Let's look at a simple example: s1: chunks ...


4

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 the side effect of balancing your traffic out as it moves chunks around, but strictly speaking it is not a load balancer. It also does not run continuously, ...


4

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 } } ); db.adminCommand( { split : "mydb.mycollection" , middle : { "region" : "region2", "foo" : MinKey , "bar" : MinKey } } ); This got me the following ...


4

1 - You can have more than 1 mongos instance and connect to whichever you want (the client driver should have that option). Nevertheless, a mongos is just a router, meaning it will only route the requests to the correct shard(s). 2 - Yes, a config server can be in the same machine as a primary/secondary, just don't put config instances together (you are ...


4

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 run against the database can filter on TenantID on every table, and you can use database partitioning to isolate the data for each tenant and speed up queries by ...


4

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 don't have that root user set. What can you do? Start replica set's (one by one) without --auth parameter (or equivalent config parameter) to maintenance mode,...


3

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 doesn't give you the data for each chunk like the answer above, but this is pretty quick and gives a good overview of what you're looking for.


3

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 horizontal scale and high availability. It also distributes your SQL statements, without requiring any changes to your application. https://github.com/citusdata/...


3

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 basic issues you face. Since Microsoft has papers on using SQL Server for scale out, I suggest that you study those first. Your scale out strategy will need ...


3

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 for MMS, the shards were running with authentication enabled but with no users populated (this only happens if all your users are in the admin database ans ...


3

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. But for table scans and JOINs, it is likely to make performance worse. Partitioning is excellent for "deleting old data". See my blog. Otherwise, the DELETE ...


3

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 rs01 and the number of chunks you are trying to move is the source of the issue here. Last time I looked at this in detail (around version 2.6), step 2 was ...


3

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: mongos> db.version() 3.4.1 mongos> version() 2.6.12 mongos> config = db.getSiblingDB('config') config mongos> config.shards.updateOne({_id:'shard01'},...


3

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 (b) far less feasible than (c) redesigning it at all the corresponding tiers, i.e.: the conceptual (modeling and defining [i] the types of things that the table ...


3

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 pool (beginRequest) or checked back into the pool (endRequest). Without these APIs the driver has really no clue about request boundaries. The driver can then ...


3

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.4) a two-step process: enable sharding on a database using sh.enableSharding() and then shard specific collections using sh.shardCollection(). Sharded and ...


3

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 capacity approximates the sum of disk and CPU capacities of all nodes (more or less). The MySQL cluster solutions you're referring to implement the shared-...


2

You need to enable sharding on the database test first: sh.enableSharding("test"); Then you need to shard the collection and pick a shard key. Based on what you were trying to split on, that would be: sh.shardCollection("test.zp", {"city" : 1}); A couple of notes: "city" is probably a poor shard key unless you are expecting an even distribution of ...


2

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 splitting and more. The query routing feature is used to achieve sharding. For a tutorial, try e.g. this one. An alternative solution could be ProxySQL, which is ...


2

If you comment out keyFile (assuming you have not specified auth explicitly) you will turn off authentication. Basically keyFile implies auth but not the other way around. When you comment out shardsvr, the port will change from 27018 to the default port number - 27017. That is actually a good thing. When you take a node out of a replica set or a sharded ...


2

So, I found a workaround... First I made a proxy function on which the first proxy function will run : CREATE OR REPLACE FUNCTION p_count_toto( in_id_toto INTEGER ) RETURNS INTEGER AS $func$ BEGIN RETURN (SELECT sum(tmp_nb_toto) out_nb_toto FROM count_comm(in_id_alias)); END; $func$ LANGUAGE plpgsql; And I modified my first PL/Proxy function ...


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