Re: Mysterious performance degradation in exceptional cases

On 9/18/22 02:30, Matthias Apitz wrote:
> El día jueves, septiembre 15, 2022 a las 08:40:24a. m. -0700, Adrian Klaver escribió:
>
>> 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:
>>

>> 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'?
>
> I do not "think" that the time is spent elsewhere in the 'app-server'.
> to make a fact of the "thinking", I enabled the tracing of our dblayer
> showing how many milliseconds have been spent between entering the dblayer and
> returning the result to the application layers. This is in effect since
> 36 hours now. Since September 13 the problem has not showed up anymore.
> We are waiting for it...

Is this with or without your every 10 sec 'ping' search program?

>
>> Also have you considered Tom Lane's suggestion of using auto_explain?
>
> I'm afraid that this would affect all other applications using the same
> server. We still have other options to use. When the above tracing shows
> a result, i.e. which of the high level operations of the dblayer uses
> more time (more milliseconds) than normal, the next level is the detailed logging
> of the ESQL/C operations (where we added time stamps which normaly this
> logging does not have in its source code).

You can load auto_explain per session as shown here:

https://www.postgresql.org/docs/current/auto-explain.html

F.4.2. Example

>
> Thanks
>
> matthias
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com