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 database to do efficient retrieval of records for listings.
The reason why we order first by deleted and then by project_id is
because when an admin does a listing with `--all-tenants` that query
will be able to use the deleted table of the new compound index.
We can see the new index this patch adds and how it allows the DB engine
to efficiently retrieve non deleted volumes from the specific project.
Reviewed: https:/ /review. opendev. org/c/openstack /cinder/ +/819669 /opendev. org/openstack/ cinder/ commit/ bbe42df26ca9df9 ee09e6885d9b777 259a888dcd
Committed: https:/
Submitter: "Zuul (22348)"
Branch: master
commit bbe42df26ca9df9 ee09e6885d9b777 259a888dcd
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, snapshots) don't have required indexes to do efficient resource
group_
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 ------- ------- ------- ------- ------- -+----- ------+ ------+ ------+ ------- ------+ ------- ---+--- ------- ---+ ------- ------- ------- ------- ------- -+----- ------+ ------+ ------+ ------- ------+ ------- ---+--- ------- ---+ 3343-4c0d- 8a64-8e7070d198 8e | available | test | 1 | lvmdriver-1 | false | | ------- ------- ------- ------- ------- -+----- ------+ ------+ ------+ ------- ------+ ------- ---+--- ------- ---+
+--
| ID | Status | Name | Size | Volume Type | Bootable | Attached to |
+--
| 8a6b11d5-
+--
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="a41464e5412 5407aab09e0236c ce2c3c" ' ------- ------- ------- ------- ------- -+----- ------- --+ ------- ------- ------- ------- ------- -+----- ------- --+ 3343-4c0d- 8a64-8e7070d198 8e | test | ------- ------- ------- ------- ------- -+----- ------- --+
+--
| id | display_name |
+--
| 8a6b11d5-
+--
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="a41464e5412 5407aab09e0236c ce2c3c" ' ----+-- ------- ----+-- ------- +------ +------ ------- --+---- --+---- -----+- -----+- ------+ ------- ------+ ----+-- ------- ----+-- ------- +------ +------ ------- --+---- --+---- -----+- -----+- ------+ ------- ------+ ----+-- ------- ----+-- ------- +------ +------ ------- --+---- --+---- -----+- -----+- ------+ ------- ------+
+--
| 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 database to do efficient retrieval of records for listings.
The reason why we order first by deleted and then by project_id is
because when an admin does a listing with `--all-tenants` that query
will be able to use the deleted table of the new compound index.
We can see the new index this patch adds and how it allows the DB engine
to efficiently retrieve non deleted volumes from the specific project.
$ mysql cinder -e 'show index from volumes' ------- +------ ------+ ------- ------- ------- ------- ----+-- ------- -----+- ------- ------- ------+ ------- ----+-- ------- ----+-- ------- -+----- ---+--- ---+--- ------- --+---- -----+- ------- ------- + ------- +------ ------+ ------- ------- ------- ------- ----+-- ------- -----+- ------- ------- ------+ ------- ----+-- ------- ----+-- ------- -+----- ---+--- ---+--- ------- --+---- -----+- ------- ------- + service_ uuid_idx | 1 | service_uuid | A | 1 | NULL | NULL | YES | BTREE | | | service_ uuid_idx | 2 | deleted | A | 1 | NULL | NULL | YES | BTREE | | | consistencygrou p_id | 1 | consistencygroup_id | A | 1 | NULL | NULL | YES | BTREE | | | deleted_ project_ id_idx | 1 | deleted | A | 1 | NULL | NULL | YES | BTREE | | | deleted_ project_ id_idx | 2 | project_id | A | 1 | NULL | NULL | YES | BTREE | | | deleted_ host_idx | 1 | deleted | A | 1 | NULL | NULL | YES | BTREE | | | deleted_ host_idx | 2 | host | A | 1 | NULL | NULL | YES | BTREE | | | ------- +------ ------+ ------- ------- ------- ------- ----+-- ------- -----+- ------- ------- ------+ ------- ----+-- ------- ----+-- ------- -+----- ---+--- ---+--- ------- --+---- -----+- ------- ------- +
+--
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--
| volumes | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| volumes | 1 | volumes_
| volumes | 1 | volumes_
| volumes | 1 | ix_volumes_
| volumes | 1 | ix_volumes_group_id | 1 | group_id | A | 1 | NULL | NULL | YES | BTREE | | |
| volumes | 1 | volumes_
| volumes | 1 | volumes_
| volumes | 1 | volumes_
| volumes | 1 | volumes_
+--
$ mysql cinder -e 'explain select id, display_name from volumes where not deleted and project_ id="a41464e5412 5407aab09e0236c ce2c3c" ' ----+-- ------- ----+-- ------- +------ +------ ------- ------- ------- -----+- ------- ------- ------- ------- ---+--- ------+ ------- ------+ ------+ ------- ------- ------- --+ ----+-- ------- ----+-- ------- +------ +------ ------- ------- ------- -----+- ------- ------- ------- ------- ---+--- ------+ ------- ------+ ------+ ------- ------- ------- --+ deleted_ project_ id_idx | volumes_ deleted_ project_ id_idx | 770 | const,const | 1 | Using index condition | ----+-- ------- ----+-- ------- +------ +------ ------- ------- ------- -----+- ------- ------- ------- ------- ---+--- ------+ ------- ------+ ------+ ------- ------- ------- --+
+--
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+--
| 1 | SIMPLE | volumes | ref | volumes_
+--
We also add another missing index for the volumes that is used by the
create volume from image.
The patch also updates 3 tests that were expecting the result from a
query to be in a specific order when there is no actual ORDER BY in the
query.
Closes-Bug: #1952443 ada76874dc0c4f5 9542e1c03ab
Change-Id: I8456a9f82bdf18