| From: | Emiliano Saenz <saenz(dot)emi(dot)jos(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | The Curious Case of the Table-Locking UPDATE Query |
| Date: | 2021-07-05 23:22:39 |
| Message-ID: | CAMdU7qsXphcRDKc33QuvVwmymH1HaahJTatgA32+2So_h7m4OA@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| Attachment | Content-Type | Size |
|---|---|---|
| Screenshot from 2021-07-05 12-14-22.png | image/png | 73.5 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Adrian Klaver | 2021-07-06 00:34:36 | Re: The Curious Case of the Table-Locking UPDATE Query |
| Previous Message | Vijaykumar Jain | 2021-07-05 12:02:19 | Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes |
