-------- Original-Nachricht --------
> Datum: Tue, 28 Aug 2007 22:37:33 +0200
> Von: "Andrzej Kukula" <[EMAIL PROTECTED]>
> An: [email protected]
> Betreff: Re: [AMaViS-user] Foreign Keys Necessary?
> Mark,
>
> On 8/28/07, Mark Martinec <[EMAIL PROTECTED]> wrote:
> > Andrzej,
> >
> > > > Btw, versions of amavisd prior to 2.4.0 (which started suggesting
> > > > the use of a foreign key) had the following example in README.sql:
> > > >
> > > > DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 7*24*60*60;
> > > > DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 60*60
> > > > AND content IS NULL;
> >
> > > Mark, that would surely prevent index on time_num from being used, so
> > > this query would be the slowest one...
> >
> > I was quoting the old README.sql.
>
> Ok nothing wrong with it, just that OP wanted high performance out of
> the queries, because of huge tables. I looked at it and found some
> things we can optimize.
>
> > The current release notes (2.5.2) suggests a swapped order:
> >
> > README.sql-mysql:
> > DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-14*24*60*60;
> > DELETE FROM msgs WHERE time_num < UNIX_TIMESTAMP()-60*60 AND content IS
> NULL;
>
> For large tables I'd suggest one improvement:
> SET @min_time := UNIX_TIMESTAMP()-14*24*60*60;
> DELETE FROM msgs WHERE time_num < @mintime;
>
> That would avoid calculating UNIX_TIMESTAMP() and the subtraction for
> every row in the table (thousands or millions times).
>
I use exactly something like that to speed up the deletion:
-----
-- --------------------------------------------------------
-- Configurable parameters/values
-- --------------------------------------------------------
SET @my_days_quarantine = 14;
SET @my_penpal_days = 10;
SET @my_timestamp = UNIX_TIMESTAMP();
-- --------------------------------------------------------
-- Clear msgs table for anything older than
-- @my_days_quarantine
-- --------------------------------------------------------
START TRANSACTION;
DELETE FROM msgs
WHERE time_num < @[EMAIL PROTECTED];
COMMIT;
-- --------------------------------------------------------
-- Delete anything that's not quarantined longer than we
-- keep data for penpals
-- --------------------------------------------------------
START TRANSACTION;
DELETE FROM msgs
WHERE quar_type IN (' ','F','Z','B','M','L')
AND (time_num < @[EMAIL PROTECTED]);
COMMIT;
-- --------------------------------------------------------
-- Clean msgs table from orphaned and invalid records
-- --------------------------------------------------------
START TRANSACTION;
DELETE FROM msgs
WHERE time_num < @my_timestamp-60*60
AND content IS NULL;
COMMIT;
-- --------------------------------------------------------
-- Clean quarantine table from orphaned records
-- --------------------------------------------------------
START TRANSACTION;
DELETE quarantine FROM quarantine
LEFT OUTER JOIN msgs ON quarantine.mail_id=msgs.mail_id
WHERE msgs.mail_id IS NULL;
COMMIT;
-- --------------------------------------------------------
-- Clean msgrcpt table from orphaned records
-- --------------------------------------------------------
START TRANSACTION;
DELETE msgrcpt FROM msgrcpt
LEFT OUTER JOIN msgs ON msgrcpt.mail_id=msgs.mail_id
WHERE msgs.mail_id IS NULL;
COMMIT;
-- --------------------------------------------------------
-- Clean maddr table from orphaned records
-- --------------------------------------------------------
-- NOTE: Disabled because of foreign key constraint failure
-- --------------------------------------------------------
-- START TRANSACTION;
-- DELETE maddr FROM maddr
-- LEFT OUTER JOIN msgs ON sid=id
-- LEFT OUTER JOIN msgrcpt ON rid=id
-- WHERE sid IS NULL OR rid IS NULL;
-- COMMIT;
-- --------------------------------------------------------
-- Optimize tables
-- --------------------------------------------------------
START TRANSACTION;
OPTIMIZE TABLE msgs, msgrcpt, quarantine, maddr;
COMMIT;
-----
It is not perfect but for me it works faster then the original purge script.
> Of course there's also problem with deleting large batch of records,
> they first must be written to the transaction log (and maybe to binary
> log used for replication), what surely take much time; it's better to
> delete them in batches, say 1000 rows in a batch, to reduce
> transaction log contention. This is pretty trivial when there are no
> joins and somewhat more sophisticated with joins; in both cases it
> needs a stored procedure; I'll write some tomorrow.
>
> > README.sql-pg:
> > DELETE FROM msgs WHERE time_iso < now() - INTERVAL '3 weeks';
> > DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h' AND content IS
> NULL;
> >
> > At least on PostgreSQL it does use an index on time_iso.
>
> They, too, force SQL to calculate now() and the subtraction for every
> row in the table (see EXPLAIN VERBOSE), and also exploit the
> transaction log problem with large deletes, but I don't know how to
> optimize these queries in pure SQL as understood by PgSQL.
>
> Regards,
> Andrzej
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems? Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >> http://get.splunk.com/
> _______________________________________________
> AMaViS-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/amavis-user
> AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
> AMaViS-HowTos:http://www.amavis.org/howto/
--
Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
