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 38570D1D80D for ; Mon, 24 Nov 2003 22:08:03 +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 50487-04 for ; Mon, 24 Nov 2003 18:07:34 -0400 (AST) Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55]) by svr1.postgresql.org (Postfix) with ESMTP id 00DD8D1B520 for ; Mon, 24 Nov 2003 18:07:32 -0400 (AST) Received: from game (12-250-179-10.client.attbi.com[12.250.179.10]) by comcast.net (sccrmhc11) with SMTP id <2003112422073301100nq75ae>; Mon, 24 Nov 2003 22:07:33 +0000 Message-ID: <2b4b01c3b2d7$6ce8e160$0201a8c0@game> Reply-To: "Nid" From: "Nid" To: References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> Subject: Re: Where to start for performance problem? Date: Mon, 24 Nov 2003 16:08:00 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_2B48_01C3B2A5.22211FD0" 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.5 tagged_above=0.0 required=5.0 tests=HTML_20_30, REFERENCES X-Spam-Level: * X-Archive-Number: 200311/230 X-Sequence-Number: 4790 This is a multi-part message in MIME format. ------=_NextPart_000_2B48_01C3B2A5.22211FD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable My apologies for the "From" name of MK Spam. That references an email acco= unt I made for signing up for things on the net. :] Nid ----- Original Message -----=20 From: MK Spam=20 To: pgsql-performance@postgresql.org=20 Sent: Monday, November 24, 2003 4:03 PM Subject: [PERFORM] Where to start for performance problem? I've scanned some of the archives and have learned a lot about different = performance tuning practices. I will be looking into using many of these i= deas 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 inh= erited many responsibilities outside of my original job description due to = layoffs. I am now the sole developer/support for a software product. *sig= h* This product uses postgresql. I am familiar with the basics of sql and= have worked on the database and code for the software but by no means am I= proficient with postgresql. The archives of this list provides many ideas for improving performance, = but the problem we are having is gradually degrading performance ending in = postgres shutting down. So it's not a matter of optimizing a complex query= to take 5 seconds instead of 60 seconds. From what I can tell we are usin= g the VACUUM command on a schedule but it doesn't seem to prevent the datab= ase 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 databas= e, recreate the database and import the dump. This seems to return perform= ance back to normal but obviously isn't a very good "solution". The slowdo= wn and subsequent crash can take as little as 1 week for databases with a l= ot 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= where to start, understanding that I am a newb, would be greatly appreciat= ed. Thank you. Nid ------=_NextPart_000_2B48_01C3B2A5.22211FD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
My apologies for the "From" name of MK Spa= m. =20 That references an email account I made for signing up for things on the=20 net.  :]
 
Nid
----- Original Message -----
Fro= m:=20 MK Spa= m=20
To: pgsql-performance@postgr= esql.org=20
Sent: Monday, November 24, 2003 4:= 03=20 PM
Subject: [PERFORM] Where to start = for=20 performance problem?

I've scanned some of the archives and ha= ve=20 learned a lot about different performance tuning practices.  I will = be=20 looking into using many of these ideas but I'm not sure they address the = issue=20 I am currently experiencing.
 
First, I'm a total newb with postgresql.=  =20 Like many before me, I have inherited many responsibilities outside of my= =20 original job description due to layoffs.  I am now the sole=20 developer/support for a software product.  *sigh*  This product= uses=20 postgresql.  I am familiar with the basics of sql and have worked on= the=20 database and code for the software but by no means am I proficient with= =20 postgresql.
 
The archives of this list provides many = ideas for=20 improving performance, but the problem we are having is gradually degradi= ng=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.&= nbsp;=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 refe= r to=20 it.  :]  Anyway, the only way I know to "fix" the problem is to= =20 export (pg_dump) the db, drop the database, recreate the database and imp= ort=20 the dump.  This seems to return performance back to normal but obvio= usly=20 isn't a very good "solution".  The slowdown and subsequent crash can= take=20 as little as 1 week for databases with a lot of data or go as long as a f= ew=20 weeks to a month for smaller data sets.
 
I don't really know where to start looki= ng for a=20 solution.  Any advice on where to start, understanding that I am a n= ewb,=20 would be greatly appreciated.  Thank you.
 
Nid
------=_NextPart_000_2B48_01C3B2A5.22211FD0--