Concurrent requests to quotas reserve sometimes lock db table

[Alexei Kornienko]

Concurrent requests to quotas reserve sometimes lock db table significantly degrading performance.

Steps to reproduce:
1) Create 3-5 instances using devstack
2) Wait until all instances are in ACTIVE state
3) Terminate all instances simultaneously

delete operation executes following SQL query to release instance quotas:

SELECT quota_usages.created_at AS quota_usages_created_at, quota_usages.updated_at AS quota_usages_updated_at, quota_usages.deleted_at AS quota_usages_deleted_at, quota_usages.deleted AS quota_usages_deleted, quota_usages.id AS quota_usages_id, quota_usages.project_id AS quota_usages_project_id, quota_usages.user_id AS quota_usages_user_id, quota_usages.resource AS quota_usages_resource, quota_usages.in_use AS quota_usages_in_use, quota_usages.reserved AS quota_usages_reserved, quota_usages.until_refresh AS quota_usages_until_refresh FROM quota_usages WHERE quota_usages.deleted = :deleted_1 AND quota_usages.project_id = :project_id_1 AND (quota_usages.user_id = :user_id_1 OR quota_usages.user_id IS NULL) FOR UPDATE

Sometimes this query can block for 50 seconds

Reprorate ~ 20%

[Alexei Kornienko (alexei-kornienko)]

2nd trace attached:

Seems that instance A is doing block_device_destroy and instance B has to wait until it finishes.

[jiang, yunhong (yunhong-jiang)]

you mean instance B's quota reserve has to wait till the block_device_destroy finished and thus cause a long time waiting?

[Alexei Kornienko (alexei-kornienko)]

Yes. That's what I see.

[jiang, yunhong (yunhong-jiang)]

But the lock for the quota reserve happens only in the session, strange why it will be impacted by the block_device_destroy. Are you collect the performance using single system, so that both the block_device_destroy and the database has intensive disk access?

[Alexei Kornienko (alexei-kornienko)]

Hi, yes it's a single node devstack installation. However I don't think that this is the source of the problem cause it has enough ram and db fits in memory.

[Joe Gordon (jogo)]

I added a test for this into the gating large ops test: https://review.openstack.org/56123

[Joe Gordon (jogo)]

I just tried to reproduce this here https://review.openstack.org/#/c/56123/ and wasn't able to. Perhaps this has been fixed or something is wrong with my test.

Since I cannot confirm this bug, ,marking this bug as incomplete. If you are still able to reproduce this please move the bug back to confirmed, and hopefully tell me what I am doing wrong.