ceilometer expirer results in regular bursts of slow queries

Bug #1424871 reported by Rohit Jaiswal
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Ceilometer
Fix Released
Medium
Rohit Jaiswal

Bug Description

mysql slow logs indicate this -

    Time: 150218 16:31:27
    User@Host: ceilometer[ceilometer] @ [10.23.32.202]
    Thread_id: 676596 Schema: ceilometer Last_errno: 0 Killed: 0
    Query_time: 33.133080 Lock_time: 0.107213 Rows_sent: 0 Rows_examined: 1578134 Rows_affected: 0 Rows_read: 1578134
    Bytes_sent: 11
    SET timestamp=1424277087;
    DELETE FROM resource WHERE NOT (EXISTS (SELECT 1
    FROM sample
    WHERE resource.internal_id = sample.resource_id));

Observation/Alternatives:

 1. Due to the nature of query we have to do full table scan of resource table.
 2. One alternative is to reduce the frequency of resource and associated Meta* table deletions say once in a day. Since the resources are more or less constant. We don't need to delete the resources so frequently.

       So we will have to modify the expirer job to delete only samples most of the time but delete the resources less frequently (once in a week)

Currently, the expirer deletes all the metering tables with each run of the expirer, the idea is to separate the execution of the samples (more frequent) and the other tables (Meter, Resource and Meta* tables) to be less frequent.

Changed in ceilometer:
assignee: nobody → Rohit Jaiswal (rohit-jaiswal-3)
description: updated
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to ceilometer (master)

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

Changed in ceilometer:
status: New → In Progress
Eoghan Glynn (eglynn)
Changed in ceilometer:
milestone: none → kilo-3
gordon chung (chungg)
Changed in ceilometer:
importance: Undecided → Medium
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to ceilometer (master)

Reviewed: https://review.openstack.org/158519
Committed: https://git.openstack.org/cgit/openstack/ceilometer/commit/?id=5893f401e30acfb8a9b3aaab7a7c8b7458cb773d
Submitter: Jenkins
Branch: master

commit 5893f401e30acfb8a9b3aaab7a7c8b7458cb773d
Author: Rohit Jaiswal <email address hidden>
Date: Mon Feb 23 16:24:47 2015 -0800

    Added option to allow sample expiration more frequently

    Added a new CLI param - sql-expire-samples-only
    to ceilometer-expirer. This will allow the
    expirer to expire samples only, since samples
    have more volume and velocity, thus allowing
    sample expiration more frequently than other
    metering data (meters, resources and metadata).

    DocImpact

    Closes-Bug: #1424871

    Change-Id: I9b3081a3bcc1a24dd7ad7706e76a886af7185af4

Changed in ceilometer:
status: In Progress → Fix Committed
Thierry Carrez (ttx)
Changed in ceilometer:
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in ceilometer:
milestone: kilo-3 → 2015.1.0
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.