Aggregate metrics grouping by a field

[nubeliu_dev]

It will be useful to natively support group by querys (ex: by project_id and/or user_id).

[Julien Danjou (jdanjou)]

Could you detail a bit more what kind of request you'd like to do?

[nubeliu_dev (o-dev-7)]

For example: getting average cpu_util measures aggregated by project_id and then by user_id. That kind of "sql like" queries would be very useful for reporting.

[Julien Danjou (jdanjou)]

Correct me if I'm wrong, but this is essentially a query to get all the cpu_util of all instances for a user or for a group?

Do you want them individually or aggregated?

[Alejandro Comisario (alejandro-f)]

That would be an example, the metrics should allways be agregated, and the GROUP BY sentence might be by either just one field or more.

eg #1
query: give me cpu_util group by tenant
expected result:
{
tenant1: 40,
tenant2: 50,
tenant3: 60,
....,
tenantX: XX
}

eg2 #2
query: give me disk.writes group by tenant, user
expected result:
{

tenant1:user1: 400,
tenant1:user2: 6000,
tenant2:user1: 10,
....
tenantx:usery: 500
}

[Sergio Colinas (sergio-n)]

Are you planning to add this feature to gnocchi? Most tsdb (influxdb, kairosdb, opentsdb) has group by queries built in. I think that this is a big feature missing in gnocchi.

[Julien Danjou (jdanjou)]

Sergio, yes, the bug is still open so it's still in the roadmap. :) We just got busy with other stuff! :)

[Sergio Colinas (sergio-n)]

Thanks Julien, do you have an estimate of when this feature will be available?

[Julien Danjou (jdanjou)]

Not really, let's keep the fingers crossed for the next release :)

[Julien Danjou (jdanjou)]

I've sent a patch at https://review.openstack.org/274682

Feel free to review and test it and let me know what you think!

[OpenStack Infra (hudson-openstack)]

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

[Julien Danjou (jdanjou)]

I meant https://review.openstack.org/274684 obviously.

[Sergio Colinas (sergio-n)]

Thank you Julien, that's great news!
I'll test the patch and send you feedback.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/274684
Committed: https://git.openstack.org/cgit/openstack/gnocchi/commit/?id=6e3a5f9bdd0c3f9fcb741b721e2b9e881bce7287
Submitter: Jenkins
Branch: master

commit 6e3a5f9bdd0c3f9fcb741b721e2b9e881bce7287
Author: Julien Danjou <email address hidden>
Date: Mon Feb 1 14:49:37 2016 +0100

    rest: implement groupby in resource/metric aggregation

    Closes-Bug: #1508446
    Change-Id: I0a32d04948fa9dad12e1175d88301237e53974ee

[Sergio Colinas (sergio-n)]

Hi Julien,

I've been testing group by functionality and I found some issues, I'm getting repeated groups with different measures. For example: the group {'project_id': '62e08edf-df81-4898-8ae7-0568fe8be307', 'flavor_id': '572cdbb9-a21e-40d6-a1ce-abca972609df'} has different measures values.

"POST /v1/aggregation/resource/instance/metric/instance?start=2016-01-01T00:00:00&needed_overlap=0.0&granularity=86400&stop=2016-02-01T00:00:00&aggregation=sum&groupby=project_id&groupby=flavor_id HTTP/1.1" 200 6208
RESP: [200] date: Fri, 19 Feb 2016 18:15:35 GMT content-length: 6208 content-type: application/json; charset=UTF-8 server: Apache/2.4.7 (Ubuntu)
RESP BODY: [{"measures": [], "group": {"project_id": "a046890d-6238-462f-9256-041f003cc06f", "flavor_id": "572cdbb9-a21e-40d6-a1ce-abca972609df"}}, {"measures": [], "group": {"project_id": "8ce5d514-2baf-4dc5-8cf2-8c07ca9e9af9", "flavor_id": "843716b8-1c7a-4522-83f2-836b80a3f11d"}}, {"measures": [], "group": {"project_id": "62e08edf-df81-4898-8ae7-0568fe8be307", "flavor_id": "572cdbb9-a21e-40d6-a1ce-abca972609df"}}, {"measures": [], "group": {"project_id": "62e08edf-df81-4898-8ae7-0568fe8be307", "flavor_id": "82e2154f-7816-4844-97b5-10523e488650"}}, {"measures": [], "group": {"project_id": "62e08edf-df81-4898-8ae7-0568fe8be307", "flavor_id": "572cdbb9-a21e-40d6-a1ce-abca972609df"}}, {"measures": [["2016-01-01T00:00:00+00:00", 86400.0, 1.0], ["2016-01-02T00:00:00+00:00", 86400.0, 1.0], ["2016-01-03T00:00:00+00:00", 86400.0, 1.0], ["2016-01-04T00:00:00+00:00", 86400.0, 1.0], ["2016-01-05T00:00:00+00:00", 86400.0, 1.0], ["2016-01-06T00:00:00+00:00", 86400.0, 1.0], ["2016-01-07T00:00:00+00:00", 86400.0, 1.0], ["2016-01-08T00:00:00+00:00", 86400.0, 1.0], ["2016-01-09T00:00:00+00:00", 86400.0, 1.0], ["2016-01-10T00:00:00+00:00", 86400.0, 1.0], ["2016-01-11T00:00:00+00:00", 86400.0, 1.0], ["2016-01-12T00:00:00+00:00", 86400.0, 1.0], ["2016-01-13T00:00:00+00:00", 86400.0, 1.0], ["2016-01-21T00:00:00+00:00", 86400.0, 1.0], ["2016-01-22T00:00:00+00:00", 86400.0, 1.0], ["2016-01-28T00:00:00+00:00", 86400.0, 1.0]], "group": {"project_id": "8ce5d514-2baf-4dc5-8cf2-8c07ca9e9af9", "flavor_id": "843716b8-1c7a-4522-83f2-836b80a3f11d"}}, {"measures": [["2016-01-01T00:00:00+00:00", 86400.0, 1.0], ["2016-01-02T00:00:00+00:00", 86400.0, 1.0], ["2016-01-03T00:00:00+00:00", 86400.0, 1.0], ["2016-01-04T00:00:00+00:00", 86400.0, 1.0], ["2016-01-05T00:00:00+00:00", 86400.0, 1.0], ["2016-01-06T00:00:00+00:00", 86400.0, 1.0], ["2016-01-07T00:00:00+00:00", 86400.0, 1.0], ["2016-01-08T00:00:00+00:00", 86400.0, 1.0], ["2016-01-09T00:00:00+00:00", 86400.0, 1.0], ["2016-01-10T00:00:00+00:00", 86400.0, 1.0], ["2016-01-11T00:00:00+00:00", 86400.0, 1.0], ["2016-01-12T00:00:00+00:00", 86400.0, 1.0], ["2016-01-13T00:00:00+00:00", 86400.0, 1.0], ["2016-01-21T00:00:00+00:00", 86400.0, 1.0], ["2016-01-22T00:00:00+00:00", 86400.0, 1.0], ["2016-01-28T00:00:00+00:00", 86400.0, 1.0]], "group": {"project_id": "62e08edf-df81-4898-8ae7-0568fe8be307", "flavor_id": "572cdbb9-a21e-40d6-a1ce-abca972609df"}}, {"measures": [], "group": {"project_id": "62e08edf-df81-4898-8ae7-0568fe8be307", "flavor_id": "82e2154f-7816-4844-97b5-10523e488650"}}, {"measur...

[Julien Danjou (jdanjou)]

Hi Sergio,

I'm trying to make sense of that, but I can't. I mean that should not be possible :(

The resources are sorted by (flavor_id, project_id, id) and then groupped by (flavor_id, project_id). In your data dump nothing is sorted correctly, so I'm completely lost. :-/

If you have more info, could you open a new bug report with more stuff? We could also try a few debug tricks maybe.

[Sergio Colinas (sergio-n)]

Hi Julien,

We've updated gnocchi to stable/2.0 and it is working fine by now :) It
seems that there were some patches missing.

Thanks a lot for your help!

On Tue, Mar 8, 2016 at 7:10 AM, Julien Danjou <email address hidden>
wrote:

> Hi Sergio,
>
> I'm trying to make sense of that, but I can't. I mean that should not be
> possible :(
>
> The resources are sorted by (flavor_id, project_id, id) and then
> groupped by (flavor_id, project_id). In your data dump nothing is sorted
> correctly, so I'm completely lost. :-/
>
> If you have more info, could you open a new bug report with more stuff?
> We could also try a few debug tricks maybe.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1508446
>
> Title:
> Aggregate metrics grouping by a field
>
> Status in Gnocchi:
> Fix Released
>
> Bug description:
> It will be useful to natively support group by querys (ex: by
> project_id and/or user_id).
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/gnocchi/+bug/1508446/+subscriptions
>

--
*Sergio Colinas*
*Software Architect | NUBELIU*
E-mail: <email address hidden> Cell: [phone]
_
www.nubeliu.com