0

I'm trying to debug a sudden increase in our load average. I looked at this page to see if we were being DDoS'ed but the standout IP address was the loopback address:

$ netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n
...
3893 127.0.0.1

Looking at why this might be, I looked at my PostgreSQL connections with lsof -i | grep postgresql:

pgbouncer  9751 postgres   42u  IPv4 83453387      0t0  TCP localhost:57025->localhost:postgresql (ESTABLISHED)
pgbouncer  9751 postgres   43u  IPv4 83479251      0t0  TCP localhost:34492->localhost:postgresql (ESTABLISHED)
pgbouncer  9751 postgres   44u  IPv4 83453393      0t0  TCP localhost:57028->localhost:postgresql (ESTABLISHED)
postgres   9791 postgres   11u  IPv4 83451361      0t0  TCP localhost:postgresql->localhost:56947 (ESTABLISHED)
postgres   9792 postgres   11u  IPv4 83451366      0t0  TCP localhost:postgresql->localhost:56948 (ESTABLISHED)
postgres   9800 postgres   11u  IPv4 83449299      0t0  TCP localhost:postgresql->localhost:56954 (ESTABLISHED)
postgres   9815 postgres   11u  IPv4 83452202      0t0  TCP localhost:postgresql->localhost:56962 (ESTABLISHED)
postgres   9816 postgres   11u  IPv4 83448763      0t0  TCP localhost:postgresql->localhost:56965 (ESTABLISHED)
postgres   9817 postgres   11u  IPv4 83448768      0t0  TCP localhost:postgresql->localhost:56967 (ESTABLISHED)
postgres   9820 postgres   11u  IPv4 83448776      0t0  TCP localhost:postgresql->localhost:56970 (ESTABLISHED)
postgres   9821 postgres   11u  IPv4 83453105      0t0  TCP localhost:postgresql->localhost:56971 (ESTABLISHED)
postgres   9822 postgres   11u  IPv4 83453107      0t0  TCP localhost:postgresql->localhost:56975 (ESTABLISHED)
postgres   9825 postgres   11u  IPv4 83453109      0t0  TCP localhost:postgresql->localhost:56979 (ESTABLISHED)
postgres   9831 postgres   11u  IPv4 83449334      0t0  TCP localhost:postgresql->localhost:56987 (ESTABLISHED)
postgres   9836 postgres   11u  IPv4 83448808      0t0  TCP localhost:postgresql->localhost:56994 (ESTABLISHED)
postgres   9841 postgres   11u  IPv4 83448812      0t0  TCP localhost:postgresql->localhost:57003 (ESTABLISHED)
postgres   9842 postgres   11u  IPv4 83446596      0t0  TCP localhost:postgresql->localhost:57010 (ESTABLISHED)
postgres   9843 postgres   11u  IPv4 83453388      0t0  TCP localhost:postgresql->localhost:57025 (ESTABLISHED)
postgres   9844 postgres   11u  IPv4 83453394      0t0  TCP localhost:postgresql->localhost:57028 (ESTABLISHED)
postgres  10489 postgres   11u  IPv4 83479252      0t0  TCP localhost:postgresql->localhost:34492 (ESTABLISHED)

When I then do ps aux | grep <PID> on some of the PIDs on this list I generally get something like this:

$ ps aux | grep 14010
postgres 14010 11.8  1.9 4191632 318244 ?      Ss   12:25   0:09 postgres: postgres mydb 127.0.0.1(58099) idle

What does this all mean? Does this mean that I've got loads of idle connections that haven't timed out? Could this be the cause of the high load average? I'm using pgbouncer but I was using the default server_idle_timeout of 600 so I've since changed that to 60.

benwad
  • 255
  • 2
  • 12

1 Answers1

0

Maybe your application using Postgresql doesn't close correctely the sockets. If it is a dynamic web site, if each page open a socket but don't close it until the timeout, you can explode your server capacities.

Could you check what is connecting to PostgreSQL ? Check if there is a high amount of connections to your web site if it is a web server

Dom
  • 6,743
  • 1
  • 20
  • 24
  • How would I do that? Doing `lsof -i | grep "\->localhost:postgresql"` just shows a load of connections from localhost on various ports, similar to the first two lines of my previous output from `lsof`. – benwad Jul 29 '14 at 17:43
  • And it is a web server, with around 300 users online at the moment. However this is not an unusually high traffic so there must be something else causing the high load avg. – benwad Jul 29 '14 at 17:44
  • Run as root : "netstat -pntu|grep 5432". you will see in the last column the PID and the process connected to your Postgres database. Do you have more data to process in your tables today ? – Dom Jul 29 '14 at 19:10