work_mem in high transaction rate database
От | Flavio Henrique Araque Gurgel |
---|---|
Тема | work_mem in high transaction rate database |
Дата | |
Msg-id | 30792576.96971236126536868.JavaMail.root@mail.4linux.com.br обсуждение исходный текст |
Ответы |
Re: work_mem in high transaction rate database
|
Список | 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
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
В списке pgsql-performance по дате отправления: