0

When searching for regular expression text in MongoDB, the speed is slow at first, so I would like to know the cause.

Only on the phenomenon JAVA Application Server will the corresponding slow query be found.

When the corresponding query is run in the MongoDB shell, it works very fast (index works well).

The number of data result values in the above query is five. The total number of data in the collection is 450,000

Below is a process-specific query.

=====JAVA Process===== (Very Slow 5,518ms)

public List<Contents> findContentList(int rowCnt, long rowNo, String searchContent){
        Query query = new Query();
        query.addCriteria((Criteria.where(DictionaryKey.content).regex("^" + searchContent)));
        if (rowNo > 0) query.addCriteria(Criteria.where(DictionaryKey.contentSeq).gt(rowNo));
        query.with(new Sort(Sort.Direction.ASC, DictionaryKey.contentSeq));
        query.limit(rowCnt);

        return this.mongoTemplate.find(query, Contents.class, Constant.CollectionName.Contents);
       
}

java Monitoring tool

   Query : Query: { "content" : { "$regex" : "^abcd"}}, Sort: { "contentSeq" : 1}
    Collection Name : contents
    MongTemplate#find() [5,518ms] -- org.springframework.data.mongodb.core.mongTemplate.find()Ljava/util/List;

=====Mongodb Shell====== Mongodb query (Very Fast, index works well)

db.contents.found ({content:{"$regex" : "^abcd"}}).sort ({"contentSeq" : 1});

'contents' collection index is content_1_contentSeq_1

Please help me.

user3689808
  • 67
  • 1
  • 5
  • See https://stackoverflow.com/a/56159506/6267549 - it's slow because Spring is mapping through 2 levels. – Sheeri Nov 16 '20 at 18:48
  • When monitored, it wasn't a marshalling issue. It took a long time to get data from the network. – user3689808 Nov 17 '20 at 01:54
  • If possible please post the actual Java code. – prasad_ Nov 17 '20 at 06:29
  • @prasad_ Added Java code to content. – user3689808 Nov 17 '20 at 07:31
  • The Java code looks okay. Are you accessing the same database/ data/collection from the `mongo` shell? – prasad_ Nov 17 '20 at 07:58
  • @prasad_ The mongo router(Mongos) IP used by the application server and Mongo router(Mongos) of Mongo shell that I tested is different from each other. But the replica set is the same. The singularity occur once per 5minutes. It doesn't happen every time. Additionally, it occurs more and more frequently when users have a lot of access in only those queries. – user3689808 Nov 17 '20 at 10:10
  • @prasad_ All queries except for those queries are OK. CPU and IOPS are also stable. What's unique is that That is, there is a delay in the query every five minutes. It doesn't happen every time. The query has a delay every for five minutes. It doesn't happen every time. – user3689808 Nov 17 '20 at 10:11
  • SInce you are using `mongos` your cluster is sharded. Is the collection sharded? – prasad_ Nov 17 '20 at 10:32
  • @prasad_ No This Collection is not sharded – user3689808 Nov 17 '20 at 10:40
  • This is from the docs: [regex and index usage](https://docs.mongodb.com/v4.2/reference/operator/query/regex/index.html#index-use). – prasad_ Nov 17 '20 at 10:43
  • @prasad_ Thank you. Let me check the document again. – user3689808 Nov 17 '20 at 11:04
  • What is `db.contents.found` ? – D. SM Nov 17 '20 at 19:29
  • My guess is you are not actually executing the query in the shell. – D. SM Nov 17 '20 at 19:30

1 Answers1

0

I found the reason

The cause is that some queries are using the wrong index.

The solution was to force the use of the index by giving a hint.

user3689808
  • 67
  • 1
  • 5