pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marty Scholes <marty@outputservices.com>
To: Joe Conway <mail@joeconway.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: rapid degradation after postmaster restart
Date: Fri, 12 Mar 2004 19:51:06 -0700
Message-ID: <4052771A.1080506@outputservices.com> (raw)
References: <4052661D.5060505@joeconway.com>

Six days ago I installed Pg 7.4.1 on Sparc Solaris 8 also.  I am hopeful 
that we as well can migrate a bunch of our apps from Oracle.

After doing some informal benchmarks and performance testing for the 
past week I am becoming more and more impressed with what I see.

I have seen similar results to what you are describing.

I found that running a full vacuum:

vacuumdb -fza

followed by a checkpoint makes it run fast again.

Try timing the update with and without a full vacuum.

I can't help but wonder if a clean shutdown includes some vacuuming.

Obviously, in a production database this would be an issue.

Please post back what you learn.

Sincerely,
Marty

I have been doing a bunch of informat

Joe Conway wrote:
> I'm trying to troubleshoot a performance issue on an application ported 
> from Oracle to postgres. Now, I know the best way to get help is to post 
> the schema, explain analyze output, etc, etc -- unfortunately I can't do 
> that at the moment. However, maybe someone can point me in the right 
> direction to figure this out on my own. That said, here are a few 
> details...
> 
> PostgreSQL 7.4.1
> bash-2.03$ uname -a
> SunOS col65 5.8 Generic_108528-27 sun4u sparc SUNW,Sun-Fire-280R
> 
> The problem is this: the application runs an insert, that fires off a 
> trigger, that cascades into a fairly complex series of functions, that 
> do a bunch of calculations, inserts, updates, and deletes. Immediately 
> after a postmaster restart, the first insert or two take about 1.5 
> minutes (undoubtedly this could be improved, but it isn't the main 
> issue). However by the second or third insert, the time increases to 7 - 
> 9 minutes. Restarting the postmaster causes the cycle to repeat, i.e. 
> the first one or two inserts are back to the 1.5 minute range.
> 
> Any ideas spring to mind? I don't have much experience with Postgres on 
> Solaris -- could it be related to that somehow?
> 
> Thanks for any insights.
> 
> Joe
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>               http://archives.postgresql.org





view thread (26+ messages)  latest in thread

Message-ID: <4052771A.1080506@outputservices.com>
Permalink:  ../4052771A.1080506@outputservices.com/
Also on:    postgresql.org/message-id/4052771A.1080506@outputservices.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: marty@outputservices.com, mail@joeconway.com
  Subject: Re: rapid degradation after postmaster restart
  In-Reply-To: <4052771A.1080506@outputservices.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox