Re: really lazy vacuums?

2011/3/22 Greg Stark <gsstark(at)mit(dot)edu>:
> On Mon, Mar 21, 2011 at 6:08 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> Has anyone looked at the overhead of measuring how long IO requests to the kernel take? If we did that not only could we get an idea of what our IO workload looked like, we could also figure out whether a block came out of cache or not. That information could potentially be useful to the planner, but even if the database couldn't use that knowledge itself it would be a damn useful statistic to have... IMHO, far more useful than our current hit rate statistics.
>>
>
> I've done this -- actually better, I used mincore to actually check
> whether the block was in cache before issuing the read -- but it turns
> out you can't get what you're looking for this way.

The linux fincore() syscall never get in the kernel, maybe something
to revive...

>
> It turns out when you do this you see one block being read from disk
> followed by n blocks that all appear to be cache hits. Because they've
> been prefetched by the kernel.

I did the same, I now believe that it is not very important to have
the very exact numbers.
Prefetech blocks *are* in memory when we request them, the first read
access read more than one block because the cost is the same.

>
> What you end up with is actually something like the number of iops
> which is also an interesting measure but not really what you were
> looking for.
>
> My getrusage patch, which I should still dig out though it's rather
> too late to be committing now unless someone tells me otherwise, would
> tell you how much i/o a plan node actually did. But you won't know
> which blocks did the i/o since I was only tracking totals for the plan
> node. That's probably what you're looking for here.

Please show us the patch :)

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support