Long-lived idle database connections - why are these lingering?

We're seeing an awfully large number of long-lived idle connections to the PostgreSQL service - on an active day like today, we've got four screens of connections (where only a handful should stick around e.g. PgAdmin client, etc):

Looking at the source of these connections, the vast majority are from AWS itself, rather than from real-world locations where developers are hitting the databases directly.

This address is for the 80% of connections we're currently seeing, some up to 2 hours old:

$ nslookup 52.40.144.163
Server:     8.8.8.8
Address:    8.8.8.8#53

Non-authoritative answer:
163.144.40.52.in-addr.arpa  name = ec2-52-40-144-163.us-west-2.compute.amazonaws.com.

Authoritative answers can be found from:

And this is an example of a non-AWS address (accounting for a handful of connections):

$ nslookup 24.21.129.109
Server:     8.8.8.8
Address:    8.8.8.8#53

Non-authoritative answer:
109.129.21.24.in-addr.arpa  name = c-24-21-129-109.hsd1.or.comcast.net.

Authoritative answers can be found from:

Discussion with Brian Grant:

@MikeTheCanuck going to put this here, might be related but gotta run. I wouldnt think we’ve manually spawned greenlets but its possible. will be online later or tmmrw around 6am due to europeans: https://github.com/jneight/django-db-geventpool#using-django-16-orm-when-not-serving-requests

MikeTheCanuck [1 day ago] Are you thinking that the long-idle DB connections is due to some long-lived, manually-spawned greenlets?

MikeTheCanuck [1 day ago] I’m wondering about the implied timeout - as in, “So if you manually spawn a greenlet (or have a task spawning one) its connections will not get cleaned up and will live until timeout.” Anytime I look at the Sessions list on the DB I can see some extremely long-lived connections - like sometimes a day old, many upwards of 60-90 minutes old.

brian_g [1 day ago] that’s what i was thinking as a possibility, such as where we are monkey_patching in the gunicorn_config.py: https://github.com/hackoregon/transportation-system-backend/blob/staging/gunicorn_config.py#L79 gunicorn_config.py:79 def do_post_fork(server, worker):

brian_g [3 hours ago] I am still not seeing where we maybe manually spawning any greenlets, as it seems we are following the standard setup for using this with monkey patching the db and then conn_max_age : 0 for django 1.6+.

We could try using the non-pooling postgis db driver (the one being used in development). This may slow down connections at scale though.

let me know your priority on this ATM though, as a few things going on. I am @ office, level 2

brian_g [3 hours ago] https://github.com/benoitc/gunicorn/issues/1296 also came up in a quick scan

Ok, so following along on the first thought, the Django ORM not closing connections issue, I have gone ahead and added the following hook into the gunicorn_conf file:

def worker_exit(server, worker):
    request_finished.send(sender="greenlet")
    worker.log.info("worker exit signal")

have just deployed so will see what effects this has on number of open connections.

just made a whole bunch of api calls, have not run into a limit as of yet but would be good to coordinate with someone with access to see number of connections currently open

Yep, as of ~9pm, the number of connections for the Transportation Systems database is minuscule compared to what we observe for the Local Elections and Neighborhood Development databases (2 connections still open for TS compared to 24 for LE and 51 for ND - and 2 for DR, 5 for HA).

Ian also suggested looking at the netstat output for clues - here's what it reports at this time:

$ netstat
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:36944 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:19745 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:11170 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8827 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:19744 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:12553 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:30813 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:13467 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40:indigo-server ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:60702 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:6630 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:11460 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:59531 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:35591 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:59659 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:21053 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:55441 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45517 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 97-115-77-39.ptld:62739 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:57147 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:39112 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 70-57-113-236.ptl:57138 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:21290 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54006 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:49485 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:52932 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8395 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:57826 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54884 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:42672 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:64903 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:41849 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:47069 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:17765 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:19416 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 174.127.220.84:53076    ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45412 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:58799 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:22734 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:7576 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:15509 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:15343 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:19615 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:39343 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:63609 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:10682 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56289 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:44715 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45723 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:38223 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:21191 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8815 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:12660 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:62577 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:57234 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:9527 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:5699 ESTABLISHED
tcp        0   1624 ip-172-31-4-206.us-:ssh 97-115-77-39.ptld:54618 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:6806 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-:mctet-master ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-1:pinghgl ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:36735 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:33707 ESTABLISHED
tcp        0      0 ip-172-31-4-206.us-:ssh 122.226.181.166:35364   TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56063 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:32384 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:10746 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:14877 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-:ifsf-hb-port ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:42539 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:35605 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 97-115-77-39.ptld:49874 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:60219 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:37694 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:60231 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:63316 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:47586 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:39838 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:34777 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:34462 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:61734 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 70-57-113-236.ptl:55367 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:65207 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:33038 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:35145 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:40098 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:63458 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:35987 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56766 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:28832 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:64221 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:25535 ESTABLISHED
udp        0      0 localhost:56420         localhost:56420         ESTABLISHED
Active UNIX domain sockets (w/o servers)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ]         DGRAM                    17668    /var/run/chrony/chronyd.sock
unix  2      [ ]         DGRAM                    9225     /run/systemd/notify
unix  2      [ ]         DGRAM                    9227     /run/systemd/cgroups-agent
unix  5      [ ]         DGRAM                    9238     /run/systemd/journal/socket
unix  14     [ ]         DGRAM                    9240     /dev/log
unix  2      [ ]         DGRAM                    10708    /run/systemd/shutdownd
unix  3      [ ]         STREAM     CONNECTED     6109236  
unix  3      [ ]         STREAM     CONNECTED     6109268  
unix  3      [ ]         STREAM     CONNECTED     6109259  
unix  3      [ ]         STREAM     CONNECTED     17530    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109227  
unix  3      [ ]         STREAM     CONNECTED     6109270  
unix  3      [ ]         STREAM     CONNECTED     6109230  
unix  3      [ ]         STREAM     CONNECTED     6109240  
unix  3      [ ]         STREAM     CONNECTED     6109280  
unix  3      [ ]         STREAM     CONNECTED     6109277  
unix  3      [ ]         STREAM     CONNECTED     6109291  
unix  3      [ ]         STREAM     CONNECTED     6106793  
unix  3      [ ]         STREAM     CONNECTED     6109233  
unix  3      [ ]         STREAM     CONNECTED     6109265  
unix  3      [ ]         STREAM     CONNECTED     6107209  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109282  
unix  3      [ ]         STREAM     CONNECTED     6109271  
unix  3      [ ]         STREAM     CONNECTED     6109237  
unix  3      [ ]         STREAM     CONNECTED     6109276  
unix  3      [ ]         STREAM     CONNECTED     19251    
unix  3      [ ]         STREAM     CONNECTED     16553    
unix  3      [ ]         STREAM     CONNECTED     6109273  
unix  3      [ ]         STREAM     CONNECTED     6109279  
unix  3      [ ]         STREAM     CONNECTED     6109274  
unix  3      [ ]         DGRAM                    6106813  
unix  3      [ ]         STREAM     CONNECTED     6109297  
unix  3      [ ]         STREAM     CONNECTED     6107875  
unix  3      [ ]         STREAM     CONNECTED     6109258  
unix  3      [ ]         STREAM     CONNECTED     6109234  
unix  3      [ ]         STREAM     CONNECTED     20865    
unix  3      [ ]         STREAM     CONNECTED     6109292  
unix  3      [ ]         STREAM     CONNECTED     6109267  
unix  2      [ ]         DGRAM                    6106795  
unix  3      [ ]         STREAM     CONNECTED     6109294  
unix  3      [ ]         STREAM     CONNECTED     6109229  
unix  2      [ ]         DGRAM                    20867    
unix  3      [ ]         DGRAM                    6106812  
unix  3      [ ]         STREAM     CONNECTED     6109295  
unix  3      [ ]         STREAM     CONNECTED     6109226  
unix  3      [ ]         STREAM     CONNECTED     21598    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6108653  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     19252    /run/systemd/journal/stdout
unix  2      [ ]         DGRAM                    6109215  
unix  3      [ ]         STREAM     CONNECTED     16554    
unix  2      [ ]         DGRAM                    6109306  
unix  3      [ ]         STREAM     CONNECTED     6109298  
unix  2      [ ]         DGRAM                    6110231  
unix  3      [ ]         STREAM     CONNECTED     16556    /var/run/dbus/system_bus_socket
unix  3      [ ]         STREAM     CONNECTED     6107198  
unix  3      [ ]         STREAM     CONNECTED     9571241  
unix  3      [ ]         STREAM     CONNECTED     17630    
unix  3      [ ]         STREAM     CONNECTED     6109241  
unix  2      [ ]         DGRAM                    16558    
unix  3      [ ]         STREAM     CONNECTED     6109253  
unix  2      [ ]         DGRAM                    17644    
unix  2      [ ]         DGRAM                    16414    
unix  3      [ ]         STREAM     CONNECTED     16423    
unix  3      [ ]         STREAM     CONNECTED     16588    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     17631    /run/systemd/journal/stdout
unix  2      [ ]         DGRAM                    9600820  
unix  2      [ ]         DGRAM                    17635    
unix  3      [ ]         STREAM     CONNECTED     17483    
unix  3      [ ]         STREAM     CONNECTED     16424    
unix  3      [ ]         STREAM     CONNECTED     17435    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109283  
unix  3      [ ]         STREAM     CONNECTED     17808    
unix  3      [ ]         STREAM     CONNECTED     17432    
unix  3      [ ]         STREAM     CONNECTED     6109246  
unix  2      [ ]         DGRAM                    17786    
unix  3      [ ]         STREAM     CONNECTED     6109244  
unix  3      [ ]         STREAM     CONNECTED     17650    
unix  3      [ ]         STREAM     CONNECTED     6109286  
unix  3      [ ]         STREAM     CONNECTED     6109255  
unix  3      [ ]         STREAM     CONNECTED     17731    
unix  3      [ ]         STREAM     CONNECTED     6109262  
unix  2      [ ]         DGRAM                    18022    
unix  3      [ ]         STREAM     CONNECTED     6106743  /var/run/dbus/system_bus_socket
unix  3      [ ]         STREAM     CONNECTED     6109250  
unix  2      [ ]         DGRAM                    17679    
unix  3      [ ]         STREAM     CONNECTED     6109264  
unix  3      [ ]         STREAM     CONNECTED     6109288  
unix  3      [ ]         STREAM     CONNECTED     6109243  
unix  2      [ ]         DGRAM                    16834    
unix  3      [ ]         STREAM     CONNECTED     6109261  
unix  3      [ ]         STREAM     CONNECTED     6109249  
unix  3      [ ]         STREAM     CONNECTED     17801    
unix  3      [ ]         STREAM     CONNECTED     17800    
unix  3      [ ]         STREAM     CONNECTED     6109247  
unix  2      [ ]         DGRAM                    9571234  
unix  3      [ ]         STREAM     CONNECTED     16722    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109256  
unix  3      [ ]         STREAM     CONNECTED     17730    
unix  3      [ ]         STREAM     CONNECTED     16721    
unix  3      [ ]         STREAM     CONNECTED     9571240  
unix  3      [ ]         STREAM     CONNECTED     6109285  
unix  3      [ ]         STREAM     CONNECTED     20151    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     17815    /run/gssproxy.sock
unix  3      [ ]         STREAM     CONNECTED     6109252  
unix  3      [ ]         STREAM     CONNECTED     19325    
unix  3      [ ]         STREAM     CONNECTED     6109289  
unix  3      [ ]         STREAM     CONNECTED     16587    /run/systemd/journal/stdout

Looking like a lot of "healthy" (at least at the TCP level) connections, almost all in ESTABLISHED state.

There are a couple of suggestions that are last-restort once we have addressed the application behaviour:

https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/ suggests PGBouncer (which is a defensive maneuver for a database that is considered "a very good idea if you have a buggy application that leaks connections anyway")
https://stackoverflow.com/a/41818410/452120 suggests idle_in_transaction_session_timeout as another way to mitigate leaky applications

But it's pretty clear that our best (not only, but most effective) mitigation is to address the application configuration when it "has a connection leak because it fails to close pooled connections".

PostgreSQL docs themselves talk about how performance falls off with more database connections: https://wiki.postgresql.org/wiki/Number_Of_Database_Connections#Reasons_for_Performance_Reduction_Past_the_.22Knee.22

As expected, we're no further ahead with 200 max_connections than with the default of 100 (and run the risk of the server falling over when it's supporting that many more connections) - there are again 500 Server Error responses coming from various APIs, and the connection count is again out of hand:

These stats are generated with the following query:

SELECT 
    pg_stat_activity.datname,
    count(pg_stat_activity.datid)
FROM 
    pg_stat_activity 
GROUP BY
    datname
ORDER BY
    count(pg_stat_activity.datid) desc

I had another look at the netstat output, and again nothing seems to suggest that we've got anything other than standard layer 4 behaviours:

$ netstat 
Active Internet connections (w/o servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:14010 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:25772 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:11847 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:60912 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:60373 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:44091 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:49569 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:62706 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8539 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:41133 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:11779 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:28065 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54878 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:44966 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:58857 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-:pnbscada ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:37137 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:40777 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:26276 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:53086 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:63235 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:43796 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8480 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:13574 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56669 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres c-24-20-184-179.h:56718 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40:acp-discovery FIN_WAIT2  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:41501 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 97-115-77-39.ptld:60368 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:14090 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:41380 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:39433 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:22575 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:37306 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:59326 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8754 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:30878 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:9459 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-1:canit_store TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-16:silkp3 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:50976 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45786 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:59688 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:48809 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144:h2gf-w-2m TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:20662 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:51639 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:58747 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45140 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:41192 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:27209 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:31361 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:6533 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:42118 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:25646 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:52508 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-1:metrics-pas ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:29739 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8348 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56849 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-16:orbixd ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:29210 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-16:brlp-1 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:19051 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:17986 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:36434 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:18569 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:16535 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:18904 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:46256 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:28508 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:62620 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:51800 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:49639 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:39663 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:41984 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54554 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:19081 FIN_WAIT2  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:18261 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:15105 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:18708 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:6854 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres c-24-20-184-179.h:56756 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:10861 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:17412 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:15711 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:14979 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:55537 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:64270 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:26582 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:60775 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:10735 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:4815 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres c-24-20-184-179.h:56436 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:42835 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8705 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:27874 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:63409 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:26754 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54173 FIN_WAIT2  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:57008 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8413 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-1:msgsrvr ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:20530 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:6036 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:41639 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:25476 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:65250 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56333 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:43292 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:48164 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:5614 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:15158 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:20389 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:4337 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:28929 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:32328 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:61616 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:41934 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:24354 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:8476 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:15953 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:7231 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-:infoexch TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:36745 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:20806 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:52849 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 174.127.220.84:49595    ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:20264 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:60590 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:48383 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45326 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:52907 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:11671 ESTABLISHED
tcp        0    348 ip-172-31-4-206.us-:ssh 97-115-77-39.ptld:54618 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-1:dsom-server ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:53515 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.u:fsr ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56419 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:51006 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:14658 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:56479 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45398 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:47788 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-1:capioverlan ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:20460 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:58526 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:60956 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:46122 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:31992 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:7574 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:27267 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54069 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:50851 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:50963 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:12787 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:58827 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:13180 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:23960 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:5479 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:35249 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:34018 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:17586 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:60905 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:27179 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:42868 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:27716 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres c-24-20-184-179.h:56164 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:36650 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:52823 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:29414 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:25160 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:17560 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:37203 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:5347 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:61050 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:39370 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:53521 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:52668 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:53965 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres c-24-20-184-179.h:56376 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:61107 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-:spytechphone ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres c-24-20-184-179.h:56796 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-1:amx-rms ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54082 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:46925 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:6299 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:21439 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:10521 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:18112 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:37567 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.u:ctp ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:17367 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres 52.129.39.162.big:41894 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:63495 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:40152 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:60375 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:47363 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:52770 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:54354 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:29481 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:22761 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:45472 TIME_WAIT  
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:7342 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144:namemunge ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:63626 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:19268 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:30008 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:26309 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:50462 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163.:6728 ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40-144-163:osaut ESTABLISHED
tcp        0      0 ip-172-31-4-20:postgres ec2-52-40:tcoflashagent TIME_WAIT  
udp        0      0 localhost:55412         localhost:55412         ESTABLISHED
Active UNIX domain sockets (w/o servers)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ]         DGRAM                    17668    /var/run/chrony/chronyd.sock
unix  2      [ ]         DGRAM                    9225     /run/systemd/notify
unix  2      [ ]         DGRAM                    9227     /run/systemd/cgroups-agent
unix  5      [ ]         DGRAM                    9238     /run/systemd/journal/socket
unix  14     [ ]         DGRAM                    9240     /dev/log
unix  2      [ ]         DGRAM                    10708    /run/systemd/shutdownd
unix  3      [ ]         STREAM     CONNECTED     6109236  
unix  3      [ ]         STREAM     CONNECTED     6109268  
unix  3      [ ]         STREAM     CONNECTED     6109259  
unix  3      [ ]         STREAM     CONNECTED     17530    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109227  
unix  3      [ ]         STREAM     CONNECTED     6109270  
unix  3      [ ]         STREAM     CONNECTED     6109230  
unix  3      [ ]         STREAM     CONNECTED     6109240  
unix  3      [ ]         STREAM     CONNECTED     6109280  
unix  3      [ ]         STREAM     CONNECTED     6109277  
unix  3      [ ]         STREAM     CONNECTED     6109291  
unix  3      [ ]         STREAM     CONNECTED     6106793  
unix  3      [ ]         STREAM     CONNECTED     6109233  
unix  3      [ ]         STREAM     CONNECTED     6109265  
unix  3      [ ]         STREAM     CONNECTED     6107209  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109282  
unix  3      [ ]         STREAM     CONNECTED     6109271  
unix  3      [ ]         STREAM     CONNECTED     6109237  
unix  3      [ ]         STREAM     CONNECTED     6109276  
unix  3      [ ]         STREAM     CONNECTED     19251    
unix  3      [ ]         STREAM     CONNECTED     16553    
unix  3      [ ]         STREAM     CONNECTED     6109273  
unix  3      [ ]         STREAM     CONNECTED     6109279  
unix  3      [ ]         STREAM     CONNECTED     6109274  
unix  3      [ ]         DGRAM                    6106813  
unix  3      [ ]         STREAM     CONNECTED     6109297  
unix  3      [ ]         STREAM     CONNECTED     6107875  
unix  3      [ ]         STREAM     CONNECTED     9623230  
unix  3      [ ]         STREAM     CONNECTED     6109258  
unix  3      [ ]         STREAM     CONNECTED     6109234  
unix  3      [ ]         STREAM     CONNECTED     20865    
unix  3      [ ]         STREAM     CONNECTED     6109292  
unix  3      [ ]         STREAM     CONNECTED     6109267  
unix  2      [ ]         DGRAM                    6106795  
unix  3      [ ]         STREAM     CONNECTED     6109294  
unix  3      [ ]         STREAM     CONNECTED     6109229  
unix  2      [ ]         DGRAM                    20867    
unix  3      [ ]         DGRAM                    6106812  
unix  3      [ ]         STREAM     CONNECTED     6109295  
unix  3      [ ]         STREAM     CONNECTED     6109226  
unix  3      [ ]         STREAM     CONNECTED     21598    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6108653  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     19252    /run/systemd/journal/stdout
unix  2      [ ]         DGRAM                    6109215  
unix  3      [ ]         STREAM     CONNECTED     16554    
unix  2      [ ]         DGRAM                    6109306  
unix  3      [ ]         STREAM     CONNECTED     9624156  /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109298  
unix  2      [ ]         DGRAM                    6110231  
unix  3      [ ]         STREAM     CONNECTED     16556    /var/run/dbus/system_bus_socket
unix  3      [ ]         STREAM     CONNECTED     6107198  
unix  3      [ ]         STREAM     CONNECTED     9571241  
unix  3      [ ]         STREAM     CONNECTED     17630    
unix  3      [ ]         STREAM     CONNECTED     6109241  
unix  2      [ ]         DGRAM                    16558    
unix  3      [ ]         STREAM     CONNECTED     6109253  
unix  2      [ ]         DGRAM                    17644    
unix  2      [ ]         DGRAM                    16414    
unix  3      [ ]         STREAM     CONNECTED     16423    
unix  3      [ ]         STREAM     CONNECTED     16588    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     17631    /run/systemd/journal/stdout
unix  2      [ ]         DGRAM                    17635    
unix  3      [ ]         STREAM     CONNECTED     17483    
unix  3      [ ]         STREAM     CONNECTED     16424    
unix  3      [ ]         STREAM     CONNECTED     17435    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109283  
unix  3      [ ]         STREAM     CONNECTED     17808    
unix  2      [ ]         DGRAM                    9788920  
unix  3      [ ]         STREAM     CONNECTED     17432    
unix  3      [ ]         STREAM     CONNECTED     6109246  
unix  2      [ ]         DGRAM                    17786    
unix  3      [ ]         STREAM     CONNECTED     6109244  
unix  3      [ ]         STREAM     CONNECTED     17650    
unix  3      [ ]         STREAM     CONNECTED     6109286  
unix  3      [ ]         STREAM     CONNECTED     6109255  
unix  3      [ ]         STREAM     CONNECTED     17731    
unix  3      [ ]         STREAM     CONNECTED     6109262  
unix  2      [ ]         DGRAM                    18022    
unix  3      [ ]         STREAM     CONNECTED     6106743  /var/run/dbus/system_bus_socket
unix  3      [ ]         STREAM     CONNECTED     6109250  
unix  2      [ ]         DGRAM                    17679    
unix  3      [ ]         STREAM     CONNECTED     6109264  
unix  3      [ ]         STREAM     CONNECTED     6109288  
unix  3      [ ]         STREAM     CONNECTED     6109243  
unix  2      [ ]         DGRAM                    16834    
unix  3      [ ]         STREAM     CONNECTED     6109261  
unix  3      [ ]         STREAM     CONNECTED     6109249  
unix  3      [ ]         STREAM     CONNECTED     17801    
unix  3      [ ]         STREAM     CONNECTED     17800    
unix  3      [ ]         STREAM     CONNECTED     6109247  
unix  2      [ ]         DGRAM                    9571234  
unix  3      [ ]         STREAM     CONNECTED     16722    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     6109256  
unix  3      [ ]         STREAM     CONNECTED     17730    
unix  3      [ ]         STREAM     CONNECTED     16721    
unix  3      [ ]         STREAM     CONNECTED     9571240  
unix  3      [ ]         STREAM     CONNECTED     6109285  
unix  3      [ ]         STREAM     CONNECTED     20151    /run/systemd/journal/stdout
unix  3      [ ]         STREAM     CONNECTED     17815    /run/gssproxy.sock
unix  3      [ ]         STREAM     CONNECTED     6109252  
unix  3      [ ]         STREAM     CONNECTED     19325    
unix  3      [ ]         STREAM     CONNECTED     6109289  
unix  3      [ ]         STREAM     CONNECTED     16587    /run/systemd/journal/stdout

It's also worth noting that the server itself seems to be performing fine (aside from this poor application connection queueing):

top - 02:57:30 up 79 days, 21:54,  1 user,  load average: 0.05, 0.05, 0.02
Tasks: 292 total,   1 running, 291 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni, 99.8 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem :  8176052 total,   296972 free,   522876 used,  7356204 buff/cache
KiB Swap:        0 total,        0 free,        0 used.  6565972 avail Mem

That is to say, the amount of free memory (296972) is not much different from days past when I've peeked at the server without such a high load of connections - and the load average and CPU are ridiculously small.

Proposed solution

We're using workers = 4 in the gunincorn_config.py, which means 4 "processes" per Django app.

Given the guidance here: https://github.com/jneight/django-db-geventpool/issues/21#issuecomment-325144847

So, MAX_CONNS should be set to (postgres's connections allowed / number of processes) or lower.

That means that if we have ~100 connections at the database, then with 10 Django projects, and 2 container instances per project, we ought to allocate no more than 4 postgres connections per container, and thus set MAX_CONNS to "1" for each project.

I'm going to experimentally try this with the currently-worst-offending project (Local Elections, which spun up 53 connections at last count), then run down the line.

Some more resources on this topic:

https://devcenter.heroku.com/articles/python-concurrency-and-database-connections (pretty clear but you need to convert the "heroku speak" into the actual django/gunicorn commands but is the same basic architecture behind)

https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/

We're better off than we have been, but nothing earth shattering. At peak I was able to get up to 18 connections to the Local Elections database from containers (plus a couple of PgAdmin sessions).

Still, this is far better than the 57 we saw earlier tonight - no matter how hard I tried, I couldn't get the numbers back up near that high for LE db. The rest were misbehaving still:

So I'm going to proceed with a PR to the rest of the projects for the MAX_CONNS = 1 change.

Further, based on this article that Brian forwarded - and his previous comments about using a database-pooling driver - I was going to PR django-db-geventpool into each project as well...

...except that it's already been implemented in housing-2018, disaster-resilience-backend, elections-2018-backend and neighborhoods-2018.

It's already in all of them for prod

@MikeTheCanuck, so removing the non-pooling driver appears to be the quickest solution to this concern. Deployed a test update to the Transportation Systems Project today, in which I commented out the Debug = False overrides for the database driver:

https://github.com/hackoregon/transportation-systems-backend-2018/blob/9ad17b6b102c235047b00609881b47042057de0b/transportation_systems_2018/settings.py#L196

From that point, django would run with the standard postgis backend (same as the one used in development) - django.contrib.gis.db.backends.postgis

Once live, I then preformed some load tests.

Prior to beginning the number of active connections to the Transportation-Systems-Main database was 10

I then began a first round of load tests, using 2 threads, with 6 tests a piece, looping twice, with a 15 second ramp up. This is equal to 24 connections. From this graph we can see the connections going up and down quickly, returning to the original 10 after testing:

screen shot 2018-06-20 at 11 29 16 am

After this original test, i ramped up users to 4, which would then be 48 calls and reran the test. Saw similar pattern:

screen shot 2018-06-20 at 11 34 16 am

This seems to be the path we want to go ASAP. Ultimately the idea of the db pooling use would to be able to increase availability under high traffic times, by using existing database connections. This did not occur, and led to decreased availability do to unused idle connections.

Lemme make sure I understand the before and after:

django.contrib.gis.db.backends.postgis is the non-pooling driver that's already being used in dev (i.e. anywhere that DEBUG=True), and when an AWS container is deployed to use this driver, we see the connections immediately back off after requests are fulfilled (like we've been trying to achieve for days)
django_db_geventpool.backends.postgis is the pooling driver that we've configured in all the API projects to be used in production setting such as AWS (i.e. this driver is enabled when DEBUG=False), and this is the one that has resisted all attempts to configure Django, Gunicorn or PostgreSQL to resist holding open the idle connections.

Recommendation

Revert all API projects immediately to use their chosen non-pooling driver in both dev and production settings:

e.g. django.contrib.gis.db.backends.postgis for any projects that need PostGIS capabilities
e.g. django.db.backends.postgresql_psycopg2 for the one project that isn't using PostGIS (i.e. Local Elections)

Do I have it right?

@MikeTheCanuck you are correct. Most likely this can just be changed by removing the code block in this commit:

https://github.com/hackoregon/backend-examplar-2018/commit/8d8f66a0a861c918ce6a2501489db6c9fa8513be

@bhgrant8 that was my thought as well, glad for your concurrence. Great minds think alike - though as my buddy Duke Thornley always appends, "...but fools seldom differ!"

All those projects that were previously using a geventpool driver - and which have now switched back to a non-pooling driver - are operating well within parameters.

Here you see the effect when all projects except Housing Affordability and Local Elections have stopped using the pooling driver.

Consider this issue resolved.

Given the results we've achieved, and all the experimental evidence to date, I have a theory why the number of connections held open by ECS containers has exceeded the theoretical maximum number of connections (MAX_CONNS x workers x # of tasks per service) on many occasions this week:

container API images are being revised and deployed quite frequently - sometimes a few per night of last-minute development activity
we've observed in the past that the Docker agent on the ECS hosts doesn't release the "stopped" container images, but hangs on to them for an extended period of time
it's quite possible then, that the Docker agent is keeping the containers (and thus their applications) alive for hours after the service has been updated with new task definitions (i.e. deployed new container versions)
for example, if we deployed the local-elections service's containers twice due to two upgrades within an hour of each other, it's possible that the extra database connections are from the n-1 or n-2 containers whose Django apps are still holding open persistent ("pooled" but zombied) database connections.

This would explain how some databases were observed at nearly a hundred active connections (all originating from within the AWS network - remote IPs in the ECS range) even though the containers were configured with far lower possible connections.

Summary of problem

In the architecture we just changed, the Django app uses the pooling database driver in a gunicorn/gevent forked worker approach, connects to the public IP address of the PostgreSQL server and obtains its results
once the result is returned from the database, we had assumed that the gunicorn/gevent worker would persist, and would continue to hold open the database connection for future requests serviced by that worker
the further assumption was that from the database's point of view, we should never see more than (MAX_CONNS x workers x container instances) numbers of connections for any one database, from any AWS EC2 IP address sources
instead however, we saw instances where the number of connections was sometimes 2-3 times what should theoretically exist if the gunicorn/gevent workers were re-using or terminating any of their database connections

Theories as to why this is happening

Docker engine doesn't terminate replaced containers when a new Task Definition is deployed, but keeps those containers alive and keeps the database connections. FALSE: ECS is documented to terminate the containers/Tasks when a new Task Definition is deployed.
Database server is holding open the connections due to one of its timeout values. FALSE: when we ran the container on a local dev box, connecting straight over the Internet to the production PostgreSQL server, and using a non-pooling database driver, the connections immediately dropped as soon as the request was completed.
Gevent/Gunicorn forked workers don't close the connection when the worker's request has closed. FALSE: AFAIK, Brian has tested this by manually inserting the close connection request that might or might not exist in the current implementation.
NAT Gateway (which lies between the Django containers and the PostgreSQL server) receives a request to terminate the TCP session, and negotiates with the Django container. Until the connection is closed, the network connection between the NAT Gateway and the PostgreSQL server stays in ESTABLISHED state. However, if the Django container doesn't properly complete the termination sequence, the NAT Gateway continues to hold an ESTABLISHED connection with the PostgreSQL server, even long after the network connection between Django and the NAT Gateway is gone.

hackoregon / civic-devops