[pgpool-general: 3882] Re: pgpool configuration

Tatsuo Ishii ishii at postgresql.org
Tue Jul 21 10:20:32 JST 2015


> Hi, 
> 
> 
> I think I found the problem but I don't know how to fix it. 
> 
> 
> I have a client that makes a lot of short living connections to the server. When the client closes the connection, the port on the client side will stay in TIME_WAIT state for 4 minutes until it can be reused. So the application runs all right until it reaches the available ports (> 28.000 all in 
> TIME_WAIT state) and then it starts returning errors. 
> 
> 
> I found some articles suggesting to change the kernel parameters:
> net.ipv4.ip_local_port_range = 1024 65000
> net.ipv4.tcp_tw_recycle = 1
> net.ipv4.tcp_fin_timeout = 10

In addition to this, you might want to set net.ipv4.tcp_tw_reuse = 1.

> I would like to ask if this is the right way to work with pgpool and I should look into adjusting these parameters or I am missing something?

I think these kernel tunings are quite common for internet servers
like apache (and maybe PostgreSQL).

> net.ipv4.tcp_fin_timeout = 10

might be too aggressive according to blogs I read (unfortunately they
are Japanese). Some articles recommend 30 seconds.

> Is anyone else using only pgpool for connection pooling for clients generating a lot of connections or they are using other connection pooling (like tomcat jdbc connection pool) on top of pgpool.
> 
> Thanks,
> ioana
> 
> 
> 
> 
> 
> On Thursday, July 9, 2015 4:26 AM, Yugo Nagata <nagata at sraoss.co.jp> wrote:
> Hi,
> 
> pgpool-II generates 200 processes and connections to PostgreSQL,
> and 200 clients can connect to pgpool-II simultaneously, since num_init_children=200. Other clients are to wait for connection
> in the listen queue of OS.
> 
> The length of listen queue can be controlled by listen_backlog_multiplier
> parameter. The default is 2, so the queue length is 400 (= 200 * 2).
> (In acutual, the length is decided by OS kernel.)
> If the queue is overflowing, OS retries connection several times
> (tcp_syn_retries) before it fails. This causes performance degression
> or connection error. So, specifying listen_backlog_multiplier to
> larger value might be resolve your situation.
> 
> You can check if the overflow occurs by `netstat -s`. If so,
> a message like the following is in TcpExt part.
> 
> 535 times the listen queue of a socket overflowed
> 
> 
> I don't know why only 60 connection are used with Tomcat JDBC
> connection pool. However, I think this can be affected by Tomcat configuration like maxThreads, maxActive and so on.
> 
> 
> 
> On Tue, 7 Jul 2015 12:48:03 +0000 (UTC)
> Ioana Danes <ioanasoftware at yahoo.ca> wrote:
> 
>> Hi Everyone,
>> 
>> I am doing a performance test on pgpool only for pooling, no replication, no failover and no load balancing. 
>> 
>> Here is a snapshot of the pgpool parameters I thought are relevant:
>> 
>> num_init_children = 200
>> max_pool = 1
>> child_life_time = 300
>> child_max_connections = 0
>> connection_life_time = 0
>> client_idle_limit = 0
>> 
>> connection_cache = on
>> reset_query_list = 'ABORT; DISCARD ALL'
>> 
>> replication_mode = off
>> load_balance_mode = off
>> master_slave_mode = off
>> use_watchdog = off
>> 
>> 
>> Postgres database has max_connections = 250 and there is no other application using it.
>> 
>> 
>> I am using 1300 clients and with tomcat jdbc connection pool it typically needs around 60 database connections. When I am using pgpool instead of tomcat jdbc connection pool I run out of the 200 connections when not even half of the clients are running.
>> 
>> Error from the web application:
>> 
>> persistence.StorageException: The connection attempt failed.
>> 
>> pgpool-II-3.4.2-1.3
>> postgresql94-9.4.1-7.2
>> 
>> SUSE Linux Enterprise Server 11 (x86_64)
>> VERSION = 11
>> PATCHLEVEL = 3
>> 
>> 
>> 
>> Is this the expected behaviour, is Tomcat connection pooling better that pgpool at reusing connections or I am doing something wrong?
>> 
>> Thank you,
>> ioana
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
> 
> 
> -- 
> Yugo Nagata <nagata at sraoss.co.jp
>>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


More information about the pgpool-general mailing list