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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
