From: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Subject: Re: Should we increase the default vacuum_cost_limit?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <david.rowley@2ndquadrant.com>,
 Jeff Janes <jeff.janes@gmail.com>, Jeremy Schneider <schnjere@amazon.com>,
 Joe Conway <mail@joeconway.com>, Peter Geoghegan <pg@bowt.ie>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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>
 <203719fc-1970-4a49-8013-eba3fd109b19@2ndQuadrant.com>
 <24300.1552154154@sss.pgh.pa.us>
Openpgp: preference=signencrypt
Autocrypt: addr=andrew.dunstan@2ndQuadrant.com; prefer-encrypt=mutual;
 keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNL0FuZHJldyBEdW5zdGFuIDxhbmRyZXcuZHVuc3RhbkAybmRRdWFkcmFudC5jb20+wsCY
 BBMBAgBCAhsDBgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgAUJDyV6hBYhBOQ+WEYd/Hy/RGkV
 pZn6f8tZ/DuBBQJcH/EVAhkBAAoJEJn6f8tZ/DuB7BEH/iDWZEbVft2m2MT6SPjARzTupt/l
 xLLZ/Cl6n2US2hov5+fBbnR5xvECk/YCOfG9ICvKNlpFjceqNLRKStd8cmndnI+kDw91uNM2
 hpL4aJMeW65XCHvqXlOLSwv5chBpXz9NG7FMaJ+q+59sWEWjcTNmjXgkEHn53y+1pc7nSLDe
 cr5ZI1aB3nGXRm1SoDY5PYZkUKMykW+YBZwgcs/Q5tRaE+siN7DKbz8E628+PerwAkmHPVXN
 MdJFD6gs4S0gDFA8IxcyIwZU2v4By+brzcJ9Se3FK5BYxBDHtaYZxgSrJAvOUOrLIZPQTQfL
 TtnVs3C3JTfrxCH1ceHtnA6dRuTOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oK
 Waqf+wmW4tpDe/VVcxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7
 llV0xSSoFiieQBsrGvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8
 BoVrRFcdzlIY/NFNhFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C
 4R+M6w7o2PLGOjdZ+kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaY
 L++LAEOqrlvm2TvqQqZRtnEAEQEAAcLAZQQYAQIADwIbDAUCXA6fpAUJDyV6xAAKCRCZ+n/L
 Wfw7geU4B/9vFFsVmWxerDWgNjyiK80zIPmlw/iwdUlU6FIu0JWLMg4093kvxBs7RMZV9hBq
 n3ekBvD4IvFsVLJrw9qzSI2FH3UeYy5WXpEs01Ppia60NOlvRDqXbryzuvfe+iYo6bVP3Ikr
 TPTNYL/BePUTD2DPvSI82SbGTyzuA7SJv6NRqqQDMIy1fU6KyQFLmQJp/QJyhKWzm9jWh8ee
 w8jVaoMY0Kbq5N7RqisEx1Lu/Iw646UphdYzQNymf+kbiU5Kf4I6DhzV8w9sie1Bo1juNj3I
 c5/fFJsmxljbnaDbXPcnFsur1Oo9mIHFakgWgOBMuxoWfjONiLjjLh/9+ZIqSSLX
Message-ID: <c88dc1b3-9d2c-5a87-717c-0fb2497b1f22@2ndQuadrant.com>
Date: Sat, 9 Mar 2019 13:58:20 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.0
MIME-Version: 1.0
In-Reply-To: <24300.1552154154@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Content-Language: en-MW
Precedence: bulk


On 3/9/19 12:55 PM, Tom Lane wrote:
> Andrew Dunstan <andrew.dunstan@2ndquadrant.com> writes:
>> On 3/9/19 4:28 AM, David Rowley wrote:
>>> 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.
> I took a quick look at that and I'm afraid it'd be a mess.  GUC doesn't
> really distinguish between a variable's storage unit, its default input
> unit, or its default output unit (as seen in e.g. pg_settings).  Perhaps
> we could split those into two or three distinct concepts, but it seems
> complicated and bug-prone.  Also I think we'd still be forced into
> making obviously-incompatible changes in what pg_settings shows for
> this variable, since what it shows right now is integer ms.  That last
> isn't a deal-breaker perhaps, but 100% compatibility isn't going to
> happen this way.
>
> The idea of converting vacuum_cost_delay into a float variable, while
> keeping its native unit as ms, seems probably more feasible from a
> compatibility standpoint.  There are two sub-possibilities:
>
> 1. Just do that and lose units support for the variable.  I don't
> think this is totally unreasonable, because up to now ms is the
> *only* workable unit for it:
>
> regression=# set vacuum_cost_delay = '1s';
> ERROR:  1000 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
>
> Still, it'd mean that anyone who'd been explicitly setting it with
> an "ms" qualifier would have to change their postgresql.conf entry.
>
> 2. Add support for units for float variables, too.  I don't think
> this'd be a huge amount of work, and we'd surely have other uses
> for it in the long run.
>
> I'm inclined to go look into #2.  Anybody think this is a bad idea?
>
> 	


Sounds good to me, seems much more likely to be future-proof.


cheers


andrew


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