SEO

Tom Lane wrote:
> Also, I tentatively reduced the threshold
> at which heapscans switch to ring mode to NBuffers/16; that probably
> needs more thought.

Yeah. One scenario where threshold < shared_buffers will hurt is if your
shared_buffers >= RAM size / 2. In that scenario, a scan on a table that
would barely fit in shared_buffers, will use the ring instead and not
fit in the OS cache either. Which means that repeatedly scanning that
table will do physical I/O with the patch, but not without it.

"swappiness", using linux terms, also makes a difference. When I started
testing the patch, I saw unexpectedly high gains from the patch with the
following configuration:
- RAM size 4 GB
- shared_buffers 1 GB
- table size 3GB

Without the patch, the table wouldn't fit in shared_buffers, and also
wouldn't fit in the OS cache, so repeatedly scanning the table always
read the table physically from disk, and it took ~20 seconds. With the
patch, however, the ring only actively used a few pages from
shared_buffers, and the kernel swapped out the rest. Thanks to that,
there was more than 3GB of RAM available for OS caching, the table fit
completely in the OS cache, and the query took < 2 seconds. It took me
quite a while to figure out what's going on.

> Lastly, I haven't done anything about making
> non-btree indexes honor the access strategy during VACUUM scans.

Also there's no attempt to not inflate usage_count, which means that
synchronized scans will spoil the buffer cache as if we didn't have the
buffer ring patch. If there's no easy solution, I think we could live
with that, but Greg's suggestion of bumping the usage_count in PinBuffer
instead of UnpinBuffer sounds like a nice solution to me.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Heikki Linnakangas <heikki@enterprisedb.com> writes:
> Also there's no attempt to not inflate usage_count, which means that
> synchronized scans will spoil the buffer cache as if we didn't have the
> buffer ring patch.

As I said, these patches are hardly independent.

> If there's no easy solution, I think we could live
> with that, but Greg's suggestion of bumping the usage_count in PinBuffer
> instead of UnpinBuffer sounds like a nice solution to me.

After thinking about it more, I'm a bit hesitant to do that because it
will change the interaction with the clock sweep for buffers that stay
pinned for awhile. I had suggested making the clock sweep not decrement
usage_count of a pinned buffer, but I think that would change the
fairness of the algorithm. OTOH it may not matter that much if we just
move the usage_count increment and leave the clock sweep alone. Do we
have any decent way of measuring the effectiveness of the clock-sweep
allocation algorithm?

I also thought about having ReadBuffer decrement the usage count when it
has a nondefault strategy and finds the buffer already in cache; this
would then cancel out the later unconditional increment in UnpinBuffer.
But that makes twice as many cycles spent holding the buffer spinlock.

Either one of these methods would require PinBuffer to be aware of the
strategy argument, which it is not at present. OTOH with the first way
we could get rid of the "normalAccess" argument to UnpinBuffer, so
there's some net conservation of cruft I guess. I think I had
originally given this task to UnpinBuffer on the theory that we'd have
better information at unpin time than pin time about what the buffer
state had been and thus be able to make smarter decisions about whether
to bump the access count or not. But at the moment it doesn't seem that
we really need any such info; AFAICS all the callers of PinBuffer know
what they want to happen.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Tue, 29 May 2007, Tom Lane wrote:

> Do we have any decent way of measuring the effectiveness of the
> clock-sweep allocation algorithm?

I put a view on top of the current pg_buffercache (now that it include
usage_count) that shows what the high usage_count buffers consist of.
Since they were basically what I hoped for (like plenty of index blocks on
popular tables) that seemed a reasonable enough measure of effectiveness
for my purposes. I briefly looked into adding some internal measurements
in this area, like how many buffers are scanned on average to satisfy an
allocation request; that would actually be easy to add to the buffer
allocation stats part of the auto bgwriter_max_pages patch I submitted
recently.

Based on my observations of buffer cache statistics, the number of pinned
buffers at any time is small enough that in a reasonably sized buffer
cache, I wouldn't expect a change in the pinned usage_count behavior to
have any serious impact. With what you're adjusting, the only time I can
think of that there would be a noticable shift in fairness would be if
ones buffer cache was very small relative to the number of clients, which
is kind of an unreasonable situation to go out of your way to accommodate.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Greg Smith <gsmith@gregsmith.com> writes:
> Based on my observations of buffer cache statistics, the number of pinned
> buffers at any time is small enough that in a reasonably sized buffer
> cache, I wouldn't expect a change in the pinned usage_count behavior to
> have any serious impact.

Fair enough. The patch I put up earlier tonight bumps usage_count at
PinBuffer instead of UnpinBuffer time, and leaves the clock sweep
behavior unchanged, which means that a buffer that had stayed pinned for
more than a clock-sweep cycle time could get recycled almost instantly
after being unpinned. That seems intuitively bad. If we make the clock
sweep code not decrement usage_count of a pinned buffer then the problem
goes away. I had expressed some discomfort with that idea, but I've got
to admit that it's only a vague worry not anything concrete. Barring
objections I'll adjust the patch to include the clock-sweep change.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend