currently, when we call query-samples API, for sql backend, we do a inner join to get a temp table, then apply filters, orderby and limit options on it, which will drain disk space or timeout exception when data set is growing.
my test shows that timeout exception raises even when sample table row is 500K, even limit=1 is specified, and there is only one request is processing.
for i.e., the sql statement generated for "ceilometer query-samples --limit 1" is:
SELECT anon_1.id AS anon_1_id, anon_1.counter_name AS anon_1_counter_name, anon_1.counter_type AS anon_1_counter_type, anon_1.counter_unit AS anon_1_counter_unit, anon_1.counter_volume AS anon_1_counter_volume, anon_1.resource_id AS anon_1_resource_id, anon_1.source_id AS anon_1_source_id, anon_1.user_id AS anon_1_user_id, anon_1.project_id AS anon_1_project_id, anon_1.resource_metadata AS anon_1_resource_metadata, anon_1.internal_id AS anon_1_internal_id, anon_1.timestamp AS anon_1_timestamp, anon_1.message_id AS anon_1_message_id, anon_1.message_signature AS anon_1_message_signature, anon_1.recorded_at AS anon_1_recorded_at
FROM (SELECT sample.id AS id, meter.name AS counter_name, meter.type AS counter_type, meter.unit AS counter_unit, sample.volume AS counter_volume, resource.resource_id AS resource_id, resource.source_id AS source_id, resource.user_id AS user_id, resource.project_id AS project_id, resource.resource_metadata AS resource_metadata, resource.internal_id AS internal_id, sample.timestamp AS timestamp, sample.message_id AS message_id, sample.message_signature AS message_signature, sample.recorded_at AS recorded_at
FROM sample INNER JOIN meter ON sample.meter_id = meter.id INNER JOIN resource ON sample.resource_id = resource.internal_id) AS anon_1 ORDER BY anon_1.timestamp DESC
LIMIT 1
actually, it should be:
SELECT sample.id AS id, meter.name AS counter_name, meter.type AS counter_type, meter.unit AS counter_unit, sample.volume AS counter_volume, resource.resource_id AS resource_id, resource.source_id AS source_id, resource.user_id AS user_id, resource.project_id AS project_id, resource.resource_metadata AS resource_metadata, resource.internal_id AS internal_id, sample.timestamp AS timestamp, sample.message_id AS message_id, sample.message_signature AS message_signature, sample.recorded_at AS recorded_at
FROM sample INNER JOIN meter ON sample.meter_id = meter.id INNER JOIN resource ON sample.resource_id = resource.internal_id ORDER BY sample.timestamp DESC
LIMIT 1
the second one can response in less than 10 seconds instead of timeout (10 minutes)
query-alarms doesn't have such issue because its doesn't generate a temp table
Fix proposed to branch: master
Review: https:/