Slow UPDATE in logs that's usually fast

From: Aldo Sarmiento <aldo(at)bigpurpledot(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow UPDATE in logs that's usually fast
Date: 2016-11-26 05:13:06
Message-ID: CAHX=r6xBYhjinfEE_TvXfNMT6y3okbwdsQo3-oiYqoTpQmgckw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

There's a slow UPDATE query in my logs (~60s). However, when I run it
manually, it's really fast ( < 0.5ms).

2016-11-23 18:13:51.962 GMT [742-25]: bpd_production bpd(at)web001(40916)
00000 Passenger RubyApp: /var/www/bpd/current (production) LOG: duration:
59876.947 ms statement: UPDATE "contacts" SET "updated_at" = '2016-11-23
18:12:52.055456' WHERE "contacts"."id" = 2845179

This particular query isn't waiting on any locks that I can see. I actually
found it because I followed a lock queue up the chain and it bubbled up to
this query. This was one that was waiting on process 742:

2016-11-23 18:13:00.095 GMT [314-118]: bpd_production bpd(at)web001(40547)
00000 Passenger RubyApp: /var/www/bpd/current (production) LOG: process
314 still waiting for ShareLock on transaction 1663649998 after 1000.067 ms
2016-11-23 18:13:00.095 GMT [314-119]: bpd_production bpd(at)web001(40547)
00000 Passenger RubyApp: /var/www/bpd/current (production) DETAIL: Process
holding the lock: 742. Wait queue: 314.
2016-11-23 18:13:00.095 GMT [314-120]: bpd_production bpd(at)web001(40547)
00000 Passenger RubyApp: /var/www/bpd/current (production) CONTEXT: while
updating tuple (288387,8) in relation "contacts"
2016-11-23 18:13:00.095 GMT [314-121]: bpd_production bpd(at)web001(40547)
00000 Passenger RubyApp: /var/www/bpd/current (production) STATEMENT:
UPDATE "contacts" SET "news_items_last_modified" = '2016-11-23
18:12:59.090806' WHERE "contacts"."id" = 2845179

Anyhow, I'm at a complete loss here since I've hit a dead end.

Thank you!

*Aldo Sarmiento*

Browse pgsql-performance by date

  From Date Subject
Next Message zhangkai.gis 2016-11-28 08:59:37 can trigger monitor two tables?
Previous Message Robert Klemme 2016-11-25 17:14:42 Re: Millions of tables