Slow volume attachment listings with many deleted attachments

[Marcin Wilk]

This LP bug is supplementary to original, already fixed, LP#1952443 [1]. The 'volume_attachment' table by default doesn't contain necessary indexes to effectively list volume attachments. As a result full table scans happen burning resources and taking long time to return results. Especially on the environments with lots of attachments marked as 'deleted'.

Following data comes from the production database where top ten volumes have got tens of thousands attachment records:
select volume_id, count(id) from cinder.volume_attachment group by 1 order by 2 desc limit 10;
+--------------------------------------+-----------+
| volume_id | count(id) |
+--------------------------------------+-----------+
| 11111111-bbbb-2222-cccc-123456abcdef | 27897 |
| 22222222-bbbb-2222-cccc-123456abcdef | 25260 |
| 33333333-bbbb-2222-cccc-123456abcdef | 13602 |
| 44444444-bbbb-2222-cccc-123456abcdef | 13602 |
| 55555555-bbbb-2222-cccc-123456abcdef | 12573 |
| 66666666-bbbb-2222-cccc-123456abcdef | 12573 |
| 77777777-bbbb-2222-cccc-123456abcdef | 12365 |
| 88888888-bbbb-2222-cccc-123456abcdef | 11107 |
| 99999999-bbbb-2222-cccc-123456abcdef | 6294 |
| 00000000-bbbb-2222-cccc-123456abcdef | 6032 |
+--------------------------------------+-----------+

And following are timings for the 'openstack volume attachment list' command:
openstack volume attachment list --project my_project --os-volume-api-version 3.27 --timing
...
+-----------------------------------------------------------------------------------------------------------------------------------------+----------+
| URL | Seconds |
+-----------------------------------------------------------------------------------------------------------------------------------------+----------+
| GET https://keystoneurl:5000/v3 | 0.018852 |
| POST https://keystoneurl:5000/v3/auth/tokens | 0.326822 |
| GET https://keystoneurl:5000/v3/projects?name=my_project | 0.029569 |
| GET https://cinderurl:8776/v3/aaaa1111bbbb2222cccc4444dddd5555/attachments?all_tenants=True&project_id=9999ffff8888eeee7777dddd6666cccc | 7.312965 |
| Total | 7.688208 |
+-----------------------------------------------------------------------------------------------------------------------------------------+----------+

The list attachments cinder API took 7.3s

After creating following index:
create index volume_attachment_deleted_volume_id_idx on volume_attachment (deleted, volume_id);

The timings for the same command looks as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| URL | Seconds |
+-----------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| GET https://keystoneurl:5000/v3 | 0.021972 |
| POST https://keystoneurl:5000/v3/auth/tokens | 0.325399 |
| GET https://keystoneurl:5000/v3/projects?name=my_project | 0.032759 |
| GET https://cinderurl:8776/v3/aaaa1111bbbb2222cccc4444dddd5555/attachments?all_tenants=True&project_id=9999ffff8888eeee7777dddd6666cccc | 0.239103 |
| Total | 0.6192329999999999 |
+-----------------------------------------------------------------------------------------------------------------------------------------+--------------------+

The list attachments cinder API now takes much less time: 0.239s.

These additional two indexes are also needed:
- Speed up attaching a volume:
create index volume_attachment_deleted_instance_uuid_idx on volume_attachment (deleted, instance_uuid);
- Speed up attaching a volume, backup service start, some volume drivers, and the cinder-manage backup update_backup_host cmd:
create index volume_attachment_deleted_attached_host_idx on volume_attachment (deleted, attached_host);

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

[OpenStack Infra (hudson-openstack)]

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