pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Rui DeSousa <rui@crazybean.net>
To: John Lumby <johnlumby@hotmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: admin control over cancelling autovacuum when blocked by a lock
Date: Tue, 30 Apr 2019 16:34:06 -0400
Message-ID: <CB30B39F-CE56-417A-8CFB-C23F07DD51B8@crazybean.net> (raw)
In-Reply-To: <DM6PR06MB5562C2FDF9D0BCB3617ADB42A33A0@DM6PR06MB5562.namprd06.prod.outlook.com>
References: <DM6PR06MB556272F9CB7629B10CE86829A3390@DM6PR06MB5562.namprd06.prod.outlook.com>
	<CANP8+jJSysBHF_QRdYmT-iKQDy4yw8OaqgiLoNJPdjTX1bJB8w@mail.gmail.com>
	<DM6PR06MB556216D6085FE0B9C267E82CA33A0@DM6PR06MB5562.namprd06.prod.outlook.com>
	<19835.1556588662@sss.pgh.pa.us>
	<CANP8+j+yBChxtUPriLttV=orwdF7RWtpP+zjF_fCB2yscuJz=g@mail.gmail.com>
	<DM6PR06MB55628354552B06395479033BA33A0@DM6PR06MB5562.namprd06.prod.outlook.com>
	<ACF14079-4541-4300-978C-BEC42339B33F@crazybean.net>
	<DM6PR06MB5562C2FDF9D0BCB3617ADB42A33A0@DM6PR06MB5562.namprd06.prod.outlook.com>



> On Apr 30, 2019, at 4:04 PM, John Lumby <johnlumby@hotmail.com> wrote:
> 
> surely the user would prefer that the 
> ANALYZE would wait until after
> the autovacuum has finished

Not really… analyze takes an exclusive lock; I believe.  The result is that readers/analyze will block other readers and writes which is bad for concurrency.  Readers should never be blocked :)… 

I had the follow problem occur; which I hope will illustrate why you wouldn’t what do what you are recommending. 

I explicitly do not allow the application to issue DDL including the analyze command against the base tables; however, developers are developers and they will try anything at least once.  The application was coded with an analyze command and the result was huge blocking issues.

1. Application issue analyze
2. Analyze waits on exclusive lock
3. As, there is already a long running query running against the table
4. New readers; get blocked and are now waiting behind the analyze command.
5. Writes are also blocked and waiting behind the analyze command.
6. Long running query completes.
7. Analyze command fails due to lack of permissions.
8. Application retries; rinse and repeat.

* The real response is we had to kill the session attempting to issue analyze and issue a hot fix otherwise the blocking would cause all sorts of application issues.  

view thread (15+ messages)  latest in thread

Message-ID: <CB30B39F-CE56-417A-8CFB-C23F07DD51B8@crazybean.net>
Permalink:  ../CB30B39F-CE56-417A-8CFB-C23F07DD51B8@crazybean.net/
Also on:    postgresql.org/message-id/CB30B39F-CE56-417A-8CFB-C23F07DD51B8@crazybean.net

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: rui@crazybean.net, johnlumby@hotmail.com, simon@2ndquadrant.com, tgl@sss.pgh.pa.us
  Subject: Re: admin control over cancelling autovacuum when blocked by a lock
  In-Reply-To: <CB30B39F-CE56-417A-8CFB-C23F07DD51B8@crazybean.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox