work_mem in high transaction rate database

From: Flavio Henrique Araque Gurgel <flavio(at)4linux(dot)com(dot)br>
To: pgsql-performance(at)postgresql(dot)org
Subject: work_mem in high transaction rate database
Date: 2009-03-04 00:28:56
Message-ID: 30792576.96971236126536868.JavaMail.root@mail.4linux.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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

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.

I see a few processes (connections) using 10 ~ 20% of total system memory and the others using no more then 1%.

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?

Thanks!

Flavio Henrique A. Gurgel

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2009-03-04 01:37:42 Re: work_mem in high transaction rate database
Previous Message Tom Lane 2009-03-03 22:34:12 Re: Postgres 8.3, four times slower queries?