Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1id0ry-0004hZ-42 for pgsql-performance@arkaria.postgresql.org; Thu, 05 Dec 2019 23:49:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1id0rv-0005oi-NR for pgsql-performance@arkaria.postgresql.org; Thu, 05 Dec 2019 23:49:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1id0rv-0005oa-6C for pgsql-performance@lists.postgresql.org; Thu, 05 Dec 2019 23:49:11 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1id0rs-0000B5-MO for pgsql-performance@postgresql.org; Thu, 05 Dec 2019 23:49:09 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xB5Nn6u2026222; Thu, 5 Dec 2019 18:49:07 -0500 From: Tom Lane To: Mike Schanne cc: "'pgsql-performance@postgresql.org'" Subject: Re: autovacuum locking question In-reply-to: <0871fcf35ceb4caa8a2204ca9c38e330@USEPRDEX1.corp.kns.com> References: <0871fcf35ceb4caa8a2204ca9c38e330@USEPRDEX1.corp.kns.com> Comments: In-reply-to Mike Schanne message dated "Thu, 05 Dec 2019 17:46:19 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <26220.1575589746.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 05 Dec 2019 18:49:06 -0500 Message-ID: <26221.1575589746@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Mike Schanne writes: > I am investigating a performance problem in our application and am seein= g something unexpected in the postgres logs regarding the autovacuum. > 2019-12-01 13:05:39.029 UTC,"wb","postgres",6966,"127.0.0.1:53976",5ddbd= 990.1b36,17099,"INSERT waiting",2019-11-25 13:39:28 UTC,12/1884256,1261502= 3,LOG,00000,"process 6966 still waiting for RowExclusiveLock on relation 3= 2938 of database 32768 after 1000.085 ms","Process holding the lock: 6045.= Wait queue: 6966.",,,,,"INSERT INTO myschema.mytable (...) VALUES (...) R= ETURNING process.mytable.mytable_id",13,,"" > 2019-12-01 13:05:39.458 UTC,,,6045,,5de3b800.179d,1,,2019-12-01 12:54:24= UTC,10/417900,0,ERROR,57014,"canceling autovacuum task",,,,,"automatic va= cuum of table ""postgres.myschema.mytable""",,,,"" > My understanding from reading the documentation was that a vacuum can ru= n concurrently with table inserts/updates, but from reading the logs it ap= pears they are conflicting over a row lock. This particular table gets ve= ry frequent inserts/updates (10-100 inserts / sec) so I am concerned that = if the autovacuum is constantly canceled, then the table never gets cleane= d and its performance will continue to degrade over time. Is it expected = for the vacuum to be canceled by an insert in this way? The main part of an autovacuum operation should go through OK. The only part that would get canceled in response to somebody taking a non-exclusive lock is the last step, which is truncation of unused blocks at the end of the table; that requires an exclusive lock. Normally, skipping that step isn't terribly problematic. > We are using postgres 9.6.10. IIRC, we've made improvements in this area since 9.6, to allow a partial truncation to be done if someone wants the lock, rather than just failing entirely. regards, tom lane