[pgpool-general: 7942] Re: Pgpool does not accept new connections (Tatsuo Ishii)
Tatsuo Ishii
ishii at sraoss.co.jp
Fri Dec 17 17:06:25 JST 2021
So what's the result of pcp_proc_info?
> 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:
> 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
> ***********************************************
>
>
More information about the pgpool-general
mailing list