Re: Cost limited statements RFC

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Cost limited statements RFC
Дата
Msg-id CAMkU=1w5+NZ3=ER-Hvf-_=Z-rDVLhVST7Rv4oTVqEAS9eR_yog@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Cost limited statements RFC  (Greg Smith <greg@2ndQuadrant.com>)
Ответы Re: Cost limited statements RFC  (Greg Smith <greg@2ndQuadrant.com>)
Список pgsql-hackers
On Sat, Jun 8, 2013 at 1:57 PM, Greg Smith <greg@2ndquadrant.com> wrote:
On 6/8/13 4:43 PM, Jeff Janes wrote:

Also, in all the anecdotes I've been hearing about autovacuum causing
problems from too much IO, in which people can identify the specific
problem, it has always been the write pressure, not the read, that
caused the problem.  Should the default be to have the read limit be
inactive and rely on the dirty-limit to do the throttling?

That would be bad, I have to carefully constrain both of them on systems that are short on I/O throughput.  There all sorts of cases where cleanup of a large and badly cached relation will hit the read limit right now.

I wouldn't remove the ability, just change the default.  You can still tune your exquisitely balanced systems :)

Of course if the default were to be changed, who knows what complaints we would start getting, which we don't get now because the current default prevents them.

But my gut feeling is that if autovacuum is trying to read faster than the hardware will support, it will just automatically get throttled, by inherent IO waits, at a level which can be comfortably supported.  And this will cause minimal interference with other processes.  It is self-limiting.  If it tries to write too much, however, the IO system is reduced to a quivering heap, not just for that process, but for all others as well.

 

I suspect the reason we don't see as many complaints is that a lot more systems can handle 7.8MB/s of random reads then there are ones that can do 3.9MB/s of random writes.  If we removed that read limit, a lot more complaints would start rolling in about the read side.

Why is there so much random IO?  Do your systems have autovacuum_vacuum_scale_factor set far below the default?  Unless they do, most of the IO (both read and write) should be sequential.  Or at least, I don't understand why they are not sequential.
 
Cheers,

Jeff

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Simon Riggs
Дата:
Сообщение: Re: Hard limit on WAL space used (because PANIC sucks)
Следующее
От: Kevin Grittner
Дата:
Сообщение: Re: Cost limited statements RFC