From: Flavio Henrique Araque Gurgel <flavio@4linux.com.br>
To: pgsql-performance@postgresql.org
Subject: work_mem in high transaction rate database
Date: Tue, 3 Mar 2009 21:28:56 -0300 (BRT)
Message-ID: <30792576.96971236126536868.JavaMail.root@mail.4linux.com.br> (raw)
In-Reply-To: <29144661.96951236126415852.JavaMail.root@mail.4linux.com.br>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: flavio@4linux.com.br
Subject: Re: work_mem in high transaction rate database
In-Reply-To: <30792576.96971236126536868.JavaMail.root@mail.4linux.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox