temp.querys_ejecutandose_csv is temporally table where we have put the result of the next query:
select
a.datname,
l.relation::regclass,
l.transactionid,
l.mode,
l.GRANTED,
a.usename,
a.query,
a.query_start,
age(now(), a.query_start) as "age",
a.pid
from
pg_stat_activity a
join pg_locks l on
l.pid = a.pid
order by
a.query_start;
This query gets the level of block by pid according to pg_stat_activity and pg_locks.
Attach the original file temp.querys_ejecutandose_csv.
The query that we are running it is a simple UPDATE by primary key:
UPDATE factura SET rubro = 13,aux_tipo_fac = 0 WHERE id_factura = 11580435
This UPDATE gets an ACCESS EXCLUSIVE block.
Bye.
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@aklaver.com
