Re: Not Deleting Mail

Hi Rich. 

Our dbmail_messages table has 6 million rows more or less, but we're on
MySQL so I can't really tell you how much is that for pgSQL. 

BTW, this is my table definition, with all the corresponding indexes -
obviously in MySQL slang: 

CREATE TABLE `dbmail_messages` (
 `message_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `mailbox_idnr` bigint(20) unsigned NOT NULL DEFAULT '0',
 `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `seen_flag` tinyint(1) NOT NULL DEFAULT '0',
 `answered_flag` tinyint(1) NOT NULL DEFAULT '0',
 `deleted_flag` tinyint(1) NOT NULL DEFAULT '0',
 `flagged_flag` tinyint(1) NOT NULL DEFAULT '0',
 `recent_flag` tinyint(1) NOT NULL DEFAULT '0',
 `draft_flag` tinyint(1) NOT NULL DEFAULT '0',
 `unique_id` varchar(70) NOT NULL DEFAULT '',
 `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `seq` bigint(20) NOT NULL DEFAULT '0',
 PRIMARY KEY (`message_idnr`),
 KEY `physmessage_id_index` (`physmessage_id`),
 KEY `mailbox_idnr_index` (`mailbox_idnr`),
 KEY `seen_flag_index` (`seen_flag`),
 KEY `unique_id_index` (`unique_id`),
 KEY `status_index` (`status`),
 KEY `mailbox_status` (`mailbox_idnr`,`status`),
 KEY `dbmail_messages_seq_index` (`seq`),
 CONSTRAINT `dbmail_messages_ibfk_1` FOREIGN KEY (`physmessage_id`)
REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE
CASCADE,
 CONSTRAINT `dbmail_messages_ibfk_2` FOREIGN KEY (`mailbox_idnr`)
REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON
UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=140465013 DEFAULT CHARSET=utf8; 

---

Andrea Brancatelli
Schema31 S.p.a.
Chief Technology Officier

ROMA - FI - PA 
ITALY
Tel: +39.06.98.358.472
Cell: +39.331.2488468
Fax: +39.055.71.880.466
Società del Gruppo OVIDIO TECH S.R.L.

On 2018-01-25 17:56, rich carroll wrote:

> Thanks Andrea, 
> 
> That worked, but was really slow. 
> 
> I started looking at deleting messages because my server is running really slow. It turns out its because of the dbmail_messages table is really slow. It took 5 minutes to delete 300 rows. My dbmail_messages table has 7628586 rows. Is that alot? Our old server with dbmail 2.2 and postgres 8 seemed to run alot better. I used the 2.2 and postgresql performance guide but I am not sure how much of that is relevent to 3.2.3 and postgresql 9.5. Is there a newer guide or can you give me some suggestions to speed it up? 
> 
> I run dbmail-util -ay every night. I thought I was clustering but when I look at my indexes I see that the index I think should be clustered is not. 
> 
> dbmail_messages_1 definition: "CREATE INDEX dbmail_messages_1 ON dbmail_messages USING btree (mailbox_idnr)" <- Not clustered 
> 
> should I do? 
> CLUSTER dbmail_messages USING dbmail_messages_1; 
> 
> Thanks
> Rich 
> 
> On Thu, Jan 25, 2018 at 3:38 AM, Andrea Brancatelli <[email protected]> wrote:
> 
> Do this: 
> 
> SELECT user_idnr FROM dbmail_users WHERE userid = "YOUR EMAIL ADDRESS" 
> 
> Write down the corresponding ID 
> 
> SELECT mailbox_idnr, name FROM dbmail_mailboxes WHERE owner_idnr = <THE ID YOU GOT FROM THE PREVIOUS SELECT> 
> 
> Find the ID of your mailbox. 
> 
> DELETE FROM dbmail_messages WHERE mailbox_idnr = <THE ID OF THE MAILBOX YOU JUST FOUND OUT> 
> 
> Done. 
> 
> dbmail-util -ay will take care of cleaning out everything else later (mimeparts, headers, etc), don't worry. 
> 
> Enjoy. 
> 
> ---
> 
> Andrea Brancatelli
> 
> On 2018-01-24 16:47, rich carroll wrote: 
> 
> Howdy Folks, 
> 
> I have a mailbox that I used for testing. It has about 300,000 messages in it. I can not open it in webmail. I am trying to empty it out. 
> 
> I have ran: 
> 
> dbmail-users -e <user> -y 
> 
> It looks like its doing something for a bit but nothing gets removed from the box. 
> 
> I am currently trying to pop them into thunderbird to see if that will remove them although that is moving very slow. It gets a message every couple seconds or so. 
> 
> When I run: 
> 
> dbmail-util -pd 
> 
> It tells me: 
> Ok. [3933954] messages have DELETE status.
> 
> but dbmail-util -pdy does not clean them out. 
> 
> I am wondering if I have a problem. Is there another way to ensure messages get deleted and to emtpy mailboxes? 
> 
> Im running dbmail 3.2.3. 
> 
> I am seeing alot of this in my logs. 
> 
> Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=92134366 ]
> Jan 24 09:44:26 mail2017 dbmail/imap4d[103602]: Error:[db] db_exec(+388): SQLException: ERROR:  current transaction is aborted, commands ignored until end of transaction 
> 
> and I have a dbmail-imapd process that is really working hard. 
> 
> -- 
> Richard Carroll 
> 
> _______________________________________________
> DBmail mailing list
> [email protected]
> http://lists.nfg.nl/mailman/listinfo/dbmail [1]
> 
> _______________________________________________
> DBmail mailing list
> [email protected]
> http://lists.nfg.nl/mailman/listinfo/dbmail [1]

-- 
Richard Carroll
[email protected]
785-288-1144 

_______________________________________________
DBmail mailing list
[email protected]
http://lists.nfg.nl/mailman/listinfo/dbmail

 

Links:
------
[1] http://lists.nfg.nl/mailman/listinfo/dbmail

_______________________________________________
DBmail mailing list
[email protected]
http://lists.nfg.nl/mailman/listinfo/dbmail