Re: Recommended Initial Settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Richard,
Thanks for your reply.  

You said:
"Your operating-system should be doing the caching for you."

My understanding is that as long as Linux has memory available it will
cache files.  Then from your comment I get the impression that since
Linux would be caching the data files for the postgres database it would
be redundant to have a large shared_buffers.  Did I understand you
correctly?

Thanks,



Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
 
-----Original Message-----
From: Richard Huxton [mailto:dev@xxxxxxxxxxxx] 
Sent: Friday, February 23, 2007 10:29 AM
To: Campbell, Lance
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: [PERFORM] Recommended Initial Settings

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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux