From: Cosimo Streppone <cosimo@streppone.it>
To: Martin Fandel <martin.fandel@alphyra-evs.de>
Cc: Postgresql Performance list <pgsql-performance@postgresql.org>
Subject: Re: postgresql-8.0.1 performance tuning
Date: Wed, 01 Jun 2005 07:30:37 +0200
Message-ID: <429D47FD.7090501@streppone.it> (raw)
In-Reply-To: <1117201312.7060.6.camel@fandelm.ecommit.de>
References: <1117201312.7060.6.camel@fandelm.ecommit.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cosimo@streppone.it, martin.fandel@alphyra-evs.de
Subject: Re: postgresql-8.0.1 performance tuning
In-Reply-To: <429D47FD.7090501@streppone.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox