[pgpool-general: 9250] Re: pgpool connection timeout

[Tatsuo Ishii]

> I see...
> 
> May I know what I should look out for when I run the command
> pcp_proc_info -a?

When you find that no client connections are accepted by pgpool. 

I use the output to count how many connections in
> used and verify if the system pool has run out?

Yes. If no connection is accepted by pgpool, it should be something
other than "Wait for connection". Also I want to know what the pgpool
process are doing. It is difficult ro tell you what to do without
knowing pgpool.conf.  Any assuming your pgpool.conf does not have any
none default settings, you need to find the backend process id and
look into the PostgreSQL log. If you have setting to log the SQL
command, you can know what the last command which was sent from
pgpool.

> On 10/30/24 7:31 AM, Tatsuo Ishii wrote:
>> I didn't say pgpool is irrelevant to the problem. I said #3 is
>> irrelevant.  So maybe #2 could be relevant. If so I need to know what
>> happend in #2 and need following information.
>>
>>>> When the incident happens, can you share the output of pcp_proc_info
>>>> -a?
>>>> Is there any non-default setting in pgpool.conf?
>>>  From what we found, the problem is very likely pgpool.
>>>
>>> When we try to run psql -p <pgpool port>, it just hanged without
>>> response. We were able to run psql  -p <postgres port>.
>>>
>>> Everything resume normal after we restarted pgpool, while no other
>>> task was restarted.
>>>
>>> On 10/29/24 6:23 PM, Tatsuo Ishii wrote:
>>>>> Hello,
>>>>>
>>>>> Recently our pgpool setup encountered an unexpected performance issue.
>>>>>
>>>>> What has happened is as follow:
>>>>>
>>>>> 1) There is an application that will make a DB connection via pgpool
>>>>> to insert new record into a table.
>>>>> 2) Once the record inserted, the task will close the connection and
>>>>> wait for the next request.
>>>>> 3) There was a sudden surge in the total records need to be inserted
>>>>> into the system.
>>>>> 4) The task halted its processing as it is unable to make new DB
>>>>> connection after it closed the previous connection.
>>>>> 5) All other task that also making DB connection also encountered
>>>>> connection timeout or DB non responding error.
>>>>> 6) psql console was not able to establish as well.
>>>> It seems #3 is totally unrelated to pgpool because the application
>>>> does not connect at that point. I am puzzled too.
>>>>
>>>>> The issue was resolved after we restart pgpool. There was no restart
>>>>> needed of postgresql.
>>>>>
>>>>> There was no error found in pgpool's log, it just has no new
>>>>> connection came in.  We came to conclude, it seems the available pool
>>>>> has been used up.
>>>> When the incident happens, can you share the output of pcp_proc_info
>>>> -a?
>>>>
>>>>> Our end was very puzzled by this incident, since our task does close
>>>>> the connection before making the next DB call, so how could the pool
>>>>> be used up when our task will always release/disconnect before making
>>>>> new connection? Is there any configuration we need to set in pgpool to
>>>>> better optimise it?
>>>>>
>>>>> The version we are using:
>>>>>
>>>>> - pgpool-II version 4.5.1 (hotooriboshi)
>>>>> - psql (PostgreSQL) 15.6
>>>> Is there any non-default setting in pgpool.conf?
>>>>
>>>> Best reagards,
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS K.K.
>>>> English: http://www.sraoss.co.jp/index_en/
>>>> Japanese:http://www.sraoss.co.jp
>>>> _______________________________________________
>>>> pgpool-general mailing list
>>>> pgpool-general at pgpool.net
>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>> -- 
>>>
>>> SendQuick Pte Ltd
>>> 76 Playfair Road, #08-01,
>>> LHK 2 Building, Singapore 367996
>>> Tel: +65 6280 2881
>>> Fax: +65 6280 6882
>>>
>>> CONFIDENTIALITY NOTE:
>>> This email and any files transmitted with it is intended only for the
>>> use
>>> of the person(s) to whom it is addressed,and may contain information
>>> that
>>> is privileged, confidential and exempt from disclosure under
>>> applicable
>>> law. If you are not the intended recipient, please immediately notify
>>> the
>>> sender and delete the email. If you are not the intended recipient
>>> please
>>> do not disclose, copy, distribute or take any action in reliance on
>>> the
>>> contents of this e-mail. Thank you.
>>>
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
> 
> -- 
> 
> SendQuick Pte Ltd
> 76 Playfair Road, #08-01,
> LHK 2 Building, Singapore 367996
> Tel: +65 6280 2881
> Fax: +65 6280 6882
> 
> CONFIDENTIALITY NOTE:
> This email and any files transmitted with it is intended only for the
> use
> of the person(s) to whom it is addressed,and may contain information
> that
> is privileged, confidential and exempt from disclosure under
> applicable
> law. If you are not the intended recipient, please immediately notify
> the
> sender and delete the email. If you are not the intended recipient
> please
> do not disclose, copy, distribute or take any action in reliance on
> the
> contents of this e-mail. Thank you.
>