Re: heavy swapping, not sure why

It is recommended to identify the processes using up high work_mem and try
to set work_mem to higher value at the session level.

I this case, all the connections using up maximum work_mem is the potential
threat. As said by Zoltan, work_mem is very high and shared_buffers as well.

Other considerations would be as following -

- Allocated kernel memory settings (like shmmax and shmget etc..)
- How much memory is used up by the system level processes (like root and
non-pg users)
- It also depends on the database size and the amount of data being
accessed across CPUs and memory.
- We need to ensure if unnecessary data is being read into the memory
( queries hitting non-vacuumed tables, slow performing queries,
unnecessary full table scans etc)

Regards,
Venkat

On Tue, Aug 30, 2011 at 3:30 PM, Boszormenyi Zoltan <zb(at)cybertec(dot)at> wrote:

> 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/
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>