Uploaded image for project: 'mod-source-record-storage'
  1. mod-source-record-storage
  2. MODSOURCE-47

Slow queries with 25k source records

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      EPAM BatchLoader Sprint 14, EPAM BatchLoader Sprint 15
    • Story Points:
      5
    • Development Team:
      Folijet

      Description

      Overview:
      We're experiencing very slow queries against the GET /source-storage/sourceRecords endpoint, especially when offset > 0

      Reproducer:

      1. Populate SRS (roughly 25k records in our case)
      2. Query the sourceRecords API
        $ time curl "$OKAPI/source-storage/sourceRecords?query=recordType==MARC&limit=50&offset=0" -H "X-Okapi-Token: $TOKEN" -o /dev/null -s
        
        real	0m5.892s
        user	0m0.152s
        sys	0m0.048s
        $ time curl "$OKAPI/source-storage/sourceRecords?query=recordType==MARC&limit=50&offset=50" -H "X-Okapi-Token: $TOKEN" -o /dev/null -s
        
        real	0m7.593s
        user	0m0.168s
        sys	0m0.052s
        $ time curl "$OKAPI/source-storage/sourceRecords?query=recordType==MARC&limit=50&offset=100" -H "X-Okapi-Token: $TOKEN" -o /dev/null -s
        
        real	0m8.897s
        user	0m0.148s
        sys	0m0.028s
        $ time curl "$OKAPI/source-storage/sourceRecords?query=recordType==MARC&limit=50&offset=150" -H "X-Okapi-Token: $TOKEN" -o /dev/null -s
        
        real	0m29.354s
        user	0m0.204s
        sys	0m0.024s
        $ time curl "$OKAPI/source-storage/sourceRecords?query=recordType==MARC&limit=50&offset=200" -H "X-Okapi-Token: $TOKEN" -o /dev/null -s
        
        real	0m33.992s
        user	0m0.120s
        sys	0m0.044s
        $ time curl "$OKAPI/source-storage/sourceRecords?query=recordType==MARC&limit=50&offset=250" -H "X-Okapi-Token: $TOKEN" -o /dev/null -s
        
        real	0m40.831s
        user	0m0.144s
        sys	0m0.052s
        

      Note the increasing response times as the offset increases
      Also note the large jump between 100 and 150

      We've seen this behavior when hitting the database directly...e.g.

      SELECT * FROM sometenant_mod_source_record_storage.source_records_view  WHERE (lower(f_unaccent(source_records_view.jsonb->>‘recordType’)) LIKE lower(f_unaccent(‘MARC’))) LIMIT 50 OFFSET 200
      

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                Anatolii Starkov Anatolii Starkov
                Reporter:
                cmcnally Craig McNally
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases