0

I have a collection with 170 millions+ documents and it is only going to increase. The size of the collection is not that huge, currently around 70 GB.

The collection has two fields indexed on: {AgentId:1, PropertyId:1}. Generally one imports a huge file(millions of documents) belonging to a particular AgentId but the PropertyId(non numeric nullable) is mostly random unique value.

Currently I have two shards with shard key based on {_id: hashed}. But I am planning to change the shard key to compound Index {AgentId:1, PropertyId:1} because I think it will improve query performance( most of the queries are based on AgentId filter). Not sure whether one can have a nullable field in the shard key. If this is the case then app will make sure that the PropertyId is random no.

So looking to get a picture as to

  1. How the data will be distributed to shards during insertion and how the range of a chunks are calculated during insertion?
  2. Since the PropertyId is random value. Does the compound key fits the definition of monotonically increasing value?

I am a newbie to MongoDB. And wanted to know if I am on the right path?

  • if the queries are mainly on AgentId only then they will be distributed to appropriate shard that houses the chunks for that AgentId. Aren't there queries based on property id also? As far as writes, if you get all writes for specific agentId at once then they will all go to the same shard (imagine the most trivial case of having exactly two agent id values and two shards - each shard would house one of the two agent ids). – Asya Kamsky Dec 28 '17 at 16:01
  • Thanks Asya. Yes I have a group by on agentid,propertyid. And most of the queries are on AgentId with other filters. I am starting to get the picture as to how this chunking and balancer works. Since this is the case of bulk loading, I think I need to pre-split to properly distribute the writes and avoid unncecessary. splitting and migration. Thanks again – Sameer Dec 28 '17 at 17:18
0

MongoDB supports compound indexes, where a single index structure holds references to multiple fields ( As MongoDB Limits and Thresholds MongoDB imposes a limit of 31 fields for any compound index.) within a collection’s documents. The following diagram illustrates an example of a compound index on two fields:

enter image description here

How the data will be distributed to shards during insertion and how the range of a chunks are calculated during insertion?

Starting in version 2.6, MongoDB can use index intersection to fulfill queries. The choice between creating compound indexes that support your queries or relying on index intersection depends on the specifics of your system.

Index intersection does not eliminate the need for creating compound indexes. However, because both the list order (i.e. the order in which the keys are listed in the index) and the sort order (i.e. ascending or descending), matter in compound indexes, a compound index may not support a query condition that does not include the index prefix keys or that specifies a different sort order.

For example, if a collection orders has the following compound index, with the status field listed before the ord_date field:

{ status: 1, ord_date: -1 }

The compound index can support the following queries:

db.orders.find( { status: { $in: ["A", "P" ] } } )
db.orders.find(
   {
     ord_date: { $gt: new Date("2014-02-01") },
     status: {$in:[ "P", "A" ] }
   }
)

But not the following two queries:

db.orders.find( { ord_date: { $gt: new Date("2014-02-01") } } )
db.orders.find( { } ).sort( { ord_date: 1 } )

However, if the collection has two separate indexes:

{ status: 1 }
{ ord_date: -1 }

The two indexes can, either individually or through index intersection, support all four aforementioned queries.

Since the PropertyId is random value. Does the compound key fits the definition of monotonically increasing value?

For the fastest processing, ensure that your indexes fit entirely in RAM so that the system can avoid reading the index from disk.

To check the size of your indexes, use the db.collection.totalIndexSize() helper, which returns data in bytes:

> db.collection.totalIndexSize() 
4294976499

The above example shows an index size of almost 4.3 gigabytes. To ensure this index fits in RAM, you must not only have more than that much RAM available but also must have RAM available for the rest of the working set.

If you have and use multiple collections, you must consider the size of all indexes on all collections. The indexes and the working set must be able to fit in memory at the same time.

For further your ref Indexing Strategies

  • 2
    index intersection is not relevant to the question asked at all. the question is about targeting of reads and writes to shards based on what the shard key is. – Asya Kamsky Dec 28 '17 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.