From: Richard Huxton <dev@archonet.com>
To: Campbell, Lance <lance@uiuc.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Recommended Initial Settings
Date: Fri, 23 Feb 2007 16:29:20 +0000
Message-ID: <45DF1660.101@archonet.com> (raw)
In-Reply-To: <A3AC4FA47DC0B1458C3E5396E685E63302395CE7@SAB-DC1.sab.uiuc.edu>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395CE7@SAB-DC1.sab.uiuc.edu>
Campbell, Lance wrote:
> I would like to get someone's recommendations on the best initial
> settings for a dedicated PostgreSQL server. I do realize that there are
> a lot of factors that influence how one should configure a database. I
> am just looking for a good starting point. Ideally I would like the
> database to reside as much as possible in memory with no disk access.
> The current database size of my 7.x version of PostgreSQL generates a 6
> Gig file when doing a database dump.
Your operating-system should be doing the caching for you.
> Dedicated PostgreSQL 8.2 Server
> Redhat Linux 4.x AS 64 bit version (EM64T)
> 4 Intel Xeon Processors
If these are older Xeons, check the mailing list archives for "xeon
context switch".
> 20 Gig Memory
> Current PostgreSQL database is 6 Gig file when doing a database dump
OK, so it's plausible the whole thing will fit in RAM (as a
rule-of-thumb I assume headers, indexes etc. triple or quadruple the
size). To know better, check the actual disk-usage of $PGDATA.
> /etc/sysctl.conf file settings:
>
> # 11 Gig
>
> kernel.shmmax = 11811160064
Hmm - that's a lot of shared RAM. See shared_buffers below.
> kernel.sem = 250 32000 100 128
>
> net.ipv4.ip_local_port_range = 1024 65000
>
> net.core.rmem_default = 262144
>
> net.core.rmem_max = 262144
>
> net.core.wmem_default = 262144
>
> net.core.wmem_max = 262144
> postgresql.conf file settings (if not listed then I used the defaults):
>
> max_connections = 300
How many connections do you expect typically/peak? It doesn't cost much
to have max_connections set high but your workload is the most important
thing missing from your question.
> shared_buffers = 10240MB
For 7.x that's probably way too big, but 8.x organises its buffers
better. I'd still be tempted to start a 1 or 2GB and work up - see where
it stops buying you an improvement.
> work_mem = 10MB
If you have large queries, doing big sorts I'd increase this. Don't
forget it's per-sort, so if you have got about 300 connections live at
any one time that could be 300*10MB*N if they're all doing something
complicated. If you only have one connection live, you can increase this
quite substantially.
> effective_cache_size = 512MB
This isn't setting PG's memory usage, it's telling PG how much data your
operating-system is caching. Check "free" and see what it says. For you,
I'd expect 10GB+.
> maintenance_work_mem = 100MB
This is for admin-related tasks, so you could probably increase it.
Workload workload workload - we need to know what you're doing with it.
Once connection summarising the entire database will want larger numbers
than 100 connections running many small queries.
HTH
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, lance@uiuc.edu
Subject: Re: Recommended Initial Settings
In-Reply-To: <45DF1660.101@archonet.com>
* 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