12

Background: I'm moving my application from npgsql v1 to npgsql v2.0.9. After a few minutes of running my application, I get a System.Exception: Timeout while getting a connection from the pool.

The web claims that this is due to leaking connections (opening a db connection, but not properly closing them).

So

I'm trying to diagnose leaking postgres connections in npgsql.

From the various web literature around; one way to diagnose leaking connections is to setup logging on npgsql, and look for the leaking connection warning message in the log. Problem is, I'm not seeing this message in the logs anywhere.

I also found utility that monitors npgsql connections, but it's unstable and crashes.

So I'm left manually inspecting code. For everyplace that creates an npgsql connection, there is a finally block disposing of it. For everyplace that opens a datareader, CommandBehavior.CloseConnection is used (and the datareader is disposed).

Any other places to check or can someone recommend a way to look for leaking pool connections?

Fung
  • 7,530
  • 7
  • 53
  • 68
Alan
  • 45,915
  • 17
  • 113
  • 134
  • I'm having the exact same problem. But I find connections only leak when I use DbConnection.BeginTransaction and DbTransaction.Commit/Rollback. I'm making sure I close/dispose all connections properly as well but no help. I think it's a problem with Npgsql itself and not our code. Do let me know if you've found the solution. Thanks. – Fung Mar 22 '11 at 03:43

1 Answers1

7

UPDATE: I'm working in a better way to check where those leak connections were allocated. Please, check my post about that: http://fxjr.blogspot.com/2012/11/better-tracing-of-npgsql-connection.html I hope it helps.

EDIT: You may want to try our latest beta version 2.0.11.91. We added some more code to improve connection pool which can help you.

Hmmmm, this is very strange.... You should be getting some messages.

Those messages only appear when ms.net runtime finalizes the object though. Would it be possible that you are using more than default max connections at same time? I mean, your peak usage may be more than the 20 max number connections...

I hope it helps.

Please, let me know what do you get.

Francisco Junior
  • 1,986
  • 18
  • 25
  • Even if I crank up the connectons to 100, I still end up with this exception. I'm pretty positive we're not using that many connections. – Alan May 06 '10 at 17:47
  • 1
    Can you try disable connection pooling and check if you see connections floating in the server? You can disable pooling by adding Pooling=false in your connection string. If after using your app for a while you see connections in your pg server, you may be leaking connections. I hope it helps. – Francisco Junior May 11 '10 at 02:51
  • 2
    Hi Francisco, Thanks for your help. I disabled pooling using Pooling=false as you suggested. In order to monitor connections I am using `SELECT * FROM pg_stat_activity` SQL query. The most connections I saw was 11, then it went back down to 1 (for the sql query) bouncing up to 3 or 4 and back down. As an aside, disabling pooling really slows down performance of npgsql/postgres. – Alan May 11 '10 at 20:58
  • Sorry for delay... This is really very strange that you are getting timeout from the pool. I have no idea about what can be happening in order to give you this error since after disabling connection pooling it seems to be closing the connections correctly. Do you think you can write a test sample where this problem appears? It would be very much helpful. Thanks in advance. – Francisco Junior May 31 '10 at 00:46
  • 1
    Hint: If you switch off pooling in the connectionstring then it works pretty good for me. – schoetbi Feb 06 '13 at 11:28
  • Still have these timeouts even with latest 2.0.13 beta 1. Will disable pooling for now. – Aleksey Kontsevich Oct 16 '13 at 11:55
  • 2
    I'm finding lots of hanging connections with "UNLISTEN *" as their query. What could that be about? – Chris May 08 '14 at 15:24
  • Is this possible in Npgsql v5? I am not seeing any performance counters available. – Mr Shoubs Jun 08 '21 at 12:16