Uploaded image for project: 'MusicBrainz Server'
  1. MusicBrainz Server
  2. MBS-10939

Improve Data::Release::find_by_artist to not cause database load issues in production

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2020-08-10
    • Component/s: None
    • Labels:
      None

      Description

      On at least 6/22, 6/29, and 7/6, we've had load issues on floyd due to some app that's been requesting every VA release via the the web service. Here's an example of one such query, directly from the nginx logs:

      https://musicbrainz.org/ws/2/release?artist=89ad4ac3-39f7-470e-963a-56509c546377&inc=genres+recordings+url-rels+release-groups&limit=100&offset=110600&fmt=json

      Our method of pagination, which maps the limit/offset parameters here nearly directly to LIMIT/OFFSET in SQL, is known to be slow, especially with high offsets. But Data::Release::find_by_artist seems to be especially slow due to it having to sort the results by release date and country name. I'd suggest modifying the find_by_artist query to only sort by name to help resolve those performance issues somewhat.

      In addition to the web service, this would change the order on artists' Releases tab. While you can still browse an artist's discography chronologically via the overview, I'd suggest we add options for filtering by date/country to this tab to make up for the lost ordering. Longer-term we'll need to add appropriate materialized tables and indexes to allow fast pagination and sorting here.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bitmap Michael Wiencek
                Reporter:
                bitmap Michael Wiencek
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Packages

                  Version Package
                  2020-08-10