Thanks for the reply Kevin.
> I'm trying to figure out why we had a build up of connections on
> our streaming replica.
Seriously, from the data provided, about all I can say is "because
you were opening them faster than you were closing them". You
don't say how many cores or how much RAM you had, but allowing 2000
connections is rarely a good idea.
http://tbeitr.blogspot.com/2015/11/for-better-service-please-take-number.html
The replica is an i2.4xl ec2 instance, so 122gb ram, 16 cores, 53 "compute units", 4 local storage ssds in raid 10. effective_cache_size is 89gb, work_mem is 128mb. Applications are connecting via pgbouncer with a default pool size of 650, steady state of 500-600 server connections for 1000-1500 clients. Normally pgbouncer limits the connections pretty well. I was most surprised when postgres wasn't timing connections out to keep them low.
Also, pgbouncer wasn't reporting an increase in the number of server connections even though they were showing up in postgres directly, so I was thinking pgbouncer thought these connections were already closed even though postgres couldn't close them.
> We're running postgres 9.3.5 on the master and 9.3.10 on
> the replica,
Because of data loss bugs in 9.3.5 which are fixed in 9.3.10, I
recommend you update the master ASAP.
Yep, we're working on it.
> linux 3.2.0 on both,
There are scheduler problems in kernels at that level; I recommend
upgrading linux to a 3.8 or later kernel; 3.13 or later is best, if
possible.
Ditto.
I don't suppose you have vmstat 1 output from the incident? If it
happens again, try to capture that.
Are you looking for a stat in particular?
This is the idle of each core during the first incident:
The dark blue at the bottom is core0, which I believe is the one pgbouncer is using. There's a drop when the connections start running away, but there's still some headroom there, and the other cores are pretty much unaffected until the restart.
Have you tuned the OS vm.dirty_* settings? How about the
transparent_hugepage settings?
I think the vm dirty settings are default:
vm.dirty_background_bytes = 0
vm.dirty_ratio = 20
vm.dirty_bytes = 0
vm.dirty_writeback_centisecs = 500
vm.dirty_expire_centisecs = 3000
If this is a NUMA environment, what does this show?:
numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
node 0 size: 124999 MB
node 0 free: 740 MB
node distances:
node 0
0: 10
Carlo Cabanilla
Datadog
