User:Gibi

Horizon instance statistic query: pastebin horizon pdf

• fact1: "instance" meter reports 1 if the instance is _allocated_ regardless of the state of the instance. Except if the VM is in error state, in that case "instance" meter does not report sample.
• fact2: ceilometer does not have samples from the "instance" meter with counter_volume 0 for deleted instances obviously
• fact3: the state of the instance is not part of the metadata of the "instance" sample

Problem 1

So first you want to calculate avg(counter_volume) grouped by resource_id then make a sum(counter_volume) grouped by project_id. The avg(counter_volume) will produce invalid result for example if we have 6 measurement in an hour but the instance was running only half of the time, as we will have only 3 samples instead of 6 so the avg will be 1 instead of 1/2 due to fact2. If it is not a problem to get 1 instead of 1/2 for this case then you can use distinct instead of avg. The other possibility is to make the measurement period available on the API and just get sum(counter_volume) from the statistics API and then on the client side calculate the avg for the known period.

Problem 2

Your query with two group bys cannot be expressed in one SELECT statement. We have to translate the first avg (or distinct) step to a subquery of the second step. Our original idea for complex query statistic will allow multiple group bys but we haven't planned to allow such group bys that needs subqueries.

Problem with DISTINCT

SELECT count(*), project_id FROM ( SELECT DISTINCT resource_id, project_id FROM Meter) GROUP BY project_id

SELECT count(*), project_id FROM (SELECT avg(counter_volume), resource_id, project_id FROM Meter GROUP BY resource_id, project_id) GROUP BY project_id

==>>

SELECT count(DISTINCT(resource_id, project_id)) FROM Meter GROUP BY project_id

• Only the count function is meaningful
• Only the instance meter can be aggregated this way
• Only the resource_id, project_id seems a valid DISTINCT parameter ???