From: Gavin Sherry <swm@alcove.com.au>
To: Harry Jackson <harryjackson@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL performance question.
Date: Thu, 15 Dec 2005 13:29:43 +1100 (EST)
Message-ID: <Pine.LNX.4.58.0512151314190.19172@linuxworld.com.au> (raw)
In-Reply-To: <45b42ce40512141751i6829eec0q6d088b1ec5bc27b4@mail.gmail.com>
References: <45b42ce40512141751i6829eec0q6d088b1ec5bc27b4@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: swm@alcove.com.au, harryjackson@gmail.com
Subject: Re: PostgreSQL performance question.
In-Reply-To: <Pine.LNX.4.58.0512151314190.19172@linuxworld.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox