From: | Emiliano Saenz <saenz(dot)emi(dot)jos(at)gmail(dot)com> |
---|---|
To: | depesz(at)depesz(dot)com |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: The Curious Case of the Table-Locking UPDATE Query |
Date: | 2021-07-08 17:35:33 |
Message-ID: | CAMdU7qvsopfvonDThnOXzeU2szqR3JVJnZ2+6Ux=_inoZXpS8Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Attach the files.
Best regards,
On Tue, Jul 6, 2021 at 6:24 AM hubert depesz lubaczewski <depesz(at)depesz(dot)com>
wrote:
> On Mon, Jul 05, 2021 at 08:22:39PM -0300, Emiliano Saenz wrote:
> > 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
>
>
> To be able to help we will need pg_stat_activity data for the for
> backend that has this lock, and pg_locks information for it too.
>
> And, please, send text, and not screenshot.
>
> Best regards,
>
> depesz
>
>
Attachment | Content-Type | Size |
---|---|---|
pg_locks.csv | text/csv | 120.0 KB |
pg_stat_activity.csv | text/csv | 136.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2021-07-08 17:42:24 | Re: The Curious Case of the Table-Locking UPDATE Query |
Previous Message | Emiliano Saenz | 2021-07-08 17:28:52 | Re: The Curious Case of the Table-Locking UPDATE Query |