Re: Recommended optimisations slows down PostgreSQL 8.4

On Thu, Aug 11, 2011 at 04:35:34PM -0700, Waldo Nell wrote:
> I have PostgreSQL 8.4.8 on Ubuntu Linux x64.  Server is a Core i7 950 with 6GB of RAM.  2GB of RAM us used by Java,
somesmall amount by the kernel / services and the rest is available to PostgreSQL.  Hard drive is a single 7200 RPM
SATA1TB Caviar Black HDD.  No other applications / processes are running when I perform my tests. 
>
> I have an application that performs about 80% reads and 20% writes for a specific billrun.   It takes about 60
minutesto complete, and I can have it perform precisely the same queries repeatedly.  I have consistently showed that
whenshared_buffers = 24MB (the default), and wal_buffers = 64kB, the system completes the process in 50 minutes.  When
Ibump shared_buffers to 1500MB, the system slows down and takes 60 minutes to complete the same process.  Changing that
backto 24MB, but then changing wal_buffers to 16MB has the same impact - performance drops from 50 minutes to about 61
minutes. Changing those two parameters back to the defaults returns the time to 50 minutes. 
>
> fsync = off for these tests - not sure if it is relevant.  All other settings are at their defaults.
>
> Please explain why the system is slower with the recommended values for these two settings?  The DB is about 74GB,
thelargest table has 180 million rows. 

One guess is that you are using the defaults for other costing parameters and they
do not accurately reflect your system. This means that it will be a crap shoot as
to whether a plan is faster or slower and what will affect the timing.

Regards,
Ken