Re: postgresql-8.0.1 performance tuning

Martin Fandel wrote:

> i'm trying to tune my postgresql-db but i don't know if the values are
> I use the following environment for the postgres-db:

I assumed you're running Linux here, you don't mention it.

> ######### Hardware ############
> cpu: 2x P4 3Ghz
> ram: 1024MB DDR 266Mhz

I think 1Gb RAM is quite minimal, nowadays.
Read below.

> partitions:
> /dev/sda3 23G 9,6G 13G 44% /
> /dev/sda1 11G 156M 9,9G 2% /var
> /dev/sdb1 69G 13G 57G 19% /var/lib/pgsql
>
> /dev/sda is in raid 1 (2x 35GB / 10000upm / sca)
> /dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)

I've seen good performance boost (and machine load lowered)
switching to 15k rpm disks.

> ######### Config ############
> /etc/sysctl.conf:
> kernel.shmall = 786432000
> kernel.shmmax = 786432000

I think you have a problem here.
kernel.shmmax should *not* be set to an amount of RAM, but
to maximum number of shared memory pages, which on a typical linux system
is 4kb. Google around:

http://www.google.com/search?q=kernel.shmall+tuning+postgresql+shared+memory

> /etc/fstab:
> /dev/sdb1 /var/lib/pgsql reiserfs acl,user_xattr,noatime,data=writeback 1 2

I use similar settings on ext3 (which I'm told it is slower than reiser
or xfs or jfs).

I indicate the values I use for a machine with 4Gb RAM
and more 15 krpm disks but layout similar to yours.
(3 x RAID1 arrays for os, logs, ... and 1 x RAID10 array with 12 disks)

For Pg configuration (others please comment on these values,
it is invaluable to have feedback from this list).

> /var/lib/pgsql/data/postgresql.conf
> superuser_reserved_connections = 2
> shared_buffers = 3000
16384

> work_mem = 131072
32768

> maintenance_work_mem = 131072
262144

> max_fsm_pages = 20000
200000

> fsync = true
false

> commit_delay = 0
> commit_siblings = 5
If you have an high transactions volume, you should
really investigate on these ones.

> effective_cache_size = 10000
40000

> random_page_cost = 4
Check out for unwanted "seq scans". If you have really fast
disks, you should experiment lowering a little this parameter.

> max_locks_per_transaction = 64
512

> I'm really new at using postgres. So i need some experience to set this
> parameters in the postgresql- and the system-config. I can't find standard
> calculations for this. :/ The postgresql-documentation doesn't help me to
> set the best values for this.

There's no such thing as "standard calculations" :-)

> The database must be high-availble. I configured rsync to sync the complete
> /var/lib/pgsql-directory to my hot-standby
> [...]
> In my tests the synchronization works fine. I synchronised the hole
> consistent.
> [...]
> Is this solution recommended? Or must i use archived wal's with
> real system-snapshots?

In some situations, I also used rsync to do the job.
Obviously, always stop the postmaster before syncing.

Maybe you can look at "slony", if you haven't yet.

http://www.slony.info

--
Cosimo