Pagination does not speed up list queries for some operations

[Carlos Eduardo]

The pagination is slow for some operations in Manila. It happens when an administrator has a busy environment with a great amount of resources.

The affected operations are:
  - Snapshot list
  - Share group list
  - Share group snapshot list
  - Message list

Steps:
  - Create more than 400 items for each one of the above mentioned resources.
  - List the resources for one of the above mentioned items using '--limit 10'

After the list command, the results for this operation should take few seconds to be returned, but actually, it spends at least 20 seconds.

[Jason Grosso (jgrosso)]

Hey, Carlos any update on this bug, any help needed?

[Carlos Eduardo (silvacarlose)]

Hi, Jason. I haven't had time to work on a fix for this bug yet. It is on my TODO list and I intend to provide a fix for it ASAP.

[Maurice Escher (maurice-escher)]

I'm interested in getting this especially for snapshot list.

Please consider https://bugs.launchpad.net/manila/+bug/1868056 first ;)

[kiran pawar (kpdev)]

Using devstack(manila master at commit dcdc1a98bb2e0027f87184eee0080283f9b2ae33), I created 400 snapshots, share-groups and share-group-snapshots. Tried running various list options, all queries ran within 2-3 seconds. Might be bug is not reproducible, need to confirm by someone else.

[Carlos Eduardo (silvacarlose)]

Hi, Kiran! I have once managed to reproduce this issue in a very busy environment while specifying '--limit' in my queries, I guess Maurice also hits it in a daily basis, considering his comment [1].
Do you have the list of commands or the scenarios you've tried?

[1] https://bugs.launchpad.net/manila/+bug/1831094/comments/3

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/manila/+/734041
Committed: https://opendev.org/openstack/manila/commit/e032c13bfb0d2ee2bc3c6d4b2adda27b6759a14b
Submitter: "Zuul (22348)"
Branch: master

commit e032c13bfb0d2ee2bc3c6d4b2adda27b6759a14b
Author: haixin <email address hidden>
Date: Mon Jun 8 17:08:51 2020 +0800

    Add generic fuzzy matching logic to the database layer

    share snap list, Fuzzy query by name or description is supported, but
    the current implementation is first get all the shares, then In the API
    layer, the for loop is used to achieve fuzzy matching, if the num of
    shares is big,
    It will seriously affect the speed of fuzzy matching.Therefore, we
    should let the database do the matching to speed up the query.
    Moving the pagination params (limit, offset, sorting) to the database
    layer for snapshot list, to optimize query speed.

    Closes-Bug:#1881865
    Partial-Bug:#1831094

    Change-Id: I283e78c9e7c2dd626d94cf6c1b01d4e2f9ae8097