Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 17FDB9FB6BD for ; Fri, 23 Mar 2007 17:50:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 72695-06 for ; Fri, 23 Mar 2007 17:50:15 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id 05F989FB39A for ; Fri, 23 Mar 2007 17:50:18 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so1393526ana for ; Fri, 23 Mar 2007 13:50:18 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=Q/T060+uprm3XbsHwmVSOnN2i7gcy1O8lsWuwNUgp4aY+u4UWn56q+44+sf4vIgl7wIOI9wi7QfJASoubhmElT2QQRb6XQLGBiT6yZdsJQyrpl+txuNHFw9FSypEH/i0suO2/bNeZmB5LAzQtD/gjTCdLoD2I9YmI/5MkNjLBVw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=bZjLAjZVsqBuk40W0mVHxC9w009Tbu2L0qBoI6t95KSKsWzIVq7+nGbvcfzdyOFjxCZXPVAtmX4l5lvU02POp0Plc+suVGSzzQPhiFXxcsar30FQj3DxlfSAdttMfH2MqUYkMm3ajRtWlNUuDzcKHh5VjIG/ET5fy84uuEIBOvo= Received: by 10.114.13.1 with SMTP id 1mr1388117wam.1174683016535; Fri, 23 Mar 2007 13:50:16 -0700 (PDT) Received: by 10.115.55.18 with HTTP; Fri, 23 Mar 2007 13:50:16 -0700 (PDT) Message-ID: <3af7f950703231350k5038c011ja07d7a288fa353e@mail.gmail.com> Date: Sat, 24 Mar 2007 03:50:16 +0700 From: "amrit angsusingh" To: pgsql-performance@postgresql.org Subject: Optimization pg 8.14 and postgresql.conf MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_209777_5236508.1174683016477" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/375 X-Sequence-Number: 23780 ------=_Part_209777_5236508.1174683016477 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I try to change my database server from the older one ie. 2Cpu Xeon 2.4 32 bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the newer one with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 and FC6 X64 PG 8.14 and try to use rather the same parameter from the previous postgresql.conf :- the older server config: shared_buffers = 31744 #sort_mem = 1024 # min 64, size in KB sort_mem = 8192 #vacuum_mem = 8064 # min 1024, size in KB vacuum_mem = 32768 # - Free Space Map - #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' #--------------------------------------------------------------------------- # WRITE AHEAD LOG #--------------------------------------------------------------------------- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8 # min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_segments = 8 #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # 0 is off, in seconds #commit_delay = 0 # range 0-100000, in microseconds commit_delay = 20 #commit_siblings = 5 # range 1-1000 #effective_cache_size = 153600 effective_cache_size = 307800 I use pgbench to test the speed of my older database server and the result is bash-3.00$ pgbench test -t 20 -c 30 -s 50 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 50 number of clients: 30 number of transactions per client: 20 number of transactions actually processed: 600/600 tps = 337.196481 (including connections establishing) tps = 375.478735 (excluding connections establishing) But my newer database server configuration is somewhat like this;- max_connections = 200 #shared_buffers = 2000 # min 16 or max_connections*2, 8KB each shared_buffers = 31744 #temp_buffers = 1000 # min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). #work_mem = 1024 # min 64, size in KB work_mem = 8192 #maintenance_work_mem = 16384 # min 1024, size in KB maintenance_work_mem = 131078 #max_stack_depth = 2048 # min 100, size in KB #commit_delay = 0 # range 0-100000, in microseconds commit_delay = 20 #commit_siblings = 5 # range 1-1000 # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_segments = 8 #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # in seconds, 0 is off #effective_cache_size = 1000 # typically 8KB each effective_cache_size = 307800 #autovacuum = off # enable autovacuum subprocess? #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 500 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before # analyze #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit and the result of pgbench from my new server is only -- pgbench test -t 20 -c 30 -s 50 tps = 197 (including connections establishing) tps = 212 1. How should I adjust my new configuration to improve the performance ? 2. And should I set autovaccum = on and if it is on what is the other proper parameter to be set? Thank a lot for your help..... Amrit Angsusingh Thailand ------=_Part_209777_5236508.1174683016477 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I try to change my database server from the older one ie. 2Cpu Xeon 2.4 32 bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the newer one with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 and FC6 X64 PG 8.14 and try to use  rather the same parameter from the previous postgresql.conf :-
the older server config:
shared_buffers = 31744
#sort_mem = 1024                # min 64, size in KB
sort_mem = 8192
#vacuum_mem = 8064              # min 1024, size in KB
vacuum_mem = 32768
 
# - Free Space Map -
 
#max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000       # min 100, ~50 bytes each
 
# - Kernel Resource Usage -
 
#max_files_per_process = 1000   # min 25
#preload_libraries = ''
 

#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
 
# - Settings -
 
#fsync = true                   # turns forced synchronization on or off
 
#wal_sync_method = fsync        # the default varies across platforms:
                                # fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8                # min 4, 8KB each
 
# - Checkpoints -
 
#checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
checkpoint_segments = 8
#checkpoint_timeout = 300       # range 30-3600, in seconds
#checkpoint_warning = 30        # 0 is off, in seconds
#commit_delay = 0               # range 0-100000, in microseconds
commit_delay = 20
#commit_siblings = 5            # range 1-1000
 
#effective_cache_size = 153600
effective_cache_size = 307800
 
 
I use pgbench to test the speed of my older database server and the result is

bash-3.00$ pgbench  test -t 20 -c 30 -s 50

starting vacuum...end.

transaction type: TPC-B (sort of)

scaling factor: 50

number of clients: 30

number of transactions per client: 20

number of transactions actually processed: 600/600

tps = 337.196481 (including connections establishing)

tps = 375.478735 (excluding connections establishing)

 
But my newer database server configuration is somewhat like this;-
 

max_connections = 200

#shared_buffers = 2000 # min 16 or max_connections*2, 8KB each

shared_buffers = 31744

#temp_buffers = 1000 # min 100, 8KB each

#max_prepared_transactions = 5 # can be 0 or more

# note: increasing max_prepared_transactions costs ~600 bytes of shared memory

# per transaction slot, plus lock space (see max_locks_per_transaction).

#work_mem = 1024 # min 64, size in KB

work_mem = 8192

#maintenance_work_mem = 16384 # min 1024, size in KB

maintenance_work_mem = 131078

#max_stack_depth = 2048 # min 100, size in KB

 

#commit_delay = 0 # range 0-100000, in microseconds

commit_delay = 20

#commit_siblings = 5 # range 1-1000

# - Checkpoints -

#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each

checkpoint_segments = 8

#checkpoint_timeout = 300 # range 30-3600, in seconds

#checkpoint_warning = 30 # in seconds, 0 is off

#effective_cache_size = 1000 # typically 8KB each

effective_cache_size = 307800

#autovacuum = off # enable autovacuum subprocess?

#autovacuum_naptime = 60 # time between autovacuum runs, in secs

#autovacuum_vacuum_threshold = 1000 # min # of tuple updates before

# vacuum

#autovacuum_analyze_threshold = 500 # min # of tuple updates before

# analyze

#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before

# vacuum

#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before

# analyze

#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for

# autovac, -1 means use

# vacuum_cost_delay

#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for

# autovac, -1 means use

# vacuum_cost_limit

and the result of pgbench from my new server is only

-- pgbench  test -t 20 -c 30 -s 50

tps = 197 (including connections establishing)

tps = 212 

 

1. How should I adjust my new configuration to improve the performance ?

2. And should I set autovaccum = on and if it is on what is the other proper parameter to be set?

 

Thank a lot for your help.....
 

Amrit Angsusingh
Thailand
------=_Part_209777_5236508.1174683016477--