On 7/5/21 4:22 PM, Emiliano Saenz wrote:
Hello!
We have a huge POSTGRES 9.4 database in the production environment
(several tables have more than 100.000.00 registers). Last two months we
have had problems with CPU utilization. Debugging the locks (on
pg_locks) we notice that sometimes simple UPDATE (by primary key)
operation takes out ACCESS_EXCLUSIVE_LOCK mode over these huge tables so
POSTGRES DB collapses and it generates excessive CPU consumption. My
question is, How is it possible that UPDATE operation takes out
ACCESS_EXCLUSIVE_LOCK mode?
More information, this system never manifests this behavior before and
we don't make software changes on last 2 years
FYI. 9.4 is ~1.5 years past EOL
Please don't post images. It would have just as easy to copy and paste
the output and would have saved hand building the below.
Where is temp.querys_ejecutandose.csv coming from?
Above you mention querying pg_locks.
What is the query you are using?
From here:
https://www.postgresql.org/docs/9.4/explicit-locking.html
"ACCESS EXCLUSIVE
Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW
EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE,
EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder
is the only transaction accessing the table in any way.
Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM
FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands.
Many forms of ALTER TABLE also acquire a lock at this level (see ALTER
TABLE). This is also the default lock mode for LOCK TABLE statements
that do not specify a mode explicitly.
"
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
