Performance issues with large volumes of discussion data

[kaustavb12]

Description

Significant performance issues noticed after migrating long-running courses with large volumes of discussion data to MySQL backend.

For a course with around 8000 threads and around 31000 comments, the get threads list API takes around 2 mins to respond and often just times out.

Open edX Release - Sumac
forum release - 0.2.0

Potential Cause

Apart from some DB queries which could be further optimized, the performance issue mostly seems to be arising due to the to_dict methods defined in models in the MySQL backends (Example : Comment to_dict() and ForumUser to_dict()), which in turn recursively fetches data from other models.

I created a POC to optimize the get threads list API and the response time in a sandbox with similar volumes of sample data came down to around 3s. Here, I did the following:

1. Optimized some queries Ref
2. Avoided using the to_dic method Ref 1 Ref2

[ormsbee]

Note: We cannot remove MongoDB support without fixing this performance regression.

[taimoor-ahmed-1]

@kaustavb12 would you like to open a PR for this optmization or do you want me to create one?

[kaustavb12]

@taimoor-ahmed-1 It would be great if you could create the PR for this. Thanks.

[taimoor-ahmed-1]

PR for the performance optimization:
#237

[kaustavb12]

Hey @taimoor-ahmed-1,

First of all thanks a lot for the PR.

Going through your PR I noticed that your fixes were limited to the get threads api. I just wanted to check with you to see if you had a chance to verify the performance of other APIs/flows in the forum app and if any other PR is lined up for other APIs.

[kaustavb12]

@taimoor-ahmed-1 Another point to add to my comment above.

In my view, things like ids_only and get_full_dict used in the PR are temporary band-aids which doesn't address the core issue. I used those in my POC just to test things out and prove performance gains.

I think we should look into getting rid of all the to_dicts used in the models. This would be a larger effort but it would be a more appropriate solution for production use and would help address performance issues of all the APIs simultaneously.

cc. @ormsbee

[taimoor-ahmed-1]

@kaustavb12 yes I agree, these were small gains and don't look at the forum service holistically. We have decided to perform load testing on the Ulmo sandbox and then I'll create tasks for the bottlenecks that are identified in order of the worst performing apis.

[kaustavb12]

@taimoor-ahmed-1 Thanks. Should we keep this issue open till then, so we can track when the optimizations are complete enough for us to start using MySQL for large courses in prod instances again?