[pgpool-general: 2779] Re: Pooling and Queueing
Tatsuo Ishii
ishii at postgresql.org
Sat Apr 19 09:13:49 JST 2014
> Newbie question here.
>
> I want to do connection pooling and throttle connections to a single
> database instance. All applications will be connecting with the same
> username, to the same database.
>
> My question is about how many connections will be queued. Is the maximum
> always a flat number -- 2 * num_init_children? So, for example, if
> num_init_children is 100, I can expect no more than 100 100 queued
> connections?
Short answer: yes, you can expect at least 200 queued connections.
Long anwer: below.
The queue is inside the kernel. To know how many queued requests
there are I use standard netstat command.
First of all UNIX domain socket.
If you connect to pgpool using psql without -h option, you connect
by UNIX domain socket.
$ sudo netstat -aop -A unix|grep 11002
unix 2 [ ACC ] STREAM LISTENING 3239334 24548/pgpool /tmp/.s.PGSQL.11002
unix 3 [ ] STREAM CONNECTING 0 - /tmp/.s.PGSQL.11002
unix 3 [ ] STREAM CONNECTED 3238746 24549/pgpool: t-ish /tmp/.s.PGSQL.11002
11002 is the port number specified by "port" directive in
pgpool.conf. I set num_init_children = 1 for testing purpose. I
started psql and it successfully connects to pgpool. So I can see
"CONNECTED" line:
unix 3 [ ] STREAM CONNECTED 3238746 24549/pgpool: t-ish /tmp/.s.PGSQL.11002
I started another psql and it does to respond because waiting in the
queue. So I can see "CONNECTING":
unix 3 [ ] STREAM CONNECTING 0 - /tmp/.s.PGSQL.11002
If I start one more psql, I will see two similar lines above. I tested
up to 100 psql and all of them were sucessfully queued. This indicates
that for UNIX domain socket connection, the listen queue length is not
limited by num_init_children * 2. Yeah, this is different from what
described in our official documents. The code path actually sets very
large number for the queue for UNIX domain socket case. Next version
of pgpool will fix this.
Next, TCP/IP connections.
$ sudo netstat -aop -A inet|grep 11002
tcp 0 0 localhost.localdo:11002 *:* LISTEN 29939/pgpool off (0.00/0/0)
tcp 0 0 localhost.localdo:37110 localhost.localdo:11002 ESTABLISHED 29989/pgbench keepalive (6284.47/0/0)
tcp 8 0 localhost.localdo:11002 localhost.localdo:37165 ESTABLISHED - off (0.00/0/0)
tcp 0 0 localhost.localdo:37165 localhost.localdo:11002 ESTABLISHED 30253/pgbench keepalive (6415.48/0/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:37103 ESTABLISHED 29940/pgpool: t-ish keepalive (6284.42/0/0)
tcp 8 0 localhost.localdo:11002 localhost.localdo:37153 ESTABLISHED - off (0.00/0/0)
tcp 0 0 localhost.localdo:37153 localhost.localdo:11002 ESTABLISHED 30211/pgbench keepalive (6382.71/0/0)
tcp 8 0 localhost.localdo:11002 localhost.localdo:37110 ESTABLISHED - off (0.00/0/0)
tcp 0 0 localhost.localdo:37103 localhost.localdo:11002 ESTABLISHED 29959/psql keepalive (6284.39/0/0)
In this test, I first fire up "psql -h localhost -p 11002" and it
successfully connects to pgpool. The bottom line indicates it. psql
uses local port 37103, and destination port is 11002, which is pgpool
listening to.
For the second and proceeding connection tests, I use pgbench for
easier testing. The pgbench sessions are all blocked since psql
already occupied the all available connections. I can see 3 pgbench
queued connections, which uses local ports 37110, 37165 and 37153. So
the queue length is 3, which is num_init_children *2 = 2, plus 1 which
my kernel gives extra bonus. If I try more pgbench sessions, I see
following lines.
tcp 0 0 localhost.localdo:11002 *:* LISTEN 29939/pgpool keepalive (0.00/0/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:38972 SYN_RECV - on (4.06/3/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:38978 SYN_RECV - on (5.66/3/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:38980 SYN_RECV - on (18446744073.37/2/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:38976 SYN_RECV - on (5.26/3/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:38971 SYN_RECV - on (3.26/3/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:38979 SYN_RECV - on (18446744073.57/2/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:38977 SYN_RECV - on (4.46/3/0)
tcp 0 0 localhost.localdo:37110 localhost.localdo:11002 ESTABLISHED 29989/pgbench keepalive (5708.52/0/0)
tcp 0 8 localhost.localdo:38976 localhost.localdo:11002 ESTABLISHED 1991/pgbench on (2.81/5/0)
tcp 0 8 localhost.localdo:38971 localhost.localdo:11002 ESTABLISHED 1984/pgbench on (0.84/5/0)
tcp 8 0 localhost.localdo:11002 localhost.localdo:37165 ESTABLISHED - off (0.00/0/0)
tcp 0 0 localhost.localdo:37165 localhost.localdo:11002 ESTABLISHED 30253/pgbench keepalive (5839.53/0/0)
tcp 0 8 localhost.localdo:38980 localhost.localdo:11002 ESTABLISHED 2006/pgbench on (5.49/5/0)
tcp 0 0 localhost.localdo:11002 localhost.localdo:37103 ESTABLISHED 29940/pgpool: t-ish keepalive (5708.47/0/0)
tcp 0 8 localhost.localdo:38978 localhost.localdo:11002 ESTABLISHED 2000/pgbench on (3.16/5/0)
tcp 8 0 localhost.localdo:11002 localhost.localdo:37153 ESTABLISHED - off (0.00/0/0)
tcp 0 0 localhost.localdo:37153 localhost.localdo:11002 ESTABLISHED 30211/pgbench keepalive (5806.73/0/0)
tcp 0 8 localhost.localdo:38977 localhost.localdo:11002 ESTABLISHED 1997/pgbench on (1.95/5/0)
tcp 0 8 localhost.localdo:38972 localhost.localdo:11002 ESTABLISHED 1987/pgbench on (1.53/5/0)
tcp 8 0 localhost.localdo:11002 localhost.localdo:37110 ESTABLISHED - off (0.00/0/0)
tcp 0 0 localhost.localdo:37103 localhost.localdo:11002 ESTABLISHED 29959/psql keepalive (5708.44/0/0)
tcp 0 8 localhost.localdo:38979 localhost.localdo:11002 ESTABLISHED 2003/pgbench on (5.25/5/0)
As you can see some are in "SYN RECV" state and some are "ESTABLISHED"
state. However they are eventually gone with pgbench side errors:
Connection to database "test" failed:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
So I would say more sessions can be temporarily queued but eventually
would be rejected.
I also did the test with num_init_children = 10. 10 pgbench sessions
are running and 20 pgbench sessions are in the queue. So this time our
kernel did not give pgpool an extra bonus. However I would say, "the
extra bonus" is not documented anywhere in the kernel docs and the
behavior could be differ among different kernel versions and
platforms.
> Related, is there a way to see the number of queued connections using pcp?
Same technique can be used as described above. Just you change the
port numer to the one assigned to pcp.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
More information about the pgpool-general
mailing list