Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Stats query performance #492

Closed
cdausmus opened this issue Oct 28, 2021 · 0 comments
Closed

Stats query performance #492

cdausmus opened this issue Oct 28, 2021 · 0 comments
Assignees
@cdausmus
Labels
Type - improvement Improving an existing feature or functionality, but not as major as an enhancement or new feature.
Milestone
6.1.0

Comments

@cdausmus
Copy link
Collaborator

cdausmus commented Oct 28, 2021 •
edited
Loading

The honourboard stats request is placing a large amount of load on the db when requesting for wildlife spotter or a large project due to the in list clause of projects.

/index/stats?disableHonourBoard=false&disableStats=false&institutionId=-1&maxContributors=5&projectId=-1&projectType=
/index/stats?disableHonourBoard=false&disableStats=false&institutionId=-1&maxContributors=5&projectId=-1&projectType=cameratraps
/index/stats?disableHonourBoard=false&disableStats=false&institutionId=17296011&maxContributors=5&projectId=-1&projectType=

Look at performance measuring using values and a temp table for performance.

Before Christmas break, discussed with Paul and Adam:

Activity feed (Contribution Feed)

  • Remove from:
    -- Institution list
    -- Expedition list
  • At Expedition level, replace expedition activity with forum activity
    -- If no posts, add link to post new thread
  • Is expedition positions causing any high db load?

No of Transcribers

  • start with 60min cache, configured in config file

Institution Admin stats

  • Average transcription time - once per day
  • Hourly contribution - generate once per day

Moved to #499:

Institution Admin stats

  • Change to download (institution level)
    -- transcriptions by day
    -- validations by day
    -- Transcriber tables

Leaderboard

  • Daily, weekly, monthly
  • All time - generate once per day
  • Add note to top 20 page stating timing of updates
@cdausmus cdausmus added this to the 6.1.0 milestone Oct 28, 2021
@cdausmus cdausmus self-assigned this Oct 28, 2021
@cdausmus cdausmus added the Type - improvement Improving an existing feature or functionality, but not as major as an enhancement or new feature. label Oct 28, 2021
cdausmus added a commit that referenced this issue Nov 9, 2021
@cdausmus
#492 Split stats request into two separate asynchronous requests
98631d7
cdausmus added a commit that referenced this issue Nov 9, 2021
@cdausmus
#492 Fixed issue on front page stats
a85e473
cdausmus added a commit that referenced this issue Nov 9, 2021
@cdausmus
#492 Added cache config, extended stats cache to 15mins (from 10)
a126056
cdausmus added a commit that referenced this issue Jan 27, 2022
@cdausmus
#492 Removed latest contributions from most pages to reduce db load
f4ad9b1
cdausmus added a commit that referenced this issue Jan 30, 2022
@cdausmus
#492 Updated stats import to separate forum activity and replace cont…
332e0ec 
…ributions with forum posts
cdausmus added a commit that referenced this issue Jan 30, 2022
@cdausmus
#492 Updated stats cache to 60min expiry
afc8782
cdausmus added a commit that referenced this issue Jan 30, 2022
@cdausmus
#492 Added 1 day cache to admin stats
7650874
cdausmus added a commit that referenced this issue Mar 31, 2022
@cdausmus
#492 Fixed bug where tags broke the query
4120f2e
cdausmus added a commit that referenced this issue Apr 7, 2022
@cdausmus
Merge tag '6.1.0' into develop
7df726c 
DigiVol 6.1 Release Notes

- #387 Fixed issue where text OCR data files could not be uploaded with tasks
- #409 Implemented background saving of task transcriptions
- #469 Updated task locking to not lock the task if it has been fully transcribed
- #481 Created new Audio transcription template and ability to transcribe audio files (e.g. FrogID)
- #487 Added the expedition summary CSV download to the Institution
  Admin menu
- #488 Fixed issue where archiving expedition lost it's institution
- #489 Fixed issue where displaying a randomly selected expedition
  (front page) was resulting in an error
- #490 Fixed issue where new institution admin application notifications
  were not being sent to the applicant
- #492 Improved database performance
- #493 Fixed issue where image burst sequence was being ignored (camera
  trap expeditions)
- #495 Fixed a display issue when saving a transcription
- #496 Changed invalid status to validation - in progress
- #497 Fixed issue where Institution Admins could not delete a user role
  when it was a legacy site-wide role (forum mod or validator)
- #498 Added forum notifications to institution admins
- #500 (linked to #492) Moved project size calculation to on task upload
  (or delete) - manageProjects now static number loaded with page
  instantly
- #504 Fixed issue where transcription score goes backwards
- #507 Fixed issue where no tasks were displayed in user's notebook
cdausmus added a commit that referenced this issue Apr 25, 2022
@cdausmus
#492 Updated query to protect against SQL injection
a40c744
cdausmus added a commit that referenced this issue May 3, 2022
@cdausmus
Merge tag '6.1.1' into develop
603ffb4 
DigiVol 6.1.1 Release Notes

- #481 Added version to wavesurfer JS include and cleaned up unnecessary
  code
- #496 Removed unnecessary code and added caching to UserService calls
- #409 Updated background save variables for production
- #492 Security updates for leaderboard queries
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@cdausmus cdausmus

Labels
Type - improvement Improving an existing feature or functionality, but not as major as an enhancement or new feature.
Projects
None yet
Milestone
6.1.0
Development

No branches or pull requests
1 participant
@cdausmus