lookit / lookit-api

Codebase for Lookit v2 and Experimenter v2. Includes an API. Docs: http://lookit.readthedocs.io/
https://lookit.mit.edu/
MIT License
10 stars 18 forks source link

lookit-bot email delay scoping #1409

Open ianchandlercampbell opened 3 months ago

ianchandlercampbell commented 3 months ago

Summary

Writing this as a placeholder scoping issue for the lookit api-- this could be a SendGrid issue or multiple things.

We've noticed hours-long delays on lookit-bot email notifications on Lab member requests as well as experiment runner deploying over a span of weeks. The severity and frequency of this problem is currently unknown, but I have been able to confirm that on both the lab member request and experiment runner building side, these appear to be working on the site itself as normal.

I have a sneaking suspicion this could lead to us going through the lookit-bot notification system and making some changes in the long run. Melissa requested that we look into cataloging the various email bots (e.g. lookit-bot) and then figuring out where they send emails in general for the CHS admin side (ideally, we want everything routed to childrenhelpingscience@gmail.com at least primarily).

For the week of 06-03, let's do some scoping on just the email notification delays and see scope out how this system works and where we might be seeing some issues.

Issue

It seems like the data on production has gotten large enough that the announcement emails are taking a very long time to complete. This has been replicated locally with the production data set. This command resolve quickly on the staging data set.

To resolve this issue, I feel that the SQL query used to generate the announcement emails should be refactored and optimized.

Implementation

Before we can get into implementation details, we'll probably need a good list of rules for the announcement emails job.

  1. Review and understand current SQL query, and underlying rules/conditions.
  2. Review the current understood functionality of this query/job.
  3. For code readability, use combination of Django's ORM and comments.
  4. Best attempt at a single call to DB where only the needed data is returned.

When we have time, it would be worth doing the following: