Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A1F779FBD90 for ; Fri, 20 Jul 2007 21:18:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 10990-01 for ; Fri, 20 Jul 2007 21:18:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.237]) by postgresql.org (Postfix) with ESMTP id 83A6B9FBCDD for ; Fri, 20 Jul 2007 21:18:54 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id h29so964624wxd for ; Fri, 20 Jul 2007 17:18:53 -0700 (PDT) Received: by 10.70.80.6 with SMTP id d6mr1677791wxb.1184977133122; Fri, 20 Jul 2007 17:18:53 -0700 (PDT) Received: by 10.70.45.9 with HTTP; Fri, 20 Jul 2007 17:18:53 -0700 (PDT) Message-ID: Date: Fri, 20 Jul 2007 20:18:53 -0400 From: "Gavin M. Roy" To: "Marc Mamin" Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM... Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_100888_32243824.1184977133070" References: X-Google-Sender-Auth: bad33879f7d54390 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/280 X-Sequence-Number: 25964 ------=_Part_100888_32243824.1184977133070 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Having done something similar recently, I would recommend that you look at adding connection pooling using pgBouncer transaction pooling between your benchmark app and PgSQL. In our application we have about 2000 clients funneling down to 30 backends and are able to sustain large transaction per second volume. This has been the #1 key to success for us in running on monster hardware. Regards, Gavin On 7/17/07, Marc Mamin wrote: > > > Postgres configuration for 64 CPUs, 128 GB RAM... > > Hello, > > We have the oppotunity to benchmark our application on a large server. I > have to prepare the Postgres configuration and I'd appreciate some comments > on it as I am not experienced with servers of such a scale. Moreover the > configuration should be fail-proof as I won't be able to attend the tests. > > Our application (java + perl) and Postgres will run on the same server, > whereas the application activity is low when Postgres has large transactions > to process. > > There is a large gap between our current produtcion server (Linux, 4GB > RAM, 4 cpus) and the benchmark server; one of the target of this benchmark > is to verify the scalability of our application. > > And you have no reason to be envious as the server doesn't belong us :-) > > Thanks for your comments, > > Marc Mamin > > > > > Posgres version: 8.2.1 > > > Server Specifications: > ---------------------- > > Sun SPARC Enterprise M8000 Server: > > *http://www.sun.com/servers/highend/m8000/specs.xml* > > File system: > > *http://en.wikipedia.org/wiki/ZFS* > > > Planned configuration: > -------------------------------- > > # we don't expect more than 150 parallel connections, > # but I suspect a leak in our application that let some idle connections > open > > max_connections=2000 > > ssl = off > > #maximum allowed > shared_buffers= 262143 > > # on our current best production server with 4GB RAM (not dedicated to > Postgres), work_mem is set to 600 MB > # this limitation is probably the bottleneck for our application as the > files in pgsql_tmp grows up to 15 GB > # during large aggregations (we have a locking mechanismus to avoid > parallel processing of such transactions) > work_mem = 31457280 # (30 GB) > > # index creation time is also an issue for us; the process is locking > other large processes too. > # our largest table so far is 13 GB + 11 GB indexes > maintenance_work_mem = 31457280 # (30 GB) > > # more than the max number of tables +indexes expected during the > benchmark > max_fsm_relations = 100000 > > max_fsm_pages = 1800000 > > # don't know if I schoud modify this. > # seems to be sufficient on our production servers > max_stack_depth = 2MB > > # vacuum will be done per hand between each test session > autovacuum = off > > > # required to analyse the benchmark > log_min_duration_statement = 1000 > > max_prepared_transaction = 100 > > # seems to be required to drop schema/roles containing large number of > objects > max_locks_per_transaction = 128 > > > > # I use the default for the bgwriter as I couldnt find recommendation on > those > > #bgwriter_delay = 200ms # 10-10000ms between rounds > #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers > scanned/round > #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round > #bgwriter_all_percent = 0.333 # 0-100% of all buffers > scanned/round > #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round > > #WAL > > fsync = on > > #use default > #wal_sync_method > > # we are using 32 on our production system > wal_buffers=64 > > # we didn't make any testing with this parameter until now, but this > should'nt be a relevant > # point as our performance focus is on large transactions > commit_delay = 0 > > #CHECKPOINT > > # xlog will be on a separate disk > checkpoint_segments=256 > > checkpoint_timeout = 5min > ------=_Part_100888_32243824.1184977133070 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Having done something similar recently, I would recommend that you look at adding connection pooling using pgBouncer transaction pooling between your benchmark app and PgSQL.  In our application we have about 2000 clients funneling down to 30 backends and are able to sustain large transaction per second volume.  This has been the #1 key to success for us in running on monster hardware.

Regards,

Gavin

On 7/17/07, Marc Mamin <M.Mamin@intershop.de> wrote:

Postgres configuration for 64 CPUs, 128 GB RAM...

Hello,

We have the oppotunity to benchmark our application on a large server. I have to prepare the Postgres configuration and I'd appreciate some comments on it as I am not experienced with servers of such a scale. Moreover the configuration should be fail-proof as I won't be able to attend the tests.

Our application (java + perl) and Postgres will run on the same server, whereas the application activity is low when Postgres has large transactions to process.

There is a large gap between our current produtcion server (Linux, 4GB RAM, 4 cpus) and the benchmark server; one of the target of this  benchmark is to verify the scalability of our application.


And you have no reason to be envious as the server doesn't belong us :-)


Thanks for your comments,

Marc Mamin





Posgres version: 8.2.1



Server Specifications:
----------------------

Sun SPARC Enterprise M8000 Server:

http://www.sun.com/servers/highend/m8000/specs.xml

File system:

http://en.wikipedia.org/wiki/ZFS



Planned configuration:
--------------------------------

# we don't expect more than 150 parallel connections,
# but I suspect a leak in our application that let some idle connections open

max_connections=2000

ssl = off

#maximum allowed
shared_buffers= 262143

# on our current best production server with 4GB RAM (not dedicated to Postgres), work_mem is set to 600 MB
# this limitation is probably the bottleneck for our application as the files in pgsql_tmp grows up to 15 GB
# during large aggregations (we have a locking mechanismus to avoid parallel processing of such transactions)
work_mem = 31457280  # (30 GB)

# index creation time is also an issue for us; the process is locking other large processes too.
# our largest table so far is 13 GB + 11 GB indexes
maintenance_work_mem = 31457280  # (30 GB)

# more than the max number of tables +indexes expected during the benchmark
max_fsm_relations = 100000

max_fsm_pages = 1800000

# don't know if I schoud modify this.
# seems to be sufficient on our production servers
max_stack_depth = 2MB

# vacuum will be done per hand between each test session
autovacuum = off



# required to analyse the benchmark
log_min_duration_statement = 1000


max_prepared_transaction = 100


# seems to be required to drop schema/roles containing large number of objects
max_locks_per_transaction = 128




# I use the default for the bgwriter as I couldnt find recommendation on those

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


#WAL

fsync = on

#use default
#wal_sync_method

# we are using 32 on our production system
wal_buffers=64


# we didn't make any testing with this parameter until now, but this should'nt be a relevant
# point as our performance focus is on large transactions
commit_delay = 0

#CHECKPOINT

# xlog will be  on a separate disk
checkpoint_segments=256

checkpoint_timeout = 5min


 
------=_Part_100888_32243824.1184977133070--