poor api performance with postgresql backend

[Mate Lakat]

With a significant number of volumes, and using postgresql as the database backend, we found that the API requests are quite slow. We have around 500k rows in volumes table, also as we mostly boot from volume, 300k rows for volume_glance_metadata.

[Mate Lakat (mate-lakat)]

Doing a tox -esmoke results in these metrics on the cinder database:

cinder=# SELECT relname, seq_scan, idx_scan, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY rows_in_table DESC;
          relname | seq_scan | idx_scan | rows_in_table
----------------------------+----------+----------+---------------
 reservations | 164 | 64 | 180
 volume_glance_metadata | 372 | 0 | 64
 quota_usages | 412 | 133 | 40
 volumes | 571 | 710 | 21
 volume_admin_metadata | 422 | 0 | 15
 volume_metadata | 986 | 0 | 10
 volume_attachment | 1010 | 40 | 8
 quota_classes | 3 | 68 | 6
 services | 753 | 119 | 2
 snapshots | 68 | 56 | 2
 image_volume_cache_entries | 24 | 7 | 1
 volume_types | 91 | 654 | 1
 group_types | 2 | 1 | 1
 workers | 174 | 0 | 1
 volume_type_extra_specs | 1261 | 0 | 1
 migrate_version | 1 | 54 | 1
 group_type_specs | 2 | 0 | 1
 volume_type_projects | 2 | 20 | 0
 group_snapshots | 2 | 0 | 0
 quality_of_service_specs | 53 | 0 | 0
 backups | 1 | 0 | 0
 groups | 2 | 514 | 0
 attachment_specs | 1 | 0 | 0
 encryption | 21 | 0 | 0
 messages | 2 | 0 | 0
 driver_initiator_data | 3 | 0 | 0
 group_volume_type_mapping | 1 | 0 | 0
 snapshot_metadata | 46 | 0 | 0
 consistencygroups | 3 | 514 | 0
 quotas | 115 | 0 | 0
 group_type_projects | 2 | 0 | 0
 transfers | 23 | 0 | 0
 clusters | 45 | 0 | 0
 cgsnapshots | 2 | 0 | 0
(34 rows)

[Mate Lakat (mate-lakat)]

Did a fresh installation with mysql to query the indexes:

mysql> SELECT DISTINCT TABLE_NAME, COLUMN_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'cinder';
+----------------------------+---------------------+----------------------------------------+
| TABLE_NAME | COLUMN_NAME | INDEX_NAME |
+----------------------------+---------------------+----------------------------------------+
| attachment_specs | id | PRIMARY |
| attachment_specs | attachment_id | attachment_id |
| backups | id | PRIMARY |
| cgsnapshots | id | PRIMARY |
| cgsnapshots | consistencygroup_id | consistencygroup_id |
| clusters | id | PRIMARY |
| clusters | name | name |
| clusters | binary | name |
| clusters | race_preventer | name |
| consistencygroups | id | PRIMARY |
| driver_initiator_data | id | PRIMARY |
| driver_initiator_data | initiator | initiator |
| driver_initiator_data | namespace | initiator |
| driver_initiator_data | key | initiator |
| driver_initiator_data | initiator | ix_driver_initiator_data_initiator |
| encryption | encryption_id | PRIMARY |
| group_snapshots | id | PRIMARY |
| group_snapshots | group_id | group_id |
| group_type_projects | id | PRIMARY |
| group_type_projects | group_type_id | group_type_id |
| group_type_projects | project_id | group_type_id |
| group_type_projects | deleted | group_type_id |
| group_type_specs | id | PRIMARY |
| group_type_specs | group_type_id | group_type_id |
| group_types | id | PRIMARY |
| group_volume_type_mapping | id | PRIMARY |
| group_volume_type_mapping | volume_type_id | volume_type_id |
| group_volume_type_mapping | group_id | group_id |
| groups | id | PRIMARY |
| image_...

[Marc Koderer (m-koderer)]

Also Manila is effected. There are no indexes created for postgres, e.g.:

# MySQL
show index from share_instances;
+-----------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| share_instances | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| share_instances | 1 | si_share_fk | 1 | share_id | A | 0 | NULL | NULL | YES | BTREE | | |
| share_instances | 1 | si_share_network_fk | 1 | share_network_id | A | 0 | NULL | NULL | YES | BTREE | | |
| share_instances | 1 | si_share_server_fk | 1 | share_server_id | A | 0 | NULL | NULL | YES | BTREE | | |
| share_instances | 1 | si_az_id_fk | 1 | availability_zone_id | A | 0 | NULL | NULL | YES | BTREE | | |
| share_instances | 1 | si_st_id_fk | 1 | share_type_id | A | 0 | NULL | NULL | YES | BTREE | | |
+-----------------+------------+---------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

# postgres
\d+ share_instances
                                      Table "public.share_instances"
         Column | Type | Modifiers | Storage | Stats target | Description
------------------------+-----------------------------+-----------+----------+--------------+-------------
 created_at | timestamp without time zone | | plain | |
 updated_at | timestamp without time zone | | plain | |
 deleted_at | timestamp without time zone | | plain | |
 deleted | character varying(36) | | extended | |
 id | character varying(36) | not null | extended | |
 share_id | character varying(36) | | extended | |
 host | character varying(255) | | extended | |
 status | character varying(255) | | extended | |
 scheduled_at | timestamp without time zone | | plain | |
 launched_at | timestamp without time zone | | plain...

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/437346

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/437313
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=c72f5ef8e32c4b036fd8c25da9d502bd16b61fc6
Submitter: Jenkins
Branch: master

commit c72f5ef8e32c4b036fd8c25da9d502bd16b61fc6
Author: Mate Lakat <email address hidden>
Date: Thu Feb 23 11:05:47 2017 +0100

    Create indexes for foreign keys

    Some database backends (for example PostgreSQL) do not automatically
    create an index on a foreign key. As a result database queries are slow.
    Adding the missing indexes and a migration that will only add indexes if
    they were not already there.

    In total, 26 foreign keys were identified as missing indexes. 2 of them
    are already covered by UniqueConstraints, for the rest, new indexes have
    been created.

    Closes-Bug: #1666547
    Change-Id: I1437c3a1aa13142ee7a7e3e7bf9ff867b9d72652

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/cinder 11.0.0.0b2 development milestone.

[OpenStack Infra (hudson-openstack)]

Change abandoned by Tom Barron (<email address hidden>) on branch: master
Review: https://review.openstack.org/437346
Reason: No activity on this one since March 2017; restore and rebase if appropriate.

[Tom Barron (tpb)]

I downgraded this one to Medium importance because we don't have active manila users reporting this issue. If it affects you please say so in this bug and we can talk about how to get a fix.

[Maurice Escher (maurice-escher)]

One index for manila was added via https://bugs.launchpad.net/manila/+bug/1776495, adding similar ones for other tables should be possible

[Vida Haririan (vhariria)]

Additional comments http://eavesdrop.openstack.org/meetings/manila/2020/manila.2020-06-11-15.00.log.html

[Vida Haririan (vhariria)]

(Manila) Additional comments at https://meetings.opendev.org/meetings/manila/2022/manila.2022-08-18-15.00.log.html