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 <abrancatelli@schema31.it> 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
> DBmail@dbmail.org
> http://lists.nfg.nl/mailman/listinfo/dbmail [1]
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://lists.nfg.nl/mailman/listinfo/dbmail [1]
--
Richard Carroll
richcarroll@gmail.com
785-288-1144
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://lists.nfg.nl/mailman/listinfo/dbmail Links:
------
[1]
http://lists.nfg.nl/mailman/listinfo/dbmail