On Tue, Mar 3, 2009 at 5:28 PM, Flavio Henrique Araque Gurgel <flavio@xxxxxxxxxxxxx> wrote: > Hello all > > In a dedicated server with 16 cores and 16GB of RAM running PostgreSQL 8.2.5 > we have a database with basically two kinds of transactions: > - short transactions with a couple of updates and inserts that runs all the > day; > - batch data loads with hundreds of inserts that runs several times a day; > - one delete for thousands of lines after each batch; > - selects are made when users need reports, low concurrency here. > > Today the max_connections is ~2500 where the application is a cluster of > JBoss servers with a pool a bit smaller then this total. > work_mem = 1GB > maintenance_work_mem = 1GB > shared_buffers = 4GB Oh my lord, that is a foot gun waiting to go off. Assuming 2k connections, and somehow a fair number of them went active with big sorts, you'd be able to exhaust all physical memory with about 8 to 16 connections. Lower work_mem now. To something like 1 to 4 Meg. Do not pass go. If some oddball query really needs a lot of work_mem, and benchmarks show something larger work_mem helps, consider raising the work_mem setting for that one query to something under 1G (way under 1G) That makes it noticeably faster. Don't allocate more than a test shows you helps. > autovacuum takes a lot of time running in the largest tables (3 large tables > in 50) causing some connections to have to wait for it to finish to start > transactioning again. Vacuum does not block transactions. unless you're dropping tables or something. > I see a few processes (connections) using 10 ~ 20% of total system memory > and the others using no more then 1%. This is commonly misread. It has to do with the vagaries of shared memory allocation and accounting. The numbers quite likely don't mean what you think they mean. Post the first 20 or so lines from top to show us. > What I want to ask is: is it better to keep the work_mem as high as it is > today or is it a safe bet triyng to reduce this number, for example, to 1 or > 2MB so I can keep the distribution of memory more balanced among all > connections? You're work_mem is dangerously high. Your current reading of top may not actually support lowering it directly. Since you've got 4G shared_buffers allocated, any process that's touched all or most of shared_buffer memory will show as using 4G of ram. That's why you should post output of top, or google on linux virtual memory and top and what the numbers mean. Let's say that 1% of your queries can benefit from > 100Meg work_mem, and 5% with 60M, and 10% with 40M, and 20% with 20M, and 30% with 16M, and 50% 8M and 4M is enough for all the else to do well. If, somehow, 100 queries fired off that could use > 100Meg, they might, with your current settings use all your memory and start using swap til swap ran out and they started getting out of memory errors and failing. This would affect all the other queries on the machine as well. OTOH, if you had work_mem limited to 16M, and 100 of those same queries fired off, they'd individually run a little slower, but they wouldn't be able to run the machine out of memory. If your work_mem and max_connections multiplied is > than some fraction of memory you're doing it wrong, and setting your machine up for mysterious, heavy load failures, the worst kind. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
