Archive

Archive for May, 2011

Removing unused filters

May 12th, 2011 Comments off

For manitou-mail installations that use a lot of filters, it may be a good idea to check from time to time which ones are still useful and which ones are unused.
Since all the filters are evaluated for each incoming message (except if a stop action is encountered), keeping around a large number of obsolete filters may have an adverse impact on CPU usage.
Fortunately, manitou-mdx gathers statistics on filter hits, so it’s easy to find out which filters no longer generate any hit, with the help of some SQL.
Let’s start with a query that retrieve filters that never had any hit:

SELECT expr_id,name FROM filter_expr LEFT JOIN filter_log USING (expr_id)
 WHERE filter_log.expr_id IS NULL;

Now it may be that some of the filters returned by this query are new so that no hit on them occurred yet. We need to filter out these by adding a condition on the last_update field, requesting that the filter hasn’t been modified or created as new since at least 3 months.
Also, we only want entries from filter_expr that have actions tied on them, because filters without actions can be used as sub-expressions (that’s advanced filter usage) and don’t generate any hit.
With these additional conditions, the query becomes:

SELECT DISTINCT expr_id,name FROM filter_action JOIN filter_expr USING (expr_id)
 LEFT JOIN filter_log USING (expr_id)
 WHERE filter_log.expr_id IS NULL AND filter_expr.last_update<now()-'3 months'::INTERVAL;

With the query above we can check in advance what we’re about to delete.

Now, the deletion itself needs two steps, one for the filter_action table and another for filter_expr. Since both tables are joined in the query, we need a preliminary step to save the expr_id to delete into a temporay table. The SQL sequence including the transaction is:

BEGIN;
 
CREATE TEMPORARY TABLE del_expr AS
SELECT DISTINCT expr_id FROM filter_action JOIN filter_expr USING (expr_id)
 LEFT JOIN filter_log USING (expr_id)
 WHERE filter_log.expr_id IS NULL
 AND filter_expr.last_update&lt;now()-'3 months'::INTERVAL;
 
DELETE FROM filter_action WHERE expr_id IN (SELECT expr_id FROM del_expr);
 
DELETE FROM filter_expr WHERE expr_id IN (SELECT expr_id FROM del_expr);
 
COMMIT;

To additionally delete the filters that haven’t been used for a significant period of time (for example one year), they could be added to our temporary table before the above deletion:

INSERT INTO del_expr SELECT expr_id
 FROM filter_log GROUP BY expr_id
 HAVING MAX(hit_date)<now()-'1 year'::INTERVAL)

Happy filters cleaning!

Categories: Database, Usage Tags: