Re: Mysterious performance degradation in exceptional cases - Mailing list pgsql-general

From Matthias Apitz
Subject Re: Mysterious performance degradation in exceptional cases
Date
Msg-id YydJFwJ9+DuNI0Un@c720-r368166
Whole thread Raw
In response to Re: Mysterious performance degradation in exceptional cases  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Mysterious performance degradation in exceptional cases
List pgsql-general
El día domingo, septiembre 18, 2022 a las 07:47:32a. m. -0700, Adrian Klaver escribió:

> 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?

The 'ping' search was stopped on September 16, 7:45 CEST. The 'ping' search
never showed the problem.

> > > 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:

Every connect from the ILL forks a new 'app-server' session which
creates a new ESQL/C session, and all this occur randomly when the ILL
wants to search for a book title if this is available in that library.
In short: no way.

Thanks

    matthias

-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Sahra Wagenknecht im Bundestag: "Aber die Vorstellung, dass wir Putin dadurch
bestrafen, dass wir Millionen Familien in Deutschland in die Armut stürzen und
dass wir unsere Industrie zerstören, während Gasprom Rekordgewinne macht – ja,
wie bescheuert ist das denn?" Recht hat sie!



pgsql-general by date:

Previous
From: Евгений Плискин
Date:
Subject: Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause
Next
From: Adrian Klaver
Date:
Subject: Re: Mysterious performance degradation in exceptional cases