Re: autovacuum_work_mem

On 11 December 2013 14:50, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Dec 11, 2013 at 9:43 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On 25 November 2013 21:51, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>>> On Sun, Nov 24, 2013 at 9:06 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>> VACUUM uses 6 bytes per dead tuple. And autovacuum regularly removes
>>>> dead tuples, limiting their numbers.
>>>>
>>>> In what circumstances will the memory usage from multiple concurrent
>>>> VACUUMs become a problem? In those circumstances, reducing
>>>> autovacuum_work_mem will cause more passes through indexes, dirtying
>>>> more pages and elongating the problem workload.
>>>
>>> Yes, of course, but if we presume that the memory for autovacuum
>>> workers to do everything in one pass simply isn't there, it's still
>>> better to do multiple passes.
>>
>> That isn't clear to me. It seems better to wait until we have the memory.
>>
>> My feeling is this parameter is a fairly blunt approach to the
>> problems of memory pressure on autovacuum and other maint tasks. I am
>> worried that it will not effectively solve the problem. I don't wish
>> to block the patch; I wish to get to an effective solution to the
>> problem.
>>
>> A better aproach to handling memory pressure would be to globally
>> coordinate workers so that we don't oversubscribe memory, allocating
>> memory from a global pool.
>
> This is doubtless true, but that project is at least two if not three
> orders of magnitude more complex than what's being proposed here, and
> I don't think we should make the perfect the enemy of the good.

It looks fairly easy to estimate the memory needed for an auto vacuum,
since we know the scale factor and the tuple estimate. We can then use
the memory estimate to alter the scheduling of work. And/or we can use
actual memory usage and block auto vac workers if they need more
memory than is currently available because of other workers.

We would still benefit from a new parameter in the above sketch, but
it would achieve something useful in practice.

That's about 2-3 days work and I know Peter can hack it. So the
situation is not perfection-sought-blocking-good, this is more like
fairly poor solution being driven through when a better solution is
available within the time and skills available.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services