I ended up adding these clusters. I am not sure if I clustered on the correct indexes but things are moving along much faster. That and the vacuum full analyze that I ran.
CLUSTER dbmail_messages USING dbmail_messages_1; CLUSTER dbmail_mailboxes USING dbmail_mailboxes_owner_name_idx; CLUSTER dbmail_physmessage USING dbmail_physmessage_pkey ; CLUSTER dbmail_headername USING dbmail_headername_1 ; CLUSTER dbmail_aliases USING dbmail_aliases_domain_alias_idx ; CLUSTER dbmail_users USING dbmail_users_2 ; CLUSTER dbmail_subscription USING dbmail_subscription_pkey ; CLUSTER dbmail_acl USING dbmail_acl_pkey ; CLUSTER dbmail_headervalue USING dbmail_headervalue_1 ; CLUSTER dbmail_messageblks USING dbmail_messageblks_1 ; CLUSTER dbmail_mimeparts USING dbmail_mimeparts_1 ; CLUSTER dbmail_partlists USING dbmail_partlists_2 ; On Thu, Jan 25, 2018 at 10:56 AM, rich carroll <richcarr...@gmail.com> 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 < > abrancate...@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 >> >> >> _______________________________________________ >> DBmail mailing list >> DBmail@dbmail.org >> http://lists.nfg.nl/mailman/listinfo/dbmail >> >> > > > -- > Richard Carroll > richcarr...@gmail.com > 785-288-1144 <(785)%20288-1144> > -- Richard Carroll richcarr...@gmail.com 785-288-1144
_______________________________________________ DBmail mailing list DBmail@dbmail.org http://lists.nfg.nl/mailman/listinfo/dbmail
