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

From Laurenz Albe
Subject Re: Mysterious performance degradation in exceptional cases
Date
Msg-id 07ea822415c23f89fd3ba92856287089f80817a3.camel@cybertec.at
Whole thread Raw
In response to Re: Mysterious performance degradation in exceptional cases  (Matthias Apitz <guru@unixarea.de>)
List pgsql-general
On Fri, 2022-10-21 at 09:48 +0200, Matthias Apitz wrote:
> > all this should not take longer than 1-2 seconds, and
> > normally it does not take longer. But, in some situations it takes
> > longer than 180 seconds, in 10% of the cases.
> 
> DBCEXIT|N|sisisinst|RDIR|4|21.10.2022 06:08:25:594
> DBCEXIT|N|adm_partab|RDIR|2|21.10.2022 06:08:25:597
> DBCEXIT|N|adm_partab|RDIR|1|21.10.2022 06:08:25:598
> DBCEXIT|N|adm_partab|RDIR|0|21.10.2022 06:08:25:599
> DBCEXIT|N|adm_partab|RDIR|0|21.10.2022 06:08:25:600
> DBCEXIT|N|adm_partab|RDIR|0|21.10.2022 06:08:25:601
> DBCEXIT|N|adm_partab|RDIR|0|21.10.2022 06:08:25:602
> DBCEXIT|N|d12termfkt|RWHR|1|21.10.2022 06:08:25:630
> DBCEXIT|N|d12termfkt|RWHR|2|21.10.2022 06:08:25:632
> DBCEXIT|N|d601biblpar|RALL|3|21.10.2022 06:08:25:636
> DBCEXIT|N|d06vw|RDIR|1|21.10.2022 06:08:25:637
> DBCEXIT|N|titel_worte|RDIR|13|21.10.2022 06:08:25:660
> DBCEXIT|N|titel_worte|FETCH|0|21.10.2022 06:08:25:660
> DBCEXIT|N|d14zweigsicht|RWHR|1|21.10.2022 06:08:25:662
> DBCEXIT|W|titel_daten|COUNT|183875|21.10.2022 06:11:29:538
>                             ^^^^^^
> 
> 'RDIR' means a SELECT with a key, ... 'COUNT' executes a 
> 
> SELECT count(*) from titel_daten
> 
> The 5th column gives the duration in/below the DBLAYER in milliseconds, in
> the above case 183875, i.e. 183 seconds. The table in question titel_daten
> has 5830370 rows. I did it right now with psql and it takes a second
> only:
> 
> sisis=# select count(*) from titel_daten;
>   count  
> ---------
>  5827383
> (1 row)
> 
> What could be a reason for 183 seconds, sometimes not always?

The most obvious idea is a lock with a statement that takes an ACCESS EXCLUSIVE
lock, like TRUNCATE or ALTER TABLE.

But there are other possibilities, like network problems or a problem
inside your application.

To narrow that down, set "log_min_duration_statement" and possibly activate
"auto_explain", so that you can verify if the long delay is due to long
processing time inside the database.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Upgrading error
Next
From: Laurenz Albe
Date:
Subject: Re: PQconsumeinput() may close the fd