X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 16CE652809 for ; Fri, 27 May 2005 10:43:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64873-06 for ; Fri, 27 May 2005 13:43:19 +0000 (GMT) Received: from titan.ecommit.de (titan.ecommit.de [195.145.58.245]) by svr1.postgresql.org (Postfix) with ESMTP id A8BD25285F for ; Fri, 27 May 2005 10:43:15 -0300 (ADT) Received: from titan.ecommit.de (localhost [127.0.0.1]) by titan.ecommit.de (8.12.6/8.12.6/SuSE Linux 0.6) with ESMTP id j4RDhI0A004976 for ; Fri, 27 May 2005 15:43:18 +0200 Received: from titan.ecommit.de (root@localhost) by titan.ecommit.de (8.12.6/8.12.6/Submit) with ESMTP id j4RDhImQ004975 for ; Fri, 27 May 2005 15:43:18 +0200 Received: from fandelm.ecommit.de (fandelm.tc.de 192.168.252.51) by titan.ecommit.de (Scalix SMTP Relay 9.0.0.26) via ESMTP; Fri, 27 May 2005 15:43:16 +0200 (CEST) Date: Fri, 27 May 2005 15:41:52 +0200 From: "Martin Fandel" To: pgsql-performance@postgresql.org Message-ID: <1117201312.7060.6.camel@fandelm.ecommit.de> Subject: postgresql-8.0.1 performance tuning x-scalix-Hops: 1 X-Mailer: Evolution 2.0.1 Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=-+JiXHr8v6YjeDVhOEhoF" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.028 tagged_above=0 required=5 tests=HTML_60_70, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200505/410 X-Sequence-Number: 12574 --=-+JiXHr8v6YjeDVhOEhoF Content-Type: text/plain Content-Disposition: inline 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 ############ ######### Config ############ /etc/sysctl.conf: kernel.shmall = 786432000 kernel.shmmax = 786432000 /etc/fstab: /dev/sdb1 /var/lib/pgsql reiserfs acl,user_xattr,noatime,data=writeback 1 2 /var/lib/pgsql/data/postgresql.conf superuser_reserved_connections = 2 shared_buffers = 3000 work_mem = 131072 maintenance_work_mem = 131072 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. 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 --=-+JiXHr8v6YjeDVhOEhoF Content-Type: text/html; charset="utf-8" Content-Disposition: inline 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 ############

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

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

/var/lib/pgsql/data/postgresql.conf
superuser_reserved_connections  = 2
shared_buffers                  = 3000
work_mem                        = 131072
maintenance_work_mem            = 131072
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.

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 --=-+JiXHr8v6YjeDVhOEhoF--