Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

Douglas J Hunley wrote:

Hello

> The DB server in question does nothing else, is running CentOS 4.5, kernel
> 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon
> 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.
>

After a very quick read of your configuration files, I found some
paramaters that need to be change if your server has 8GB of RAM. The
values of these parameters depend a lot of how much RAM you have, what
type of database you have (reading vs. writing) and how big the database is.

I do not have experience with 8.2.x yet, but with 8.1.x we are using as
defaults in out 8GB RAM servers these values in some of the paramaters
(they are not the only ones, but they are the minimum to change):

25% of RAM for shared_buffers
2/3 of ram for effective_cache_size
256MB for maintenance_work_mem
32-64MB for work_mem
128 checkpoint_segments
2 random_page_cost

And the most important of all:

fsync should be ***ON*** if you appreciate your data.

It looks like you are using default values ....

>
> #shared_buffers = 32MB # min 128kB or max_connections*16kB
> #work_mem = 1MB # min 64kB
> #maintenance_work_mem = 16MB # min 1MB
> fsync = off # turns forced synchronization on or off
> #effective_cache_size = 128MB
[........................]

--
Rafael Martinez, <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/>