0

Postgres 9.1 sever is running under Debian. It is accessed by Mono MVC4 web applications using ngpsql, Apache and mod_mono. It is also accessed by pg_dump in same computer every night and from 5432 port from internet. Connections were cleared at June 20. pgAdmin statuc window shows lot of idle connections starting from this day. (see image below).

After some time number of connections goes to 100 and server stops to accept connections. Connections needs to be manually killed in pgAdmin. Only after that server start accepting connections again.

It is interesting that start times of some of those connections are exactly the same as shown in image. How to fix or diagnose the issue ? I tried /etc/init.d/apache2 restart . This closed 25 and 27 June connections but 20 June connections are still open.

How to close idle connections automatically or find the reason of open connections ? Maybe there is some postgres or Debian setting which closes idle connections automatically ?

open Using

"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit"

Update

Application uses the following connection string:

    NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder()
    {
        SearchPath = schema == null ? "public" : schema + ",public",
        Timeout = 115,
        CommandTimeout = 115,
        Host = Config.Server,
        Database = Config.DefaultDataBase,
        UserName = Config.ServerUser,
        Port = Config.Port,
        // Apache allows 150 connections, MaxPoolSize is set accordingly:
        MaxPoolSize = 155
    };
Andrus
  • 26,339
  • 60
  • 204
  • 378
  • 1
    I think that is much more important to find the reason of open connections than how to close them (which would be a temporary hack). Have you inspected the web application (if you have access to it)? Are statements closed after data are fetched from the database? Are connections closed for each request or maintained between them? There is a connection pool? – Renzo Jun 27 '15 at 16:39
  • What are your npgsql Pooling, MaxPoolSize and ConnectionLifeTime settings? – Tomasz Myrta Jun 27 '15 at 21:19
  • I updated question with connection string used. MaxPoolSize is 155 and ConnectionLifeTime is not set so it has probably its default value of 15 seconds – Andrus Jun 28 '15 at 07:28
  • @Renzo . Application is written by me. It uses `using` command to access data. This should release used connection to pool. Connection pool is used, I updated question. How to diagnose the issue ? How to find which queries cause this ? – Andrus Jun 28 '15 at 07:32
  • which program is that? i want to see how my connections work in my server – Gonzalo Apr 21 '18 at 17:52

1 Answers1

0

Check out this post Is there a timeout for idle PostgreSQL connections?

And consider of using pgbouncer in front of your database.

Community
  • 1
  • 1
zhigaev
  • 51
  • 7