Discussion:
Facing issues when document contain more than 3 crore document
(too old to reply)
Ankit Rathore
2018-03-12 06:56:30 UTC
Permalink
Hi All,

I have faced issues when document contain more than 3 crore document. PFA
stats output.
Using Mongodb shell version 3.4.2 and also performed sharding.
I faced following issue.
1. Application takes 10 to 30 seconds while navigating in pagination, I
want 3 to 4 seconds to navigate.
2. In Search field also application take 17 to 20 seconds

A) Using below criteria to get record for pagination :

Criteria criteria = new Criteria();
Query query = null;

criteria.and(CommonConstant.REPORT_USER_CODE).in("List of user
Code");
query = this.createQuery(criteria, pageRequest, sort);

query.addCriteria(Criteria.where(CommonConstant.REPORT_DATE_TIME).gte(fromDate).lte(toDate));

B) Using below criteria to get total number of record count for pagination:

long count = 0;

for (String userCode : "list of user_code") {
Criteria criteria = new Criteria();

criteria.and(CommonConstant.REPORT_USER_CODE).is(userCode );

criteria.and(CommonConstant.REPORT_DATE_TIME).gte(fromDate).lte(toDate);
Query query = this.createQuery(criteria, null, null);

long temp = this.mongoTemplate.count(query,
abcReportDTO.class);
count = count + temp;
}
Linux information:
cpu MHz : 2593.944
MemTotal: 7994324 kB



Thanks & regards,
Ankit
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/69011a29-c754-4f2e-8ae8-8b559ff7b126%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
'Kevin Adistambha' via mongodb-user
2018-03-13 23:51:24 UTC
Permalink
Hi Ankit

Using Mongodb shell version 3.4.2 and also performed sharding.

Could you elaborate what do you mean by “sharding”? You only posted the
hardware spec for a single server. Are you using multiple servers for
sharding, or are you using a single machine and run multiple mongod on it?

Also, I’m not clear on the query you’re doing. Could you post the query in
mongo shell and the output of db.collection.explain().find(
)
<https://docs.mongodb.com/manual/reference/explain-results/>?

Best regards
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/258b1f39-e7f4-4ca6-9903-b1437e623f46%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Ankit Rathore
2018-03-14 09:56:11 UTC
Permalink
Hi Kevin,

Thanks for your reply.

Sorry for the mistake we are not using sharding. We are using replica-set
with 3 config and multiple mongos.

hardware spec of all three mongod systems is:

RAM: 32 GB
Hard Disk: 700Gb

example:
collection:

report_db.abc_report:{
_id:Sring,
user_code:string,
dateTime:date,
userName:String
.......
.......
.......lots of more field.
}

Query: if i applied search on username with filter usercode and
dateTime(for second point for 1st comment):
db.getCollection('abc_report').explain().find({"$and":[ {"$or": [ {
"userName" : { "$regex" : "Vomputer-40200" , "$options" : "i"}
}]},{"userCode" : { "$in" : [ "X1KQ8J", "PN9VGS", "T2CCQN",
"4XB8GF", "SUZG3K", "42L1ON", "6MZKRM", "8GFWCV", "F0SBAV", "GW3LX2",
"4EVSFI", "BH0QTZ",
"6FG4RA", "9K9RHQ", "6WMELA", "PHWGR5", "WP0EUZ", "B6XCMV", "11KHTT",
"E3AFQV", "F7TXM9",
"JQFNQ2", "TZDJ9C", "B6R6KO", "GQVC23", "VN0VS5", "M6GXYO", "OMX3LS",
"MGPP6S", "V1FQWC",
"ZXZIT7", "ARDLG6", "8F6BHO", "0ESRZO", "MVGL7U", "CGU35W", "USQ015",
"RK84IY", "HRE9QT", "6ZZUFZ", "C6LSYO" ]}},
{"dateTime": { $gte: new Date(1518499674000), $lte: new
Date(1520918874000) }}]}).skip(0).limit(30)

PFA output of db.collection.explain().find(
)
<https://docs.mongodb.com/manual/reference/explain-results/>.

for same query search(username) or without search,
if "skipAmount"(skip(-)) is increasing then I faced navigating problem in
pagination(1st point of above comment)

Thanks & Regards,
Ankit
Post by 'Kevin Adistambha' via mongodb-user
Hi Ankit
Using Mongodb shell version 3.4.2 and also performed sharding.
Could you elaborate what do you mean by “sharding”? You only posted the
hardware spec for a single server. Are you using multiple servers for
sharding, or are you using a single machine and run multiple mongod on it?
Also, I’m not clear on the query you’re doing. Could you post the query in
mongo shell and the output of db.collection.explain().find(
)
<https://docs.mongodb.com/manual/reference/explain-results/>?
Best regards
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/92bf39c1-25a7-41b8-bd93-3debedea496b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
'Kevin Adistambha' via mongodb-user
2018-03-19 06:55:50 UTC
Permalink
Hi Ankit

I noticed some things from the information you posted:

1.

Your index is {userCode: 1, dateTime: -1}
This is not exactly in line with the query you’re doing, which is {userName:
{$regex:...}, userCode: {$in:...}, dateTime: {$lt:..., $gt:...}},
meaning MongoDB cannot use the index for the userName field.
2.

You have a case insensitive $regex on the query, of which the field is
not included in the index.
A case insensitive regex query generally cannot use an index
efficiently. Please see $regex index use
<https://docs.mongodb.com/manual/reference/operator/query/regex/#index-use>
for more information.
3.

Your query is slower the bigger value you pass into skip()
This is sort of expected. Using skip() and limit() do not make the query
result smaller. It only result in a smaller result that is sent to the
client. Internally, MongoDB still need to page through all the matching
results.

Some suggestiond you may be able to implement (subject to your use case):

- Remove case insensitivity from your regex query. It’ll be much better
if you can remove the $regex term altogether for maximum performance.
- Include userName in the compound index.
- Instead of using skip() and limit(), try to use a range query. For
example, see the Pagination Example
<https://docs.mongodb.com/manual/reference/method/cursor.skip/#pagination-example>,
especially the Using Range Queries section.

Best regards
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/a33f3c73-4188-4bf9-b13e-75f0d8f36840%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Ankit Rathore
2018-03-19 09:52:54 UTC
Permalink
Hi Kevin,

Thanks for your reply.

I have included userName in Compound index. This working fine, If userName
present in Query.

@Problem:
If username not present in query( If user apply search on username field
then only username coming in query)
example: query without userName:
find({"$and":[ {"userCode" : { "$in" : [ "X1KQ8J", "PN9VGS", "T2CCQN",
"4XB8GF", "SUZG3K", "42L1ON", "6MZKRM", "8GFWCV", "F0SBAV", "GW3LX2",
"4EVSFI", "BH0QTZ",
"6FG4RA", "9K9RHQ", "6WMELA", "PHWGR5", "WP0EUZ", "B6XCMV", "11KHTT",
"E3AFQV", "F7TXM9",
"JQFNQ2", "TZDJ9C", "B6R6KO", "GQVC23", "VN0VS5", "M6GXYO", "OMX3LS",
"MGPP6S", "V1FQWC",
"ZXZIT7", "ARDLG6", "8F6BHO", "0ESRZO", "MVGL7U", "CGU35W", "USQ015",
"RK84IY", "HRE9QT", "6ZZUFZ", "C6LSYO" ]}},
{"dateTime": { $gte: new Date(1518499674000), $lte: new
Date(1520918874000) }}]}).skip(0).limit(30) .

Above query take more than 60 seconds, If I included UserName in compound
index.
If we are not include userName in compound index then above query take 4
seconds.

Thanks & Regards,
Ankit Rathore
Post by 'Kevin Adistambha' via mongodb-user
Hi Ankit
1.
Your index is {userCode: 1, dateTime: -1}
{$regex:...}, userCode: {$in:...}, dateTime: {$lt:..., $gt:...}},
meaning MongoDB cannot use the index for the userName field.
2.
You have a case insensitive $regex on the query, of which the field is
not included in the index.
A case insensitive regex query generally cannot use an index
efficiently. Please see $regex index use
<https://docs.mongodb.com/manual/reference/operator/query/regex/#index-use>
for more information.
3.
Your query is slower the bigger value you pass into skip()
This is sort of expected. Using skip() and limit() do not make the
query result smaller. It only result in a smaller result that is sent to
the client. Internally, MongoDB still need to page through all the matching
results.
- Remove case insensitivity from your regex query. It’ll be much
better if you can remove the $regex term altogether for maximum
performance.
- Include userName in the compound index.
- Instead of using skip() and limit(), try to use a range query. For
example, see the Pagination Example
<https://docs.mongodb.com/manual/reference/method/cursor.skip/#pagination-example>,
especially the Using Range Queries section.
Best regards
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/21b814b8-52a4-491e-893e-687a559b954c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
'Kevin Adistambha' via mongodb-user
2018-04-17 05:24:39 UTC
Permalink
Hi Ankit

It’s been some time since you posted this. Are you still having issues with
the query?

If you are, please post the output of:

db.collection.explain('executionStats').find(...)

which will output how MongoDB plans to execute your query, along with some
metrics when the query was actually executed. The output of that command is
described in Explain Results
<https://docs.mongodb.com/manual/reference/explain-results/>.

The output of explain() is the first query performance troubleshooting tool
in MongoDB, and it should provide a hint whether:

1. The index is not optimal, or
2. There’s not enough hardware resources to answer the query quickly, or
3. Both (1) and (2)

It will also be helpful to know the size of your collection (
db.collection.stats()) and the size of your RAM.

Best regards
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/0d8ac4d6-4765-4101-a2ec-b0ffc8e5a08f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Ankit Rathore
2018-04-26 11:10:05 UTC
Permalink
Hi Kevin,

Above issue is Fixed. I have to used multiple compound indexed on
collection and remove regex from query.

Thanks & Regards,
Ankit Rathore
Post by 'Kevin Adistambha' via mongodb-user
Hi Ankit
It’s been some time since you posted this. Are you still having issues
with the query?
db.collection.explain('executionStats').find(...)
which will output how MongoDB plans to execute your query, along with some
metrics when the query was actually executed. The output of that command is
described in Explain Results
<https://docs.mongodb.com/manual/reference/explain-results/>.
The output of explain() is the first query performance troubleshooting
1. The index is not optimal, or
2. There’s not enough hardware resources to answer the query quickly, or
3. Both (1) and (2)
It will also be helpful to know the size of your collection (
db.collection.stats()) and the size of your RAM.
Best regards
Kevin
​
--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.

For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+***@googlegroups.com.
To post to this group, send email to mongodb-***@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/1ca462cb-375d-47b8-bafc-4249a58a834b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Loading...