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.
Fix proposed to branch: master
Review: https:/