"nova usage" taking too much time with many VMs in database

[Antonio Messina]

Issue found on Kilo 2015.1.0 on Ubuntu Trusty (1:2015.1.0-0ubuntu1.1~cloud0) from http://ubuntu-cloud.archive.canonical.com/ubuntu

When running "nova usage" on a tenant that started many instances O(100k) during the current month, the following happens:

* nova-api is stuck at 100% for a long time
* as a consequence, nova CLI returns "ERROR (ConnectionRefused):
Unable to establish connection to ..."
* on MySQL slow query log I see there is a query like:

SELECT instance_system_metadata.created_at AS
instance_system_metadata_created_at,
instance_system_metadata.updated_at AS
instance_system_metadata_updated_at,
instance_system_metadata.deleted_at AS
instance_system_metadata_deleted_at, instance_system_metadata.deleted
AS instance_system_metadata_deleted, instance_system_metadata.id AS
instance_system_metadata_id, instance_system_metadata.`key` AS
instance_system_metadata_key, instance_system_metadata.value AS
instance_system_metadata_value, instance_system_metadata.instance_uuid
AS instance_system_metadata_instance_uuid
FROM instance_system_metadata
WHERE instance_system_metadata.deleted = 0 AND
 instance_system_metadata.instance_uuid IN (<list of ~100k UUID>)

which took 1.8 seconds.

Also, when logging in from Horizon, login is very slow, and I get an error
"Error: Unable to retrieve usage information.".

[Cale Rath (ctrath)]

Have prior instance been "deleted"? When this occurs, the actual data is not removed from the DB, but is soft deleted. There's a patch here that hasn't landed yet to purge soft-deleted instance data: https://review.openstack.org/#/c/203751/

[Markus Zoeller (markus_z) (mzoeller)]

@Zhenzan Zhou:

Are you still actively working on a patch for this bug? If "yes", please provide a patch in Gerrit in the near future, if "no", please remove yourself as assignee.

[stgleb (gstepanov)]

Could you provide table dump? It will allow me reproduce your problem without annoying creating/deleting instances on
my enviroment.

[Attila Fazekas (afazekas)]

The situation can be even worse with the usage-list call (all tenant),
it can permanently grow the memory allocated by the n-api processes by a huge extend (multiple Gigabytes, each worker).

1. The aggregation should be done on the DB side.
2. n-api should not ever to fetch more then osapi_max_limit of things ever.
3. some these statics should be handled by the telemetry service or depending on service which consuming the telemetry data, instead of having nova to this job.

[Attila Fazekas (afazekas)]

The situation can be even worse with the usage-list call (all tenant),
it can permanently grow the memory allocated by the n-api processes by a huge extend (multiple Gigabytes, each worker).

1. The aggregation should be done on the DB side.
2. n-api should not ever fetch more than osapi_max_limit of things
3. most of these statistics should be handled by the telemetry service or depending on service which consuming the telemetry data, instead of having nova to (re)do this job.

[OpenStack Infra (hudson-openstack)]

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

[Sean Dague (sdague)]

Automatically discovered version kilo in description. If this is incorrect, please update the description to include 'nova version: ...'

[OpenStack Infra (hudson-openstack)]

Change abandoned by Sean Dague (<email address hidden>) on branch: master
Review: https://review.openstack.org/343734
Reason: This review is > 4 weeks without comment, and is not mergable in it's current state. We are abandoning this for now. Feel free to reactivate the review by pressing the restore button and leaving a 'recheck' comment to get fresh test results.