By Date: <-- -->
By Thread: <-- -->

postgres connection limit exceeded

Brad Anderson wrote: 
Ernest Ellingson wrote:
  
The problem exists in tracd also.  I have one project running in tracd. 
I login with a browser.  I close the browser I log in with another
instance of the browser another connection.  The number of connections
in pg_stat_activity keeps growing until I kill tracd.  Once it's gone. 
The number of records in pg_stat_activity returns to previous levels
(before tracd).  I have repeated this many times to double check.

It does no appear to be a mod_python and apache problem.

Ernie

    

I agree, because I also see the issue in tracd.

So I turned on the 'gc' module and traced some of the uncollectible objects to
see if any of them were holding on to connection objects so they weren't
collected.  This would cause the __del__() method of a PooledConnection object
to not be called, and therefore not call close(), and therefore not return the
connection to the pool.  (whew.)  But this turned up nothing suspicious.

So I started adding some debug/print statements to the get_cnx() and
_return_cnx() methods of the ConnectionPool object.  This yielded:

  
tracd --port 8000 /var/trac/test
    
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET /test/timeline HTTP/1.1" 200 -
gave cnx -1229517904 from pool
gave cnx -1229517904 from pool
released cnx -1229517904 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET /test/chrome/common/css/trac.css
HTTP/1.1" 304 -
released cnx -1229517904 back to pool
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET /test/chrome/common/css/timeline.css
HTTP/1.1" 304 -
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET /test/chrome/common/js/trac.js
HTTP/1.1" 304 -
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET
/test/chrome/common/topbar_gradient.png HTTP/1.1" 200 -
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET /test/chrome/common/dots.gif
HTTP/1.1" 200 -
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET
/test/chrome/common/topbar_gradient2.png HTTP/1.1" 200 -
released cnx -1215272016 back to pool
gave cnx -1215272016 from pool
gave cnx -1215272016 from pool
released cnx -1215272016 back to pool
localhost - - [17/Feb/2006 14:02:18] "GET /test/chrome/common/xml.png
HTTP/1.1" 200 -
released cnx -1215272016 back to pool



Every connection that was given seems to have returned to the pool.  And yet,
I still have idle connections:

  
ps -ef | grep pg
    
pg 5997 1 0 09:48 ? 00:00:00 /usr/bin/postmaster -D /var/lib/postgresql/data
pg    6158 5997 0 09:48 ? 00:00:00 pg: writer process
pg    6159 5997 0 09:48 ? 00:00:00 pg: stats buffer process
pg    6160 6159 0 09:48 ? 00:00:00 pg: stats clctor process
pg   18518 5997 0 14:02 ? 00:00:00 pg: trac test 127.0.0.1(53129) idle
pg   18519 5997 0 14:02 ? 00:00:00 pg: dsource dsource 127.0.0.1(53130) idle
pg   18521 5997 0 14:02 ? 00:00:00 pg: trac test 127.0.0.1(53132) idle
brad 18567 7616 0 14:16 ? 00:00:00 grep pg

Anyone else having any luck?

BA
_______________________________________________
Trac mailing list
Trac (at) lists.edgewall.com
http://lists.edgewall.com/mailman/listinfo/trac
I've isolated some of the data base behavior,  I'm not a python programmer (Ruby instead).  What I've noticed.  When connecting to tracd with Firefox, one connection is established that does not get deleted from pg_stat_activity when the browser is closed. However, when Firefox is opened  (only one instance of Firefox is open at any time) tracd uses the connection that the first instance of Firefox had. i.e. NO new connection to the database is established. I can close and reopen that one instance of Firefox and the connections in pg_stat_activity do not increase.  Opening a second instance of Firefox and connecting to tracd doesn't increase the connections.

When I open an instance of IE I get 3 connections to the database.  (IE uses the maximum of three connections permitted by HTTP 1.1 to query for pieces of each page).  When I close the instance of IE the connections remain in pg_stat_activity.  I open IE again connect to tracd.  The number of connections to the database DO NOT increase.  Apparently trac reuses the connections it initially established for IE. I open a second instance of IE on the same machine.  The number of connections to the database does NOT increase.

I open an instance of IE on another machine.  NO new connections to the database.

I think the connection pool is adding connections to the database to the pool as the need for connections increases. Releasing a connection doesn't disconnect it from the database but simply returns it to the pool where it becomes available to another process.  If the tracd server gets very busy connections could multiply quickly.  The pooling mechanism sees no available connections in the pool, it adds one.  The problem is that when there are plenty of connections in the pool, given the traffic, there is no reduction in connections.   The pool can only get bigger, it can't get smaller.  Depending on the number of browsers hitting the site at the same time, the pool could get very large.  Other than quotas on the number of connections, I don't think this should cause a problem with the database server unless the number of connections affects its performance.  I don't have any information about that right now.

I think that's the crucial point here though.  If the number of connections is irrelevant to performance, then this is not an issue.  If the number of connections is important, then it is an issue.  But think,  If we put a quota on the number of connections the pool could use, wouldn't that impose a performance burden on trac.
When the quota is in effect, processes would have to wait for a connection to come back to the pool.

I'm tired now.

Ernie
_______________________________________________
Trac mailing list
Trac (at) lists.edgewall.com
http://lists.edgewall.com/mailman/listinfo/trac