[SERVER-39191] Performance regression for counts post-sharding Created: 25/Jan/19  Updated: 29/Oct/23  Resolved: 15/Oct/20

Status: Closed
Project: Core Server
Component/s: Sharding
Affects Version/s: 4.0.5
Fix Version/s: 4.9.0

Type: Improvement Priority: Major - P3
Reporter: Julien Moutte Assignee: Ian Boros
Resolution: Fixed Votes: 2
Labels: qexec-team
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Text File mongos_plan.txt    
Issue Links:
Duplicate
Problem/Incident
causes SERVER-78071 A targeted query with a concurrent yi... Open
is caused by SERVER-3645 Sharded collection counts (on primary... Closed
Related
related to SERVER-39241 Plan scoring incorrectly applies noFe... Closed
related to SERVER-23017 Fast approximate count with predicate Backlog
is related to SERVER-50857 Improve count() performance in sharde... Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 2020-10-05, Query 2020-10-19
Participants:
Case:

 Description   

To filter orphans during counts, sharded clusters avoid COUNT_SCAN in favor of an IXSCAN, and examine documents. Especially for queries that do not include the shard key, this may be difficult to avoid. But, the impact can be noticeable for some workloads.

Original report follows:


I have a simple collection with 40M documents distributed ver 2 shards. The documents contain a field named COUNTRY with a sparse index on it.

I am trying to count documents with a specific value for COUNTRY. When counting on the shards directly the plan looks correct and uses COUNT_SCAN with the index, returns 6M docs on each shard without loading them.

{{2019-01-11T16:03:02.643+0000 I COMMAND [conn102] command segmentation.contacts appName: "MongoDB Shell" command: count { count: "contacts", query:{ COUNTRY: "US" }, fields: {}, lsid: \{ id: UUID("60be11b5-6299-416b-9ead-8c58fd3656b8") }, $clusterTime: { clusterTime: Timestamp(1547221085, 2), signature:{ hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 }}, $db: "segmentation" } planSummary: COUNT_SCAN \{ COUNTRY: 1 } keysExamined:6923922 docsExamined:0 numYields:54093 reslen:340 locks:{ Global: { acquireCount:{ r: 54094 }}, Database: { acquireCount:{ r: 54094 }}, Collection: { acquireCount:{ r: 54094 }} } protocol:op_msg 2747ms}}

Running the exact same query on MONGOS, the count is distributed to the shards but I can see in the logs of each shard that the plan changed and it used IDX_SCAN instead and loaded all the documents which was much slower.

{{2019-01-11T16:04:13.104+0000 I COMMAND [conn111] command segmentation.contacts appName: "MongoDB Shell" command: count { count: "contacts", query:{ COUNTRY: "US" }, allowImplicitCollectionCreation: false, shardVersion: [ Timestamp(474, 1),ObjectId('5c37917aa0f162a86b270897') ], lsid: \{ id: UUID("9be8fb16-f158-4099-bf25-a3a0c97a33c2"), uid: BinData(0, E3B0C44298FC1C149AFBF4C8996FB92427AE41E4649B934CA495991B7852B855) }, $clusterTime: { clusterTime: Timestamp(1547222609, 1), signature:{ hash: BinData(0, 1F79735A80E06C35800DE9CDC4E92608B2759F1F), keyId: 6644928741854150685 }}, $client: { application:{ name: "MongoDB Shell" }, driver: \{ name: "MongoDB Internal Client", version: "4.0.5" }, os: \{ type: "Linux", name: "Ubuntu", architecture: "x86_64", version: "18.04" }, mongos: \{ host: "ip-192-168-169-237:27019", client: "127.0.0.1:59430", version: "4.0.5" } }, $configServerState: { opTime:{ ts: Timestamp(1547222609, 1), t: 1 }}, $db: "segmentation" } planSummary: IXSCAN \{ COUNTRY: 1 } keysExamined:6923921 docsExamined:6923921 numYields:54093 reslen:320 locks:{ Global: { acquireCount:{ r: 54094 }}, Database: { acquireCount:{ r: 54094 }}, Collection: { acquireCount:{ r: 54094 }} } protocol:op_msg 34784ms}}

Can someone explain me why the plan changed or why the docs have to be scanned ? The performance impact is huge...

Attached execution plan from MONGOS. This issue was also referenced in StackExchange 



 Comments   
Comment by Githook User [ 15/Oct/20 ]

Author:

{'name': 'Sebastian Mayr', 'email': 'smayr@atlassian.com'}

Message: SERVER-39191 Optimize count queries on sharded collections

When counts were made orphan-aware the addition of the SHARD_FILTER
stage started preventing the COUNT_SCAN optimization on all sharded
collections, leading to significant performance loss on some workloads.

However, there can not be any orphans if the shard key is fully
specified: A mongod will never have orphans for chunk ranges
it owns.

This patch changes the query system to omit the SHARDING_FILTER stage in cases
where the query specifies the full shard key, regaining lost performance.

Closes #1369.

Signed-off-by: Ian Boros <ian.boros@mongodb.com>
Branch: master
https://github.com/mongodb/mongo/commit/14bfbd8833706912617c0c904cce9847fbbbd0f1

Comment by Sebastian Mayr [ 27/Aug/20 ]

I opened a PR with what I think should be the correct changes on GH, but it probably needs a bit of attention from a Mongo team member: https://github.com/mongodb/mongo/pull/1369

Comment by Garaudy Etienne [ 27/Aug/20 ]

Based on sebmaster's suggestion, query seems best equipped to make the right optimizations here. kateryna.kamenieva

Comment by Sebastian Mayr [ 05/Jun/20 ]

We've encountered this issue quite recently as well.

We had the following plan to lessen the impact of this considerably in one special case: Counting with a fully specified $eq shard key. The idea is that in those cases, mongos would only send the count request to a single shard that is responsible for the respective chunk. This should guarantee that the shard cannot have orphans matching the query.

Could someone check our thinking on that? If this is correct, I have some code + tests already written up that strips the sharding filter in this case that we'd like to contribute.

Comment by Asya Kamsky [ 11/Mar/20 ]

Note that sharding filter still has to be applied, but it can be done from an index, if the index covers query field PLUS shard filter field(s).

Comment by Asya Kamsky [ 11/Mar/20 ]

I believe this is (somewhat) a duplicate of SERVER-39241 which is fixed in 4.3.2.

Comment by Julien Moutte [ 01/Feb/19 ]

Sure, let's try.

Our use case is basically a graphical query builder where users can add filtering criteria progressively to segment a big data collection. They start with the whole data collection and they see the total count or a count of distinct values on a specific indexed field, they can then incrementally add filtering criteria and see the size of each segment as they build that segmentation model. The count helps building the model and it should come back fast. Accuracy does not have to be 100%, counting distinct entries with hyperloglog is a possibility.

For example you could be querying a massive events table from IOT devices belonging to users and you would want to incrementally segment that user base based on criteria applied to that events collection. Hope this is clear enough.

Comment by Alyson Cabral (Inactive) [ 31/Jan/19 ]

Thanks, Julien for the info!

Without going into the specific intricacies of your data (if possible), can you describe what value counts bring to your user-facing application? Having a well-rounded understanding of use cases for count usage in applications will really help us prioritize this work appropriately. 

 

 

Comment by Julien Moutte [ 31/Jan/19 ]

Thanks Eric for the feedback.

This is a POC to evaluate MONGO for a Big Data segmentation use case:

Given a massive collection of documents with proper indices we want to count matching documents based on a set of criteria. Sharding is used to distribute both storage and workload in a scale-out scenario.

Initial tests were done on a non sharded database and the query execution time was okayish (2.7s) for a simple count on an indexed field.

We were expecting that sharding would add 2 extra qualities :

  1. a linear execution time reduction by adding shards and splitting the data / counting.
  2. the possibility to handle data growth while maintaining execution time stable.

The opposite happens. When querying shards through mongos, the same query runs in (34s) as documents are examined for this orphaned documents counting. One could argue that quality 2) is somehow still there but the execution time is way too long for our user interface.

I understand the reason why this happens, so we should change the issue type to improvement instead of bug.

Comment by Eric Sedor [ 29/Jan/19 ]

Hi Julien, thanks for your patience.

To accurately count documents in a sharded environment, shards must examine them. This is working as designed as of SERVER-3645 and is necessary to avoid counting orphaned documents.

While it may one day be possible to do this without examining the documents directly, it is unlikely this would be able to be changed soon.

That said, we'd like to ask for more information to help us consider this problem.

What purpose are the counts serving in your application, are there strict SLAs around the counts in particular, and exactly how much slower did they get for you?

In the event overall performance has been impacted by the memory requirement of these counts, can you classify the total impact?

Thank you in advance!

Generated at Thu Feb 08 04:51:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.