Skip site navigation (1) Skip section navigation (2)

Re: Seeking performance advice and explanation for high I/O on 8.3

From: "Scott Otis" <scott(dot)otis(at)intand(dot)com>
To: "Magnus Hagander" <magnus(at)hagander(dot)net>
Cc: "Robert Haas" <robertmhaas(at)gmail(dot)com>,"Ivan Voras" <ivoras(at)freebsd(dot)org>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Seeking performance advice and explanation for high I/O on 8.3
Date: 2009-09-04 21:55:50
Message-ID: F406B740738CB44A8BCBEF9734ECF663683F8A@albus.intand.com (view raw or flat)
Thread:
Lists: pgsql-performance
So is there anything I can do in 8.3 to help this?  I have tried setting ' track_activities', 'track_counts' and 'autovacuum' to 'off' (which has reduced CPU and I/O a bit) - but the stats collector process is still using up a good deal of CPU and I/O - is there any way to turn stats collecting completely off?

Scott Otis
CIO / Lead Developer
Intand
www.intand.com


-----Original Message-----
From: Magnus Hagander [mailto:magnus(at)hagander(dot)net] 
Sent: Friday, September 04, 2009 1:19 AM
To: Scott Otis
Cc: Robert Haas; Ivan Voras; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

On Fri, Sep 4, 2009 at 08:54, Scott Otis<scott(dot)otis(at)intand(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com>:
>
>> Do you by any chance have a bazillion databases in this cluster?  Can 
>> you do these?
>
>> select sum(1) from pg_database;
>
> 1555

Note that there are two features in 8.4 specifically designed to deal with the situation where you have lots of databases and/or lots of tables (depending on how many tables you have in each database, this would definitely qualify). They both deal with the "pgstats temp file too large generating i/o issue".

First, it will only write the file when it's actually necessary - 8.3 and earlier will always write it.

Second, you will have the ability to move the location of the file to a different filesystem - specifically intended so that you can move it off to a ramdrive.

Could be worth investigating an upgrade for this issue alone. The fact that you don't have to struggle with tuning the FSM in 8.4 is another thing that makes life a *lot* easier in this kind of installations.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2009-09-04 22:09:37
Subject: Re: Seeking performance advice and explanation for high I/O on 8.3
Previous:From: Karl DenningerDate: 2009-09-04 19:15:19
Subject: Planner question - "bit" data types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group