[SERVER-32200] ISSUE: Wrong Document Count returning from MONGODBServer Created: 07/Dec/17  Updated: 07/Jan/18  Resolved: 08/Dec/17

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 3.4.0
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Sandeep [X] Assignee: Mark Agarunov
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Zip Archive SERVER-32200.zip     Zip Archive diagnostic.data.zip     Zip Archive log.zip     Zip Archive systemprofile_gt-4minutes.zip    
Participants:

 Description   

Description:
We are using MongoDB Server version 3.4
We have a collection with 5.5 Million=(5482968) documents and indexSize=283.6MB . we have set the RAM Size as 1.1GB
When we try to retrieve the document count from MongoDBServer taking long time to respond and finally returning wrong count
we have created indexes on 8 fields on this collection .Our Collection Size =7.8GB

Issue we are facing:
1. Some times Wrong document count is returned from MongoDBServer
Actual Count Returned from MonogoDBServer=5080966
Expected Count Returned from MonogoDBServer=5482968
2. Some times MongoDBServer taking too long time to respond [in minutes]

Note: In production, this collection can grow to have more documents (Billions of documents)

Requesting you to Kindly address our below queries ,
a)Whether increasing the RAM Size will address our above Mentioned issues[count ]
b)Will reducing number of indexed columns/fields help in addressing these issues?

How to Reproduce:

1.Create a collection say 'testCollection' with 5.5Million or more document with average document size of 1.5KiB
2.Create index for 8 fields ensure that indexSize grows to >=280MB
3.Set the mongoDB RAMSize to 1.1GB
*db.adminCommand(

{setParameter: 1, internalQueryExecMaxBlockingSortBytes: 1073741824}

)*
4.Perform db.testCollection.count(with some condition on a field)



 Comments   
Comment by Sandeep [X] [ 14/Dec/17 ]

Dear Mark,

Please let us know in case new ticket needs to be opened here ...

Thanks & Regards
Sandeep KS

Comment by Sandeep [X] [ 13/Dec/17 ]

Dear Mark,
I am also attaching the system.profile output for your analysis

Note:
We recorded the query by setting time as 4 minutes['240000']
systemprofile_gt-4minutes.zip

Thanks & Regards
Sandeep KS

Comment by Sandeep [X] [ 12/Dec/17 ]

Dear Mark,

Thank you for your inputs!!
Based on your inputs,

  • We Executed validate command on the collection, later we performed explain() on the DB Query and observed that the SORT stage with 'timestamp' field as index was inside the 'RejectionPlan'.
  • Now we modified index creation so that the SORT stage with 'timestamp' field as index came inside the 'WinningPlan'

Queries:
Query1: How ever , Still we observe that execution of queries from mongoserver is taking time in minutes [ >15 minutes!!!]
We have attached below logs for your analysis
a)Query plan Output[]
FileName:QueryPlan-Explain-output-12122017
*b)db.currentOp Operation output *
FileName:currentOp-output-12122017
Note: we waited for 5 minutes and we took the 'currentOp' output
*c) log and diagnostic.data log files
Also attached fresh db->log and db->diagnostic.data * for your analysis

Query2: We are not sure why other index which we created went to rejection Plan?
Example : We created 'deviceId' field as one of the index, only after removal of this index
then only the 'timestampIdx' ,went to winningPlan

SERVER-32200.zip

Thanks & Regards
Sandeep KS

Comment by Mark Agarunov [ 08/Dec/17 ]

Hello Urs,

Thank you for the report. After looking over this, the incorrect counts may be due to previous unclean shutdowns. This can be fixed by running the validate command on the collection.

Additional memory will likely improve performance. In addition to increasing the cache size, more memory would allow sorts to fit in memory and not have to be written to disk, causing a significant performance hit. Looking over the logs, it appears that the queries are not effectively using indexes, which is likely the cause of the long run times for these queries. Additionally, it appears that the Indexes that did exist for the table were dropped just before the queries started to get slower. In addition to using indexes for these queries, I would recommend upgrading MongoDB to at least the latest 3.4 release, as there have been many performance improvements in more recent versions, especially relating to the aggregation framework.

As I do not see anything in the logs or diagnostic data to indicate a bug in MongoDB, I've closed this ticket. If additional information comes to light, or you are still seeing slow performance after upgrading and making sure the queries effectively use the index, please let us know and we can reopen the ticket.

Thanks,
Mark

Comment by Sandeep [X] [ 08/Dec/17 ]

Hello Mark,

Thanks for your support, kindly find archived $dbpath/diagnostic.data directory & log.zip attached to this ticket.
Let us know if anything else is required.

Thanks,
Sandeep

Comment by Mark Agarunov [ 07/Dec/17 ]

Hello Urs,

Thank you for the report. To get a better idea of why this may be happening, could you please provide the following:

  • The complete logs from all affected mongod nodes
  • Please archive (tar or zip) the $dbpath/diagnostic.data directory and attach it to this ticket.

This should give some insight into why the count is inaccurate and taking a long time.

Thanks,
Mark

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