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.

What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and max_transaction_locks on postgresql.conf?

Our application has over 200 concurrent processes, each of which regularly reads and writes to the database. Using session-level, I would expect the number of pooled connections to be around the number of concurrent processes working on the DB at a given time, but I don't really know how to translate that to transaction-level pooling.

Thanks very much! Let me know if more information is necessary and I will gladly oblige.

share|improve this question
    
What kind of application? Is this a web app? A thick client? If it is a thick client, how many queries are active, usually, at any given point, and how many connections are idle? –  Chris Travers Mar 14 '13 at 7:35
    
@Chris. How can I check for this information? Is there a command or SQL statement that I can run to know this? Thanks :) –  Juan Carlos Coto Mar 14 '13 at 16:20
    
@ChrisTravers Oh, and this is a distributed application. We have both a Web (WSGI) front end, and the concurrent processes are back-ends using SQLAlchemy and running asynchronously. –  Juan Carlos Coto Mar 14 '13 at 17:37
    
look at pg_stat_activity and compare those which are idle to those not. The following query will do: select SUM(case when current_query like '%IDLE%' THEN 0 ELSE 1 END) as idle, count(*) as total from pg_stat_activity; –  Chris Travers Mar 20 '13 at 10:16
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.