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

help / color / mirror / Atom feed
From: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
To: David Rowley <david.rowley@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Jeremy Schneider <schnjere@amazon.com>
Cc: Joe Conway <mail@joeconway.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Should we increase the default vacuum_cost_limit?
Date: Sat, 9 Mar 2019 08:28:22 -0500
Message-ID: <203719fc-1970-4a49-8013-eba3fd109b19@2ndQuadrant.com> (raw)
In-Reply-To: <CAKJS1f8s5Cxat65_-uE97oUpoonO3p_2rFMf-cKA7kpAaS1-OA@mail.gmail.com>
References: <CAKJS1f_YbXC2qTMPyCbmsPiKvZYwpuQNQMohiRXLj1r=8_rYvw@mail.gmail.com>
	<CAH2-Wz=WQ9ju6sjPF7RcW0g6NPXAenZeC5qVme6uj+eMRw5q2g@mail.gmail.com>
	<241b80ab-7af0-c606-7b2c-b0a5d6be696a@joeconway.com>
	<CAKJS1f_5xofbg0K16MkhQPeCXV6oGCuD2SVcV76OW7ydFDHDkw@mail.gmail.com>
	<df548503-d261-d8c4-97d0-61be6617df41@2ndQuadrant.com>
	<4a068b09-5a6f-e975-07c9-2333fcef0a8a@amazon.com>
	<30411a5b-ef11-71ef-e9ee-4d26ef3fd0c0@2ndQuadrant.com>
	<CAMkU=1zM7sK9Xo2jjwCLj-t+GHR5nQJnPyFUp+puBiFpOQ7aBA@mail.gmail.com>
	<21034.1552068630@sss.pgh.pa.us>
	<CAKJS1f9wbS+SzEDUXyMuLCsNgwMH=1Ztj3QE3WxuKdJtbqrOEA@mail.gmail.com>
	<13d143dd-0a44-0c16-218c-bc924b5922f0@2ndQuadrant.com>
	<26656.1552096451@sss.pgh.pa.us>
	<28720.1552101086@sss.pgh.pa.us>
	<CAKJS1f8s5Cxat65_-uE97oUpoonO3p_2rFMf-cKA7kpAaS1-OA@mail.gmail.com>


On 3/9/19 4:28 AM, David Rowley wrote:
> On Sat, 9 Mar 2019 at 16:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I propose therefore that instead of increasing vacuum_cost_limit,
>> what we ought to be doing is reducing vacuum_cost_delay by a similar
>> factor.  And, to provide some daylight for people to reduce it even
>> more, we ought to arrange for it to be specifiable in microseconds
>> not milliseconds.  There's no GUC_UNIT_US right now, but it's time.
>> (Perhaps we should also look into using other delay APIs, such as
>> nanosleep(2), where available.)
> It does seem like a genuine concern that there might be too much all
> or nothing. It's no good being on a highspeed train if it stops at
> every platform.
>
> I agree that vacuum_cost_delay might not be granular enough, however.
> If we're going to change the vacuum_cost_delay into microseconds, then
> I'm a little concerned that it'll silently break existing code that
> sets it.  Scripts that do manual off-peak vacuums are pretty common
> out in the wild.


Maybe we could leave the default units as msec but store it and allow
specifying as usec. Not sure how well the GUC mechanism would cope with
that.


[other good ideas]


>> I don't have any particular objection to kicking up the maximum
>> value of vacuum_cost_limit by 10X or so, if anyone's hot to do that.
>> But that's not where we ought to be focusing our concern.  And there
>> really is a good reason, not just nannyism, not to make that
>> setting huge --- it's just the wrong thing to do, as compared to
>> reducing vacuum_cost_delay.
> My vote is to 10x the maximum for vacuum_cost_limit and consider
> changing how it all works in PG13.  If nothing happens before this
> time next year then we can consider making vacuum_cost_delay a
> microseconds GUC.
>

+1.


cheers


andrew

-- 
Andrew Dunstan                https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





view thread (44+ messages)  latest in thread

Message-ID: <203719fc-1970-4a49-8013-eba3fd109b19@2ndQuadrant.com>
Permalink:  ../203719fc-1970-4a49-8013-eba3fd109b19@2ndQuadrant.com/
Also on:    postgresql.org/message-id/203719fc-1970-4a49-8013-eba3fd109b19@2ndQuadrant.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-hackers@postgresql.org
  Cc: andrew.dunstan@2ndquadrant.com, david.rowley@2ndquadrant.com, tgl@sss.pgh.pa.us, jeff.janes@gmail.com, schnjere@amazon.com, mail@joeconway.com, pg@bowt.ie, pgsql-hackers@lists.postgresql.org
  Subject: Re: Should we increase the default vacuum_cost_limit?
  In-Reply-To: <203719fc-1970-4a49-8013-eba3fd109b19@2ndQuadrant.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