Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ?

I know 25% of the memory of the Server should be allocated. But how do we allocate based on the DB connections? How do we know, what is the maximum number of connections should be allocated?

Also in a multi-nodes environment can we allocate more connections for each node (in Postgres-ds.xml max connections) than what is actually allocated in the DB?

share|improve this question

migrated from stackoverflow.com Sep 5 '13 at 12:21

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 2 down vote accepted

When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ?

There is no per-connection pre-allocated memory that a DBA would define. A session will allocate dynamically what is needed, or use the shared memory pre-allocated at server start depending on the kind of usage. See Resource consumption in the doc about what can be defined.

I know 25% of the memory of the Server should be allocated. But how do we allocate based on the DB connections? How do we know, what is the maximum number of connections should be allocated?

The maximum number of client connections (max_connections) is factored into the amount of shared memory that is pre-allocated at server start.
In Managing Kernel Resources, the doc says that its size in bytes is:
(1800 + 270 * max_locks_per_transaction) * max_connections

But memory is not the main factor in deciding the maximum number of connections, it's the raw power and number of cores of the machine, and wether a connection pooler is going to be associated to the server. The point is mostly to avoid too many concurrent active queries provoking the server to halt to a crawl.

Also in a multi-nodes environment can we allocate more connections for each node (in Postgres-ds.xml max connections) than what is actually allocated in the DB?

If there are multiple client nodes that point to the same PostgreSQL instance, the combined count of their connections cannot outreach max_connections.

However when using a connection pooler, connection as a word becomes ambiguous. You want to know whether it's a connection from the client to the pooler or a connection from the pooler to the database server. Generally there are much more allowed connections to the pool than to the database.

share|improve this answer
    
Thanks Daniel for the update. With regards to the final comments, What I need to know from both aspects ? Also what is the best connection pool handling tool we can implement with PostgreSQL. I hope we could use Pgpool. Any other options ? –  kds Sep 9 '13 at 4:26
    
@kds: the reference to postgres-ds.xml suggests you use JBoss. You want to check what they recommend for pooling connections. I don't have any experience with it personally. –  Daniel Vérité Sep 9 '13 at 14:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.