Re: Vacuum, Freeze and Analyze: the big picture

On Mon, Jun 3, 2013 at 1:20 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sun, Jun 2, 2013 at 2:44 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> Do we know why anti-wraparound uses so many resources in the first place?
>> The default settings seem to be quite conservative to me, even for a system
>> that has only a single 5400 rpm hdd (and even more so for any real
>> production system that would be used for a many-GB database).
>>
>> I wonder if there is something simple but currently unknown going on which
>> is causing it to damage performance out of all proportion to the resources
>> it ought to be using.
>
> I can't rule that out.  Personally, I've always attributed it to the
> fact that it's (a) long and (b) I/O-intensive.  But it's not
> impossible there could also be bugs lurking.

It could be related to the OS. I have no evidence for or against, but
it's possible that OS write-out routines defeat the careful cost based
throttling that PostgreSQL does by periodically dumping a large
portion of dirty pages into the write queue at once. That does nasty
things to query latencies as evidenced by the work on checkpoint
spreading.

Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de