Home POSTGRESQL strange update time?
Reply: 1

POSTGRESQL strange update time?

testef Published in 2018-02-13 17:45:39Z

Hy all, i'm trying to find an explanation for a strange behavior happening on postgres from a vbnet application.


1) vbnet windows form application

2) postgres 9.6 on win7 with raid5 (default installation so VACUUM on for all tables)

3) npgsql 3.1.9

4) a table with 250 rows and a unique index on the field used in the where clause of the update statement

5) Un update inside a transaction

the strange behavior is that this update:

UPDATE work.wip_pallets SET 
  WPAL_DATA_INI = value ,
  STAZ_ID = value, 
  WPAL_SCARTO = value 
WHERE CEPA_CODICE =filter value;

normally runs in 0 or less than 50ms but in some occasion it takes 400ms or more! Randomly (?) The update have to run to store the start time of an operation and is supposed to fire at a frequence that could be less then 1s but it is not predictable (it could take longer).

Anyone experienced the same? It is normally? Do you have some actions that i can try to make in order to identify a problem?

If possible i can provide some others information if required.


P.S. ... sorry for my english ....

Laurenz Albe
Laurenz Albe Reply to 2018-02-14 07:52:18Z

There are two possibilities:

  1. Overloaded I/O system.

  2. Concurrent transactions locking the table or the row.

You could turn on auto_explain:

  • Add auto_explain to shared_preload_libraries in postgresql.conf.

  • Set auto_explain.log_analyze = on and auto_explain.log_buffers = on.

  • Set auto_explain.log_min_duration = 100 to log everything that takes more than 100 ms.

  • Restart PostgreSQL.

Then you'll get execution plans from the long runs in the log. These should provide some information what is going on.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.313014 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO