X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 71A21D1DC15 for ; Sat, 13 Mar 2004 02:51:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 46834-08 for ; Fri, 12 Mar 2004 22:51:05 -0400 (AST) Received: from outputservices.com (outputt1130.customer.frii.net [216.17.159.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9DB0BD1D342 for ; Fri, 12 Mar 2004 22:51:00 -0400 (AST) Received: from outputservices.com (outputservices.com [137.106.76.15]) by outputservices.com (8.11.3/8.11.3) with ESMTP id i2D2p6i15908; Fri, 12 Mar 2004 19:51:07 -0700 (MST) Message-ID: <4052771A.1080506@outputservices.com> Date: Fri, 12 Mar 2004 19:51:06 -0700 From: Marty Scholes User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:1.0.1) Gecko/20020920 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joe Conway Cc: pgsql-performance Subject: Re: rapid degradation after postmaster restart References: <4052661D.5060505@joeconway.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/168 X-Sequence-Number: 6024 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