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
