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

Re: heavy swapping, not sure why

From: Boszormenyi Zoltan <zb(at)cybertec(dot)at>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: heavy swapping, not sure why
Date: 2011-08-30 10:00:43
Message-ID: 4E5CB4CB.8090303@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-general
Hi,

2011-08-29 22:36 keltezéssel, Lonni J Friedman írta:
> ...  I read that
> (max_connections * work_mem) should never exceed physical RAM, and if
> that's accurate, then I suspect that's the root of my problem on
> systemA (below).

work_mem is process-local memory so

(max_connections * work_mem)  < (physical RAM - shared_buffers)

Some queries may allocate multiples of work_mem, too.

Also, the kernel uses some memory for internal accounting, caching
and you need to account for the process binary in memory.

>   However, I'd like confirmation before I start
> tweaking things, as one of these servers is in production, and I can't
> easily tweak settings to experiment (plus this problem takes a few
> weeks before swapping gets bad enough to impact performance).
>
> A few examples:
>
> 0) system A: 56GB RAM, running postgresql-8.4.8 with the following parameters:
> maintenance_work_mem = 96MB
> effective_cache_size = 40GB
> work_mem = 256MB
> wal_buffers = 16MB
> shared_buffers = 13GB
> max_connections = 300

RAM (56GB) - shared_buffers (13GB) = 43GB

which is less than

work_mem * max_connections = 300 * 0.25GB = 75GB

The system would start swapping before 43GB/0.25GB = 172 clients.

> 1) system B: 120GB RAM, running postgresql-9.0.4 with the following parameters:
> maintenance_work_mem = 1GB
> effective_cache_size = 88GB
> work_mem = 576MB
> wal_buffers = 4MB
> shared_buffers = 28GB
> max_connections = 200

Similarly:

120GB - 28GB = 92GB

is less than

work_mem * max_connections = 200 * 576MB = 112.5GB

Also, if you run anything else on the machine then the system would start
swapping much sooner than hitting max_connections number of clients.

I would never set work_mem that high by default. 8 - 16MB is usually
enough for the common case and you can set work_mem for special
queries from the client and then reset it.

Best regards,
Zoltán Böszörményi

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
     http://www.postgresql.at/


In response to

Responses

pgsql-general by date

Next:From: Venkat BalajiDate: 2011-08-30 10:47:37
Subject: Re: heavy swapping, not sure why
Previous:From: Scott MarloweDate: 2011-08-30 09:53:36
Subject: Re: heavy swapping, not sure why

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