[Pgpool-general] patch for pgpool 2.6.5 to limit the lifetime of a connection

[Tatsuo Ishii]

Thanks, your patches seem very usefull. I have merged your patches
into pgpool 2.7, which was released today. I have made small changes
to your patches however:

1) I prefer "child_max_connections" instead of "child_max_requests"
   since your patches actually limits the number of connection times
   from frontend, rather than number of query requests.

2) I changed the place where the connection counter is counted up,
   since the original place might be counted up mistakenly by
   an signal interruption while waiting the connection request from
   frontend.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Hello,
> 
> while using pgpool, I noticed that when keeping the connections to the 
> database, the corresponding 'postgres' processes where getting bigger and 
> bigger after a few days (it seems some RAM is not freed as long as the 
> connection is not closed with the database).
> 
> At first, I used 'child_life_time' to kill the pgpool processes that were 
> not active for a given time, which forced a connection close and freed 
> the memory. But as the server is getting more and more used, there's no 
> idle period where the number of queries is low enough to have 
> 'child_life_time' timer to work.
> 
> So, I patched pgpool to add a new parameter : child_max_requests.
> 
> When set to 0, everything work as before.
> 
> But if you set the parameter to a non null value, this will indicate the 
> maximum number of requests that a particular pgpool process can handle. 
> Once this number is reached, the child process exits (and is re-forked by 
> the master pgpool). This way, the postgres processes are not kept running 
> for an unlimited time, and this will avoid to have the free memory going 
> lower and lower.
> 
> In pgpool.conf, add this line for example :
> 
> child_max_requests = 1000
> 
> 
> This patch has been tested on a production machine that handles between 
> 300.000 and 400.000 queries per hour since more than 1 week and didn't 
> create any crash or similar problem, so it should be quite safe to use.
> 
> When looking at memory graphs, this give a better repartition, with less 
> peak than before and no trend where memory used by postgres was getting 
> bigger and bigger because the connection was kept open for too long.
> 
> The patch is rather simple, so I hope it can be merged in the next version 
> of pgpool ?
> 
> Feel free to try it and report any problem.
> 
> 
> Appart from that, happy new year to all and thanks for pgpool which really 
> makes a great job !
> 
> 
> Regards,
> 
> 
> ----------------
> Nicolas Pomarede                   e-mail:   npomarede at corp.free.fr
> 
> "In a world without walls and fences, who needs windows and gates ?"