[pgpool-general: 7941] Re: Pgpool does not accept new connections (Tatsuo Ishii)
ehansmair at prompt-software.de
ehansmair at prompt-software.de
Thu Dec 16 17:48:49 JST 2021
Hello,
thank you for your answer.
There should be are enough connections available. The num_init_children is
set to 300, reserved_connections = 2.
Postgres offers 400 connections with 3 reserved connections.
The application which sends the statement opens 4 connections with 2
different combinations user / application-names. So we have with 3
running the application 3 times we have at most 12 connections in use.
We have played with the different parameters for connections, max_pool,
child_max_connections, num_init_children and see no change.
Authentication is set to allow_clear_text_frontend_auth = 1 since we have
over 1000 login roles in the database and user can change their password
at any time.
There are no specific error messages in the Pgpool logfile. When Pgpool
stalls we only see the regular watchdog messages "WATCHDOG LOG: new IPC
connection received".
Regards,
Ernst Hansmair
Von: pgpool-general-request at pgpool.net
An: pgpool-general at pgpool.net
Datum: 16.12.2021 05:54
Betreff: pgpool-general Digest, Vol 122, Issue 12
Gesendet von: "pgpool-general" <pgpool-general-bounces at pgpool.net>
Send pgpool-general mailing list submissions to
pgpool-general at pgpool.net
To subscribe or unsubscribe via the World Wide Web, visit
https://www.pgpool.net/mailman/listinfo/pgpool-general
or, via email, send a message with subject or body 'help' to
pgpool-general-request at pgpool.net
You can reach the person managing the list at
pgpool-general-owner at pgpool.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of pgpool-general digest..."
Today's Topics:
1. [pgpool-general: 7937] Pgpool does not accept new
connections (ehansmair at prompt-software.de)
2. [pgpool-general: 7938] Pgpool docker image md5
authentification issue (Nikolay Mastilo (HM))
3. [pgpool-general: 7939] Re: Pgpool does not accept new
connections (Tatsuo Ishii)
----------------------------------------------------------------------
Message: 1
Date: Wed, 15 Dec 2021 18:48:41 +0100
From: ehansmair at prompt-software.de
To: pgpool-general at pgpool.net
Subject: [pgpool-general: 7937] Pgpool does not accept new
connections
Message-ID:
<OF94B3674C.CA227CDC-ONC12587AC.005F7A13-C12587AC.0061D74E at prompt-software.de>
Content-Type: text/plain; charset="us-ascii"
Hello,
we have the problem, that Pgpool does not accept new connections when some
longer running SQLs are on their way.
We are using Pgpool 4.2.5 till now only for automated fail over and online
recovery - connection pooling, load balancing, in memory query cache are
switched off. PostgreSQL streaming replication is used.
We can provoke the effect with a SQL like
select field_a, field_b, pg_sleep(120)
from table_a
where field_a = 12345
The statement simulates long running statements we are using.
When we start the above statement 3 times or more with a delay of 1 second
Pgpool does not accept new connections for some minutes. We do not get an
error message or time out. Direct connections to Postgres are as fast as
always.
The effect exists in a 2-node cluster and in a degenerated, single machine
cluster too.
Any help is welcome.
Sincerely Yours
Ernst Hansmair
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <
http://www.pgpool.net/pipermail/pgpool-general/attachments/20211215/942bcda9/attachment-0001.htm
>
------------------------------
Message: 2
Date: Wed, 15 Dec 2021 22:00:54 +0200
From: "Nikolay Mastilo (HM)" <nikm71 at hotmail.com>
To: pgpool-general at pgpool.net
Subject: [pgpool-general: 7938] Pgpool docker image md5
authentification issue
Message-ID:
<AM9P191MB127067AD787FC005D563F126C8769 at AM9P191MB1270.EURP191.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=utf-8; format=flowed
Hello All,
Our Pgpool runs on Kubernetes and we use it most often as a load balancer.
When the PostgreSQL connection was trusted without requiring a password,
everything was fine.
Now we decided to connect to PG with password in md5 mode and got a
problem with Pgpool. We receive messages like this:
? "ERROR: Could not authenticate with backend using md5"
? "Detail: no valid password found"
Pgpool fails user authentication while we add an entry to the
pool_passwd file with md5 username and password.
This pool_passwd file contains only one user and password entry, which I
specify in the POSTGRES_USERNAME and POSTGRES_PASSWORD env variables.
Pgpool runs in docker image pgpool / pgpool: 4.2.2 in k8s.
Can anyone answer two questions, at least one:
1.Is there any parameters or env variable in this docker image to enter
more than one user entry into the pool_passwd file in the docker image
2. Is it possible to pass md5 authentication directly to PostgreSQL
without checking the password on Pgpool.
Help, please, the problem is very critical for us.
We will be very grateful for any help or in the direction of where to
move in deciding .
Thanks in advance.
--
Regards,
Nikolay
------------------------------
Message: 3
Date: Thu, 16 Dec 2021 08:53:43 +0900 (JST)
From: Tatsuo Ishii <ishii at sraoss.co.jp>
To: ehansmair at prompt-software.de
Cc: pgpool-general at pgpool.net
Subject: [pgpool-general: 7939] Re: Pgpool does not accept new
connections
Message-ID:
<20211216.085343.665071583336796189.t-ishii at sranhm.sra.co.jp>
Content-Type: Text/Plain; charset=us-ascii
> Hello,
>
> we have the problem, that Pgpool does not accept new connections when
some
> longer running SQLs are on their way.
>
> We are using Pgpool 4.2.5 till now only for automated fail over and
online
> recovery - connection pooling, load balancing, in memory query cache are
> switched off. PostgreSQL streaming replication is used.
>
> We can provoke the effect with a SQL like
>
> select field_a, field_b, pg_sleep(120)
> from table_a
> where field_a = 12345
>
> The statement simulates long running statements we are using.
>
> When we start the above statement 3 times or more with a delay of 1
second
> Pgpool does not accept new connections for some minutes. We do not get
an
> error message or time out. Direct connections to Postgres are as fast as
> always.
>
> The effect exists in a 2-node cluster and in a degenerated, single
machine
> cluster too.
It is possible that all pgpool child process are busy. You can check
by using pcp_proc_info command. Try:
pcp_proc_info -p [port number specified by pcp_port] -h localhost -v
-a|grep Wait
If you get no lines at all, then all pgpool child process are occupied
by the long running queries. In this case you can:
- increase num_init_children (requires restarting pgpool)
- decrease the chance to issue the long running query.
Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
------------------------------
Subject: Digest Footer
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general
------------------------------
End of pgpool-general Digest, Vol 122, Issue 12
***********************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211216/d42a3bfc/attachment.htm>
More information about the pgpool-general
mailing list