Re: work_mem in high transaction rate database

On Tue, Mar 3, 2009 at 5:28 PM, Flavio Henrique Araque Gurgel
<flavio@4linux.com.br> 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.