Slow listings with many projects and deleted resources

[Gorka Eguileor]

Cinder's resource tables (volumes, snapshots, backups, groups) don't have required indexes to do efficient resource listings on the database engine.

This forces the database to go through all existing database records for any listing (even when there are no additional user requested filtering) and check one by one the conditions.

As an example a simple listing for a project with a single volume:

[vagrant@localhost devstack]$ cinder list
+--------------------------------------+-----------+------+------+-------------+----------+-------------+
| ID | Status | Name | Size | Volume Type | Bootable | Attached to |
+--------------------------------------+-----------+------+------+-------------+----------+-------------+
| 8a6b11d5-3343-4c0d-8a64-8e7070d1988e | available | test | 1 | lvmdriver-1 | false | |
+--------------------------------------+-----------+------+------+-------------+----------+-------------+

May result in the database going through thousand of records (all deleted records and all records for other projects) as demonstrated by the following SQL queries where 10435 rows were checks to return a single on:

[vagrant@localhost ~]$ mysql cinder -e 'select id, display_name from volumes where not deleted and project_id="a41464e54125407aab09e0236cce2c3c"'
+--------------------------------------+--------------+
| id | display_name |
+--------------------------------------+--------------+
| 8a6b11d5-3343-4c0d-8a64-8e7070d1988e | test |
+--------------------------------------+--------------+

[vagrant@localhost ~]$ mysql cinder -e 'explain select id, display_name from volumes where not deleted and project_id="a41464e54125407aab09e0236cce2c3c"'
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | volumes | ALL | NULL | NULL | NULL | NULL | 10435 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+-------+-------------+

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/cinder/+/819669

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/cinder/+/819669
Committed: https://opendev.org/openstack/cinder/commit/bbe42df26ca9df9ee09e6885d9b777259a888dcd
Submitter: "Zuul (22348)"
Branch: master

commit bbe42df26ca9df9ee09e6885d9b777259a888dcd
Author: Gorka Eguileor <email address hidden>
Date: Fri Nov 26 10:38:47 2021 +0100

    Improve resource listing efficiency

    Cinder's resource tables (volumes, snapshots, backups, groups,
    group_snapshots) don't have required indexes to do efficient resource
    listings on the database engine.

    This forces the database to go through all existing database records for
    any listing (even when there are no additional user requested filtering)
    and check one by one the conditions, resulting in high CPU load on the
    database servers.

    As an example a listing for a project with a single volume:

    $ cinder list
    +--------------------------------------+-----------+------+------+-------------+----------+-------------+
    | ID | Status | Name | Size | Volume Type | Bootable | Attached to |
    +--------------------------------------+-----------+------+------+-------------+----------+-------------+
    | 8a6b11d5-3343-4c0d-8a64-8e7070d1988e | available | test | 1 | lvmdriver-1 | false | |
    +--------------------------------------+-----------+------+------+-------------+----------+-------------+

    May result in the database going through thousand of records (all
    deleted records and all records for other projects), as demonstrated by
    the following SQL queries where 10435 rows existed in the database and
    had to be checked just to return a single one.

    This is the SQL equivalent of the earlier cinder list command:

    $ mysql cinder -e 'select id, display_name from volumes where not deleted and project_id="a41464e54125407aab09e0236cce2c3c"'
    +--------------------------------------+--------------+
    | id | display_name |
    +--------------------------------------+--------------+
    | 8a6b11d5-3343-4c0d-8a64-8e7070d1988e | test |
    +--------------------------------------+--------------+

    Which if we look at the numbers of rows that it hits with `explain` we
    can see it hits every single row:

    $ mysql cinder -e 'explain select id, display_name from volumes where not deleted and project_id="a41464e54125407aab09e0236cce2c3c"'
    +------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +------+-------------+---------+------+---------------+------+---------+------+-------+-------------+
    | 1 | SIMPLE | volumes | ALL | NULL | NULL | NULL | NULL | 10435 | Using where |
    +------+-------------+---------+------+---------------+------+---------+------+-------+-------------+

    This patch introduces a deleted and project_id index for the volumes,
    snapshots, groups, group_snapshots, and backups tables, which will allow
    the da...

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/cinder 22.0.0.0rc1 release candidate.

[Marcin Wilk (wilkmarcin)]

Created supplementary LP#2058360 [1] for the missing indexes in the volume_attachment table.

[1] https://bugs.launchpad.net/cinder/+bug/2058360