get_meter_statistics with query is unacceptably slow on sqlalchemy

[Ondergetekende]

The existing queries rely heavilly on extremely greedy filtered subselects, which result in horrible perfomance. Rewriting these subselects to joins resulted in a 10000x performance increase for us (yes, that's ten thousand).

[Ondergetekende (kvdveer)]

Original query:

SELECT
    meter.unit AS meter_unit,
    min(sample.timestamp) AS tsmin,
    max(sample.timestamp) AS tsmax,
    count(sample.volume) AS count,
    max(sample.volume) AS max,
    sum(sample.volume) AS sum,
    avg(sample.volume) AS avg,
    min(sample.volume) AS min,
    sample.resource_id AS sample_resource_id
FROM meter,
    sample,
    (SELECT
        metadata_text.id AS id,
        metadata_text.meta_key AS meta_key,
        metadata_text.value AS value
    FROM metadata_text
    WHERE metadata_text.meta_key = 'network_id'
    AND metadata_text.value = '00000000-0000-0000-0000-000000000000') AS anon_1,
    (SELECT
        metadata_text.id AS id,
        metadata_text.meta_key AS meta_key,
        metadata_text.value AS value
    FROM metadata_text
    WHERE metadata_text.meta_key = 'tenant_id'
    AND metadata_text.value = '49ea53c44f8340dbb990471dcf700288') AS anon_2
WHERE meter.id = sample.meter_id
AND meter.name = 'port'
AND sample.timestamp >= '1400364000'
AND sample.timestamp < '1400436093'
AND sample.id = anon_1.id
AND sample.id = anon_2.id
GROUP BY sample.resource_id;

-- 494 rows in set (6 min 12.59 sec)

After manually rewriting the subselects to joins:

SELECT
    meter.unit AS meter_unit,
    min(sample.timestamp) AS tsmin,
    max(sample.timestamp) AS tsmax,
    count(sample.volume) AS count,
    max(sample.volume) AS max,
    sum(sample.volume) AS sum,
    avg(sample.volume) AS avg,
    min(sample.volume) AS min,
    sample.resource_id AS sample_resource_id
FROM meter
INNER JOIN sample on (meter.id = sample.meter_id)
INNER JOIN metadata_text mtn on (mtn.id = sample.id)
INNER JOIN metadata_text mtt on (mtt.id = sample.id)
WHERE mtn.meta_key = 'network_id'
AND mtn.value = '00000000-0000-0000-0000-000000000000'
AND mtt.meta_key = 'tenant_id'
AND mtt.value = '49ea53c44f8340dbb990471dcf700288'
AND meter.name = 'port'
AND sample.timestamp >= '1400364000'
AND sample.timestamp < '1400436093'
GROUP BY sample.resource_id;

-- 494 rows in set (0.06 sec)

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/94325
Committed: https://git.openstack.org/cgit/openstack/ceilometer/commit/?id=578e1450d0724fec216946e316ec7303b9fd7324
Submitter: Jenkins
Branch: master

commit 578e1450d0724fec216946e316ec7303b9fd7324
Author: Koert van der Veer <email address hidden>
Date: Tue May 20 10:53:30 2014 +0200

    Use joins instead of subqueries for metadata filtering

    Previously, Ceilometer used subqueries to apply metadata filters. These
    subqueries, however, defeated MySQL's query optimizer, leading to absurdly
    bad performance. In our production setup, this patch improved performance on
    two metadata filters by up to 10000x.

    Change-Id: Ie3bea14d05fb63a5565e2d5cced74068955b8f08
    Closes-bug: #1320772