Extremely slow performance with 2.2.1 + postgresql

Hello all, I recently upgraded my production system to 2.2.1
from 2.0.10 
(after testing of course) and everything is now running VERY
slow.  I ran 
the sql conversion script and then dbmail-util -by.  I have
~130users --> 
the sql script took ~4 minutes, the  dbmail-util -by took
from about 12:30am 
to 9:00am.

It now takes ~10 seconds to load my INBOX with imap on
squirrelmail.  
Thunderbird + imap is just as slow.  pop3 is also very slow
(only tried with 
thunderbird).  While I am trying to load the messages the
pgsql server 
(dedicated seperate machine - 2.3ghz 1gb ram) sits at 100%
for the entire 
time.

Any ideas on this? 2.0.10 for me was very snappy.

Thanks,
Eric

____________________________________________________________
_____
MSN Shopping has everything on your holiday list. Get expert
picks by style, 
age, and price. Try it! 
http:/
/shopping.msn.com/content/shp/?ctId=8000,ptnrid=176,ptnr
data=200601&tcode=wlmtagline

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

> Hello all, I recently upgraded my production system to
2.2.1 from 2.0.10
> (after testing of course) and everything is now running
VERY slow.  I ran
> the sql conversion script and then dbmail-util -by.  I
have ~130users -->
> the sql script took ~4 minutes, the  dbmail-util -by
took from about
> 12:30am
> to 9:00am.
>
> It now takes ~10 seconds to load my INBOX with imap on
squirrelmail.
> Thunderbird + imap is just as slow.  pop3 is also very
slow (only tried
> with
> thunderbird).  While I am trying to load the messages
the pgsql server
> (dedicated seperate machine - 2.3ghz 1gb ram) sits at
100% for the entire
> time.
>
> Any ideas on this? 2.0.10 for me was very snappy.
>
> Thanks,
> Eric
>
>
____________________________________________________________
_____
> MSN Shopping has everything on your holiday list. Get
expert picks by
> style,
> age, and price. Try it!
> http:/
/shopping.msn.com/content/shp/?ctId=8000,ptnrid=176,ptnr
data=200601&tcode=wlmtagline
>
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>


did you vacuum analize the database ??

leonel


_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Leonel Nunez wrote:

> 
> did you vacuum analize the database ??
> 
> leonel
> 

Yeah...  I found that my original assessment of dbmail was,
"Gosh, this really 
sucks" but that was before I did any tuning of the
database.

After I tuned the database it was still really depressing.

Then I realized that the autovacuum process under postgres
wasn't turned on...

Ran a VACUUM FULL ANALYZE on it...

It was immediately better.
Within an hour it was much better.

Now it's great.
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

I ran dbmail-util -ay, is this what you mean? Or something
else?  I am 
running postgresql 7.4 and would be very interested in the
steps taken to 
full-vacuum analyze.  Also what steps did you take to
"tune" the database?

Thanks very much for the help,
Eric


>From: Tom Allison <tomtacocat.net>
>Reply-To: DBMail mailinglist <dbmaildbmail.org>
>To: DBMail mailinglist <dbmaildbmail.org>
>Subject: Re: [Dbmail] Extremely slow performance with
2.2.1 + postgresql
>Date: Sat, 25 Nov 2006 19:41:02 -0500
>
>Leonel Nunez wrote:
>
>>
>>did you vacuum analize the database ??
>>
>>leonel
>>
>
>Yeah...  I found that my original assessment of dbmail
was, "Gosh, this 
>really sucks" but that was before I did any tuning
of the database.
>
>After I tuned the database it was still really
depressing.
>
>Then I realized that the autovacuum process under
postgres wasn't turned 
>on...
>
>Ran a VACUUM FULL ANALYZE on it...
>
>It was immediately better.
>Within an hour it was much better.
>
>Now it's great.
>_______________________________________________
>DBmail mailing list
>DBmaildbmail.org
>htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

____________________________________________________________
_____
Fixing up the home? Live Search can help 
http://imagine-windowslive.com/search/kit
s/default.aspx?kit=improve&locale=en-US&source=hmema
iltaglinenov06&FORM=WLMTAG

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Eric Hiller wrote:
> I ran dbmail-util -ay, is this what you mean? Or
something else?  I am 
> running postgresql 7.4 and would be very interested in
the steps taken 
> to full-vacuum analyze.  Also what steps did you take
to "tune" the 
> database?
> 
> Thanks very much for the help,
> Eric
> 

I am on 8.1 so I don't know that everything will apply.

But there are a lot of settings in the postgres.conf file
that you need to 
consider for the tuning of the database.  Far more than I
should probably go 
into there on the dbmailing list.  But none of this should
vary between versions 
of dbmail.

As for the vacuum analyze..

as dbmail or postgres user: 'VACUUM FULL ANALYZE' will do a
complete cleanup of 
the database in terms of discarded rows and analyze the data
to optimize the 
query execution.  This ANALYZE should be done periodically
as it can greatly 
improve performance.

Under 8.1 and maybe 7.x there is a autovacuum deamon that
does this for you. 
But if you have very large amounts of data to vacuum then it
might pass because 
of the potential for performance impact.

As far as I know, the -a option under dbmail-utils doesn't
to an ANALYZE.  But 
I'm rather new.

Considering you had to rebuild some database tables when you
migrated the 
versions, I think you should take the time out to make sure
that the database 
has been 'VACUUM FULL ANALYZE'.  You might also check the
database to make sure 
that the indexes are not damaged.  But I believe the
previous command will tell 
you this.
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

I read up a bit about this command and it seems that while
it is running the 
database is unusable.  About how long did this take for you
when you ran it?

Thanks,
Eric


>From: Tom Allison <tomtacocat.net>
>Reply-To: DBMail mailinglist <dbmaildbmail.org>
>To: DBMail mailinglist <dbmaildbmail.org>
>Subject: Re: [Dbmail] Extremely slow performance with
2.2.1 + postgresql
>Date: Sun, 26 Nov 2006 13:49:14 -0500
>
>Eric Hiller wrote:
>>I ran dbmail-util -ay, is this what you mean? Or
something else?  I am 
>>running postgresql 7.4 and would be very interested
in the steps taken to 
>>full-vacuum analyze.  Also what steps did you take
to "tune" the database?
>>
>>Thanks very much for the help,
>>Eric
>>
>
>I am on 8.1 so I don't know that everything will apply.
>
>But there are a lot of settings in the postgres.conf
file that you need to 
>consider for the tuning of the database.  Far more than
I should probably 
>go into there on the dbmailing list.  But none of this
should vary between 
>versions of dbmail.
>
>As for the vacuum analyze..
>
>as dbmail or postgres user: 'VACUUM FULL ANALYZE' will
do a complete 
>cleanup of the database in terms of discarded rows and
analyze the data to 
>optimize the query execution.  This ANALYZE should be
done periodically as 
>it can greatly improve performance.
>
>Under 8.1 and maybe 7.x there is a autovacuum deamon
that does this for 
>you. But if you have very large amounts of data to
vacuum then it might 
>pass because of the potential for performance impact.
>
>As far as I know, the -a option under dbmail-utils
doesn't to an ANALYZE.  
>But I'm rather new.
>
>Considering you had to rebuild some database tables when
you migrated the 
>versions, I think you should take the time out to make
sure that the 
>database has been 'VACUUM FULL ANALYZE'.  You might also
check the database 
>to make sure that the indexes are not damaged.  But I
believe the previous 
>command will tell you this.
>_______________________________________________
>DBmail mailing list
>DBmaildbmail.org
>htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

____________________________________________________________
_____
MSN Shopping has everything on your holiday list. Get expert
picks by style, 
age, and price. Try it! 
http:/
/shopping.msn.com/content/shp/?ctId=8000,ptnrid=176,ptnr
data=200601&tcode=wlmtagline

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Eric Hiller wrote:
> I read up a bit about this command and it seems that
while it is running 
> the database is unusable.  About how long did this take
for you when you 
> ran it?
> 

Under VACUUM FULL, it's very slow.  But I don't know that
it's unusable.
I don't have what you would call a lot but it took maybe 10
minutes.  I have a 
relatively slow system.

But since FULL locks the table, it will have an effect.  You
might have to take 
dbmail down while you do the largest tables.  But I would
start with a VACUUM 
ANALYZE and make certain that the autovacuum process is
running.

Only after that would I consider going to a VACUUM FULL.  It
will probably 
require a short system outage to do the message related
tables but not the users 
or other smaller tables.
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Very cool, that worked wonders for me, back to about the
same speed(maybe a 
little slower but it should speed up over time as you said).
 Also, it 
halved the physical size of the database.  for my ~130 users
it took 
~20minutes to run VACUUM FULL ANALYZE;

What is required to make it VACUUM ANALYZE every night?

Thanks for all the help!


>From: Tom Allison <tomtacocat.net>
>Reply-To: DBMail mailinglist <dbmaildbmail.org>
>To: DBMail mailinglist <dbmaildbmail.org>
>Subject: Re: [Dbmail] Extremely slow performance with
2.2.1 + postgresql
>Date: Sun, 26 Nov 2006 21:07:17 -0500
>
>Eric Hiller wrote:
>>I read up a bit about this command and it seems that
while it is running 
>>the database is unusable.  About how long did this
take for you when you 
>>ran it?
>>
>
>Under VACUUM FULL, it's very slow.  But I don't know
that it's unusable.
>I don't have what you would call a lot but it took maybe
10 minutes.  I 
>have a relatively slow system.
>
>But since FULL locks the table, it will have an effect. 
You might have to 
>take dbmail down while you do the largest tables.  But I
would start with a 
>VACUUM ANALYZE and make certain that the autovacuum
process is running.
>
>Only after that would I consider going to a VACUUM FULL.
 It will probably 
>require a short system outage to do the message related
tables but not the 
>users or other smaller tables.
>_______________________________________________
>DBmail mailing list
>DBmaildbmail.org
>htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

____________________________________________________________
_____
Stay up-to-date with your friends through the Windows Live
Spaces friends 
list. 
http://clk.atdmt.com
/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://spaces.
live.com/spacesapi.aspx?wx_action=create&wx_url=/friends
.aspx&mk

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Eric Hiller wrote:
> Very cool, that worked wonders for me, back to about
the same 
> speed(maybe a little slower but it should speed up over
time as you 
> said).  Also, it halved the physical size of the
database.  for my ~130 
> users it took ~20minutes to run VACUUM FULL ANALYZE;
> 
> What is required to make it VACUUM ANALYZE every night?
> 
> Thanks for all the help!

postgres.conf

make sure you have at least:
stats_start_collector = on
stats_row_level = on
autovacuum = on                 # enable autovacuum
subprocess?



Watch the logs.  They'll tell you if autovacuum is running
or not.
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail