pagination does not speed up list queries

[Maurice Escher]

Hi,

let's say I have 500 shares in my project, listing this with all export locations, metadata etc. can take a while (up to 1 min in our production env, subject to missing indices due to postgresql).
So I decide to use pagination to speed up the listing, but this helps nothing at all:

e.g. GET /shares/detail?limit=10 fetches all the 500 shares and related objects and applies the limit to the result set afterwards (Ergo to get all 500 shares in 50 pages, I have to wait 50 times 1 minute worst case).

https://github.com/openstack/manila/blob/0957b33e9b82f30edf630c51f3966b088721ae36/manila/api/v1/shares.py#L170-L177

This is common to all list operations.

Solution idea: get the limit down to the db query. (The way cinder still does it today was thrown away in https://github.com/openstack/manila/commit/f1f8ca0ad17e25af87b511e9d0f555cc43e84f13)

[Goutham Pacha Ravi (gouthamr)]

Thanks for this bug report Maurice.

I haven't done any perf tests against the API, do you think the solution here [1] will be better than what we're doing today? If it does, we might need a volunteer to fix our current "list" APIs to take advantage of the pagination at the DB layer than the API layer. Would you be interested in committing this fix?

[1] https://review.openstack.org/#/c/572283/22/manila/db/sqlalchemy/api.py@2146

[Maurice Escher (maurice-escher)]

Hi Goutham, yes, I think your referenced solution is better.

I may start working on it, but not in the next three months.

[Maurice Escher (maurice-escher)]

Sadly I won't have time this year, feel free to pick this.

[Goutham Pacha Ravi (gouthamr)]

Review is here: https://review.opendev.org/#/c/650986/

[Chuan Miao (chuanmiao)]

current patch takes parameter `limit`, and disregards `offset`. Do you plan to implement the offset in another bug fix?

[Carlos Eduardo (silvacarlose)]

Hi, Chuan! We can plan a implementation to the offset in another bug fix in the future.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/650986
Committed: https://git.openstack.org/cgit/openstack/manila/commit/?id=57edcbd1da4b61ce1a61db3be0d69755d968c36c
Submitter: Zuul
Branch: master

commit 57edcbd1da4b61ce1a61db3be0d69755d968c36c
Author: silvacarloss <email address hidden>
Date: Mon Apr 8 17:33:33 2019 -0300

    Fix pagination does not speed up queries bug

    This patch modifies the database api layer to fix the pagination
    slowness bug, which causes a delay while the administrator tries
    to list shares using the `--limit` option.
    This change was tested in a very busy environment with 800 shares.
    Before the change, the operation took about 25 seconds to be
    finished. Now, the operation takes about 3 seconds in the same
    environment.

    Change-Id: I89659452b0e033631f1318a2eabb7e120c9e5743
    Closes-bug: #1795463

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/stein
Review: https://review.opendev.org/685135

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/manila 9.0.0.0rc1 release candidate.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/685135
Committed: https://git.openstack.org/cgit/openstack/manila/commit/?id=462b0e7461cdd00d51f15aacaa43854aeb0689f4
Submitter: Zuul
Branch: stable/stein

commit 462b0e7461cdd00d51f15aacaa43854aeb0689f4
Author: silvacarloss <email address hidden>
Date: Mon Apr 8 17:33:33 2019 -0300

    Fix pagination does not speed up queries bug

    This patch modifies the database api layer to fix the pagination
    slowness bug, which causes a delay while the administrator tries
    to list shares using the `--limit` option.
    This change was tested in a very busy environment with 800 shares.
    Before the change, the operation took about 25 seconds to be
    finished. Now, the operation takes about 3 seconds in the same
    environment.

    Change-Id: I89659452b0e033631f1318a2eabb7e120c9e5743
    Closes-bug: #1795463
    (cherry picked from commit 57edcbd1da4b61ce1a61db3be0d69755d968c36c)

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/rocky
Review: https://review.opendev.org/688542

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/688542
Committed: https://git.openstack.org/cgit/openstack/manila/commit/?id=bad03feea9a8470c9fe55f1a925d83e026353989
Submitter: Zuul
Branch: stable/rocky

commit bad03feea9a8470c9fe55f1a925d83e026353989
Author: silvacarloss <email address hidden>
Date: Mon Apr 8 17:33:33 2019 -0300

    Fix pagination does not speed up queries bug

    This patch modifies the database api layer to fix the pagination
    slowness bug, which causes a delay while the administrator tries
    to list shares using the `--limit` option.
    This change was tested in a very busy environment with 800 shares.
    Before the change, the operation took about 25 seconds to be
    finished. Now, the operation takes about 3 seconds in the same
    environment.

    Change-Id: I89659452b0e033631f1318a2eabb7e120c9e5743
    Closes-bug: #1795463
    (cherry picked from commit 57edcbd1da4b61ce1a61db3be0d69755d968c36c)
    (cherry picked from commit 462b0e7461cdd00d51f15aacaa43854aeb0689f4)

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/queens
Review: https://review.opendev.org/689604

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/689604
Committed: https://git.openstack.org/cgit/openstack/manila/commit/?id=8263f911beaef26865d30c2cfee760c594175e5e
Submitter: Zuul
Branch: stable/queens

commit 8263f911beaef26865d30c2cfee760c594175e5e
Author: silvacarloss <email address hidden>
Date: Mon Apr 8 17:33:33 2019 -0300

    Fix pagination does not speed up queries bug

    This patch modifies the database api layer to fix the pagination
    slowness bug, which causes a delay while the administrator tries
    to list shares using the `--limit` option.
    This change was tested in a very busy environment with 800 shares.
    Before the change, the operation took about 25 seconds to be
    finished. Now, the operation takes about 3 seconds in the same
    environment.

    Change-Id: I89659452b0e033631f1318a2eabb7e120c9e5743
    Closes-bug: #1795463
    (cherry picked from commit 57edcbd1da4b61ce1a61db3be0d69755d968c36c)
    (cherry picked from commit 462b0e7461cdd00d51f15aacaa43854aeb0689f4)
    (cherry picked from commit bad03feea9a8470c9fe55f1a925d83e026353989)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/manila 6.3.2 release.

[Dmitry Galkin (galkindmitrii)]

Hi All,

Seems like this patch is partially changing the --limit functionality.

When using the list with --limit in current/active project as a normal user that works as expected.
But when using the list with limit as admin with --all-tenants and --project-id we don't get the expected number of shares.

Example - a test project has exactly 1000 shares. Listing with limit works fine:

zproject01_S4 > manila list --limit=1
+--------------------------------------+---------------------------+------+-------------+-----------+-----------+-----------------+------+-------------------+
| ID | Name | Size | Share Proto | Status | Is Public | Share Type Name | Host | Availability Zone |
+--------------------------------------+---------------------------+------+-------------+-----------+-----------+-----------------+------+-------------------+
| e0916e9f-99fd-47ae-a514-e65c6d6ef9a7 | manila_share_network2_497 | 1 | NFS | available | False | | | qa-de-1b |
+--------------------------------------+---------------------------+------+-------------+-----------+-----------+-----------------+------+-------------------+

Now checking the same project as admin:

cloud_admin > manila list --project-id=b25c933351f54df5a9a122b72ba523c8 --all-tenants --limit=1
+----+------+------+-------------+--------+-----------+-----------------+------+-------------------+------------+
| ID | Name | Size | Share Proto | Status | Is Public | Share Type Name | Host | Availability Zone | Project ID |
+----+------+------+-------------+--------+-----------+-----------------+------+-------------------+------------+
+----+------+------+-------------+--------+-----------+-----------------+------+-------------------+------------+

No shares are returned. If no --limit is specified, it works as expected:

manila list --project-id=b25c933351f54df5a9a122b72ba523c8 --all-tenants | wc -l
1004

(1004 lines where 1000 are shares and the rest 4 are from manila cli visual table).

Another example, limit set to 20 shares, but we only get 7 returned:

manila list --project-id=b25c933351f54df5a9a122b72ba523c8 --all-tenants --limit=20
+--------------------------------------+---------------------------+------+-------------+-----------+-----------+-----------------+------------------------------------------------------------------------+-------------------+----------------------------------+
| ID | Name | Size | Share Proto | Status | Is Public | Share Type Name | Host | Availability Zone | Project ID |
+--------------------------------------+---------------------------+------+-------------+-----------+-----------+-----------------+------------------------------------------------------------------------+-------------------+----------------------------------+
| e0916e9f-99fd-47ae-a514-e65c6d6ef9a7 | manila_share_network2_497 | 1 | NFS | available | False | | manila-share-netapp-ma01-md005@netapp-multi#aggr_ssd_stnpa1_02_md005_1 | ...

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/manila 8.1.0 release.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/manila 7.4.0 release.

[Maurice Escher (maurice-escher)]

Hi Dmitry, your issue is being worked on in https://bugs.launchpad.net/manila/+bug/1860061 via https://review.opendev.org/#/c/703025