Mailing List Archive: DBMail: users

Pruning Header table in DBmail 3

Index | Next | Previous | View Threaded

daniel at cwa

Jul 28, 2013, 10:35 AM

Post #1 of 2 (60 views)
Permalink

Pruning Header table in DBmail 3

(Very sorry if this is a duplication, but I sent this on 26/07 and it never
appeared in the digests I receive, so I assume it went astray - I've now
completed the migration btw and the dump/restore took about 9 hours).

I’m part way of through a migration from dbmail2.17 to 3.02 and I’ve gotten
the stage where I need to perform a mysql dump/restore (no idea how long
that will take with 150gb data; I’m hoping less than 48 hours) to reclaim
the space from my innodb file.

Before I do that however I thought it couldn’t hurt to prune some of the
dbmail_header table as I would have for dbmail2.2.x

Having deleted some likely headers by

SELECT * FROM dbmail_headername where headername = "mime-version"

Etc

I noticed dbmail_headervalue wasn’t decreasing in size and the query

SELECT * from dbmail_headervalue where id not in (select headervalue_id from
dbmail_header)

Returns a great many results, which to me implies they are orphaned.

So have I missed something, has the mechanism for clearing out the old
dbmail_header table changed in dbmail 3 in which case can someone tell me
what the procedure is? Is it safe to delete anything from
dbmail_headervalue not referenced in dbmail_header ? i.e.

delete from dbmail_headervalue where id not in (select headervalue_id from
dbmail_header)

The object here is to reduce the size of the indices more than anything else
as with a 16gb box and 6gb innodb buffer the indicies for the dbmail_header
tables are larger than that already.

Regards

Daniel

------------------------

CWA International

Balmoral House

9 John Street

London
WC1N 2ES

(t) + 44 (0)20 7242 8444

(e) <mailto:dms [at] cwa> dms [at] cwa

(w) <http://www.cwa.uk.com/> http://www.cwa.uk.com/

h.reindl at thelounge

Jul 28, 2013, 11:06 AM

Post #2 of 2 (58 views)
Permalink

Re: Pruning Header table in DBmail 3 [In reply to]

Am 28.07.2013 19:35, schrieb Daniel Schütze:
> I noticed dbmail_headervalue wasn’t decreasing in size and the query
> SELECT * from dbmail_headervalue where id not in (select headervalue_id from dbmail_header)
> Returns a great many results, which to me implies they are orphaned.
>
> So have I missed something, has the mechanism for clearing out the old dbmail_header table changed in dbmail 3 in
> which case can someone tell me what the procedure is? Is it safe to delete anything from dbmail_headervalue not
> referenced in dbmail_header?
>
> delete from dbmail_headervalue where id not in (select headervalue_id from dbmail_header)

my daily cronjob
IMHO that is a bug and dbmail-util should to this cleanup itself

/usr/sbin/dbmail-util -y -c -t -u -b -p -d -r 24h
mysql -u dbmail -p**************** dbmail -v -e "delete from dbmail_headername where id not in (select
headername_id from dbmail_header);"
mysql -u dbmail -p**************** dbmail -v -e "delete from dbmail.dbmail_headervalue where id not in (select
headervalue_id from dbmail.dbmail_header);"
___________________________________________

as well as there are a lot of cached headers nobody needs
IMHO there should be a internal *whitelist* which are worth for the cache-table

use dbmail; delete from dbmail_headername where headername like '%abuse%' or headername like '%antivirus%' or
headername like '%x-aol%' or headername like '%x-apple%' or headername like '%x-autorespond%' or headername like
'%x-barracuda%' or headername like '%x-ec-messenger%' or headername like '%x-imp%' or headername like
'%x-ironport%' or headername like '%linkedin%' or headername like '%exchange%' or headername like '%newsticker%' or
headername like '%original%' or headername like '%univie%' or headername like 'x-cleanmail%' or headername like
'x-cloud-security%' or headername like '%mailscanner%' or headername='x-helo' or headername like '%antivirus%';

Attachments:

signature.asc (0.26 KB)

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads