share server list slow

[Maurice Escher]

Hi,

listing share servers (on xena) is taking too long or even timing out.

$ time manila share-server-list
manila share-server-list 0.98s user 0.28s system 0% cpu 5:16.89 total

I see it in the slow-queries.log of my mariadb: https://paste.opendev.org/show/bWQWZKPi5TIezltlKkx2/

Some more numbers:
select count(id) from share_servers;
1199
select count(id) from network_allocations;
3515
select count(id) from share_instances;
16402
select count(id) from share_instance_export_locations;
42332
select count(id) from share_networks;
367
select count(id) from share_network_subnets;
557

It is always joining share instances and share instance export locations in there
https://opendev.org/openstack/manila/src/branch/stable/xena/manila/db/sqlalchemy/api.py#L4195-L4201
 - I think this is too heavy.

Can you help speeding this up?

Best regards,
Maurice

[Vida Haririan (vhariria)]

This issue may be similar to https://bugs.launchpad.net/manila/+bug/1831094

Additional information is at https://meetings.opendev.org/meetings/manila/2022/manila.2022-03-17-15.00.log.html

[Helena (helenadantas)]

Two scenarios were used to reproduce this bug:

For the first scenario, 1000 shares and 50 shares servers were created (using 50 shares networks). The running time to list the share-servers was:
0.12s user 0.0s system
For the second scenario, 2000 shares and 100 shares servers were created (using 100 shares network). The running time to list the share-servers was:
0.22s user 0.1s system

[OpenStack Infra (hudson-openstack)]

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