Improve notices endpoint

samhotep commented 2 months ago

Done

Querying notices takes quite a while to complete, ~15s on average. This PR hopes to improve the db query by reworking how the notice json is built.

QA

Check out this feature branch
Run the site using the command ./run serve
View the site locally in your web browser at: http://0.0.0.0:8030/
Open /security/cves.json and /security/notices.json
Check that they both return in reasonable time

Notes

You'll need a database with a significant number of records to replicate the issue (~9000 notices, ~240000 cves)

codecov[bot] commented 2 months ago

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

Project coverage is 85.85%. Comparing base (55311e3) to head (0fd7100). Report is 10 commits behind head on main.

:exclamation: Current head 0fd7100 differs from pull request most recent head 6b7c018

Please upload reports for the commit 6b7c018 to get more accurate results.

Additional details and impacted files

```diff @@ Coverage Diff @@ ## main #146 +/- ## ========================================== + Coverage 85.00% 85.85% +0.85% ========================================== Files 29 29 Lines 1480 1570 +90 ========================================== + Hits 1258 1348 +90 Misses 222 222 ``` | [Flag](https://app.codecov.io/gh/canonical/ubuntu-com-security-api/pull/146/flags?src=pr&el=flags&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=canonical) | Coverage Δ | | |---|---|---| | [python](https://app.codecov.io/gh/canonical/ubuntu-com-security-api/pull/146/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=canonical) | `85.85% <ø> (+0.85%)` | :arrow_up: | Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=canonical#carryforward-flags-in-the-pull-request-comment) to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

hloeung commented 2 months ago

FWIW, there's been a few timeout related issues in the past, some of it being resolved by reducing the query limit. Hopefully this fixes the DB query itself and for good. Here are some:

https://github.com/canonical/ubuntu-com-security-api/issues/129 https://github.com/canonical/ubuntu-com-security-api/issues/101 https://github.com/canonical/ubuntu-com-security-api/issues/59 https://github.com/canonical/ubuntu-com-security-api/issues/31 https://github.com/canonical/ubuntu-com-security-api/issues/20

I'm not sure if we can improve cacheability of these requests as well. This would reduce the backend load and have the fronting content-cache units service requests from their local cache.

sudhackar commented 2 months ago

I suppose this is causing https://github.com/canonical/ubuntu.com/issues/13956

canonical / ubuntu-com-security-api