Date: Thu, 15 Dec 2005 13:29:43 +1100 (EST)
From: Gavin Sherry <swm@alcove.com.au>
To: Harry Jackson <harryjackson@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance question.
In-Reply-To: <45b42ce40512141751i6829eec0q6d088b1ec5bc27b4@mail.gmail.com>
Message-ID: <Pine.LNX.4.58.0512151314190.19172@linuxworld.com.au>
References: <45b42ce40512141751i6829eec0q6d088b1ec5bc27b4@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 15 Dec 2005, Harry Jackson wrote:

> Hi all,

> I have been using PostgreSQL (currently 7.4.7) for several years now and
> am very happy with it but I currently run a website that has had a
> little bit of a boost and I am starting to see some performance problems
> (Not necessarily PostgreSQL).

Definately plan an 8.1 upgrade.

[snip]

> The database has been allocated 2Gb worth of shared buffers and I have
> tweaked most of the settings in the config recently to see if I could
> increase the performance any more and have seen very little performance
> gain for the various types of queries that I am running.

2 GB is too much for 7.4. I'm not sure about 8.1 because there hasn't been
any conclusive testing I think. OSDL is using 200000, which is ~1.5GB.

Why not turn on log_min_duration_statement or process the log with PQA
(http://pgfoundry.org/projects/pqa/) to look for expensive queries.

Also, why kind of IO load are you seeing (iostat will tell you).

> It would appear that the only alternative may be a new machine that has
> a better disk subsystem or a large disk array then bung more RAM in the
> Opteron machine (max 16Gb 4Gb fitted) or purchase another machine with
> built in U320 SCSI ie an HP Proliant DL380 or Dell 2850.

Have a look at what your IO load is like, first.


> Some indication of current performance is as follows. I know these
> statements are hardly indicative of a full running application and
> everything that goes with it but I would be very interested in hearing
> if anyone has a similar setup and is able to squeeze a lot more out of
> PostgreSQL. From what I can see here the numbers look OK for the
> hardware I am running on and that its not PostgreSQL that is the
> problem.

> Inserting 1 million rows into the following table.These are raw insert
> statements.

[snip]

Yes, the performance looks a bit poor. I'd say that 8.1 will help address
that.

Also, don't under estimate the effects of CLUSTER on performance,
particularly <8.1.

Thanks,

Gavin