Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 473869FA304 for ; Thu, 28 Dec 2006 22:12:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 77317-04 for ; Thu, 28 Dec 2006 22:11:56 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from smtp111.sbc.mail.re2.yahoo.com (smtp111.sbc.mail.re2.yahoo.com [68.142.229.94]) by postgresql.org (Postfix) with SMTP id BDFF29FA2FD for ; Thu, 28 Dec 2006 22:11:56 -0400 (AST) Received: (qmail 80362 invoked from network); 29 Dec 2006 02:11:55 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net; h=Received:X-YMail-OSG:From:To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE:In-Reply-To:Importance; b=dfUkpT81HIU1sEbkX5vq6V4FR+7zXsvEyVt8xCKY8wrWgLNrlQ9kyXL96BTEuKgf+tUMlsSgjR42ZhYHHvXuysuNYYO5AYXP8iJBgWWq92riMTuhc3r97gXEnhfEIoHoTr45R1UioE3m7hnjg6nn4UcCmpoE5FfIojWErLjumjI= ; Received: from unknown (HELO dualcore) (adam.r@sbcglobal.net@75.2.73.81 with login) by smtp111.sbc.mail.re2.yahoo.com with SMTP; 29 Dec 2006 02:11:54 -0000 X-YMail-OSG: IPfT7SIVM1nACInxwYYQr76EsY0prThm7jyA0tJYshz.6_GT5YO9U22Xf7Waa5T_4crEzIxsTHD.1UGueLGPS4mfWgyx9Vk1WuHjPVyoyvg1bhV_UQv_KeBa0n.fcbthB4Q.gdY8h.3bwHBRY1XYtbgFsXBmVhFXKnyBKJ.W_Tm_KaqI8PW.FDxd9pmXjamlLWAVOMA8w6.xKbEf5L5HZTEIWSdS9BxxCA-- From: "Adam Rich" To: =?iso-8859-1?Q?'fabrix_pe=F1uelas'?= , Subject: Re: Postgresql Configutation and overflow Date: Thu, 28 Dec 2006 20:12:44 -0600 Message-ID: <01b901c72aee$d3b9f130$6400a8c0@dualcore> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01BA_01C72ABC.891F8130" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757 In-Reply-To: Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.943 tagged_above=0 required=5 tests=AWL, BAYES_05, HTML_40_50, HTML_MESSAGE, NORMAL_HTTP_TO_IP, SARE_RAND_1 X-Spam-Level: * X-Archive-Number: 200612/521 X-Sequence-Number: 22577 This is a multi-part message in MIME format. ------=_NextPart_000_01BA_01C72ABC.891F8130 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable What are your table sizes? What are your queries like? (Mostly read, mostly write?) Can you post the "analyze" output for some of the slow queries? =20 =20 The three things that stand out for me is your disk configuration (RAID 5 is not ideal for databases, you really want RAID 1 or 1+0) and also that you have enable_seqscan set to off. I would leave that turned on. Lastly, your effective_cache_size looks low. Your OS is probably caching more than 512 MB, I know mine is usually 1-2 GB and I don't have 12 GB of ram available. =20 -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of fabrix pe=F1uelas Sent: Thursday, December 28, 2006 7:58 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Postgresql Configutation and overflow Good day, =20 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.=20 Before, the server reached 2000 connections to postgresql (with max_connections=3D3000 in it for future workflow).=20 I divided the load with another server for better performance, and now reach 500 connections, but yet is overflow.=20 My question is about how much memory should i configure in shared_buffers and effective_cache_size.=20 Features: - 4 Processsors Intel Xeon Dual 3.0Ghz - 12 GB RAM - 2 discos en RAID 1 for OS=20 - 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=20 2.1 Shared buffers # 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 #----------------------------------------------------------------------- ----=20 # 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' # use data in another directory=20 #hba_file =3D 'ConfigDir/pg_hba.conf' # host-based authentication = file #ident_file =3D 'ConfigDir/pg_ident.conf' # IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written.=20 #external_pid_file =3D '(none)' # write an extra pid file #----------------------------------------------------------------------- ---- # 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=20 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). You # might also need to raise shared_buffers to support more connections.=20 #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) #----------------------------------------------------------------------- ---- # - Memory -=20 shared_buffers =3D 81920 # min 16 or max_connections*2, 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 shared memory=20 # per transaction slot, plus lock space (see 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=20 # - Free Space Map - #max_fsm_pages =3D 20000 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations =3D 1000 # min 100, ~70 bytes each # - Kernel Resource Usage - #max_files_per_process =3D 1000 # min 25=20 #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=20 #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 = rounds #bgwriter_lru_percent =3D 1.0 # 0-100% of LRU buffers = scanned/round #bgwriter_lru_maxpages =3D 5 # 0-1000 buffers max written/round #bgwriter_all_percent =3D 0.333 # 0-100% of all buffers scanned/round=20 #bgwriter_all_maxpages =3D 5 # 0-1000 buffers max written/round #----------------------------------------------------------------------- ---- # 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:=20 # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync #full_page_writes =3D on # recover from partial page writes=20 #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 = each=20 #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 #----------------------------------------------------------------------- ---- # 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 # typically 8KB each #random_page_cost =3D 4 # units are one sequential page fetch # cost=20 #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=20 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. ------=_NextPart_000_01BA_01C72ABC.891F8130 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
What=20 are your table sizes?  What are your queries like?  (Mostly = read,=20 mostly write?)
Can=20 you post the "analyze" output for some of the slow queries? =20
 
The=20 three things that stand out for me is your disk configuration (RAID 5 is = not=20 ideal for databases,
you=20 really want RAID 1 or 1+0) and also that you have enable_seqscan set to=20 off.   I would leave
that=20 turned on.    Lastly, your effective_cache_size looks = low. =20 Your OS is probably caching more
than=20 512 MB, I know mine is usually 1-2 GB and I don't have 12 GB of ram=20 available.
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = fabrix=20 pe=F1uelas
Sent: Thursday, December 28, 2006 7:58 = PM
To:=20 pgsql-performance@postgresql.org
Subject: [PERFORM] = Postgresql=20 Configutation and overflow

Good = day,
  =20  
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 = tables are=20 indexed and made vacuum regularly, i monitor with top, ps and = pg_stat_activity=20 and when i checked was slow without a heavy load overage. =

Before, the=20 server reached 2000 connections to postgresql (with = max_connections=3D3000 in it=20 for future workflow).

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


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

Features:

- 4=20 Processsors Intel Xeon Dual 3.0Ghz
- 12 GB RAM
- 2 discos en = RAID 1 for=20 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=20 internet i found this:

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

#  Start at 4MB = (512) for=20 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=20 = is:

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

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

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

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


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

# - Connection Settings -

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



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

#=20 - Memory -


shared_buffers =3D 81920   =    =20      # min 16 or max_connections*2, 8KB=20 each
temp_buffers =3D 5000       =    =20  # min 100, 8KB each
max_prepared_transactions =3D = 1000  =20  # can be 0 or more

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

# per transaction slot, plus = lock=20 space (see max_locks_per_transaction).
work_mem =3D = 10240  =20          # min 64, size in=20 KB
maintenance_work_mem =3D 253952       =  # min=20 1024, size in KB
max_stack_depth =3D 4096   =    =20      # min 100, size in KB

# - Free Space = Map=20 -

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

# - Kernel Resource Usage=20 -

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

# - Cost-Based Vacuum = Delay=20 -

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

# -=20 Background writer -

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


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

# - Settings -

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

# - Checkpoints -

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

# - Archiving = -

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


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

#=20 - Planner Method Configuration -

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

# - = Planner Cost=20 Constants -

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


the=20 sysctl.conf

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

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

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

------=_NextPart_000_01BA_01C72ABC.891F8130--