Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 971CA9FB3A5 for ; Fri, 5 Oct 2007 18:40:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 79173-05 for ; Fri, 5 Oct 2007 18:40:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 404B69FB312 for ; Fri, 5 Oct 2007 18:40:43 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so626218ugf for ; Fri, 05 Oct 2007 14:40:41 -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=2b2XV7UoTQl51DxQzadWJEG26Z7yRm/zuAk3sjzu+cA=; b=iUtfUqreIAZTh0HVtNr/khtSuJZ+gpuWVvQGD1/WCT4O+8oR1mva9B0Xg2IG6JVcFanXSFIDaWIGHl9ZvH3+Lc9NMduvwLGfqgxKrf/IBEyIu6A2yugXDH1GCCbIkJWZMrnBn0ejmmBgUnqa+2+iiFbDePLqENAHbvSxAUwfGLg= 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=ibR/JzxzuUk5OGPHP3YESiT/cJTTCvkIfLcC36XBrW+SCGovO6zpW/qU0Z6HwMJxLv4FBqycJaYzc4zx8alrI4sDMZV9WswG7TNHnm7WM6hatLYWsxrDa7FYVXsixftA49KJ/X6y+GMJ++dIu39WUUNvnAd88I9g8mBz5J9RQkg= Received: by 10.67.22.14 with SMTP id z14mr3720941ugi.1191620440709; Fri, 05 Oct 2007 14:40:40 -0700 (PDT) Received: by 10.66.220.7 with HTTP; Fri, 5 Oct 2007 14:40:40 -0700 (PDT) Message-ID: Date: Fri, 5 Oct 2007 16:40:40 -0500 From: "Scott Marlowe" 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/related; boundary="----=_Part_43730_33039740.1191620440700" References: <3D5445983859B84B92669C0D883EA6FB0A807B75@ZENMAILHQ1.ind.zensar.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/29 X-Sequence-Number: 26943 ------=_Part_43730_33039740.1191620440700 Content-Type: multipart/alternative; boundary="----=_Part_43731_17893184.1191620440701" ------=_Part_43731_17893184.1191620440701 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/4/07, Suresh Gupta VG wrote: > > Hi Team, > > > > 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. > FIrst thing, you need to schedule an update to 7.4.18 or whatever the latest 7.4 patch release is. 7.4.2 is so far back that I'm 100% certain there are data eating bugs in it, and you will likely get bitten if you stay on it. Note that there is no need for dump / restore, unless something goes horribly wrong. Always make a backup just in case though. Next in answer to how long an analyze will take, the answer is some time. That's as accurate as I can be given how little I know of your database. How much space on disk is it using? How fast is your drive subsystem. A similar question would be how long does it take to paint a bridge. Apparently, on the Golden Gate bridge, they start on one end, and when they get to the other, the go back and start again. > 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? > Hard to say. The two most important things I can think of for speeding you up right off the bat are to upgrade both your OS (to BSD or Linux on the same hardware) and to upgrade PostgreSQL. Barring that, we're looking at performance tuning. I'd suggest taking a walk over to the pgsql-perform list and telling folks all about your hardware (CPU / Disk arrays / Memory) and what you're doing with your box: types of queries, examples that are slow, how often you run analyze, how often you vacuum, how large your current db is, what does vacuum verbose tell you, those kinds of things. with thanks and regards, > > *G.V. Suresh Gupta* > > Sr. Software Engineer > > Batelco Phase II > > Mo: +91 9890898688 > > Ph : +9120 66453213 > > > > DISCLAIMER: > This email may contain confidential or privileged information for the > intended recipient(s) and the views expressed in the same are not > necessarily the views of Zensar Technologies Ltd. If you are not the > intended recipient or have received this e-mail by error, its use is > strictly prohibited, please delete the e-mail and notify the sender. Zensar > Technologies Ltd. does not accept any liability for virus infected mails. > ------=_Part_43731_17893184.1191620440701 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/4/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:

Hi Team,

 

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.


FIrst thing, you need to schedule an update to 7.4.18 or whatever the latest 7.4 patch release is. 7.4.2 is so far back that I'm 100% certain there are data eating bugs in it, and you will likely get bitten if you stay on it.  Note that there is no need for dump / restore, unless something goes horribly wrong.  Always make a backup just in case though.

Next in answer to how long an analyze will take, the answer is some time.  That's as accurate as I can be given how little I know of your database.  How much space on disk is it using?  How fast is your drive subsystem.  A similar question would be how long does it take to paint a bridge.  Apparently, on the Golden Gate bridge, they start on one end, and when they get to the other, the go back and start again.

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?


Hard to say.  The two most important things I can think of for speeding you up right off the bat are to upgrade both your OS (to BSD or Linux on the same hardware) and to upgrade PostgreSQL.

Barring that, we're looking at performance tuning.  I'd suggest taking a walk over to the pgsql-perform list and telling folks all about your hardware (CPU / Disk arrays / Memory) and what you're doing with your box: types of queries, examples that are slow, how often you run analyze, how often you vacuum, how large your current db is, what does vacuum verbose tell you, those kinds of things.
 

with thanks and regards,

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails.


------=_Part_43731_17893184.1191620440701-- ------=_Part_43730_33039740.1191620440700 Content-Type: image/gif; name="image001.gif" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: 0.1 R0lGODlhegBCAHcAACH/C01TT0ZGSUNFOS4wDQAAAAFzUkdCAK7OHOkAIf8LTVNPRkZJQ0U5LjAX AAAAC21zT1BNU09GRklDRTkuMEI8pPUALAAAAAB6AEIAhzQBmv///zQwLYiFg9lKOLCurIZpw1pW VON2Z1gwrN/a5rSh2vbRyu2onc3J0fPw8wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAj/AAMIHEiwoMGDCBMqXMiwocOHECNKnEixosWLGDNqTPjgAYMHG0OKHDmQAYMGIEmqXDmR AYKXDFjKnIkQ5EkCCBrQ3EnTZoMGCAgQQMmzqMoGLoMKRWq0KcaOJoEKnYrAgdOrEEHaPNkAp9el OrGKVWiSK1ClU4W+dBBzrFuCDArk9Io2KFqmb3sycOBAQceaXdMKpkrU4oAFBhYcTrxYMWLHBhQ0 ZaBggIEEADJrNnDYKkGldqniRNrWogPNqFOrBrCg6IPLq1UnSBngQWDBOdluXKAZs+zemmnPfOAb dfHUCQrGpYvA5EgDsaMDmL1TgfTVyQvadCBc5PXY2Tdq/61dcIB0zMcNHKT9UWB39+s9cjSQ+PGC +8dTqyfY8b7/v/z1tYACfYHkAH0JJJBYSrxhN+CDCuAnmXv30XdZgtMlsABtDyBmIX2tMQCdgu8p 1OBqG5a0AIbGDTChQCtOJyOCsoF0YmboebYQi9FpOFB+rDlQ3H4OQRdbawMZKR2RSn6XWQBNorZg aQY94GRmrRGnGmybOSRidC8GAGRsE44pHQM3YqegjgNZZ1yPD7h5XYoLaYldmFHiiF1tZoKZ5pJh 8qZgfYiN2aGTSJJlJnUDCbllh/klJ6eeCRwGXHBWglfjQPcdZORxh/aZWZgHxYhdd3kOMJB5qUG5 2n55Av8A45VYahfhgYjFJmZ09RlQIqdmEjlQpvr5l+eXqqUEJJHEidpbm7G++hp2bCoULQDCckor jrsilxKKBd03gLOz0urrkQ6xKlui4WInI3ozanmcqgFMqhmpw1ZW3L4B/Ikjl6gtwICuDfVJZUHL dhTnRwr3B26/q/2apLQOdgTkAqepxm5Ci7bW18cE+hXre3wJpC5qbeWZnIIDlvaRqandtynEyZ6s WbUHZazpvtyOqeECBRSKrUCxurdstxkmKCqyxjlcY6z4GiQnz0vS7C5qZW4JI9WtXQekqr7tqyBy ONq578GlbqtZiM6WHcDAGpcbM9zbSnZtbAbQbVxDd4P/ufV32fk7oc3TUbbtACnpfGVirzY0rtKQ Ry65y9dihuSFkaeEudK+voahz9huDPNmCmyeIH6Sb5xQTAy//VfrH7FepbiODchdSq+/XlLuHLoO MoTcIYSmZZ2V5DrD8sGe1/ISL+/8VXzFRaVHfL0nfZUCtcdfTADCVQDO8q3XAM4COVAA2mx19wBf ODswPkMDHCA/mw4cMMAAYQ3UgPwHsPnAASaj10DkFwAHCLB88TtA/gRSgAMShH8FMMj95lcQ+UWQ IPG7n3DqNwABNK82GtzgAawngNa8ryQCCCB/BgAB940Qg6pigACoVAAAShCACjhAmOoXkwFUq4bd OUCW/yqoKoExpAEODEAN70el+tVEAPcTwAL/F78aVvCCB7ggA21YHnopkCBWRFsBkyi/AWixfAI4 gBgN0kCDcNBFBFkAF5UjxRqe8X8MsN8cAyBEgfRxIFaUoBfPuL88ZrEgBjSI/fBXpfitkSBI7I4T q/TC7/HHhver4Pqg2EWBeBCSezQZAPMoQj8uUImh7F/OrGI/+CEOkWm0n3BqSMGSvDCTw1IjH/dY vzLCUpaXzKB2EiiAOx4AAnTyI//Y1MEOPnJY7+lIX9BmG+sNSznuiWYDohayKpXsIAQi2fqUwxcF HMx8z3yeOtfJzna6853wjKc850nPetrznvjMpz73ef+VcYavIeurXkTyqLqIAK0h5qSJE+P3EAdA QAACIJ9CCpBCimwzALhUiAMgmsRGSVQiCpxhvwYQE4zFpYAYA+QhO9SaBlqlQyR922H+98JIvq1T ijFglhgZAE6e9AFmRGBh5NgADxowgh0qAE37A8cAIBGB6cRoGlHZyvvlUKoCzGNMiqqqBEq1lRb8 n1F9yUKSxlJ+HwlrGh9gxfgVk49pvGD9KKrEDm7SgweY4UYJmECl5vWDBeSkLD3IRABmtICqlCNI aphCDRr2lgAcLFBHONf+9U+sqvpiIdUYv/8dFY2tNKAU62eVTO5vAY3NoGcbkkdWqmqGjuXjAVvr VACqUtGwr60NX3HbUwZksrUj5Oz9MmsVxaZwf3zMY1jqlxI9fu+WqqqhAhprP+7okiH/iyBFCVhU ArYyl1ax4l5T+4AUyvGxVEUvaYMLVGEy9L3y00lw6VXIt9mvhMzFKAQ61D/q+nC12LVkv16IUdP6 L6bmA8lwC6hdjOpQiWGJn2Tc9zalWpgy9tMuSc0nU1WxNaDuyZ8BETdOmXIHJTHsrBIBy88Wuzgj AQEAOw== ------=_Part_43730_33039740.1191620440700--