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 <z...@cybertec.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >