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

help / color / mirror / Atom feed
From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Luís Roberto Weck <luisroberto@siscobra.com.br>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow query on a one-tuple table
Date: Fri, 20 Sep 2019 09:09:35 -0400
Message-ID: <2d20142b-16b4-32e9-cf63-81d813d4718c@sqlexec.com> (raw)
In-Reply-To: <18657.1568933831@sss.pgh.pa.us>
References: <CA+t6e1kacbVHq69aihEVeBzH_nMY5eK7bZ-UXL_FuUquEgF_gg@mail.gmail.com>
	<24571.1568910502@sss.pgh.pa.us>
	<774d6080-d6d7-e9e7-7351-9a412c7d5956@siscobra.com.br>
	<CAHOFxGqMpUxn54jMLoU4-HcUata2yfLgjEbRHdJG3k0BBDPbog@mail.gmail.com>
	<ab6ea98e-6266-7665-40b7-151c5736c08d@siscobra.com.br>
	<BN6PR1701MB1890E31E15D486A85FEC8510DA890@BN6PR1701MB1890.namprd17.prod.outlook.com>
	<bf03aedc-9201-f7c9-ef25-e795d4e1626a@siscobra.com.br>
	<BN6PR1701MB1890A207FBF02BBC5E3C9F01DA890@BN6PR1701MB1890.namprd17.prod.outlook.com>
	<18ae8108-acaf-e9cc-96a9-ab20f4147f5a@siscobra.com.br>
	<35df2bd8-4c7c-7ace-6bdc-d6d97a101a14@siscobra.com.br>
	<1980a708-ed4a-6fc6-2792-33c07b07d704@siscobra.com.br>
	<CAHOFxGosug4xfpieTqFdcFqwF-mK3hn8Tr9boN=jLFDU3y-JNQ@mail.gmail.com>
	<f1468794-1681-3f1e-2f9c-b491cb87ed5d@siscobra.com.br>
	<18657.1568933831@sss.pgh.pa.us>

Hi all,

I sometimes set autovacuum_vacuum_scale factor = 0 but only when I also 
set autovacuum_vacuum_threshold to some non-zero number to force vacuums 
after a certain number of rows are updated.  It takes the math out of it 
by setting the threshold explicitly.

But in this case he has also set autovacuum_vacuum_threshold to only 
25!  So I think you have to fix your settings by increasing one or both 
accordingly.

Regards,
Michael Vitale


Tom Lane wrote on 9/19/2019 6:57 PM:
> =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br> writes:
>> As fas as autovacuum options, this is what I'm using:
>> autovacuum_vacuum_scale_factor=0,
> Ugh ... maybe I'm misremembering, but I *think* that has the effect
> of disabling autovac completely.  You don't want zero.
>
> Check in pg_stat_all_tables.last_autovacuum to see if anything
> is happening.  If the dates seem reasonably current, then I'm wrong.
>
> 			regards, tom lane
>
>






view thread (13+ messages)

Message-ID: <2d20142b-16b4-32e9-cf63-81d813d4718c@sqlexec.com>
Permalink:  ../2d20142b-16b4-32e9-cf63-81d813d4718c@sqlexec.com/
Also on:    postgresql.org/message-id/2d20142b-16b4-32e9-cf63-81d813d4718c@sqlexec.com

 · 

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-performance@postgresql.org
  Cc: MichaelDBA@sqlexec.com, tgl@sss.pgh.pa.us, luisroberto@siscobra.com.br, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
  Subject: Re: Slow query on a one-tuple table
  In-Reply-To: <2d20142b-16b4-32e9-cf63-81d813d4718c@sqlexec.com>

* 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