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.

/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 ############
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:

/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).

superuser_reserved_connections  = 2
shared_buffers                  = 3000

work_mem                        = 131072

maintenance_work_mem            = 131072

max_fsm_pages                   = 20000

fsync                           = true

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

effective_cache_size            = 10000

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

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.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to