From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Bill Moran <wmoran(at)potentialtech(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Subject: | Re: Autovacuum stuck for hours, blocking queries |
Date: | 2017-02-23 15:56:27 |
Message-ID: | CAMkU=1yV97--3k62tKhMMa4w0BK4NoyhgC9TNMMzSPr1fL=w1A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Feb 23, 2017 at 2:42 AM, Bill Moran <wmoran(at)potentialtech(dot)com>
wrote:
> On Wed, 22 Feb 2017 13:19:11 -0800
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>
> > On Mon, Feb 20, 2017 at 5:40 PM, Merlin Moncure <mmoncure(at)gmail(dot)com>
> wrote:
> > >
> > > On Thursday, February 16, 2017, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >
> > >> Tim Bellis <Tim(dot)Bellis(at)metaswitch(dot)com> writes:
> > >> > Even though this is a read only query, is it also expected to be
> > >> blocked behind the vacuum? Is there a way of getting indexes for a
> table
> > >> which won't be blocked behind a vacuum?
> > >>
> > >> It's not the vacuum that's blocking your read-only queries. It's the
> > >> ALTER TABLE, which needs an exclusive lock in order to alter the
> table's
> > >> schema. The ALTER is queued waiting for the vacuum to finish, and
> lesser
> > >> lock requests queue up behind it. We could let the non-exclusive lock
> > >> requests go ahead of the ALTER, but that would create a severe risk
> of the
> > >> ALTER *never* getting to run.
> > >>
> > >> I'd kill the ALTER and figure on trying again after the vacuum is
> done.
> > >>
> > >>
> > > I've been drilled by this and similar lock stacking issues enough
> times to
> > > make me near 100% sure deferring the ALTER would be the better choice
> > >
> > >
> > This seems like a rather one-sided observation. How could you know how
> > often the unimplemented behavior also would have "drilled" you, since it
> is
> > unimplemented?
> >
> > There was a proposal for a "LOCK TABLE...DEFERABLE" which would allow
> other
> > requestors jump the queue if they were compatible with the held lock. If
> > that is implemented, then you would just manually lock the table
> deferably
> > before invoking the ALTER TABLE command, if that is the behavior you
> wanted
> > (but it wouldn't work for things that can't be run in transactions)
>
> This seems redundant to me.
>
> We already have LOCK ... NOWAIT. It's fairly trivial to write an upgrade
> script that uses LOCK to explicitly lock tables that it's going to ALTER,
> then busy-waits if the lock is not immediately grantable.
>
As fairly trivial as it is, I bet I would mess it up a few times before I
got it right. And then it would probably still be wrong in corner cases.
What if it fails not because the lock is unavailable, but for some obscure
error other than the ones anticipated or encountered during testing? And
busy-waiting is generally nasty and a waste of resources.
>
> The fact that so many ORMs and similar tools don't take advantage of that
> functionality is rather depressing.
>
> In my experience, I've also seen heavily loaded systems that this wouldn't
> work on, essentially because there is _always_ _some_ lock on every table.
> This is a case where experienced developers are required to take some
> extra time to coordinate their upgrades to work around the high load. But
> the proposed LOCK TABLE ... DEFERRABLE wouldn't help there either, because
> the ALTER would be deferred indefinitely.
>
True. That is why it would be available only upon request, not the new
default.
> Personally, I feel like the existing behavior is preferrable. Software
> teams need to take the time to understand the locking implications of their
> actions or they'll have nothing but trouble anyway.
>
> As I've seen time and again: writing an application that handles low load
> and low concurrency is fairly trivial, but scaling that app up to high
> load and/or high concurrency generally sorts out the truely brilliant
> developers from the merely average.
>
So why not give the merely average some better tools?
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | hari.prasath | 2017-02-23 15:57:53 | Re: ShmemAlloc maximum size |
Previous Message | Ertan Küçükoğlu | 2017-02-23 15:26:31 | Re: Latest PostgreSQL on Raspbian Jessie - solved |