poor api performance with postgresql backend

Bug #1666547 reported by Mate Lakat on 2017-02-21
18
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Cinder
Undecided
Mate Lakat
Manila
Medium
Unassigned

Bug Description

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) wrote :

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) wrote :
Download full text (7.9 KiB)

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_...

Read more...

Marc Koderer (m-koderer) wrote :
Download full text (3.9 KiB)

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...

Read more...

Changed in manila:
importance: Undecided → High
assignee: nobody → Marc Koderer (m-koderer)
Changed in cinder:
assignee: nobody → Mate Lakat (mate-lakat)
status: New → In Progress

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

Changed in manila:
status: New → In Progress

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

Changed in cinder:
status: In Progress → Fix Released

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

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) on 2019-03-17
Changed in manila:
assignee: Marc Koderer (m-koderer) → nobody
Tom Barron (tpb) on 2019-04-25
Changed in manila:
importance: High → Medium
Tom Barron (tpb) wrote :

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.

Jason Grosso (jgrosso) on 2019-08-15
Changed in manila:
status: In Progress → Confirmed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers