I looked at the code for dbmail-util and the cleanup of the partlists
and mimeparts tables are not there. Thus your mimeparts will never be
cleared out of parts that are no longer in ues.
Run the normal dbmail-util -dy; dbmail-util -py; dbmail-util -cy to
clean out any messages pending db deletion and shrink the db files as
much as possible.
Then get a total byte count for the mail that is stored, and current
disk usage for the db.
Then run these queries in this order to remove part listings for
messages that have been purged, then to remove the parts that are no
longer referenced by the partlists.
DELETE dbmail_partlists.* FROM dbmail_partlists LEFT JOIN
dbmail_messages USING (physmessage_id) WHERE
dbmail_messages.message_idnr IS NULL;
DELETE dbmail_mimeparts.* FROM dbmail_mimeparts LEFT JOIN
dbmail_partlists ON (dbmail_mimeparts.id=dbmail_partlists.part_id) WHERE
dbmail_partlists.physmessage_id IS NULL;
OPTIMIZE TABLE dbmail_partlists;
OPTIMIZE TABLE dbmail_mimeparts;
Then report the current disk usage for the db after running these. It
should shrink quite a lot
I think that this will solve your issue. Attached is a hacked patch to
do these for you on a -py run. I have tested it with my 5.1 mysql
server, but not with sqlite or postgres.
Enjoy,
-Jon
Jorge Bastos wrote:
You're running into my major complaint about innodb. It never gives space
back. the file continues to grow until it uses all available resources. I
have heard about altering the storage engine to MyISAM and then changing it
back to get your disk space back, but I've never tried it. running
mysqloptimize will only make the file grow larger.
--------
That cannot be done, if I do it I'll lose foreign keys.(on dbmail_mimeparts
I could do it since it doesn't have any foreign keys)
But yes, doing that will free space.
InnoDB table space, never goes back on file size, true, but, if I only have
about 9GB of email, why the 53GB of size?
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Index: maintenance.c
===================================================================
--- maintenance.c (revision 3052)
+++ maintenance.c (working copy)
@@ -402,6 +402,16 @@
return db_update("DELETE FROM %smessages WHERE status=%d", DBPFX,
MESSAGE_STATUS_PURGE);
}
+static int db_deleted_partlists(void)
+{
+ return db_update("DELETE %spartlists.* FROM %spartlists LEFT JOIN
%smessages USING (physmessage_id) WHERE %smessages.message_idnr IS NULL",
DBPFX, DBPFX, DBPFX, DBPFX);
+}
+
+static int db_deleted_mimeparts(void)
+{
+ return db_update("DELETE %smimeparts.* FROM %smimeparts LEFT JOIN
%spartlists ON (%smimeparts.id=%spartlists.part_id) WHERE
%spartlists.physmessage_id IS NULL", DBPFX, DBPFX, DBPFX, DBPFX, DBPFX, DBPFX);
+}
+
static int db_deleted_count(u64_t * rows)
{
C c; R r; int t = FALSE;
@@ -444,8 +454,22 @@
return -1;
}
qprintf("Ok. Messages deleted.\n");
- }
- return 0;
+ qprintf("\nDeleting orphaned partlists...\n");
+ if (! db_deleted_partlists()) {
+ qerrorf ("Failed. An error occured. Please check
log.\n");
+ serious_errors = 1;
+ return -1;
+ }
+ qprintf("Ok. Partlists deleted.\n");
+ qprintf("\nDeleting unused mimeparts...\n");
+ if (! db_deleted_mimeparts()) {
+ qerrorf ("Failed. An error occured. Please check
log.\n");
+ serious_errors = 1;
+ return -1;
+ }
+ qprintf("Ok. Mimeparts deleted.\n");
+ }
+ return 0;
}
int do_set_deleted(void)
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
