List Info

Thread: SQL-Quarantine DB LOCK while cleaning
SQL-Quarantine DB LOCK while cleaning
country flaguser name
Italy		 2007-07-09 09:47:06 
Hi,

i have the similar problem like 
http://marc.info/?l=amavis-user&m=1135782208226
55&w=2

I user amavis-new 2.4.2 with 100 processes on a server with
8 GB RAM and 
4 CPU 3.40GHz.
I don't have performance problems.
I use Red Hat Enterprise Linux ES release 4 with 
perl-BerkeleyDB-0.27-1.2 and db4-4.2.52-7.1.
The daily mailtraffic is 1.000.000 incoming (95 % Spammails)
on 2 server.


When I use the daily  quarantine cleanup script every night
the DB are always locked for this (long) time.

The ceanup script is: (README.sql)

1. DELETE FROM msgs WHERE time_num <
UNIX_TIMESTAMP()-14*24*60*60;
2. DELETE FROM msgs WHERE time_num <
UNIX_TIMESTAMP()-60*60 AND content 
IS NULL;
3. DELETE FROM quarantine WHERE NOT EXISTS (SELECT 1 FROM
msgs WHERE 
mail_id=quarantine.mail_id);
4. DELETE FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs
WHERE 
mail_id=msgrcpt.mail_id);
5. DELETE FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs   
WHERE 
sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE
rid=id);
6. OPTIMIZE TABLE msgs, msgrcpt, quarantine, maddr;


The time for the script is 3-4 ours and in this time the DB
is always 
locked and so
i have a lot of mail in mailq and in the TMP directory of
amavis I
have a lot of new directorys. My problem is, that in the
night I could not
receive mails for some ours. The mails goes in the mailq and
then ours 
after
I receive the mails.

So I have alwas to delete the new direcotries in the TMP of
Amavis.


Here's the log excerpt from one of the messages that got
deferred:

Jul  9 02:02:55 mailserver postfix/smtpd[3937]: A89B423405D:

client=unknown[60.212.142.152]
Jul  9 02:03:01 mailserver postfix/cleanup[4964]:
A89B423405D: 
message-id=<001501c7c1ff$928d8d40$027c968capavwgiw2iufbc>
Jul  9 02:03:08 mailserver postfix/qmgr[18226]: A89B423405D:

from=<clementsslu4email.net>, size=16124, nrcpt=1 (queue
active)
Jul  9 02:16:38 mailserver amavis[24584]: (24584-09-9) SEND
via SQL 
(DBI:mysql:database=amavisdb;host=localhost;port=3306): 
<clementsslu4email.net> -> <mlnttob27nexample.com>, mail_id qb6tlabrf46l
Jul  9 02:16:38 mailserver postfix/smtpd[7492]: disconnect
from 
nobelium.inotronic.de[83.136.129.229]
Jul  9 02:16:38 mailserver amavis[24584]: (24584-09-9)
writing mail text 
to SQL failed: Error closing, flush: sql inserting text
failed, sql 
exec: err=1216, S1000, DBD::mysql::st execute failed: Cannot
add or 
update a ch
ild row: a foreign key constraint fails at (eval 39) line
153, <GEN259> 
line 542. at (eval 43) line 177, <GEN259> line 542. at
(eval 43) line 
68, <GEN259> line 542.
Jul  9 02:16:38 mailserver amavis[24584]: (24584-09-9)
mail_via_sql: 
rollback done
Jul  9 02:16:38 mailserver amavis[24584]: (24584-09-9) (!!)
TROUBLE in 
check_mail: quar+notif FAILED: temporarily unable to
quarantine: 451 
4.5.0 Storing to sql db as mail_id qb6tlabrf46l failed:
writing mail 
text to SQL
 failed: Error closing, flush: sql inserting text failed,
sql exec: 
err=1216, S1000, DBD::mysql::st execute failed: Cannot add
or update a 
child row: a foreign key constraint fails at (eval 39) line
153, 
<GEN259> line 54
2. at (eval 43) line 177, <GEN259> line 542. at (eval
43) line 68, 
<GEN259> line 542. at (eval 43) line 293,
<GEN259> line 542., 
id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259>
line 542.
Jul  9 02:16:38 mailserver amavis[24584]: (24584-09-9) (!)
PRESERVING 
EVIDENCE in /var/amavis/tmp/amavis-20070709T021207-24584
Jul  9 02:16:38 mailserver amavis[24584]: (24584-09-9)
TIMING [total 
270244 ms] - mkdir tempdir: 0 (0%)0, create email.txt: 0
(0%)0, SMTP 
pre-DATA-flush: 2 (0%)0, SMTP DATA: 84 (0%)0, body_digest: 1
(0%)0, 
sql-enter: 266
092 (98%)98, mkdir parts: 10 (0%)98, mime_decode: 40 (0%)99,

get-file-type3: 19 (0%)99, decompose_part: 1 (0%)99,
parts_decode: 0 
(0%)99, AV-scan-1: 29 (0%)99, spam-wb-list: 2 (0%)99, SA msg
read: 1 
(0%)99, SA parse: 3 (
0%)99, SA check: 3930 (1%)100, SA finish: 7 (0%)100,
update_cache: 1 
(0%)100, decide_mail_destiny: 1 (0%)100, write-header: 5
(0%)100, 
fwd-sql: 13 (0%)100, rundown: 1 (0%)100
Jul  9 02:16:38 mailserver postfix/smtp[30670]: A89B423405D:

to=<mlnttob27nexample.com>,
relay=127.0.0.1[127.0.0.1], delay=826, 
status=deferred (host 127.0.0.1[127.0.0.1] said: 451-4.5.0
Error in 
processing, id=
24584-09-9, quar+notif FAILED: temporarily unable to
quarantine: 451 
4.5.0 Storing to sql db as mail_id qb6tlabrf46l failed:
writing mail 
text to SQL failed: Error closing, flush: sql inserting text
failed, sql 
exec: err
=1216, S1000, DBD::mysql::st execute failed: Cannot add or
update a 
child row: a foreign key constraint fails at (eval 39) line
153, 
<GEN259> line 542. at (eval 43) line 177,
<GEN259> line 542. at (eval 
43) line 68, <GEN
259> line 542. at (eval 43) line 293 451 4.5.0 ,
<GEN259> line 542., 
id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259>
line 542. (in 
reply to end of DATA command))
Jul  9 02:16:38 mailserver postfix/smtp[30670]: A89B423405D:

to=<mlnttob27nexample.com>,
relay=127.0.0.1[127.0.0.1], delay=826, 
status=deferred (host 127.0.0.1[127.0.0.1] said: 451-4.5.0
Error in 
processing, id=24584-09-9, quar+notif
FAILED: temporarily unable to quarantine: 451 4.5.0 Storing
to sql db as 
mail_id qb6tlabrf46l failed: writing mail text to SQL
failed: Error 
closing, flush: sql inserting text failed, sql exec:
err=1216, S1000, 
DBD::mysql::st execute failed:
Cannot add or update a child row: a foreign key constraint
fails at 
(eval 39) line 153, <GEN259> line 542. at (eval 43)
line 177, <GEN259> 
line 542. at (eval 43) line 68, <GEN259> line 542. at
(eval 43) line 293 
451 4.5.0 , <GEN259> line 542.,
id=24584-09-9 at /usr/sbin/amavisd line 8659, <GEN259>
line 542. (in 
reply to end of DATA command))
Jul  9 02:42:53 mailserver postfix/qmgr[18226]: A89B423405D:

from=<clementsslu4email.net>, size=16124, nrcpt=1 (queue
active)
Jul  9 02:43:00 mailserver postfix/smtp[14831]: A89B423405D:

to=<mlnttob27nexample.com>,
relay=127.0.0.1[127.0.0.1], delay=2408, 
status=sent (254 2.7.1 Ok, discarded, id=11361-05-3 - SPAM)
Jul  9 02:43:00 mailserver postfix/qmgr[18226]: A89B423405D:
removed

My 3 questaons are:
1. Exist some solution to cleanup the DB without locking
long time.
2. It is vetter to delete first the INDEX
    DROP INDEX msgs_idx_sid         ON msgs;
    DROP INDEX msgrcpt_idx_rid      ON msgrcpt;
    DROP INDEX msgrcpt_idx_mail_id  ON msgrcpt;
    and after cleaning reindex the INDEX?
        CREATE INDEX msgs_idx_sid        ON msgs    (sid);
        CREATE INDEX msgrcpt_idx_rid     ON msgrcpt (rid);
    CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);
3. Some other people has the same problem?

I would use a TMP in the RAM, but if I have this problem I
could use it.

Thanks!

Egon



README.sql:

BRIEF MySQL EXAMPLE of a log/report/quarantine database
housekeeping
============================================================
========

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;
DELETE FROM maddr
  WHERE NOT EXISTS (SELECT 1 FROM msgs    WHERE sid=id)
    AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);


BRIEF MySQL EQUIVALENT EXAMPLE based on time_iso if its data
type is 
TIMESTAMPS
============================================================
===================
(don't forget to set: $timestamp_fmt_mysql=1 in
amavisd.conf)

DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() -
INTERVAL 14 day;
DELETE FROM msgs WHERE time_iso < UTC_TIMESTAMP() -
INTERVAL 1 hour
  AND content IS NULL;
DELETE FROM maddr
  WHERE NOT EXISTS (SELECT 1 FROM msgs    WHERE sid=id)
    AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);


BRIEF PostgreSQL EXAMPLE of a log/report/quarantine database
housekeeping
============================================================
=============

DELETE FROM msgs WHERE time_iso < now() - INTERVAL '14
days';
DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h'
AND content IS 
NULL;
DELETE FROM maddr
  WHERE NOT EXISTS (SELECT 1 FROM msgs    WHERE sid=id)
    AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);


COMMENTED LONGER EXAMPLE of a log/report/quarantine database
housekeeping
============================================================
=============

--  discarding indexes makes deletion faster; if we expect a
large 
proportion
--  of records to be deleted it may be quicker to discard
index, do 
deletions,
--  and re-create index (not necessary with PostgreSQL, may
benefit MySQL);
--  for daily maintenance this does not pay off
--DROP INDEX msgs_idx_sid         ON msgs;
--DROP INDEX msgrcpt_idx_rid      ON msgrcpt;
--DROP INDEX msgrcpt_idx_mail_id  ON msgrcpt;

--  delete old msgs records based on timestamps only (for
time_iso see 
next),
--  and delete leftover msgs records from aborted mail
checking operations
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;

--  provided the time_iso field was created as type
TIMESTAMP DEFAULT 0 
(MySQL)
--  or TIMESTAMP WITH TIME ZONE (PostgreSQL), instead of
purging based on
--  numerical Unix timestamp as above, one may select
records based on 
ISO 8601
--  UTC timestamps. This is particularly suitable for
PostgreSQL:
--DELETE FROM msgs WHERE time_iso < now() - INTERVAL '14
days';
--DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1
h' AND content 
IS NULL;
and is also possible with MySQL, using slightly different
format:
--DELETE FROM msgs
--  WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 14 day;
--DELETE FROM msgs
--  WHERE time_iso < UTC_TIMESTAMP() - INTERVAL 1 hour
AND content IS NULL;

--  optionally certain content types may be given shorter
lifetime
--DELETE FROM msgs WHERE time_num <
UNIX_TIMESTAMP()-7*24*60*60
--  AND (content='V' OR (content='S' AND
spam_level>20));

--  (optional) just in case the ON DELETE CASCADE did not do
its job, we may
--  explicitly delete orphaned records (with no
corresponding msgs entry);
--  if ON DELETE CASCADE did work, there should be no
deletions at this step
DELETE FROM quarantine
  WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE
mail_id=quarantine.mail_id);
DELETE FROM msgrcpt
  WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE
mail_id=msgrcpt.mail_id);

--  re-create indexes (if they were removed in the first
step):
--CREATE INDEX msgs_idx_sid        ON msgs    (sid);
--CREATE INDEX msgrcpt_idx_rid     ON msgrcpt (rid);
--CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);

--  delete unreferenced e-mail addresses
DELETE FROM maddr
  WHERE NOT EXISTS (SELECT 1 FROM msgs    WHERE sid=id)
    AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);

--  (optional) optimize tables once in a while
--OPTIMIZE TABLE msgs, msgrcpt, quarantine, maddr;

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
Re: SQL-Quarantine DB LOCK while cleaning
country flaguser name
Italy		 2007-07-09 10:11:43 
Hi Egon,
our traffic peaks at 1Mmsg/day on weaker hardware than
yours, and I worked out 
a workaround that:

- completes the cleaning in 12 hours
- does not lock the DB

My deletion procedure takes advantage of the "ON DELETE
CASCADE" on MySQL/InnoDB.

How do we do it? Rather than running a single deletion query
"DELETE FROM 
msgs", I do a "SELECT mail_id FROM msgs WHERE
..." to get candidate message 
IDs from the database. I pipe the output through a sed
command and create a 
long list of _single delete statements_:

DELETE FROM msgs WHERE mail_id = '0sdfsddfk';
DELETE FROM msgs WHERE mail_id = '4904fkfkf';
DELETE FROM msgs WHERE mail_id = 'ddkkkddkd';
....

Then I run the SQL script as an input to mysql command line
client.

I am NOT deleting/cleaning maddr, although I should do it
one day or another 
to limit table grow.

I DO NOT recreate indexes.

Hope this helps,
Paolo

------------------------------------------------------------
-------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and
take
control of your XML. No limits. Just data. Click to get it
now.
http://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
Re: SQL-Quarantine DB LOCK while cleaning
country flaguser name
Slovenia		 2007-07-24 09:35:11 
Egon,

> i have the similar problem like
> http://marc.info/?l=amavis-user&m=1135782208226
55&w=2
>
> I user amavis-new 2.4.2 with 100 processes on a server
with 8 GB RAM and
> 4 CPU 3.40GHz. [...]
>
> When I use the daily  quarantine cleanup script every
night
> the DB are always locked for this (long) time.
> The ceanup script is: (README.sql) [...]
>
> The time for the script is 3-4 ours and in this time
the DB is always
> locked and so i have a lot of mail in mailq and in the
TMP directory
> [...] My problem is, that in the night I could not
> receive mails for some hours. ...

Paolo Cravero writes:
> How do we do it? Rather than running a single deletion
query "DELETE FROM
> msgs", I do a "SELECT mail_id FROM msgs WHERE
..." to get candidate message
> IDs from the database. I pipe the output through a sed
command and create a
> long list of _single delete statements_:

Paolo advice seems to be a good solution.

I gave up on using MySQL for SQL logging for precisely this
reason,
finding out that the purging procedure on PostgreSQL works
much
faster. I now have Bayes on MySQL/InnoDB (faster that
PostgreSQL),
but amavisd logging on PostgreSQL 8.2.

See also: http://marc.info/
?t=118190428300003

  Mark

------------------------------------------------------------
-------------
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
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
Re: SQL-Quarantine DB LOCK while cleaning
country flaguser name
United States		 2007-07-24 10:31:35 
Mark wrote:

> Paolo Cravero writes:
>> How do we do it? Rather than running a single
deletion query "DELETE FROM
>> msgs", I do a "SELECT mail_id FROM msgs
WHERE ..." to get candidate message
>> IDs from the database. I pipe the output through a
sed command and create a
>> long list of _single delete statements_:

> Paolo advice seems to be a good solution.

I wonder if Paolo would share his scripts?

Gary V


------------------------------------------------------------
-------------
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
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
Re: SQL-Quarantine DB LOCK while cleaning
country flaguser name
United States		 2007-07-24 17:00:52 
Gary wrote:

> Mark wrote:

>> Paolo Cravero writes:
>>> How do we do it? Rather than running a single
deletion query "DELETE FROM
>>> msgs", I do a "SELECT mail_id FROM
msgs WHERE ..." to get candidate message
>>> IDs from the database. I pipe the output
through a sed command and create a
>>> long list of _single delete statements_:

>> Paolo advice seems to be a good solution.

> I wonder if Paolo would share his scripts?

I came up with this as an example:

 #!/bin/bash
 echo "USE amavis;" >amavistrim.sql
 mysql -uamavis -pamavis_password amavis -B -N -e
"SELECT CONCAT('DELETE FROM msgs WHERE mail_id = ',
CHAR(39), mail_id, CHAR(39), ' LIMIT 1;' ) FROM msgs WHERE
time_iso < NOW() - INTERVAL 14 DAY;"
>>amavistrim.sql
 mysql -uamavis -pamavis_password <amavistrim.sql

 # cat amavistrim.sql
 USE amavis;
 DELETE FROM msgs WHERE mail_id = 'W1CPj2MVZX-3' LIMIT 1;
 DELETE FROM msgs WHERE mail_id = 'tMXbu4C7R1Bj' LIMIT 1;
 DELETE FROM msgs WHERE mail_id = 'lvyaj5Dfd8Ns' LIMIT 1;
 <...>

Gary V


------------------------------------------------------------
-------------
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
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
Re: SQL-Quarantine DB LOCK while cleaning
country flaguser name
United States		 2007-07-26 08:58:15 
Paolo wrote:

> Gary V wrote:

>> I came up with this as an example:
>> 
>> #!/bin/bash echo "USE amavis;"
>amavistrim.sql

>> mysql -uamavis -pamavis_password amavis -B -N -e
"SELECT CONCAT('DELETE
>> FROM msgs WHERE mail_id = ', CHAR(39), mail_id,
CHAR(39), ' LIMIT 1;' )
>> FROM msgs WHERE time_iso < NOW() - INTERVAL 14
DAY;" >>amavistrim.sql

>> mysql -uamavis -pamavis_password
<amavistrim.sql

> That's it, even better than mine since I do a
"sed" pass to compose the 
> amavistrim.sql script.

> Question: why "LIMIT 1" ?

> Paolo

I'm not certain whether it makes a difference or not but I
think
if it does make a difference it would make things go
quicker.
The idea is to stop SELECT'ing once the first matching
record is found.

http://dev.mysql.com/doc/refman/5.0/en/limit-optim
ization.html

I would be curious if your purge time is improved with LIMIT
1.

Gary V



------------------------------------------------------------
-------------
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
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
Re: SQL-Quarantine DB LOCK while cleaning
country flaguser name
Slovenia		 2007-07-26 09:39:55 
> > Question: why "LIMIT 1" ?
>
> I'm not certain whether it makes a difference or not
but I think
> if it does make a difference it would make things go
quicker.
> The idea is to stop SELECT'ing once the first matching
record is found.

The LIMIT is redundant, the msgs.mail_id is a PRIMARY KEY
and is unique, there can only be one record with each
msgs.mail_id.

  Mark

------------------------------------------------------------
-------------
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
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user

AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
[1-7]

about | contact  Other archives ( Real Estate discussion Medical topics )