0

How can i find the connections made/utilized to my PostgreSQL at a particular given time frame ?

In the PostgreSQL logs i am getting 

JSTERROR:  canceling statement due to user request

Sar logs :

MEM      AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
09:00:01 AM  25773712  40158364     60.91    257512  35876668   4552540      5.53
09:10:01 AM  25728656  40203420     60.98    258080  35920500   4555720      5.53

Some config settings:

max_connections = 2000
superuser_reserved_connections = 3
shared_buffers = 1GB
effective_cache_size = 128MB
work_mem = 1GB
maintenance_work_mem = 16MB

How can i troubleshoot this?

Advice welcome.

wazoox
  • 6,782
  • 4
  • 30
  • 62
Alex
  • 1
  • You said connection, but do you mean query? For example a connection pool may open a connection, then reuse it 100s of times for unrelated clients and queries before closing it. And why didn't you try configuring the logging on the postgres server? – Peter Nov 28 '16 at 18:37

1 Answers1

1

I am not sure if I understand your problem correctly but generally speaking PostgreSQL so far ( pg 9.6 2016/12) does not show any history of these statistics. Therefore I use monitoring with postgresql_exporter + prometheus + Grafana to see some metrics history - standard setting on prometheus is 15 days. In combination with node_exported you can see also metrics of OS. New Grafana 4.0 has even alerting. And you can run all these components in docker without necessity to install anything directly - I highly recommend it.

JosMac
  • 131
  • 2