"All Files" search is too slow
Categories
(Tecken :: General, defect, P2)
Tracking
(Not tracked)
People
(Reporter: willkg, Assigned: willkg)
Details
Attachments
(2 files)
I was hunting for a specific symbol file today using the All Files filters and it took me several attempts to finally get back an answer. All the failing attempts returned an HTTP 504 which probably means the request took so long that it timed out.
I suspect this is a counts problem again like in bug #1668371. We should nix all the counts and see if that helps.
|
Assignee | |
Comment 1•3 years ago
|
||
This is a big problem. Going to grab this to look into it now.
|
|
Assignee | |
Comment 2•3 years ago
|
||
A while back, when we were looking at bug #1668371, Eduardo tried to set up his local db with data to see if he could reproduce the issue, but he was never able to. That got me wondering whether prod is "broken" in some way. Maybe it was out of sync with what it should be set up? Maybe it's not being maintained?
I had DSRE capture the structure of the database and I compared it with my local database (what Django builds initially) and it's the same, so I don't think it's "busted" in some way or out of sync.
I'll find out how it's being maintained next.
|
|
Assignee | |
Comment 3•3 years ago
|
||
Harold says the RDS was built with the default parameters which includes running the autovacuum daemon. I think that nixes the lack-of-maintenance theory from bug #1668371 comment 17. Sure seems like this is the classic Postgres-and-counts problem.
The last time we ran the cleanse command to remove expired rows from the upload_upload and upload_fileupload tables was November 2022:
cleanse_upload: count before cleansing: upload=232990, fileupload=17321040
cleanse_upload: try=True, cutoff=2022-10-04: deleted upload=24348, fileupload=4239212
cleanse_upload: try=False, cutoff=2020-11-03: deleted upload=42227, fileupload=2271485
real 9m5.139s
user 0m0.889s
sys 0m0.131s
It took around 9 minutes to run and removed 6.5m or 17m rows in the upload_fileupload table. Bug #1668371 comment 17 says on August 9th, 2022 we had 14,590,035 rows in the table. So in 3 months, we accumulated 3m rows.
We want to cleanse the tables on a weekly basis. That's depending on bug #1651415 (2020) because we don't currently have infrastructure to run scheduled tasks. We'll need to wait until we finish the AWS to GCP migration for that.
Next steps:
- run the cleanse scripts in production which should reduce the problem; we should measure count timings before and after so we can see how much it reduces the problem
- continue the work done in bug #1668371 and remove the rest of the counts from the UI; even with that, this will continue to be a problem with the Django admin interface
- look at upgrading from Django 3.2 LTS to 4.2 LTS--is there anything in Django 4.2 that helps us here?
|
|
Assignee | |
Comment 4•3 years ago
|
||
|
|
Assignee | |
Comment 5•3 years ago
|
||
|
|
Assignee | |
Comment 6•3 years ago
|
||
These changes were deployed just now with bug #1840635.
|
|
Assignee | |
Comment 7•3 years ago
|
||
Before cleansing
Table counts:
- upload_fileupload 18,599,924 23.92s
- upload_upload 314,347 3.39s
Timings:
Upload Files page, logged in user with "view symbol uploads" permissions: /api/uploads/files/?page=1&size=>1gb
- took too long--504 error
- 142s
- 141s
From front page, logged in user with "view symbol uploads" permissions: /api/stats/
- 95s
- 93s
- 93s
Cleansing
$ time /app/manage.py cleanse_upload
cleanse_upload:
>>> count before cleansing: upload=314472, fileupload=18605938
Elapsed: count: 26.63s
>>> try=True, cutoff=2023-05-29: deleted upload=47919, fileupload=4364672
Elapsed: delete: 246.46s
>>> try=False, cutoff=2021-06-28: deleted upload=41745, fileupload=2458332
Elapsed: delete: 146.35s
real 7m1.291s
user 0m0.882s
sys 0m0.172s
| when | upload | fileupload |
|---|---|---|
| before cleansing | 314,472 | 18,605,938 |
| after cleansing | 224,808 | 11,782,934 |
| total removed | 89,664 (28%) | 6,823,004 (36%) |
After cleansing
Upload Files page, logged in user with "view symbol uploads" permissions: /api/uploads/files/?page=1&size=>1gb
- 67s
- 82s
- 100s
- 131s
From front page, logged in user with "view symbol uploads" permissions: /api/stats/
- 3.8s
- 3.9s
- 4.1s
Summary
Timings are better now. I think I can punt on the "front page is slow" issue (bug #1840655), but I still need to do something about the uploads pages.
It's also possible the timings get better after vacuum has run. I don't know how often it runs, but maybe it's once a day. We should redo the timings tomorrow just out of curiosity.
|
|
Assignee | |
Comment 8•3 years ago
|
||
|
|
Assignee | |
Comment 9•3 years ago
|
||
willkg merged PR #2753: "bug 1814436: default uploads and files pages to last 30 days" in 6acaaa9.
I want to see how that works on stage and prod, but I think it's good enough that we can move on without making major changes like switching pagination systems.
|
|
Assignee | |
Comment 10•3 years ago
|
||
I did another prod deploy pushing out the changes to restrict to the last 30 days by default for the uploads and uploads files pages. I redid all my timings because I wanted to see if anything changed a day later.
From front page, logged in user with "view symbol uploads" permissions: /api/stats/
- 93s
- 92s
- 91s
That's consistent with the timings I was getting before we cleaned up those tables yesterday. I have no idea why the timings I got after running cleanse were so low. Maybe I screwed up how I was doing the timings, but I'm not sure how I could have done that. Frustrating. 90s is too high, so I'm going to have to look at fixing the front page again.
Upload Files page, logged in user with "view symbol uploads" permissions: /api/uploads/files/?page=1&size=>1gb
- < 1s
- < 1s
- < 1s
I think we can close this bug out and I'll work on bug #1840655 next.
Description
•