X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0E4B2D1C92F for ; Mon, 24 Nov 2003 22:03:20 +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 52085-01 for ; Mon, 24 Nov 2003 18:02:51 -0400 (AST) Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64]) by svr1.postgresql.org (Postfix) with ESMTP id B66CBD1D643 for ; Mon, 24 Nov 2003 18:02:48 -0400 (AST) Received: from game (12-250-179-10.client.attbi.com[12.250.179.10]) by comcast.net (sccrmhc13) with SMTP id <2003112422025001600etecle>; Mon, 24 Nov 2003 22:02:50 +0000 Message-ID: <2b1f01c3b2d6$c403ba70$0201a8c0@game> Reply-To: "MK Spam" From: "MK Spam" To: Subject: Where to start for performance problem? Date: Mon, 24 Nov 2003 16:03:17 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_2B1C_01C3B2A4.7921BA20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.2 tagged_above=0.0 required=5.0 tests=HTML_10_20 X-Spam-Level: * X-Archive-Number: 200311/229 X-Sequence-Number: 4789 This is a multi-part message in MIME format. ------=_NextPart_000_2B1C_01C3B2A4.7921BA20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've scanned some of the archives and have learned a lot about different pe= rformance tuning practices. I will be looking into using many of these ide= as but I'm not sure they address the issue I am currently experiencing. First, I'm a total newb with postgresql. Like many before me, I have inher= ited many responsibilities outside of my original job description due to la= yoffs. I am now the sole developer/support for a software product. *sigh*= This product uses postgresql. I am familiar with the basics of sql and h= ave worked on the database and code for the software but by no means am I p= roficient with postgresql. The archives of this list provides many ideas for improving performance, bu= t the problem we are having is gradually degrading performance ending in po= stgres shutting down. So it's not a matter of optimizing a complex query t= o take 5 seconds instead of 60 seconds. From what I can tell we are using = the VACUUM command on a schedule but it doesn't seem to prevent the databas= e from becoming "congested" as we refer to it. :] Anyway, the only way I = know to "fix" the problem is to export (pg_dump) the db, drop the database,= recreate the database and import the dump. This seems to return performan= ce back to normal but obviously isn't a very good "solution". The slowdown= and subsequent crash can take as little as 1 week for databases with a lot= of data or go as long as a few weeks to a month for smaller data sets. I don't really know where to start looking for a solution. Any advice on w= here to start, understanding that I am a newb, would be greatly appreciated= . Thank you. Nid ------=_NextPart_000_2B1C_01C3B2A4.7921BA20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I've scanned some of the archives and have= learned=20 a lot about different performance tuning practices.  I will be looking= into=20 using many of these ideas but I'm not sure they address the issue I am curr= ently=20 experiencing.
 
First, I'm a total newb with postgresql.&n= bsp; Like=20 many before me, I have inherited many responsibilities outside of my origin= al=20 job description due to layoffs.  I am now the sole developer/support f= or a=20 software product.  *sigh*  This product uses postgresql.  I = am=20 familiar with the basics of sql and have worked on the database and code fo= r the=20 software but by no means am I proficient with postgresql.
 
The archives of this list provides many id= eas for=20 improving performance, but the problem we are having is gradually degrading= =20 performance ending in postgres shutting down.  So it's not a matter of= =20 optimizing a complex query to take 5 seconds instead of 60 seconds.&nb= sp;=20 From what I can tell we are using the VACUUM command on a schedule but it= =20 doesn't seem to prevent the database from becoming "congested" as we refer = to=20 it.  :]  Anyway, the only way I know to "fix" the problem is to e= xport=20 (pg_dump) the db, drop the database, recreate the database and import the= =20 dump.  This seems to return performance back to normal but obviously i= sn't=20 a very good "solution".  The slowdown and subsequent crash can take as= =20 little as 1 week for databases with a lot of data or go as long as a few we= eks=20 to a month for smaller data sets.
 
I don't really know where to start looking= for a=20 solution.  Any advice on where to start, understanding that I am a new= b,=20 would be greatly appreciated.  Thank you.
 
Nid
------=_NextPart_000_2B1C_01C3B2A4.7921BA20--