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 1hLUJU-0002PM-In for pgsql-admin@arkaria.postgresql.org; Tue, 30 Apr 2019 15:04:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLUJS-0002Ig-5o for pgsql-admin@arkaria.postgresql.org; Tue, 30 Apr 2019 15:04:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLUJR-0002EL-RG for pgsql-admin@lists.postgresql.org; Tue, 30 Apr 2019 15:04:53 +0000 Received: from mout.perfora.net ([74.208.4.196]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLUJP-0000jR-AG for pgsql-admin@postgresql.org; Tue, 30 Apr 2019 15:04:53 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MYuNZ-1hHpZg10d9-00VjKv; Tue, 30 Apr 2019 17:04:43 +0200 Subject: Re: admin control over cancelling autovacuum when blocked by a lock To: Rui DeSousa Cc: John Lumby , Simon Riggs , Tom Lane , pgsql-admin References: <19835.1556588662@sss.pgh.pa.us> From: MichaelDBA Message-ID: <3ed07258-724a-b8e7-9af3-6a07d39d7aa4@sqlexec.com> Date: Tue, 30 Apr 2019 11:04:40 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/6.1.15 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A14E7503EA4ECC96E4CE79F8" Content-Language: en-US X-Provags-ID: V03:K1:eWDBF1UKrELBFE/L4aJMDoWuuRuwZ196MpIBRf4ynOIvdgABzwL Jt4+mCl9pJ7FXPNYlZdk9Qm7hmICuwW06q0559CSc/OrgFcoQ/erfQkjlvXZhaPB5DNZyXI znykULTx8H/PWRlsnIh4A4M5W/6ce771CUUnpZz8g9TS8WYcdESWEGbZaTMYq6znBwH3ppO KpW5Z+YQ/igeRliAomQrw== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:5/6A0un3Hqw=:pvL71dKNgZHUrB5jhw8cun Ct7HlabLIUUXFk/C6DatBJOrMWe2KqbsLzjt4OlaaKzLbM5sCrI3p8YiF+MAeSJQa22rlcvst JLAwC6qF8Iz9xX0WRV7/wq6XYMeulx48RyLtbZHYkYzHxAUH+vfghKZvhszhz28T2horXGh7q x/LJJ49nipBVp0vRzGT7yruuorspdTDKeIP8fIdNo5okUusMtTF+isjPvSr6pfqQpZC8mzMCh kAz7c+9gVK3Q6FP+fTJ6Mq08CRsfZFuJgNiRs0eeZJM4Jb/CPRc1AdvCSHb4j7wVmKfcIp82O UGsZKvfjuE0YzLs7W21o+R7DpF9QkuvstYB/yv2BYynmZh7nZtQim9F8tPfAh/28YIK4wQHlG Uj2ow/q6Scc2f0x0AfV/8Mb+avw/DinrXNHxqwnr8YhsA0T0flwxdY7i3uWz6KZcmHVDGOpn/ NX48W0sXNRYalel4AUB8H6nhyPj7loMsLbl12imVsqm3vD0DgGpUuxoRwdAwFXnYnB4YHbhQj xK6MkH0iEcz/SqzVoxDICBk8pNeZHcvVM/EEZZrZ3fNA7+i4vI1YjFVeTT2uLdW625Q4cm4Ro 0ythTr2H9YN7H7CV8v/GUDjfv2ksVHgoCgXuTAJX36+MhCEhVYViFayyyc8q+76F1+AdlXhgr fUordaC+JfG+r7lS79v9szN6eSmiaoJGqnc1DYI5ECjqaEj/uePTECGeiPH+S4wGPAAej0N7a zvY0BwKrZEJwxmZ/UNiJNeCLNRwdyUnLDVfp34Q/6muj2/UA9wWvenBWfTQ= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A14E7503EA4ECC96E4CE79F8 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 8bit As I remember, it used to be "in the old days" that autovacuum processes acted like primary ones blocking user requests.  But now, I think PG got it right making them act like secondary type processes that must be "outed" when conflicting with user requests. What I find is the best vacuuming solution is a combination of 3 things: 1. separate autovacuum tuning for high and low load times (triggered via crontab) 2. ALTER table commands to specify autovacuum parms at the table level based on sql loads on particular tables. 3. Scheduled manual vacuums to help take the stress off of autovacuum daemons. Regards, Michael Vitale Rui DeSousa wrote on 4/30/2019 10:12 AM: > > >> On Apr 30, 2019, at 9:27 AM, John Lumby > > wrote: >> >> it does do ANALYZE  -  in this context is that "DDL"? > > I would consider that DDL and the application should avoid doing it; > otherwise, you end up with blocking issues where both readers and > writers are blocked unnecessarily.  The application issuing analyze on > its own temp tables is fine and non blocking.  If you must analyze > tables then I would limit which processes have access to those tables > to avoid blocking issues. > > I would look at your auto vacuum/analyze settings as oppose to the > application issuing analyze commands as those are going to be a non > blocking option. > --------------A14E7503EA4ECC96E4CE79F8 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit As I remember, it used to be "in the old days" that autovacuum processes acted like primary ones blocking user requests.  But now, I think PG got it right making them act like secondary type processes that must be "outed" when conflicting with user requests.

What I find is the best vacuuming solution is a combination of 3 things:
1. separate autovacuum tuning for high and low load times (triggered via crontab)
2. ALTER table commands to specify autovacuum parms at the table level based on sql loads on particular tables.
3. Scheduled manual vacuums to help take the stress off of autovacuum daemons.

Regards,
Michael Vitale



Rui DeSousa wrote on 4/30/2019 10:12 AM:


On Apr 30, 2019, at 9:27 AM, John Lumby <johnlumby@hotmail.com> wrote:

 it does do ANALYZE  -  in this context is that "DDL"?

I would consider that DDL and the application should avoid doing it; otherwise, you end up with blocking issues where both readers and writers are blocked unnecessarily.  The application issuing analyze on its own temp tables is fine and non blocking.  If you must analyze tables then I would limit which processes have access to those tables to avoid blocking issues.

I would look at your auto vacuum/analyze settings as oppose to the application issuing analyze commands as those are going to be a non blocking option.


--------------A14E7503EA4ECC96E4CE79F8--