[Pgpool-general] patch for pgpool 2.6.5 to limit the lifetime of a
connection
Pomarede Nicolas
npomarede at corp.free.fr
Mon Jan 2 15:05:43 GMT 2006
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 ?"
-------------- next part --------------
diff -ru pgpool-2.6.5/child.c pgpool-2.6.5_new/child.c
--- pgpool-2.6.5/child.c 2005-11-09 15:12:21.000000000 +0100
+++ pgpool-2.6.5_new/child.c 2005-12-27 16:26:25.336068163 +0100
@@ -80,6 +80,8 @@
int child_idle_sec;
struct timeval timeout;
static int connected;
+ int requests_count = 0; /* used if child_max_requests > 0 */
+
pool_debug("I am %d", getpid());
@@ -133,6 +135,17 @@
idle = 1;
+
+ /* check if maximum requests count for this child reached */
+ if ( ( pool_config.child_max_requests > 0 ) &&
+ ( requests_count >= pool_config.child_max_requests ) )
+ {
+ pool_log("child exiting, %d queries reached", pool_config.child_max_requests);
+ send_frontend_exits();
+ exit(2);
+ }
+
+
/* perform accept() */
frontend = do_accept(unix_fd, inet_fd, &timeout);
@@ -274,6 +287,11 @@
connected = 1;
+ /* increment queries counter if necessary */
+ if ( pool_config.child_max_requests > 0 )
+ requests_count++;
+
+
/* query process loop */
for (;;)
{
diff -ru pgpool-2.6.5/pgpool.conf.sample pgpool-2.6.5_new/pgpool.conf.sample
--- pgpool-2.6.5/pgpool.conf.sample 2005-06-25 02:58:46.000000000 +0200
+++ pgpool-2.6.5_new/pgpool.conf.sample 2005-12-22 19:26:11.264020425 +0100
@@ -42,6 +42,9 @@
# no timeout
connection_life_time = 0
+# if child_max_requests requests were received, child exits. 0 means no exit.
+child_max_requests = 0
+
# logging directory
logdir = '/tmp'
diff -ru pgpool-2.6.5/pool_config.c pgpool-2.6.5_new/pool_config.c
--- pgpool-2.6.5/pool_config.c 2005-10-19 05:56:31.000000000 +0200
+++ pgpool-2.6.5_new/pool_config.c 2005-12-26 18:55:48.366787013 +0100
@@ -1,7 +1,7 @@
/* A lexical scanner generated by flex */
/* Scanner skeleton version:
- * $Header: /cvsroot/pgpool/pgpool/pool_config.c,v 1.4 2005/10/19 03:56:31 t-ishii Exp $
+ * $Header: /home/daffy/u0/vern/flex/RCS/flex.skl,v 2.91 96/09/10 16:58:48 vern Exp $
*/
#define FLEX_SCANNER
@@ -399,7 +399,7 @@
/* -*-pgsql-c-*- */
/*
*
- * $Header: /cvsroot/pgpool/pgpool/pool_config.c,v 1.4 2005/10/19 03:56:31 t-ishii Exp $
+ * $Header: /cvsroot/pgpool/pgpool/pool_config.l,v 1.4 2005/08/16 13:42:19 t-ishii Exp $
*
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
@@ -597,7 +597,7 @@
YY_DECL
{
register yy_state_type yy_current_state;
- register char *yy_cp = NULL, *yy_bp = NULL;
+ register char *yy_cp, *yy_bp;
register int yy_act;
#line 77 "pool_config.l"
@@ -1649,6 +1649,7 @@
pool_config.num_init_children = 32;
pool_config.child_life_time = 300;
pool_config.connection_life_time = 0;
+ pool_config.child_max_requests = 0;
pool_config.max_pool = 4;
pool_config.logdir = DEFAULT_LOGDIR;
@@ -1863,6 +1864,17 @@
}
pool_config.connection_life_time = v;
}
+ else if (!strcmp(key, "child_max_requests"))
+ {
+ int v = atoi(yytext);
+
+ if (token != POOL_INTEGER || v < 0)
+ {
+ pool_error("pool_config: %s must be higher than 0 numeric value", key);
+ return(-1);
+ }
+ pool_config.child_max_requests = v;
+ }
else if (!strcmp(key, "max_pool"))
{
int v = atoi(yytext);
diff -ru pgpool-2.6.5/pool_config.l pgpool-2.6.5_new/pool_config.l
--- pgpool-2.6.5/pool_config.l 2005-08-16 15:42:19.000000000 +0200
+++ pgpool-2.6.5_new/pool_config.l 2005-12-22 19:23:12.113608582 +0100
@@ -112,6 +112,7 @@
pool_config.num_init_children = 32;
pool_config.child_life_time = 300;
pool_config.connection_life_time = 0;
+ pool_config.child_max_requests = 0;
pool_config.max_pool = 4;
pool_config.logdir = DEFAULT_LOGDIR;
@@ -326,6 +327,17 @@
}
pool_config.connection_life_time = v;
}
+ else if (!strcmp(key, "child_max_requests"))
+ {
+ int v = atoi(yytext);
+
+ if (token != POOL_INTEGER || v < 0)
+ {
+ pool_error("pool_config: %s must be higher than 0 numeric value", key);
+ return(-1);
+ }
+ pool_config.child_max_requests = v;
+ }
else if (!strcmp(key, "max_pool"))
{
int v = atoi(yytext);
diff -ru pgpool-2.6.5/pool.h pgpool-2.6.5_new/pool.h
--- pgpool-2.6.5/pool.h 2005-10-22 14:57:57.000000000 +0200
+++ pgpool-2.6.5_new/pool.h 2005-12-27 11:59:54.938167131 +0100
@@ -119,6 +119,7 @@
int num_init_children; /* # of children initially pre-forked */
int child_life_time; /* if idle for this seconds, child exits */
int connection_life_time; /* if idle for this seconds, connection closes */
+ int child_max_requests; /* if max_requests received, child exits */
int max_pool; /* max # of connection pool per child */
char *logdir; /* logging directory */
char *backend_socket_dir; /* Unix domain socket directory for the PostgreSQL server */
diff -ru pgpool-2.6.5/pool_process_query.c pgpool-2.6.5_new/pool_process_query.c
--- pgpool-2.6.5/pool_process_query.c 2005-11-09 15:12:21.000000000 +0100
+++ pgpool-2.6.5_new/pool_process_query.c 2005-12-22 19:20:27.606856201 +0100
@@ -1708,6 +1708,11 @@
status[i].desc = "if idle for this seconds, connection closes";
i++;
+ status[i].name = "child_max_requests";
+ snprintf(status[i].value, MAXVALLEN, "%d", pool_config.child_max_requests);
+ status[i].desc = "if max_requests received, chile exits";
+ i++;
+
status[i].name = "max_pool";
snprintf(status[i].value, MAXVALLEN, "%d", pool_config.max_pool);
status[i].desc = "max # of connection pool per child";
More information about the Pgpool-general
mailing list