We should be chunking these queries to do like 50 - 100 messages at a time and calculating whatever we need to calculate for everything in that chunk of messages. Doing things one at a time was basically quick-and-dirty to get the functionality working, but indeed does not scale well. Our justification for making it a lower priority to work on has been that dbmail-util is not a user-facing, time-sensitive app -- but yeah, at some point this code needs some serious attention.
Aaron On Sun, 2007-08-05 at 09:51 -0700, Jonathan Fealy wrote: > I know it seems like a lot of queries, but it basically runs like this. > A beginning SELECT * FROM dbmail_messages WHERE status='3'; // This > selects the list of message ids to be deleted. > Now each ID is iterated over like so: > SELECT * FROM dbmail_messages WHERE message_idnr='x'; // Grab the > physmessage_id that was being used. > DELETE FROM dbmail_messages WHERE message_idnr='x'; > SELECT * FROM dbmail_messages WHERE physmessage_id='y'; // Check > if the physmessage is still in use in a different message. > DELETE FROM dbmail_physmessages WHERE physmessage_id='y'; // Run > this query if no other messages use this physmessage. > DELETE FROM dbmail_messageblks WHERE physmessage_id='y'; // And > this one too if not needed anymore. > End Loop > > Unfortunately not all SQL servers used by dbmail handle constraints > and/or triggers making dbmail take care of the deletes for the > physmessage and messageblk rows. > > > There has been some talk about how to delete orphaned physmessages, so > it is possible to do the deletes by hand some thing like this provided > that you have your database has the proper constraints or triggers to > delete the other rows in tables like dbmail_headervalue, > dbmail_messageblks, etc. > > > dbmail-util -dy # Set delete on messages that can be dumped. > > DELETE FROM dbmail_messages WHERE status='3'; // Delete the deleted > messages. > DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id > FROM dbmail_messages); // Delete the orphans now > > dbmail-util -ay # Check the whole thing > > Marc Dirix wrote: > > Hi, > > > > I'm deleting about 15GB of mail, from unused mailboxes. > > > > I've done this by setting status=3 and then running dbmail-util. Which > > is now running for hours and hours. > > > > So I started checking which statements are executed, if I'm right it > > is doing: > > > > SELECT physmessage_id > > FROM dbmail_messages > > WHERE message_idnr = 7714566; > > DELETE FROM dbmail_messages > > WHERE message_idnr = 7714566; > > SELECT message_idnr > > FROM dbmail_messages > > WHERE physmessage_id = 4015611; > > DELETE FROM dbmail_physmessage > > WHERE id = 4015611; > > DELETE FROM dbmail_messageblks > > WHERE physmessage_id = 4015611; > > > > > > Now the questions are: > > > > 1) What use has the second SELECT statement? > > 2) Why are you not using a transaction for this? (Or did I mis that?) > > 3) Is there no way to batch multiple deletes with per statement? > > > > > > Marc > > _______________________________________________ > > Dbmail-dev mailing list > > [email protected] > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > > > > > > > _______________________________________________ > Dbmail-dev mailing list > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
