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
