Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id F069C9F9BA6 for ; Sat, 6 Oct 2007 14:30:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 88202-07 for ; Sat, 6 Oct 2007 14:30:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.182]) by postgresql.org (Postfix) with ESMTP id 956619F9B90 for ; Sat, 6 Oct 2007 14:30:13 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so1151248waf for ; Sat, 06 Oct 2007 10:30:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=jGJWQHfVyu/L1gNzPGMAIIoNLu4BeIrwhGilP9+icwo=; b=QKxYE6GE9KTNopm7bwVPjZgDTVKLZGOuUrNkojsy8zBqyeU8lp33Ea/GX1tZxZGrEJjGB4O0EFG3wfNu70SWBII4lzn6zshWaB5/UxRnwkNfuh77ZfyaQTx5XiVFAL6ctHPBzO3BDZkndeYILGN84LFyvrSqwUCDqcaIhH1dcsI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=YDrGaZ7M7Bore/9vStbABJvTSUbOkOxMiixqP48qAatgnWIhlpF9tcacZ4ti2WbNheashSv1O/ryFyBsK2+4OCNfw4QzJ0/L87GpWZKoqdFh5z1W1gsmK1oxrIUIqovo/6gL8rU6/dSTZRd7Z9I60QWcnSkMAurCnMmbmtI/v9A= Received: by 10.114.37.1 with SMTP id k1mr8684509wak.1191691812281; Sat, 06 Oct 2007 10:30:12 -0700 (PDT) Received: by 10.114.197.19 with HTTP; Sat, 6 Oct 2007 10:30:12 -0700 (PDT) Message-ID: <4544e0330710061030hd4a494y97b350076a76a17d@mail.gmail.com> Date: Sat, 6 Oct 2007 12:30:12 -0500 From: "Peter Koczan" To: "Suresh Gupta VG" Subject: Re: Postgresql takes more time to update Cc: pgsql-admin@postgresql.org In-Reply-To: <3D5445983859B84B92669C0D883EA6FB0A807B75@ZENMAILHQ1.ind.zensar.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_34383_9057568.1191691812273" References: <3D5445983859B84B92669C0D883EA6FB0A807B75@ZENMAILHQ1.ind.zensar.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/30 X-Sequence-Number: 26944 ------=_Part_34383_9057568.1191691812273 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > > We are using "psql 7.4.2" version of Postgresql, these days all the > transactions on the database are taking long time to execute. We are > planning to do "ANALYZE" command on the database. Could you please advice > us, how much time it takes and what are the conditions we need to keep on an > eye. > As an alternative to Scott's suggestion (upgrading to the newest 7.4), you could update your postgresql installation to 8.2, or if you can wait a few months, 8.3. There are *huge* performance gains (I recently made a similar switch and everything is blazing fast). Please note that this will require a dump/restore of the data and more involved testing, so only do it if you can devote the time, money, and energy. As far as analyze goes, you should be running ANALYZE VERBOSE, or better yet, VACUUM ANALYZE VERBOSE (see http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html) so you can interpret the output. The vacuum also helps manage disk space, and this isn't a big performance hit because it doesn't require exclusive locks (though a VACUUM FULL would, again, read the docs). In fact, you should be doing this regularly, daily if possible. For me, I have a 30 GB database cluster, and vacuum/analyze takes about 3 minutes, though YMMV. You want to look for output regarding FSM pages and relations and adjust as necessary (otherwise you're running into index bloat). > Can you please tell us whether we had any other commands are available on > postgresql to increase the performance of the database and database tools > available for Postgresql on Solaris sparc machine? > I think Scott covered all of this. Alternatively, you could look to upgrading your hardware (multi-core x86 hardware is very nice), but without knowing your needs, usage, or budget, I can't make that determination. Hope this helps. Peter ------=_Part_34383_9057568.1191691812273 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

We are using "psql 7.4.2" version of Postgresql, these days all the transactions on the database are taking long time to execute. We are planning to do "ANALYZE " command on the database. Could you please advice us, how much time it takes and what are the conditions we need to keep on an eye.


As an alternative to Scott's suggestion (upgrading to the newest 7.4), you could update your postgresql installation to 8.2, or if you can wait a few months, 8.3. There are *huge* performance gains (I recently made a similar switch and everything is blazing fast). Please note that this will require a dump/restore of the data and more involved testing, so only do it if you can devote the time, money, and energy.

As far as analyze goes, you should be running ANALYZE VERBOSE, or better yet, VACUUM ANALYZE VERBOSE (see http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html ) so you can interpret the output. The vacuum also helps manage disk space, and this isn't a big performance hit because it doesn't require exclusive locks (though a VACUUM FULL would, again, read the docs). In fact, you should be doing this regularly, daily if possible.

For me, I have a 30 GB database cluster, and vacuum/analyze takes about 3 minutes, though YMMV. You want to look for output regarding FSM pages and relations and adjust as necessary (otherwise you're running into index bloat).

Can you please tell us whether we had any other commands are available on postgresql to increase the performance of the database and database tools available for Postgresql on Solaris sparc machine?

I think Scott covered all of this. Alternatively, you could look to upgrading your hardware (multi-core x86 hardware is very nice), but without knowing your needs, usage, or budget, I can't make that determination.

Hope this helps.

Peter
 


------=_Part_34383_9057568.1191691812273--