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