[pgsql-jp: 33692] Re: pgpool の接続が増え続けます

[Tatsuo Ishii]

石井です．

> 引き続きお手数をおかけいたします。
> 以下の部分でわかりますでしょうか。
> よろしくお願いいたします。

現在のpgpoolでは，connection_life_timeでタイムアウトを設定しない限り，
一度確立されたコネクションは解放されません．ですから，多数のユーザやデー
タベースをとっかえ引っ替え使うようなケースではこういう現象が起きるので
はないかと想像しています．

コネクションキャッシュが一杯になってしまったら，一番古いものを切断して
新しい接続を受け入れるように修正してみました．よかったらお試し下さい．
--
Tatsuo Ishii

----------------------------------------------------------------------
*** pool_connection_pool.c	30 Jun 2004 08:01:52 -0000	1.21
--- pool_connection_pool.c	16 Jul 2004 05:47:32 -0000
***************
*** 43,48 ****
--- 43,49 ----
  POOL_CONNECTION_POOL *pool_connection_pool;	/* connection pool */
  
  static POOL_CONNECTION_POOL_SLOT *create_cp(POOL_CONNECTION_POOL_SLOT *cp, int secondary_backend);
+ static POOL_CONNECTION_POOL *new_connection(POOL_CONNECTION_POOL *p);
  
  /*
  * initialize connection pools. this should be called once at the startup.
***************
*** 129,134 ****
--- 130,137 ----
  {
  	int i;
  	int fd;
+ 	time_t closetime;
+ 	POOL_CONNECTION_POOL *oldestp;
  
  	POOL_CONNECTION_POOL *p = pool_connection_pool;
  
***************
*** 141,188 ****
  	for (i=0;i<pool_config.max_pool;i++)
  	{
  		if (MASTER_CONNECTION(p) == NULL)
! 		{
! 			/* create master connection */
! 			MASTER_CONNECTION(p) = malloc(sizeof(POOL_CONNECTION_POOL_SLOT));
! 			if (MASTER_CONNECTION(p) == NULL)
! 			{
! 				pool_error("pool_create_cp: malloc() failed");
! 				return NULL;
! 			}
! 			create_cp(MASTER_CONNECTION(p), 0);
  
! 			/* initialize Paramter Status save structure */
! 			if (pool_init_params(&MASTER(p)->params))
! 			{
! 				return NULL;
! 			}
! 			p->num = 1;	/* number of slots */
  
! 			/* create secondary connection */
! 			if (pool_config.replication_enabled)
! 			{
! 				SECONDARY_CONNECTION(p) = malloc(sizeof(POOL_CONNECTION_POOL_SLOT));
! 				if (SECONDARY_CONNECTION(p) == NULL)
! 				{
! 					pool_error("pool_create_cp: malloc() failed");
! 					return NULL;
! 				}
! 				create_cp(SECONDARY_CONNECTION(p), 1);
  
! 				/* initialize Paramter Status save structure */
! 				if (pool_init_params(&MASTER(p)->params))
! 				{
! 					return NULL;
! 				}
  
! 				p->num++;	/* number of slots */
! 			}
  
! 			return p;
! 		}
! 		p++;
  	}
! 	return NULL;
  }
  
  /*
--- 144,198 ----
  	for (i=0;i<pool_config.max_pool;i++)
  	{
  		if (MASTER_CONNECTION(p) == NULL)
! 			return new_connection(p);
! 		p++;
! 	}
  
! 	pool_debug("no empty connection slot was found");
  
! 	/*
! 	 * no empty connection slot was found. look for the oldest connection and discard it.
! 	 */
! 	oldestp = p = pool_connection_pool;
! 	closetime = MASTER_CONNECTION(p)->closetime;
! 	for (i=0;i<pool_config.max_pool;i++)
! 	{
! 		pool_debug("user: %s database: %s closetime: %d",
! 				   MASTER_CONNECTION(p)->sp->user,
! 				   MASTER_CONNECTION(p)->sp->database,
! 				   MASTER_CONNECTION(p)->closetime);
! 		if (MASTER_CONNECTION(p)->closetime < closetime)
! 		{
! 			closetime = MASTER_CONNECTION(p)->closetime;
! 			oldestp = p;
! 		}
! 		p++;
! 	}
  
! 	p = oldestp;
! 	pool_send_frontend_exits(p);
  
! 	pool_debug("discarding old %d th connection. user: %s database: %s", 
! 			   oldestp - pool_connection_pool,
! 			   MASTER_CONNECTION(p)->sp->user,
! 			   MASTER_CONNECTION(p)->sp->database);
  
! 	free(MASTER_CONNECTION(p)->sp->user);
! 	free(MASTER_CONNECTION(p)->sp->database);
! 	free(MASTER_CONNECTION(p)->sp->startup_packet);
! 	pool_close(MASTER_CONNECTION(p)->con);
! 	memset(p, 0, sizeof(POOL_CONNECTION_POOL));
! 
! 	if (pool_config.replication_enabled)
! 	{
! 		free(SECONDARY_CONNECTION(p)->sp->user);
! 		free(SECONDARY_CONNECTION(p)->sp->database);
! 		free(SECONDARY_CONNECTION(p)->sp->startup_packet);
! 		pool_close(SECONDARY_CONNECTION(p)->con);
! 		memset(p, 0, sizeof(POOL_CONNECTION_POOL));
  	}
! 
! 	return new_connection(p);
  }
  
  /*
***************
*** 408,411 ****
--- 418,462 ----
  	cp->con = pool_open(fd);
  	cp->closetime = 0;
  	return cp;
+ }
+ 
+ static POOL_CONNECTION_POOL *new_connection(POOL_CONNECTION_POOL *p)
+ {
+ 	/* create master connection */
+ 	MASTER_CONNECTION(p) = malloc(sizeof(POOL_CONNECTION_POOL_SLOT));
+ 	if (MASTER_CONNECTION(p) == NULL)
+ 	{
+ 		pool_error("pool_create_cp: malloc() failed");
+ 		return NULL;
+ 	}
+ 	create_cp(MASTER_CONNECTION(p), 0);
+ 
+ 			/* initialize Paramter Status save structure */
+ 	if (pool_init_params(&MASTER(p)->params))
+ 	{
+ 		return NULL;
+ 	}
+ 	p->num = 1;	/* number of slots */
+ 
+ 	/* create secondary connection */
+ 	if (pool_config.replication_enabled)
+ 	{
+ 		SECONDARY_CONNECTION(p) = malloc(sizeof(POOL_CONNECTION_POOL_SLOT));
+ 		if (SECONDARY_CONNECTION(p) == NULL)
+ 		{
+ 			pool_error("pool_create_cp: malloc() failed");
+ 			return NULL;
+ 		}
+ 		create_cp(SECONDARY_CONNECTION(p), 1);
+ 
+ 		/* initialize Paramter Status save structure */
+ 		if (pool_init_params(&MASTER(p)->params))
+ 		{
+ 			return NULL;
+ 		}
+ 
+ 		p->num++;	/* number of slots */
+ 	}
+ 
+ 	return p;
  }