Re: Mysterious performance degradation in exceptional cases

On 9/14/22 22:33, Matthias Apitz wrote:
> El día miércoles, septiembre 14, 2022 a las 07:19:31a. m. -0700, Adrian Klaver escribió:
> 
>> On 9/14/22 01:31, Matthias Apitz wrote:

>> Where is the inter library software, in your application or are you reaching
>> out to another application?
> 
> The above 'app-server' fulfills the search requested by the
> 'ILL-software' (or the 'test search'), i.e. looks up for one single
> librarian record (one row in the PostgreSQL database) and delivers
> it to the 'ILL-software'. The request from the 'ILL-software' is not
> a heavy duty, more or less 50 requests per day.
> 
>> Is the search running across a remote network?
> 
> The real search comes over the network through a stunnel. But we
> watched with tcpdump the incoming search and the response by the
> 'app-server' locally. In the case of the timeout, the 'app-server' does not
> answer within 180 seconds, i.e. does not send anything into the stunnel,
> and the remote 'ILL-software' terminates the connection with an F-packet.

The 'app-server' does not answer, but does the database not answer also?

Have you looked to see if the database is providing a response in a 
timely manner and if it is getting 'lost' in the 'app-server'?

Also have you considered Tom Lane's suggestion of using auto_explain?

> 
> I will now:
> 
> - shutdown the test search every 10 secs to see if the problem re-appears
> - set 'log_autovacuum_min_duration = 0' in postgresql.conf to see if
>    the times of the problem matches;
> 
> Thanks for your feedback in any case.
> 
>     matthias
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com