Paolo,
> I have read and (probably) understood what has to be
done on MySQL
> before migrating from 2.3 to 2.4.
> /* I am currently filling-up a test database in order
to test if the
> maintainance is actually faster with these DELETE
CASCADE statements.
> All 2.4 native. */
Let us know of the results.
Shortly before a 2.4.0 release I switched SQL logging from
MySQL
to PostgreSQL, just to make sure that it works. I'm quite
pleased
with the result, the purging operation is now several times
faster,
but I don't have a good explanation of why that is so.
> I am wondering if any DELETEion improvement can be
achieved with 2.3.3
> if I skip this (source: htt
p://www.ijs.si/software/amavisd/README.sql.txt)
>
> ALTER TABLE msgs ADD quar_loc varchar(255) DEFAULT
'';
You can't skip this, the quar_loc field is written-to by
2.4.0.
There would be no difference in speed with or without this
field.
> and just apply these to the existing DB:
>
> ALTER TABLE quarantine
> ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id)
ON DELETE CASCADE;
> ALTER TABLE msgrcpt
> ADD FOREIGN KEY (mail_id) REFERENCES msgs(mail_id)
ON DELETE CASCADE;
It remains to be seen if there are any speedups in doing so.
There shouldn't be any slowdowns though.
> --ALTER TABLE msgs
> -- ADD FOREIGN KEY (sid) REFERENCES maddr(id) ON
DELETE RESTRICT;
> --ALTER TABLE msgrcpt
> -- ADD FOREIGN KEY (rid) REFERENCES maddr(id) ON
DELETE RESTRICT;
>
> Moreover why are these commented out? CREATE TABLE
statements do have them.
Aside from preventing undesired manual purges, this one has
no impact on
speed or functionality. I left it commented out because if
one is using
MySQL and declaring maddr.id as data type SERIAL, one gets
in trouble
because SERIAL is incompatible with INT UNSIGNED (SERIAL is
longer data type)
and MySQL complains bitterly when you try to do ALTER ... ON
DELETE RESTRICT.
Try it, if ALTER ... RESTRICT works, use it by all means.
If it doesn't, don't worry, just ignore it. As said, it
should
have no impact on speed or functionality, apart from
providing
and additional safety net.
> We have a 50GB (fifty GigaB) quarantine table on the
production server,
> and the last quarantine purge run took 14 hours to
complete. All traffic
> was queued because of DB locks and timeouts. We clean
every second day
> for spam >30 days and viruses >15 days, but will
probably move to
> once-a-week.
It would be better (faster) to clean more often, perhaps
every hour.
> So, what I'm asking is: can I apply just "ALTER
TABLE... ON DELETE
> RESTRICT|CASCADE" statements to my 2.3.x MySQL
installation
Yes
> and expect delete speed improvements?
Not necessarily. (but won't be any worse)
> The DB schema should stay compatible with
> amavisd-new 2.3.3, or not?
Upgrading to 2.4.0 requires new field quar_loc to be
inserted.
If you later decide to switch back to 2.3.3, there is no
need
to remove the quar_loc field, it would just remain NULL.
> Should I need to re-create indexes?
I don't think so.
> Has anyone done some tests?
I'm beginning to apreciate PostgreSQL 
Mark
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web
services, security?
Get stuff done quickly with pre-integrated technology to
make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on
Apache Geronimo
http://sel.as-us.falkag.net/
sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
AMaViS-user mailing list
AMaViS-user lists.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/
|