(09:40:10 AM) kohsuke: I'm trying to understand large number of idle postgres processes that appear to be kept around by mirrorbrain: https://issues.jenkins-ci.org/browse/JENKINS-12297
(09:40:46 AM) kohsuke: I'm seeing 30-40 of them constantly, and it occasionally goes up to 100 and causes starvation with other clients of postgres.
(09:41:03 AM) kohsuke: I wonder if anyone can kindly tell me where I should look.
(02:00:34 PM) poeml: kohsuke:
(02:00:36 PM) poeml: hi
(02:02:16 PM) poeml: idle postgres processes shouldn't harm as such, as long as they don't use resources. However, often postgresql has a connection limit configured, like 100, and if clients try to use that much connections, one runs into a problem.
(02:03:36 PM) poeml: are you using the connections via mod_mirrorbrain, or via another way (Drupal)?
(02:04:29 PM) kohsuke: poeml: we are hitting that 100 cap indeed
(02:04:35 PM) poeml: mod_mirrorbrain uses connection pooling, so it needs only a handful of connections - provided that Apache runs threaded (threaded MPM).
(02:05:09 PM) poeml: Drupal means PHP, so you maybe use prefork. Then you can easily have 100 Apache (preforked) processes where each of them wants to open a postgresql connection.
(02:05:23 PM) kohsuke: We are using mod_mirrorbrain, I think, but I think we are forking Apache like mad
(02:05:26 PM) poeml: or do you use PHP via fastcgi or something else?
(02:05:44 PM) kohsuke: So I guess that'd be the quick fix --- to change to threaded MPM
(02:06:11 PM) poeml: you can either increase the number of allowed connections in postgresql - as long as there are the resources for it (cpu and ram).
(02:06:17 PM) kohsuke: right
(02:06:27 PM) poeml: or you can limit the number of Apache processes that can be spawned (that's useful anyway).
(02:07:16 PM) kohsuke: Got it. Come to think of it, it all makes sense
(02:07:28 PM) poeml: If you have to keep prefork MPM for the reason of PHP, you can still limit the number of processes in the Apache process pool to a number that fits the number of allowed postgresql processes.
(02:07:32 PM) kohsuke: at least one connection needs to be kept around by apache process to be able to serve requests rapidly
(02:08:06 PM) poeml: anyway, if you have so many Apache processes lingering around, odds are that you don't really need them (I would expect most of them to be idle, or rather in keepalive state)
(02:08:53 PM) poeml: get rid of keepalive, or at least limit the keepalive time to 2 seconds at most. If you switch keepalive off even, you'll have dramatically fewer Apache processes -- I predict ;)
(02:09:41 PM) poeml: the small benefits of keepalive are more than outweighed by the benefit of keeping Apache small and responsive for everybody
(02:10:26 PM) kohsuke: poeml: thank you very much for your help. I know what to tweak now.