Обсуждение: pgpool-II (max_pool and num_init_children)
I'm trying to get a handle on sane values for these two parameters. I assume that they should somehow correlate to my existing max_connections in my postgresql.conf file. Anyone using pgpool-II care to comment? I'm sure it's a balancing act between how many connections you want verses how many cached connections you have, but not sure how to properly assess this issue. (I've tried posting to the pgpool list, but it's apparently unavailable at this time) -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
> I'm trying to get a handle on sane values for these two parameters. I > assume that they should somehow correlate to my existing > max_connections in my postgresql.conf file. Anyone using pgpool-II care > to comment? > > I'm sure it's a balancing act between how many connections you want > verses how many cached connections you have, but not sure how to > properly assess this issue. > > (I've tried posting to the pgpool list, but it's apparently unavailable > at this time) Here is an recommended rule: max_pool*num_init_children <= (max_connections - superuser_reserved_connections) Because num_init_children defines the max concurrent connections pgpool-II can accept, you might want to keep num_init_children as large as possible. In this case you could lower max_pool to as low as 1, which may cause performance degration because of low effect of connection cache if you would have more than 1 user/database combinations. However lower max_pool will not any error or any problems except performance. If you want to use query cancelation frequetntly, the formula above will become: max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) because you need extra connection to use query cancelation. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Tatsuo Ishii wrote: >> I'm trying to get a handle on sane values for these two parameters. I >> assume that they should somehow correlate to my existing >> max_connections in my postgresql.conf file. Anyone using pgpool-II care >> to comment? >> >> I'm sure it's a balancing act between how many connections you want >> verses how many cached connections you have, but not sure how to >> properly assess this issue. >> >> (I've tried posting to the pgpool list, but it's apparently unavailable >> at this time) > > Here is an recommended rule: > > max_pool*num_init_children <= (max_connections - superuser_reserved_connections) > > Because num_init_children defines the max concurrent connections > pgpool-II can accept, you might want to keep num_init_children as > large as possible. In this case you could lower max_pool to as low as > 1, which may cause performance degration because of low effect of > connection cache if you would have more than 1 user/database > combinations. However lower max_pool will not any error or any > problems except performance. We have a complicated set up. We have 13 databases with 12 postmasters. So that means 2 databases share a postmaster, the rest have their own. Consequently, separate pools for each postmaster. We will frequently have 20-30 or more users connecting to any one database, although some databases are much lower. This is further complicated by a web application that also connects to the databases. To complicate matters even further, we will likely have multiple pools for each database, in order to give priority to local users over the web access. In other words, a large pool of connections for local users, and a smaller pool for web access. That being said, I'm assuming: (max_pool1*num_init_children) + (max_pool2*num_init_children) <= (max_connections - superuser_reserved_connections) > If you want to use query cancelation frequetntly, the formula above > will become: > > max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) > > because you need extra connection to use query cancelation. I don't believe we'll have frequent query cancellations. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
Le 02/07/2010 15:46, Geoffrey a écrit : > Tatsuo Ishii wrote: >>> I'm trying to get a handle on sane values for these two parameters. >>> I assume that they should somehow correlate to my existing >>> max_connections in my postgresql.conf file. Anyone using pgpool-II >>> care to comment? >>> >>> I'm sure it's a balancing act between how many connections you want >>> verses how many cached connections you have, but not sure how to >>> properly assess this issue. >>> >>> (I've tried posting to the pgpool list, but it's apparently >>> unavailable at this time) >> >> Here is an recommended rule: >> >> max_pool*num_init_children <= (max_connections - >> superuser_reserved_connections) >> >> Because num_init_children defines the max concurrent connections >> pgpool-II can accept, you might want to keep num_init_children as >> large as possible. In this case you could lower max_pool to as low as >> 1, which may cause performance degration because of low effect of >> connection cache if you would have more than 1 user/database >> combinations. However lower max_pool will not any error or any >> problems except performance. > > We have a complicated set up. We have 13 databases with 12 postmasters. > So that means 2 databases share a postmaster, the rest have their > own. Consequently, separate pools for each postmaster. We will > frequently have 20-30 or more users connecting to any one database, > although some databases are much lower. This is further complicated by > a web application that also connects to the databases. > You won't be able to handle more than one postmaster with one pgPool-II in pooling mode. You'll need at least one pgPool-II for each postmaster. And so, do one configuration for each pgPool-II. -- Guillaume http://www.postgresql.fr http://dalibo.com
Guillaume Lelarge wrote: > Le 02/07/2010 15:46, Geoffrey a écrit : >> Tatsuo Ishii wrote: >>>> I'm trying to get a handle on sane values for these two parameters. >>>> I assume that they should somehow correlate to my existing >>>> max_connections in my postgresql.conf file. Anyone using pgpool-II >>>> care to comment? >>>> >>>> I'm sure it's a balancing act between how many connections you want >>>> verses how many cached connections you have, but not sure how to >>>> properly assess this issue. >>>> >>>> (I've tried posting to the pgpool list, but it's apparently >>>> unavailable at this time) >>> Here is an recommended rule: >>> >>> max_pool*num_init_children <= (max_connections - >>> superuser_reserved_connections) >>> >>> Because num_init_children defines the max concurrent connections >>> pgpool-II can accept, you might want to keep num_init_children as >>> large as possible. In this case you could lower max_pool to as low as >>> 1, which may cause performance degration because of low effect of >>> connection cache if you would have more than 1 user/database >>> combinations. However lower max_pool will not any error or any >>> problems except performance. >> We have a complicated set up. We have 13 databases with 12 postmasters. >> So that means 2 databases share a postmaster, the rest have their >> own. Consequently, separate pools for each postmaster. We will >> frequently have 20-30 or more users connecting to any one database, >> although some databases are much lower. This is further complicated by >> a web application that also connects to the databases. >> > > You won't be able to handle more than one postmaster with one pgPool-II > in pooling mode. You'll need at least one pgPool-II for each postmaster. > And so, do one configuration for each pgPool-II. Yes, that is the intent. But, we are considering having multiple pgPool-II for one postmaster. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson