Re: Relationship between ulimit and effective_cache_size, work_mem, shared_buffers, maintenance_work_mem and vacuum

Thanks for the comprehensive reply. It definitely cleared a lot of things up
for me.

regards
Sandeep

On Mon, Aug 16, 2010 at 12:39 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> Sandeep Srinivasa wrote:
>
>>
>> I was looking at ways to optimize the postgres database and yet limit the
>> amount of memory that it can consume.
>>
>
> You can't usefully limit the amount of memory that PostgreSQL will consume
> yet. Each client can easily allocate multiples of work_mem worth of memory
> as they run queries, and there's temp_buffers to be concerned about too.
> One way to work around problems like this is to significantly limit the
> number of clients that can be running at a time, using something like a
> connection pooler, and then keep a low setting for max_connections. This
> can have some positive performance benefits of its own, and it will keep the
> number of clients (the only real source of variable allocations on the
> server) from getting too large. Software suitable for that purpose includes
> pgbouncer and pgpool.
>
>
> Now, the "effective cache size" variable seems more of a hint to the query
>> planner, than any hard limit on the database server.
>>
>
> That's correct. It doesn't allocate anything. Doesn't limit anything
> either.
>
>
> Q1. if I add "ulimit -m" and "ulimit -v" lines in my postgres upstart
>> files will that be good enough to hard-limit Postgres memory usage ?
>>
>
> After fighting a few random crashes where the server runs into ulimit, you
> will find that trying to hard limit PostgreSQL memory usage is more trouble
> than it's worth. It's really a bad place to go.
>
>
> Q2. once I have decided my max memory allocation (call it MY_ULIMIT) -
>> should effective cache size be set to MY_ULIMIT - 256 - 12 -20 ? round it
>> off to MY_ULIMIT - 512mb maybe....
>>
>
> effective_cache_size has no relation to the limits. Take a look at how
> much of your operating system cache you think might be free at any time.
> Figure out what percentage of that you might want PostgreSQL to be able to
> use sometime. Set effective_cache_size to it. If a query goes wild and
> decides to execute a really bad query plan that reads a bunch of data, it
> will trash the operating system cache regardless; you can't stop it like
> this.
>
>
> Q3. Or will doing something like this play havoc with the query
>> planner/unexplained OOM/crashes ?
>>
>
> If you ever let the system get so low on RAM that the Linux OOM killer
> becomes active, it will almost always kill the main database server process,
> the one that spawns all the clients off, due to how Linux does shared memory
> accounting. This is another really bad things to be avoided.
>
>
> 1. will this affect the memory usage of vacuum (going to be using default
>> vacuum settings for 8.4) - because ideally I would want to have some control
>> over it as well.
>>
>
> Each of the autovacuum processes (defaulting to 3) will use up to
> maintenance_work_mem worth of memory when they are running. You should
> account for that when estimating peak usage.
>
>
> 2. Would I have to tune max_connections, max_files_per_process (and any
>> related variables) ?
>>
>
> Limiting max_connections, and accordingly dealing with the fact that some
> connections might be refused temporarily in your application, is the most
> effective thing you can do here. max_files_per_process is really secondary
> to any of the other bits you're asking about.
>
>
> 3. When I turn on WAL, would I have to tune wal_buffers accordingly set
>> effective cache size to account for wal_buffers as well ?
>>
>
> Set wal_buffers somewhere between 1MB and 16MB, include it in the general
> server shared memory overhead, and then ignore it. It takes up a little
> memory but isn't nearly as important as these other bits.
>
> --
> Greg Smith 2ndQuadrant US Baltimore, MD
> PostgreSQL Training, Services and Support
> greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us
>
>