[SERVER-46874] Create view becomes very slow when system has many (10000+) views Created: 15/Mar/20  Updated: 28/Apr/20  Resolved: 28/Apr/20

Status: Closed
Project: Core Server
Component/s: Performance
Affects Version/s: 4.2.0
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Xiaoqiang Zhou Assignee: Dmitry Agranat
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File collMode.png     PNG File collMode_createIndex.png    
Issue Links:
Depends
depends on SERVER-46865 collMod should not take database MODE... Closed
Backwards Compatibility: Fully Compatible
Participants:

 Description   

Mongo Server reloads the view catalog when view created / updated / dropped. We have tens of thousands of views in database, and it's getting more and more slower to create or modify view.

Is it possible to make the view reload optional? 



 Comments   
Comment by Dmitry Agranat [ 24/Mar/20 ]

Hi xiaoqiang.chou@gmail.com,

I've received an update that it will be better to watch SERVER-46865 in regards to improvements for this specific workload. Please add yourself to "Start watching this issue" for receive updates. I've linked this ticket to SERVER-46865 for better visibility and to provide our development team with better visibility into this use case.

Regards,
Dima

Comment by Xiaoqiang Zhou [ 24/Mar/20 ]

@Dmitry Agranat. Thanks for looking into this.

We are building a generic project management app on top of MongoDB. In the app, each user might have many different views (kanban, grid, calendar), each view may have different filtering conditions and displaying columns. The filtering part are implemented using MongoDB View and Pipelines, hence having so many views.

I'll give 4.4.0-rc0 a try and get back to you. Thanks very much.

Comment by Dmitry Agranat [ 19/Mar/20 ]

Hi xiaoqiang.chou@gmail.com, thank you for the report.

After examining the provided data, we can see that there is some lock contention between collMode and createIndex commands. We have an internal project where we are working on eliminating locks which I think could help in this situation. We are aiming to complete this project once MongoDB 4.4 is out. Is it feasible for you to try 4.4.0-rc0 when it's out and report back with the results?

Thanks,
Dima

Comment by Dmitry Agranat [ 19/Mar/20 ]

Hi xiaoqiang.chou@gmail.com, thank you for providing the requested information. Could you please explain your app logic in regards to creating indexes when you create views?

Comment by Xiaoqiang Zhou [ 16/Mar/20 ]

diagnostic.data and mongod.log have been uploaded.

I've truncated my mongod.log. You can easily spot the 'collMod' in the last few lines of mongod.log. Here is an example:

 

2020-03-16T23:07:40.720+0800 I  COMMAND  [conn12] command hipa.view_5e6f9624b3f19a9642b47666 command: collMod { collMod: "view_5e6f9624b3f19a9642b47666", viewOn: "rec_5e6f9624b3f19a9642b47662", pipeline: [], lsid: { id: UUID("ed3f76b3-12ab-4ac3-9f6c-775bcfdd85df") }, $db: "hipa", $readPreference: { mode: "primaryPreferred" } } keysInserted:0 keysDeleted:0 numYields:0 reslen:38 locks:{ ParallelBatchWriterMode: { acquireCount: { r: 1 } }, ReplicationStateTransition: { acquireCount: { w: 1 } }, Global: { acquireCount: { w: 1 } }, Database: { acquireCount: { W: 1 } }, Collection: { acquireCount: { r: 1 } }, Mutex: { acquireCount: { r: 2 } } } flowControl:{ acquireCount: 1 } storage:{} protocol:op_msg 1361ms
2020-03-16T23:07:55.605+0800 I  COMMAND  [conn12] command hipa.view_5e6f9624b3f19a9642b47666 command: collMod { collMod: "view_5e6f9624b3f19a9642b47666", viewOn: "rec_5e6f9624b3f19a9642b47662", pipeline: [], lsid: { id: UUID("ed3f76b3-12ab-4ac3-9f6c-775bcfdd85df") }, $db: "hipa", $readPreference: { mode: "primaryPreferred" } } keysInserted:0 keysDeleted:0 numYields:0 reslen:38 locks:{ ParallelBatchWriterMode: { acquireCount: { r: 1 } }, ReplicationStateTransition: { acquireCount: { w: 1 } }, Global: { acquireCount: { w: 1 } }, Database: { acquireCount: { W: 1 } }, Collection: { acquireCount: { r: 1 } }, Mutex: { acquireCount: { r: 2 } } } flowControl:{ acquireCount: 1 } storage:{} protocol:op_msg 1443ms 

Comment by Dmitry Agranat [ 15/Mar/20 ]

Hi xiaoqiang.chou@gmail.com

Would you please archive (tar or zip) the mongod.log files and the $dbpath/diagnostic.data directory (the contents are described here) and upload them to this support uploader location?

Please also mention the exact time when you've experienced slower view creation/modification.

Files uploaded to this portal are visible only to MongoDB employees and are routinely deleted after some time.

Thanks,
Dima

Generated at Thu Feb 08 05:12:41 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.