Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: DBMail: users

emptying database

 

 

DBMail users RSS feed   Index | Next | Previous | View Threaded


pwadas at jewish

Jun 17, 2009, 6:30 AM

Post #1 of 18 (2653 views)
Permalink
emptying database

Hello,
I have one user on new installation of dbmail 2.3.5 on postgres 8.3.x.
I started with empty database (1-5MB total database size). Then,
I put 200 messages (100MB), into his mailbox (inbox), then I deleted them,
then,
I did dbmail-util -d -p etc. etc., then I did some vacuuming on pg, etc.
etc.
Database still have about 86MB, although the messages are gone from
the mailbox. The biggest table (almost total of these 86mb) is
dbmail_mimeparts.
So, what's going on? I expected the database approx. similar to original
size..

Regards,
P.

server:~# dbmail-util -ay

Repairing DBMAIL messageblocks integrity...
Ok. Found [0] unconnected messageblks.

Repairing DBMAIL physmessage integrity...
Ok. Found [0] unconnected physmessages.

Repairing DBMAIL message integrity...
Ok. Found [0] unconnected messages.

Repairing DBMAIL mailbox integrity...
Ok. Found [0] unconnected mailboxes.

Repairing DBMAIL for NULL messages...
Ok. Found [0] NULL messages.

Deleting messages with DELETE status...
Ok. Messages deleted.

Repairing DBMAIL for rfcsize field...
Ok. Found [0] missing rfcsize values.

Repairing DBMAIL for incorrect is_header flags...
Ok. Found [0] incorrect is_header flags.

Repairing DBMAIL for cached envelopes...
Ok. Found [0] missing envelope values.

Repairing DBMAIL for cached header values...
Ok. Found [0] un-cached physmessages.

Setting DELETE status for deleted messages...
Ok. Messages set for deletion.
Re-calculating used quota for all users...
Ok. Used quota updated for all users.

Removing aliases with nonexistent delivery userid's...
Ok. Found [0] dangling aliases.

Vacuuming and optimizing database...
Ok. Database cleaned up.

Maintenance done. No errors found.

--
View this message in context: http://www.nabble.com/emptying-database-tp24067701p24067701.html
Sent from the dbmail users mailing list archive at Nabble.com.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


pwadas at jewish

Jun 17, 2009, 6:30 AM

Post #2 of 18 (2580 views)
Permalink
Re: emptying database [In reply to]

The same behaviour with 1GB mailbox (about 750 messages) - after
deleting, emptying trash, cleaning, purging, vacuuming, reindexing etc. etc.
the disk space is still used (database size about 1GB).
--
View this message in context: http://www.nabble.com/emptying-database-tp24067701p24068393.html
Sent from the dbmail users mailing list archive at Nabble.com.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


paul at nfg

Jun 17, 2009, 6:33 AM

Post #3 of 18 (2575 views)
Permalink
Re: emptying database [In reply to]

Piotr Wadas wrote:
> The same behaviour with 1GB mailbox (about 750 messages) - after
> deleting, emptying trash, cleaning, purging, vacuuming, reindexing etc. etc.
> the disk space is still used (database size about 1GB).

Garbage collection on dbmail_mimeparts is still missing.


--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


pwadas at jewish

Jun 17, 2009, 6:39 AM

Post #4 of 18 (2575 views)
Permalink
Re: emptying database [In reply to]

>>Garbage collection on dbmail_mimeparts is still missing.

Shall I expect the same behaviour, no matter what kind of backend
(mysql/pgsql/sqlite) I'll use?

Any simple sql hint to cleanup this table manually, something like

delete
from dbmail_mimeparts
where
message_id
is not in ( select message_idnr from dbmail_messages )

or similar?
--
View this message in context: http://www.nabble.com/emptying-database-tp24067701p24073906.html
Sent from the dbmail users mailing list archive at Nabble.com.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


paul at nfg

Jun 17, 2009, 8:05 AM

Post #5 of 18 (2579 views)
Permalink
Re: emptying database [In reply to]

Piotr Wadas wrote:
>
>>> Garbage collection on dbmail_mimeparts is still missing.
>
> Shall I expect the same behaviour, no matter what kind of backend
> (mysql/pgsql/sqlite) I'll use?
>
> Any simple sql hint to cleanup this table manually, something like
>
> delete
> from dbmail_mimeparts
> where
> message_id
> is not in ( select message_idnr from dbmail_messages )

more like:

delete from dmail_mimeparts
where id not in (select part_id from dbmail_partlists)

though this is of course a very slow query.


--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


vulture at netvulture

Jun 17, 2009, 9:22 AM

Post #6 of 18 (2581 views)
Permalink
Re: emptying database [In reply to]

Paul J Stevens wrote:
> delete from dmail_mimeparts
> where id not in (select part_id from dbmail_partlists)
>
> though this is of course a very slow query
Hmm. I thought we have put in that cleanup in the dbmail-util for trunk
(2.3.x) already?? dm_db.c:db_icheck_mimeparts(t/f)

-Jon

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


mysql.jorge at decimal

Jun 17, 2009, 9:35 AM

Post #7 of 18 (2575 views)
Permalink
Re: emptying database [In reply to]

>
> Paul J Stevens wrote:
> > delete from dmail_mimeparts
> > where id not in (select part_id from dbmail_partlists)
> >
> > though this is of course a very slow query
> Hmm. I thought we have put in that cleanup in the dbmail-util for trunk
> (2.3.x) already?? dm_db.c:db_icheck_mimeparts(t/f)
>

I think there's something incomplete on that function 'cause when I used the
git version when that was added, messages were not being completely removed.


_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


vulture at netvulture

Jun 17, 2009, 9:58 AM

Post #8 of 18 (2579 views)
Permalink
Re: emptying database [In reply to]

Jorge Bastos wrote:
> I think there's something incomplete on that function 'cause when I used the
> git version when that was added, messages were not being completely removed.
>
It might be that we are missing the partlists cleanup. If the parts
aren't being deleted, then that db_icheck_mimeparts() won't do anything.

-Jon

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


mysql.jorge at decimal

Jun 17, 2009, 10:41 AM

Post #9 of 18 (2576 views)
Permalink
Re: emptying database [In reply to]

> Jorge Bastos wrote:
> > I think there's something incomplete on that function 'cause when I
> used the
> > git version when that was added, messages were not being completely
> removed.
> >
> It might be that we are missing the partlists cleanup. If the parts
> aren't being deleted, then that db_icheck_mimeparts() won't do
> anything.
>

Hum it can be that, i'm not using the GIT version right now, I'm on the old
2.3.2.
Maybe Paul can clarify this matter.
I think this should be corrected for 2.3.6 ad 2.3.6 is going to the most
string version the 2.4x preparation.

Jorge,

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


vulture at netvulture

Jun 17, 2009, 9:37 PM

Post #10 of 18 (2571 views)
Permalink
Re: emptying database [In reply to]

What tables still have a lot of rows?
select count(*) from dbmail_xxx;

For a single user, you should probably have <10 rows in all of the
tables. Let me know what tables still have more than that.

-Jon

Piotr Wadas wrote:
>
>>> Garbage collection on dbmail_mimeparts is still missing.
>>>
>
> Shall I expect the same behaviour, no matter what kind of backend
> (mysql/pgsql/sqlite) I'll use?
>
> Any simple sql hint to cleanup this table manually, something like
>
> delete
> from dbmail_mimeparts
> where
> message_id
> is not in ( select message_idnr from dbmail_messages )
>
> or similar?
>


--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


pwadas at jewish

Jun 17, 2009, 10:22 PM

Post #11 of 18 (2572 views)
Permalink
Re: emptying database [In reply to]

here you are - one user, 1GB mail moved to DB, one folder (inbox), all
(actually there's 2-3 users, anyway they're all are just tests, and
remaining both does not contain anything).

echo "select 'name' as name ,-1 as ile `cat tblist|while read line; do
echo
"union select '$line' as name, count(*) from $line as ile "; done` order
by ile asc "| psql -U postgres mydb

name | ile
---------------------------+-------
name | 0
dbmail_acl | 0
dbmail_aliases | 0
dbmail_auto_notifications | 0
dbmail_auto_replies | 0
dbmail_keywords | 0
dbmail_messageblks | 0
dbmail_pbsp | 0
dbmail_replycache | 0
dbmail_sievescripts | 0
dbmail_usermap | 0
dbmail_users | 6
dbmail_mailboxes | 10
dbmail_subscription | 10
dbmail_replytofield | 56
dbmail_messages | 56
dbmail_headername | 82
dbmail_ccfield | 111
dbmail_referencesfield | 189
dbmail_subjectfield | 992
dbmail_datefield | 994
dbmail_physmessage | 994
dbmail_fromfield | 994
dbmail_envelope | 1057
dbmail_tofield | 5011
dbmail_mimeparts | 10001
dbmail_partlists | 11467
dbmail_headervalue | 18115
(28 rows)



On Wed, 17 Jun 2009, Jonathan Feally wrote:

> What tables still have a lot of rows?
> select count(*) from dbmail_xxx;
>
> For a single user, you should probably have <10 rows in all of the
> tables. Let me know what tables still have more than that.
>
> -Jon
>
> Piotr Wadas wrote:
> >
> >>> Garbage collection on dbmail_mimeparts is still missing.
> >>>
> >
> > Shall I expect the same behaviour, no matter what kind of backend
> > (mysql/pgsql/sqlite) I'll use?
> >
> > Any simple sql hint to cleanup this table manually, something like
> >
> > delete
> > from dbmail_mimeparts
> > where
> > message_id
> > is not in ( select message_idnr from dbmail_messages )
> >
> > or similar?
> >
>
>
>
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


pwadas at jewish

Jun 17, 2009, 11:18 PM

Post #12 of 18 (2575 views)
Permalink
Re: emptying database [In reply to]

additional empty folders like trash and sent are created by imap
on log-on, that's why dbmail_mailboxes is higher a little bit.
--
View this message in context: http://www.nabble.com/emptying-database-tp24067701p24087007.html
Sent from the dbmail users mailing list archive at Nabble.com.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


paul at nfg

Jun 18, 2009, 3:13 AM

Post #13 of 18 (2573 views)
Permalink
Re: emptying database [In reply to]

Jonathan Feally wrote:
> Jorge Bastos wrote:
>> I think there's something incomplete on that function 'cause when I used the
>> git version when that was added, messages were not being completely removed.
>>
> It might be that we are missing the partlists cleanup. If the parts
> aren't being deleted, then that db_icheck_mimeparts() won't do anything.

partlists has a key restraint on physmessage so that should cover things
just fine.

Point is, that those cleanups were added post-2.3.5 which is what Piotr
is using.




--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


vulture at netvulture

Jun 18, 2009, 12:18 PM

Post #14 of 18 (2574 views)
Permalink
Re: emptying database [In reply to]

Piotr Wadas wrote:
> dbmail_physmessage | 994
> dbmail_fromfield | 994
>
These match - that is good - should be one from header for each message
> dbmail_envelope | 1057
>
A couple extra maybe?? Not too far off.
> dbmail_tofield | 5011
>
This is about 5x the correct size - this is just a view so nothing can
be deleted from it. This count be close to the fromfield count. Only one
to and from header per message.
> dbmail_mimeparts | 10001
> dbmail_partlists | 11467
>
This would mean that we have 11.5 or so parts per message? I have an
average of 3.75 parts per messages.
> dbmail_headervalue | 18115
>
This seems to be about right at 18 headers per message.


So from looking at this I think that partlists rows are being left
around, thus the mimeparts can't be dropped because they are still being
pointed to.

Try:
select count(*) from dbmail_partlists left join dbmail_physmessage on
(dbmail_physmessage.id=dbmail_partlists.physmessage_id) where
dbmail_physmessage.id is null;

That should confirm if there are any parts being left there. Count=0
means no leftovers, Count>0 then there are extra parts not being cleaned
up. Perhaps a constraint was missed, or maybe you just need to optimize
all of the tables to regain the space.

-Jon


--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


pwadas at jewish

Jun 18, 2009, 12:25 PM

Post #15 of 18 (2571 views)
Permalink
Re: emptying database [In reply to]

> Try:
> select count(*) from dbmail_partlists left join dbmail_physmessage on
> (dbmail_physmessage.id=dbmail_partlists.physmessage_id) where
> dbmail_physmessage.id is null;
>
> That should confirm if there are any parts being left there. Count=0
> means no leftovers, Count>0 then there are extra parts not being cleaned
> up. Perhaps a constraint was missed, or maybe you just need to optimize
> all of the tables to regain the space.

The count(*) result is zero, anyway rows are still available in mentioned
tables, as listed :/
P.


_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


vulture at netvulture

Jun 18, 2009, 12:33 PM

Post #16 of 18 (2571 views)
Permalink
Re: emptying database [In reply to]

Piotr Wadas wrote:
> The count(*) result is zero, anyway rows are still available in mentioned
> tables, as listed :/
>
Well then it sounds like the tables just need to be shrunk/optimized in
postgres. I'm not sure what the syntax is to do that as I'm a mysql guy.

-Jon

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


paul at nfg

Jun 18, 2009, 12:39 PM

Post #17 of 18 (2568 views)
Permalink
Re: emptying database [In reply to]

Jonathan Feally wrote:

> A couple extra maybe?? Not too far off.
>> dbmail_tofield | 5011
>>
> This is about 5x the correct size - this is just a view so nothing can
> be deleted from it. This count be close to the fromfield count. Only one
> to and from header per message.

Nonsense. Piotr is on 2.3.5 Jon, old-style dbmail_tofield table!!

Piotr, I'll release 2.3.6 real soon now, which should fix all your issues.


--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail


pwadas at jewish

Jun 18, 2009, 10:18 PM

Post #18 of 18 (2571 views)
Permalink
Re: emptying database [In reply to]

>
> Piotr, I'll release 2.3.6 real soon now, which should fix all your issues.

Allright, I'll look forward to checkout :)
regards,
P.
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

DBMail users RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.