From mk_spam@comcast.net Mon Nov 24 22:03:20 2003 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-- From mk_spam@comcast.net Mon Nov 24 22:08:03 2003 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-- From chriskl@familyhealth.com.au Tue Nov 25 01:12:09 2003 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 D16CBD1C914 for ; Tue, 25 Nov 2003 01:12:09 +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 88612-04 for ; Mon, 24 Nov 2003 21:11:41 -0400 (AST) Received: from houston.familyhealth.com.au (fhnet.arach.net.au [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id A0F7DD1C951 for ; Mon, 24 Nov 2003 21:11:36 -0400 (AST) Received: from familyhealth.com.au (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9p1/8.12.9) with ESMTP id hAP1BGoD032541; Tue, 25 Nov 2003 09:11:18 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <3FC2ACCF.2030303@familyhealth.com.au> Date: Tue, 25 Nov 2003 09:13:51 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031013 Thunderbird/0.3 X-Accept-Language: en-us, en MIME-Version: 1.0 To: MK Spam Cc: pgsql-performance@postgresql.org Subject: Re: Where to start for performance problem? References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> In-Reply-To: <2b1f01c3b2d6$c403ba70$0201a8c0@game> 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: 200311/231 X-Sequence-Number: 4791 > 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 using the VACUUM command on a schedule but it doesn't seem to > prevent the database from becoming "congested" as we refer to it. :] Our busy website has a cronjob that runs VACUUM ANALYZE once an hour (vacuumdb -a -q -z). Have you tried going 'VACUUM FULL ANALYZE' (vacuumdb -a -q -z -f) instead of a dump and reload? Chris From mk_spam@comcast.net Tue Nov 25 03:05:56 2003 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 BB1A0D1C96C for ; Tue, 25 Nov 2003 03:05:56 +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 16802-01 for ; Mon, 24 Nov 2003 23:05:29 -0400 (AST) Received: from sccrmhc12.comcast.net (sccrmhc12.comcast.net [204.127.202.56]) by svr1.postgresql.org (Postfix) with ESMTP id B66F6D1C976 for ; Mon, 24 Nov 2003 23:05:24 -0400 (AST) Received: from game (12-250-179-10.client.attbi.com[12.250.179.10]) by comcast.net (sccrmhc12) with SMTP id <2003112503052801200l34a2e>; Tue, 25 Nov 2003 03:05:28 +0000 Message-ID: <2b8e01c3b301$0bc2f090$0201a8c0@game> Reply-To: "Nid" From: "Nid" To: "Christopher Kings-Lynne" Cc: References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> <3FC2ACCF.2030303@familyhealth.com.au> Subject: Re: Where to start for performance problem? Date: Mon, 24 Nov 2003 21:05:56 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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-Archive-Number: 200311/235 X-Sequence-Number: 4795 I've been digging around in the code and found where we are executing the VACUUM command. VACUUM ANALYZE is executed every 15 minutes. We haven't tried VACUUM FULL ANALYZE. I think I read that using FULL is a good idea once a day or something. Just doing a VACUUM ANALYZE doesn't seem to be preventing our problem. Thank you for the responses. nid ----- Original Message ----- From: "Christopher Kings-Lynne" To: "MK Spam" Cc: Sent: Monday, November 24, 2003 7:13 PM Subject: Re: [PERFORM] Where to start for performance problem? > > 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 using the VACUUM command on a schedule but it doesn't seem to > > prevent the database from becoming "congested" as we refer to it. :] > > Our busy website has a cronjob that runs VACUUM ANALYZE once an hour > (vacuumdb -a -q -z). > > Have you tried going 'VACUUM FULL ANALYZE' (vacuumdb -a -q -z -f) > instead of a dump and reload? > > Chris > > > From chriskl@familyhealth.com.au Tue Nov 25 03:16:40 2003 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 CB6FED1DBAC for ; Tue, 25 Nov 2003 03:16:40 +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 16507-03 for ; Mon, 24 Nov 2003 23:16:13 -0400 (AST) Received: from houston.familyhealth.com.au (fhnet.arach.net.au [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id CDB3AD1D888 for ; Mon, 24 Nov 2003 23:16:07 -0400 (AST) Received: from familyhealth.com.au (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9p1/8.12.9) with ESMTP id hAP3G8oD033859; Tue, 25 Nov 2003 11:16:08 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <3FC2CA18.3080001@familyhealth.com.au> Date: Tue, 25 Nov 2003 11:18:48 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031013 Thunderbird/0.3 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Nid Cc: pgsql-performance@postgresql.org Subject: Re: Where to start for performance problem? References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> <3FC2ACCF.2030303@familyhealth.com.au> <2b8e01c3b301$0bc2f090$0201a8c0@game> In-Reply-To: <2b8e01c3b301$0bc2f090$0201a8c0@game> 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: 200311/236 X-Sequence-Number: 4796 Nid wrote: > I've been digging around in the code and found where we are executing the > VACUUM command. VACUUM ANALYZE is executed every 15 minutes. We haven't > tried VACUUM FULL ANALYZE. I think I read that using FULL is a good idea > once a day or something. Just doing a VACUUM ANALYZE doesn't seem to be > preventing our problem. Thank you for the responses. Try upgrading to PostgreSQL 7.4 and use the new pg_autovacuum daemon. This daemon will monitor your tables and vacuum and analyze whenever necessary. Chris From rob@dsvr.net Tue Nov 25 14:05:46 2003 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 9AB24D1DC04 for ; Tue, 25 Nov 2003 14:05:46 +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 57255-01 for ; Tue, 25 Nov 2003 10:05:19 -0400 (AST) Received: from mail.dsvr.co.uk (mail.dsvr.co.uk [212.69.192.9]) by svr1.postgresql.org (Postfix) with ESMTP id 53F83D1DC3E for ; Tue, 25 Nov 2003 10:05:15 -0400 (AST) Received: from dsvr.net (eth0.rt1.o1-1.tck.dsvr.net [::ffff:212.69.216.20]) by mail.dsvr.co.uk with esmtp; Tue, 25 Nov 2003 14:05:17 +0000 Message-ID: <3FC3623E.3020304@dsvr.net> Date: Tue, 25 Nov 2003 14:07:58 +0000 From: Rob Fielding User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030807 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Where to start for performance problem? References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> In-Reply-To: <2b1f01c3b2d6$c403ba70$0201a8c0@game> 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: 200311/239 X-Sequence-Number: 4799 The problems with giving suggestions about increasing performance is that one persons increase is another persons decrease. having said that, there are a few general suggestions : Set-up some shared memory, about a tenth of your available RAM, and configure shared_memory and max_clients correctly. I've used the following formula, ripped off the net from somewhere. It's not entirely acurate, as other settings steal a little shared memory, but it works for the most part : ((1024*RAM_SIZE) - (14.2 * max_connections) - 250) / 8.2 as I say, it should get you a good value, otherwise lower it bit by bit if you have trouble starting your db. Increase effective_cache (50%-70% avail ram) and sort_mem (about 1/20th ram) and lower you random_page_cost to around 2 or less (as low as 0.3) if you have fast SCSI drives in a RAID10 set-up - this was a big speedup ;) But this might not be the answer though. The values detailed above are when tuning an already stable setup. Perhaps you need to look at your system resource usage. If you're degrading performance over time it sounds to me like you are slowly running out of memory and swap ? Generall if I take something over, I'll try and get it onto my terms. Have you tried importing the DB to a fresh installation, one where you know sensible defaults are set, so you aren't inheriting any cruft from the previous sysadmin. To be honest tho, I've never run pg so that it actually shutdown because it was running so badly - i just wouldn't think it would do that. -- Rob Fielding rob@dsvr.net www.dsvr.co.uk Development Designer Servers Ltd From bruno@wolff.to Tue Nov 25 16:54:05 2003 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 8DFA0D1B440 for ; Tue, 25 Nov 2003 16:54:05 +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 95033-06 for ; Tue, 25 Nov 2003 12:53:33 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id CAD60D1B438 for ; Tue, 25 Nov 2003 12:53:33 -0400 (AST) Received: (qmail 25386 invoked by uid 500); 25 Nov 2003 16:52:32 -0000 Date: Tue, 25 Nov 2003 10:52:32 -0600 From: Bruno Wolff III To: MK Spam Cc: pgsql-performance@postgresql.org Subject: Re: Where to start for performance problem? Message-ID: <20031125165232.GA25043@wolff.to> Mail-Followup-To: MK Spam , pgsql-performance@postgresql.org References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2b1f01c3b2d6$c403ba70$0201a8c0@game> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/241 X-Sequence-Number: 4801 On Mon, Nov 24, 2003 at 16:03:17 -0600, MK Spam wrote: > > 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 using the VACUUM command on a schedule but it doesn't seem to prevent the database 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 performance 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. A couple of things you might look for are index bloat and having FSM set too small for your plain vacuums. Upgrading to 7.4 may help with index bloat if that is your problem. From tgl@sss.pgh.pa.us Tue Nov 25 17:08:45 2003 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 6E737D1B448 for ; Tue, 25 Nov 2003 17:08:45 +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 98193-07 for ; Tue, 25 Nov 2003 13:08:13 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id C764AD1B444 for ; Tue, 25 Nov 2003 13:07:50 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAPH7n19028468; Tue, 25 Nov 2003 12:07:49 -0500 (EST) To: MK Spam Cc: pgsql-performance@postgresql.org Subject: Re: Where to start for performance problem? In-reply-to: <20031125165232.GA25043@wolff.to> References: <2b1f01c3b2d6$c403ba70$0201a8c0@game> <20031125165232.GA25043@wolff.to> Comments: In-reply-to Bruno Wolff III message dated "Tue, 25 Nov 2003 10:52:32 -0600" Date: Tue, 25 Nov 2003 12:07:49 -0500 Message-ID: <28467.1069780069@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/242 X-Sequence-Number: 4802 MK Spam wrote: > ... the problem we are having is gradually degrading > performance ending in postgres shutting down. As someone else commented, that's not an ordinary sort of performance problem. What exactly happens when the database "shuts down"? regards, tom lane