From: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
To: Marc Mamin <M.Mamin@intershop.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM...
Date: Tue, 17 Jul 2007 17:06:03 +0200
Message-ID: <469CDADB.5040103@kaltenbrunner.cc> (raw)
In-Reply-To: <CA896D7906BF224F8A6D74A1B7E54AB301750B12@JENMAIL01.ad.intershop.net>
References: <CA896D7906BF224F8A6D74A1B7E54AB301750B12@JENMAIL01.ad.intershop.net>
Marc Mamin wrote:
>
> Postgres configuration for 64 CPUs, 128 GB RAM...
there are probably not that much installation out there that large -
comments below
>
> 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.
>
[...]
> Posgres version: 8.2.1
upgrade to 8.2.4
> File system:
>
> _http://en.wikipedia.org/wiki/ZFS_
way more important is what kind of disk-IO subsystem you have attached ...
>
>
>
> 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
this is probably on the lower side for a 128GB box
>
> # 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)
this is simply ridiculous - work_mem is PER SORT - so if your query
requires 8 sorts it will feel free to use 8x30GB and needs to be
multiplied by the number of concurrent connections.
>
> # 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)
this is ridiculous too - testing has shown that there is not much point
in going beyond 1GB or so
>
> # more than the max number of tables +indexes expected during the benchmark
> max_fsm_relations = 100000
>
> max_fsm_pages = 1800000
this is probably way to low for a database the size of yours - watch the
oputput of VACUUM VERBOSE on a database wide vacuum for some stats on that.
>
> # 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
values up to 512 or so have been reported to help on systems with very
high concurrency
what is missing here is your settings for:
effective_cache_size
and
random_page_cost
Stefan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stefan@kaltenbrunner.cc, M.Mamin@intershop.de
Subject: Re: Postgres configuration for 64 CPUs, 128 GB RAM...
In-Reply-To: <469CDADB.5040103@kaltenbrunner.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox