Josh, I like it: very useful. But I thought it could use some improvements since this approach is not forward compatible with dbmail-2.3 because of the extensive use of triggers in 2.3.
So I've stuck it in a script, made it so that postgres and sqlite users can also use it, and allow for more variants of trash boxes (Trash/deletedmailbox, INBOX/Trash, etc). Josh Marshall wrote: > Hi, > > Every night I run the following SQL to delete old emails left in > people's Trash folder: > > echo "START TRANSACTION; > CREATE TEMPORARY TABLE dbmail_tmp_oldtrash > SELECT message_idnr FROM dbmail_messages m > INNER JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr > INNER JOIN dbmail_physmessage p ON p.id=m.physmessage_id > WHERE b.name = 'Trash' AND > p.internal_date < date_sub(now(), interval 60 day); > SELECT COUNT(*) AS oldtrash_messages FROM dbmail_tmp_oldtrash; > UPDATE dbmail_messages SET deleted_flag=1,status=2 > WHERE message_idnr IN (SELECT message_idnr FROM dbmail_tmp_oldtrash); > COMMIT;" | mysql > > I got this code from a previous post on this list. > > The last few days this has been taking a very long time to process. Our > mail database is around 40Gb and the average number of trash items to > set as deleted was around 5000 mails. The above code took nearly 6 hours > to complete, with the CPU pegged at 99% the whole time. (I noticed sieve > scripts weren't being processed correctly during this time but no mail > was lost.) > > I couldn't believe it would take that long. From what I could see it was > the UPDATE code that took the time. I replaced the above with: > > echo "update dbmail_messages m, dbmail_mailboxes b, dbmail_physmessage p > set m.deleted_flag=1,m.status=2 where m.mailbox_idnr=b.mailbox_idnr and > p.id=m.physmessage_id and b.name = 'Trash' and p.internal_date < > date_sub(now(), interval 60 day);" | mysql > > It took 0.46 seconds ! > > I couldn't believe it so I ran the sql again with the deleted flag and > status to set to a different value, then set it back again. Both those > operations took less than half a second to complete. > > Hope this helps someone, > > Regards, > Josh. > _______________________________________________ > DBmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail > -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl
expiretrash.sh
Description: application/shellscript
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
