Message-ID: <429CB109.5080908@arbash-meinel.com>
Date: Tue, 31 May 2005 13:46:33 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: Martin Fandel <martin.fandel@alphyra-evs.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgresql-8.0.1 performance tuning
References: <1117201312.7060.6.camel@fandelm.ecommit.de>
In-Reply-To: <1117201312.7060.6.camel@fandelm.ecommit.de>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig4D765F634D1FADE6C5F7A1D4"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig4D765F634D1FADE6C5F7A1D4
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Martin Fandel wrote:

> Hi @ all,
>
> i'm trying to tune my postgresql-db but i don't know if the values are
> right
> set.
>
> I use the following environment for the postgres-db:
>
> ######### Hardware ############
> cpu: 2x P4 3Ghz
> ram: 1024MB DDR 266Mhz
>
> 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)
> ######### /Hardware ############

You probably want to put the pg_xlog file onto /dev/sda rather than
having it in /dev/sdb. Having it separate from the data usually boosts
performance a lot. I believe you can just mv it to a different
directory, and then recreate it as a symlink. (Stop the database first :)

>
> ######### Config ############
> /etc/sysctl.conf:
> kernel.shmall = 786432000
> kernel.shmmax = 786432000
>
Not really sure about these two.

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

> /var/lib/pgsql/data/postgresql.conf
> superuser_reserved_connections  = 2
> shared_buffers                  = 3000
> work_mem                        = 131072
> maintenance_work_mem            = 131072

These both seem pretty large. But it depends on how many concurrent
connections doing sorting/hashing/etc you expect. If you are only
expecting 1 connection, these are probably fine. Otherwise with 1GB of
RAM I would probably make work_mem more like 4096/8192.
Remember, running out of work_mem means postgres will spill to disk,
slowing that query. Running out of RAM causes the system to swap, making
everything slow.

> max_stack_depth                 = 2048
> max_fsm_pages                   = 20000
> max_fsm_relations               = 1000
> max_files_per_process           = 1000
> vacuum_cost_delay               = 10
> vacuum_cost_page_hit            = 1
> vacuum_cost_page_miss           = 10
> vacuum_cost_page_dirty          = 20
> vacuum_cost_limit               = 200
> bgwriter_delay                  = 200
> bgwriter_percent                = 1
> bgwriter_maxpages               = 100
> fsync                           = true
> wal_sync_method                 = fsync
> wal_buffers                     = 64
> commit_delay                    = 0
> commit_siblings                 = 5
> checkpoint_segments             = 256
> checkpoint_timeout              = 900
> checkpoint_warning              = 30
> effective_cache_size            = 10000
> random_page_cost                = 4
> cpu_tuple_cost                  = 0.01
> cpu_index_tuple_cost            = 0.001
> cpu_operator_cost               = 0.0025
> geqo                            = true
> geqo_threshold                  = 12
> geqo_effort                     = 5
> geqo_pool_size                  = 0
> geqo_generations                = 0
> geqo_selection_bias             = 2.0
> deadlock_timeout                = 1000
> max_locks_per_transaction       = 64
> ######### /Config ############
>
> ######### Transactions ############
> we have about 115-300 transactions/min in about 65 tables.
> ######### /Transactions ############
>
> 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.
>
> The database must be high-availble. I configured rsync to sync the
> complete
> /var/lib/pgsql-directory to my hot-standby. On the hotstandby i will
> make the
> dumps of the database to improve the performance of the master-db.
>
I didn't think an rsync was completely valid. Probably you should look
more into Slony.
http://slony.info

It is a single-master asynchronous replication system. I believe it is
pretty easy to setup, and does what you really want.

> In my tests the synchronization works fine. I synchronised the hole
> directory
> and restarted the database of the hotstandby. While restarting,
> postgresql turned
> back the old (not archived) wals and the database of my hotstandby was
> consistent. Is this solution recommended? Or must i use archived wal's
> with
> real system-snapshots?
>
> best regards,
>
> Martin Fandel

John
=:->


--------------enig4D765F634D1FADE6C5F7A1D4
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCnLELJdeBCYSNAAMRAh4eAJ0TJTxzs7nMs57ibp8BgIS8PS8u0gCfUxBx
SPCvFGnKvPTyMV9DzfbJAyw=
=+1i+
-----END PGP SIGNATURE-----

--------------enig4D765F634D1FADE6C5F7A1D4--