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