Skip site navigation (1) Skip section navigation (2)

Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: proposal: Set effective_cache_size to greater of .conf value, shared_buffers
Date: 2013-01-09 02:01:55
Message-ID: 50ECCF93.3060101@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
All,

>> Well, the problem of "find out the box's physical RAM" is doubtless
>> solvable if we're willing to put enough sweat and tears into it, but
>> I'm dubious that it's worth the trouble.  The harder part is how to know
>> if the box is supposed to be dedicated to the database.  Bear in mind
>> that the starting point of this debate was the idea that we're talking
>> about an inexperienced DBA who doesn't know about any configuration knob
>> we might provide for the purpose.

Frankly, you'd need to go through a whole decision tree to do this right:

- How much RAM do you have?
- Is that RAM shared with other services?
- Is this a DW or OLTP server?
... etc.

We just don't want to get into that in the core code.  However ...

>> I'd prefer to go with a default that's predictable and not totally
>> foolish --- and some multiple of shared_buffers seems like it'd fit the
>> bill.
> 
> +1. That seems to be by far the biggest bang for the buck. Anything else
> will surely involve a lot more code for not much more benefit.

I don't think we're going far enough here.  I think there should be an
optional setting in postgresql.conf called:

available_ram

The, shared_buffers, wal_buffers, and effective_cache_size (and possible
other future settings) can be set to -1.  If they are set to -1, then we
use the figure:

shared_buffers = available_ram * 0.25
	(with a ceiling of 8GB)
wal_buffers = available_ram * 0.05
	(with a ceiling of 32MB)
effective_cache_size = available_ram * 0.75
	(with a floor of 128MB)

If they are set to an amount, then we use the amount they are set to.

It would be nice to also automatically set work_mem, maint_work_mem,
temp_buffers, etc. based on the above, but that would be considerably
more difficult and require performance testing we haven't done yet.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2013-01-09 02:10:24
Subject: Re: PL/perl should fail on configure, not make
Previous:From: Daniel FarinaDate: 2013-01-09 01:59:43
Subject: Re: Cascading replication: should we detect/prevent cycles?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group