Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 221E89FA102 for ; Thu, 28 Dec 2006 23:35:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 68123-03 for ; Thu, 28 Dec 2006 23:35:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by postgresql.org (Postfix) with SMTP id 5171B9F9F47 for ; Thu, 28 Dec 2006 23:35:24 -0400 (AST) Received: (qmail 3239 invoked from network); 29 Dec 2006 03:35:18 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.14?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 29 Dec 2006 03:35:18 -0000 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: multipart/alternative; boundary=Apple-Mail-6-77568909 Message-Id: <9389A1F0-9492-48EB-8802-C635F59BB9AE@fastcrypt.com> Cc: pgsql-performance@postgresql.org From: Dave Cramer Subject: Re: Postgresql Configutation and overflow Date: Thu, 28 Dec 2006 22:35:29 -0500 To: =?ISO-8859-1?Q?fabrix_pe=F1uelas?= X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/522 X-Sequence-Number: 22578 --Apple-Mail-6-77568909 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Hi, On 28-Dec-06, at 8:58 PM, fabrix pe=F1uelas wrote: > Good day, > > I have been reading about the configuration of postgresql, but I =20 > have a server who does not give me the performance that should. The =20= > tables are indexed and made vacuum regularly, i monitor with top, =20 > ps and pg_stat_activity and when i checked was slow without a heavy =20= > load overage. > > Before, the server reached 2000 connections to postgresql (with =20 > max_connections=3D3000 in it for future workflow). Why would you need 2000 connections ? > > I divided the load with another server for better performance, and =20 > now reach 500 connections, but yet is overflow. > > > My question is about how much memory should i configure in =20 > shared_buffers and effective_cache_size. start with 25% of your 12G as shared buffers, and 75% of 12G for =20 effective cache You can go higher for shared buffers, but only do so with testing. Dave > > Features: > > - 4 Processsors Intel Xeon Dual 3.0Ghz > - 12 GB RAM > - 2 discos en RAID 1 for OS > - 4 discs RAID 5 for DB > - S.O Slackware 11.0 Linux 2.6.17.7 > - Postgres 8.1.4 > > > =3D=3D=3D=3D=3DIn internet i found this: > > Tuning PostgreSQL for performance > 2 Some basic parameters > 2.1 Shared buffers > > # Start at 4MB (512) for a workstation > # Medium size data set and 256-512MB available RAM: 16-32MB =20 > (2048-4096) > # Large dataset and lots of available RAM (1-4GB): 64-256MB =20 > (8192-32768) > =3D=3D=3D=3D=3D=3D > > > My postgresql.conf configuration is: > > #---------------------------------------------------------------------=20= > ------ > # FILE LOCATIONS > #---------------------------------------------------------------------=20= > ------ > > # The default values of these variables are driven from the -D =20 > command line > # switch or PGDATA environment variable, represented here as =20 > ConfigDir. > > #data_directory =3D 'ConfigDir' # use data in another directory > #hba_file =3D 'ConfigDir/pg_hba.conf' # host-based authentication =20= > file > #ident_file =3D 'ConfigDir/pg_ident.conf' # IDENT configuration = file > > # If external_pid_file is not explicitly set, no extra pid file is =20 > written. > #external_pid_file =3D '(none)' # write an extra pid file > > > #---------------------------------------------------------------------=20= > ------ > # CONNECTIONS AND AUTHENTICATION > #---------------------------------------------------------------------=20= > ------ > > # - Connection Settings - > > listen_addresses =3D '*' # what IP address(es) to listen on; > # comma-separated list of addresses; > # defaults to 'localhost', '*' =3D all > port =3D 5432 > max_connections =3D 3000 > # note: increasing max_connections costs ~400 bytes of shared =20 > memory per > # connection slot, plus lock space (see =20 > max_locks_per_transaction). You > # might also need to raise shared_buffers to support more connections. > #superuser_reserved_connections =3D 2 > #unix_socket_directory =3D '' > #unix_socket_group =3D '' > #unix_socket_permissions =3D 0777 # octal > #bonjour_name =3D '' # defaults to the computer name > > > > #---------------------------------------------------------------------=20= > ------ > # RESOURCE USAGE (except WAL) > #---------------------------------------------------------------------=20= > ------ > > # - Memory - > > > shared_buffers =3D 81920 # min 16 or max_connections*2, =20 > 8KB each > temp_buffers =3D 5000 # min 100, 8KB each > max_prepared_transactions =3D 1000 # can be 0 or more > > # note: increasing max_prepared_transactions costs ~600 bytes of =20 > shared memory > > # per transaction slot, plus lock space (see =20 > max_locks_per_transaction). > work_mem =3D 10240 # min 64, size in KB > maintenance_work_mem =3D 253952 # min 1024, size in KB > max_stack_depth =3D 4096 # min 100, size in KB > > # - Free Space Map - > > #max_fsm_pages =3D 20000 # min max_fsm_relations*16, 6 =20 > bytes each > #max_fsm_relations =3D 1000 # min 100, ~70 bytes each > > # - Kernel Resource Usage - > > #max_files_per_process =3D 1000 # min 25 > #preload_libraries =3D '' > > # - Cost-Based Vacuum Delay - > > #vacuum_cost_delay =3D 0 # 0-1000 milliseconds > #vacuum_cost_page_hit =3D 1 # 0-10000 credits > #vacuum_cost_page_miss =3D 10 # 0-10000 credits > #vacuum_cost_page_dirty =3D 20 # 0-10000 credits > #vacuum_cost_limit =3D 200 # 0-10000 credits > > # - Background writer - > > #bgwriter_delay =3D 200 # 10-10000 milliseconds between =20 > rounds > #bgwriter_lru_percent =3D 1.0 # 0-100% of LRU buffers scanned/=20= > round > #bgwriter_lru_maxpages =3D 5 # 0-1000 buffers max written/round > #bgwriter_all_percent =3D 0.333 # 0-100% of all buffers =20 > scanned/round > #bgwriter_all_maxpages =3D 5 # 0-1000 buffers max written/round > > > #---------------------------------------------------------------------=20= > ------ > # WRITE AHEAD LOG > #---------------------------------------------------------------------=20= > ------ > > # - Settings - > > #fsync =3D on # turns forced synchronization on or off > #wal_sync_method =3D fsync # the default is the first option > # supported by the operating system: > # open_datasync > # fdatasync > # fsync > # fsync_writethrough > # open_sync > #full_page_writes =3D on # recover from partial page writes > #wal_buffers =3D 8 # min 4, 8KB each > #commit_delay =3D 0 # range 0-100000, in microseconds > #commit_siblings =3D 5 # range 1-1000 > > # - Checkpoints - > > checkpoint_segments =3D 20 # in logfile segments, min 1, 16MB =20= > each > #checkpoint_timeout =3D 300 # range 30-3600, in seconds > #checkpoint_warning =3D 30 # in seconds, 0 is off > > # - Archiving - > > #archive_command =3D '' # command to use to archive a = logfile > # segment > > > #---------------------------------------------------------------------=20= > ------ > # QUERY TUNING > #---------------------------------------------------------------------=20= > ------ > > # - Planner Method Configuration - > > #enable_bitmapscan =3D on > #enable_hashagg =3D on > #enable_hashjoin =3D on > #enable_indexscan =3D on > #enable_mergejoin =3D on > enable_nestloop =3D off > enable_seqscan =3D off > #enable_sort =3D on > #enable_tidscan =3D on > > # - Planner Cost Constants - > > effective_cache_size =3D 65536 # typically 8KB each > #random_page_cost =3D 4 # units are one sequential page = fetch > # cost > #cpu_tuple_cost =3D 0.01 # (same) > #cpu_index_tuple_cost =3D 0.001 # (same) > #cpu_operator_cost =3D 0.0025 # (same) > > > the sysctl.conf > > kernel.shmmax =3D 970170573 > kernel.shmall =3D 970170573 > kernel.sem =3D 400 42000 32 1024 > vm.overcommit_memory =3D 2 > > =3D=3D=3D=3D=3D=3D=3D=3D=3DThe configuration is correct?=3D=3D=3D=3D=3D=3D= =3D > > If you can help me i will be pleased, thanks. > --Apple-Mail-6-77568909 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 Hi,

On = 28-Dec-06, at 8:58 PM, fabrix pe=F1uelas wrote:

Good = day,
=A0=A0 =A0
I have been reading about the configuration of = postgresql, but I have a server who does not give me the performance = that should. The tables are indexed and made vacuum regularly, i monitor = with top, ps and pg_stat_activity and when i checked was slow without a = heavy load overage.

Before, the server reached 2000 connections = to postgresql (with max_connections=3D3000 in it for future workflow). =
Why would you need 2000 connections ?=A0

I divided the load with another server for better = performance, and now reach 500 connections, but yet is overflow. =


My question is about how much memory should i configure in = shared_buffers and effective_cache_size.

start with 25% of your 12G as = shared buffers, and 75% of 12G for effective cache

You can go higher for = shared buffers, but only do so with testing.

Dave

Features:

- 4 Processsors Intel Xeon Dual = 3.0Ghz
- 12 GB RAM
- 2 discos en RAID 1 for OS
- 4 discs RAID = 5 for DB
- S.O Slackware 11.0 Linux 2.6.17.7
- Postgres = 8.1.4


=3D=3D=3D=3D=3DIn internet i found this:

Tuning = PostgreSQL for performance
2 Some basic parameters
2.1 Shared = buffers

#=A0 Start at 4MB (512) for a workstation
# Medium = size data set and 256-512MB available RAM: 16-32MB (2048-4096)
# = Large dataset and lots of available RAM (1-4GB): 64-256MB = (8192-32768)
=3D=3D=3D=3D=3D=3D


My postgresql.conf = configuration = is:

#--------------------------------------------------------------= -------------
# FILE = LOCATIONS
#------------------------------------------------------------= ---------------

# The default values of these variables are = driven from the -D command line
# switch or PGDATA environment = variable, represented here as ConfigDir.

#data_directory =3D = 'ConfigDir'=A0=A0 =A0=A0=A0 =A0# use data in another directory =
#hba_file =3D 'ConfigDir/pg_hba.conf'=A0=A0 =A0# host-based = authentication file
#ident_file =3D 'ConfigDir/pg_ident.conf'=A0=A0 = =A0# IDENT configuration file

# If external_pid_file is not = explicitly set, no extra pid file is written.
#external_pid_file =3D = '(none)'=A0=A0 =A0=A0=A0 =A0# write an extra pid = file


#---------------------------------------------------------= ------------------
# CONNECTIONS AND = AUTHENTICATION
#-------------------------------------------------------= --------------------

# - Connection Settings = -

listen_addresses =3D '*'=A0=A0 =A0=A0=A0 =A0# what IP = address(es) to listen on;
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0= =A0# comma-separated list of addresses;
=A0=A0 =A0=A0=A0 =A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0# defaults to 'localhost', '*' =3D all
port =3D= 5432
max_connections =3D 3000
# note: increasing max_connections = costs ~400 bytes of shared memory per
# connection slot, plus lock = space (see max_locks_per_transaction).=A0 You
# might also need to = raise shared_buffers to support more connections. =
#superuser_reserved_connections =3D 2
#unix_socket_directory =3D = ''
#unix_socket_group =3D ''
#unix_socket_permissions =3D 0777=A0=A0= =A0=A0=A0 =A0# octal
#bonjour_name =3D ''=A0=A0 =A0=A0=A0 =A0=A0=A0 = =A0# defaults to the computer name =



#---------------------------------------------------------= ------------------
# RESOURCE USAGE (except = WAL)
#-----------------------------------------------------------------= ----------

# - Memory -


shared_buffers =3D 81920=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0# min 16 or max_connections*2, 8KB = each
temp_buffers =3D 5000=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# min 100, = 8KB each
max_prepared_transactions =3D 1000=A0=A0 =A0# 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 =3D 10240=A0=A0 =A0=A0=A0 = =A0=A0=A0 =A0# min 64, size in KB
maintenance_work_mem =3D 253952=A0=A0= =A0=A0=A0 =A0# min 1024, size in KB
max_stack_depth =3D 4096=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0# min 100, size in KB

# - Free Space Map = -

#max_fsm_pages =3D 20000=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# min = max_fsm_relations*16, 6 bytes each
#max_fsm_relations =3D 1000=A0=A0 = =A0=A0=A0 =A0# min 100, ~70 bytes each

# - Kernel Resource Usage = -

#max_files_per_process =3D 1000=A0=A0 =A0=A0=A0 =A0# min 25 =
#preload_libraries =3D ''

# - Cost-Based Vacuum Delay = -

#vacuum_cost_delay =3D 0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# 0-1000 = milliseconds
#vacuum_cost_page_hit =3D 1=A0=A0 =A0=A0=A0 =A0# 0-10000 = credits
#vacuum_cost_page_miss =3D 10=A0=A0 =A0=A0=A0 =A0# 0-10000 = credits
#vacuum_cost_page_dirty =3D 20=A0=A0 =A0=A0=A0 =A0# 0-10000 = credits
#vacuum_cost_limit =3D 200=A0=A0 =A0=A0=A0 =A0# 0-10000 = credits

# - Background writer -

#bgwriter_delay =3D 200=A0=A0= =A0=A0=A0 =A0=A0=A0 =A0# 10-10000 milliseconds between rounds
= #bgwriter_lru_percent =3D 1.0=A0=A0 =A0=A0=A0 =A0# 0-100% of LRU buffers = scanned/round
#bgwriter_lru_maxpages =3D 5=A0=A0 =A0=A0=A0 =A0# = 0-1000 buffers max written/round
#bgwriter_all_percent =3D 0.333=A0=A0 = =A0=A0=A0 =A0# 0-100% of all buffers scanned/round =
#bgwriter_all_maxpages =3D 5=A0=A0 =A0=A0=A0 =A0# 0-1000 buffers max = written/round


#------------------------------------------------= ---------------------------
# WRITE AHEAD = LOG
#------------------------------------------------------------------= ---------

# - Settings -

#fsync =3D on=A0=A0 =A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0# turns forced synchronization on or = off
#wal_sync_method =3D fsync=A0=A0 =A0=A0=A0 =A0# the default is = the first option
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# = supported by the operating system:
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0= =A0=A0=A0 =A0#=A0=A0 open_datasync
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0= =A0=A0=A0 =A0#=A0=A0 fdatasync
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 = =A0=A0=A0 =A0#=A0=A0 fsync
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0= =A0#=A0=A0 fsync_writethrough
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 = =A0=A0=A0 =A0#=A0=A0 open_sync
#full_page_writes =3D on=A0=A0 =A0=A0=A0= =A0=A0=A0 =A0# recover from partial page writes
#wal_buffers =3D = 8=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# min 4, 8KB each
#commit_delay =3D = 0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# range 0-100000, in = microseconds
#commit_siblings =3D 5=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# = range 1-1000

# - Checkpoints -

checkpoint_segments =3D = 20=A0=A0 =A0=A0=A0 =A0# in logfile segments, min 1, 16MB each =
#checkpoint_timeout =3D 300=A0=A0 =A0=A0=A0 =A0# range 30-3600, in = seconds
#checkpoint_warning =3D 30=A0=A0 =A0=A0=A0 =A0# in seconds, 0 = is off

# - Archiving -

#archive_command =3D ''=A0=A0 =A0=A0=A0= =A0=A0=A0 =A0# command to use to archive a logfile
=A0=A0 =A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# = segment


#------------------------------------------------------= ---------------------
# QUERY = TUNING
#---------------------------------------------------------------= ------------

# - Planner Method Configuration = -

#enable_bitmapscan =3D on
#enable_hashagg =3D = on
#enable_hashjoin =3D on
#enable_indexscan =3D = on
#enable_mergejoin =3D on
enable_nestloop =3D = off
enable_seqscan =3D off
#enable_sort =3D on
#enable_tidscan = =3D on

# - Planner Cost Constants -

effective_cache_size =3D= 65536=A0=A0 =A0=A0=A0 =A0# typically 8KB each
#random_page_cost =3D = 4=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# units are one sequential page = fetch
=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# cost =
#cpu_tuple_cost =3D 0.01=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0# = (same)
#cpu_index_tuple_cost =3D 0.001=A0=A0 =A0=A0=A0 =A0# = (same)
#cpu_operator_cost =3D 0.0025=A0=A0 =A0=A0=A0 =A0# = (same)


the sysctl.conf

kernel.shmmax =3D = 970170573
kernel.shmall =3D 970170573
kernel.sem =3D 400 42000 32 = 1024
vm.overcommit_memory =3D 2

=3D=3D=3D=3D=3D=3D=3D=3D=3DThe = configuration is correct?=3D=3D=3D=3D=3D=3D=3D

If you can help me = i will be pleased, = thanks.


= --Apple-Mail-6-77568909--