Per-table random_page_cost for tables that we know are always cached

It started with this query :

EXPLAIN ANALYZE SELECT * FROM relations r JOIN nodes n ON
(n.id=r.child_id) WHERE r.parent_id=16330;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=370.96..496.29 rows=543 width=273) (actual
time=18.887..21.164 rows=543 loops=1)
Hash Cond: (r.child_id = n.id)
-> Index Scan using relations_unique on relations r
(cost=0.00..111.75 rows=543 width=58) (actual time=0.022..0.519 rows=543
loops=1)
Index Cond: (parent_id = 16330)
-> Hash (cost=243.76..243.76 rows=10176 width=215) (actual
time=18.830..18.830 rows=10176 loops=1)
-> Seq Scan on nodes n (cost=0.00..243.76 rows=10176 width=215)
(actual time=0.006..5.135 rows=10176 loops=1)
Total runtime: 21.453 ms

SET enable_hashjoin TO 0;

EXPLAIN ANALYZE SELECT * FROM relations r JOIN nodes n ON
(n.id=r.child_id) WHERE r.parent_id=16330;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..514.50 rows=543 width=273) (actual
time=0.037..4.412 rows=543 loops=1)
-> Index Scan using relations_unique on relations r
(cost=0.00..111.75 rows=543 width=58) (actual time=0.023..0.476 rows=543
loops=1)
Index Cond: (parent_id = 16330)
-> Index Scan using nodes_pkey on nodes n (cost=0.00..0.73 rows=1
width=215) (actual time=0.004..0.005 rows=1 loops=543)
Index Cond: (n.id = r.child_id)
Total runtime: 4.638 ms

In order to have it use the fast plan I must set random_page_cost to 1
which I absolutely don't want to do.
Setting effective_cache_size to huge values has no effect.
If I select a value of parent_id that has much less children, the index
will be used, but in this case I think the threshold is misplaced, it
should be slightly higher. Here we have about 5% of values selected. Hash
join becomes better at about 15% because the table is cached.
This is 8.3.

Perhaps there would be a need for a per-object setting
(object=table,index,partition) to alter the aggressiveness/lazyness of the
page flushing and how long the pages for this object are kept in
shared_buffers... this would be used to modify random_page_cost on a
per-table/index/partition basis.

Example : let's imagine a "cache priority" setting.

- "cache priority" set to the minimum means this table is mostly write-only
- "cache priority" set to default would give current behaviour (which is
correct in most cases)
- "cache priority" set to a high value would tell Postgres "I know this
table/index/partition is small and often accessed rather randomly, so I
want you to keep it in shared_buffers, purge it if you must but otherwise
keep it in memory, flush something else instead which has lower
cache_priority".

The optimizer could then use a different (much lower) value of
random_page_cost for tables for which "cache priority" is set highest
since it would know.

An alternative would be for the background writer to keep some stats and
do the thing for us :

- begin bgwriter scan
- setup hashtable of [relid => page count]
- at each page that is scanned, increment "page count" for this relation
(uses very little CPU)
- end bgwriter stats
- for each relation, compare the number of pages we found in
shared_buffers with the number of pages in the relation and draw
conclusions about how well cached the relation is
- update random_page_cost accordingly for this relation

This would not examine whatever is in the OS' cache, though.

On Apr 22, 2008, at 5:13 AM, PFC wrote:
> In order to have it use the fast plan I must set random_page_cost
> to 1 which I absolutely don't want to do.
> Setting effective_cache_size to huge values has no effect.
> If I select a value of parent_id that has much less children, the
> index will be used, but in this case I think the threshold is
> misplaced, it should be slightly higher. Here we have about 5% of
> values selected. Hash join becomes better at about 15% because the
> table is cached.
> This is 8.3.
>
> Perhaps there would be a need for a per-object setting
> (object=table,index,partition) to alter the aggressiveness/lazyness
> of the page flushing and how long the pages for this object are
> kept in shared_buffers... this would be used to modify
> random_page_cost on a per-table/index/partition basis.
>
> Example : let's imagine a "cache priority" setting.
>
> - "cache priority" set to the minimum means this table is mostly
> write-only
> - "cache priority" set to default would give current behaviour
> (which is correct in most cases)
> - "cache priority" set to a high value would tell Postgres "I know
> this table/index/partition is small and often accessed rather
> randomly, so I want you to keep it in shared_buffers, purge it if
> you must but otherwise keep it in memory, flush something else
> instead which has lower cache_priority".
>
> The optimizer could then use a different (much lower) value of
> random_page_cost for tables for which "cache priority" is set
> highest since it would know.

"cache priority" to me sounds like we're trying to influence caching
behavior, which isn't what's happening. I do agree that we need a
better way to tell the planner what tables are in memory.

> An alternative would be for the background writer to keep some
> stats and do the thing for us :
>
> - begin bgwriter scan
> - setup hashtable of [relid => page count]
> - at each page that is scanned, increment "page count" for this
> relation (uses very little CPU)
> - end bgwriter stats
> - for each relation, compare the number of pages we found in
> shared_buffers with the number of pages in the relation and draw
> conclusions about how well cached the relation is
> - update random_page_cost accordingly for this relation
>
> This would not examine whatever is in the OS' cache, though.

Actually, there's no need for bgwriter to do that; we can just look
at the hit rate for the object. But we'd also need stats for how
often we find pages for a relation in the OS cache, which no one has
come up with a good method for.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

On Wed, Apr 23, 2008 at 12:11 AM, Decibel! <decibel(at)decibel(dot)org> wrote:

> On Apr 22, 2008, at 5:13 AM, PFC wrote:
>
> > In order to have it use the fast plan I must set random_page_cost to 1
> > which I absolutely don't want to do.
> > Setting effective_cache_size to huge values has no effect.
> > If I select a value of parent_id that has much less children, the index
> > will be used, but in this case I think the threshold is misplaced, it should
> > be slightly higher. Here we have about 5% of values selected. Hash join
> > becomes better at about 15% because the table is cached.
> > This is 8.3.
> >
> > Perhaps there would be a need for a per-object setting
> > (object=table,index,partition) to alter the aggressiveness/lazyness of the
> > page flushing and how long the pages for this object are kept in
> > shared_buffers... this would be used to modify random_page_cost on a
> > per-table/index/partition basis.
> >
> > Example : let's imagine a "cache priority" setting.
> >
> > - "cache priority" set to the minimum means this table is mostly
> > write-only
> > - "cache priority" set to default would give current behaviour (which is
> > correct in most cases)
> > - "cache priority" set to a high value would tell Postgres "I know this
> > table/index/partition is small and often accessed rather randomly, so I want
> > you to keep it in shared_buffers, purge it if you must but otherwise keep it
> > in memory, flush something else instead which has lower cache_priority".
> >
> > The optimizer could then use a different (much lower) value of
> > random_page_cost for tables for which "cache priority" is set highest since
> > it would know.
> >
>
> "cache priority" to me sounds like we're trying to influence caching
> behavior, which isn't what's happening. I do agree that we need a better way
> to tell the planner what tables are in memory.
>
> An alternative would be for the background writer to keep some stats and
> > do the thing for us :
> >
> > - begin bgwriter scan
> > - setup hashtable of [relid => page count]
> > - at each page that is scanned, increment "page count" for this relation
> > (uses very little CPU)
> > - end bgwriter stats
> > - for each relation, compare the number of pages we found in
> > shared_buffers with the number of pages in the relation and draw conclusions
> > about how well cached the relation is
> > - update random_page_cost accordingly for this relation
> >
> > This would not examine whatever is in the OS' cache, though.
> >
>
> Actually, there's no need for bgwriter to do that; we can just look at the
> hit rate for the object. But we'd also need stats for how often we find
> pages for a relation in the OS cache, which no one has come up with a good
> method for.

Something related... a per-relation cost setting would also allow users to
tune based on the kind of storage those objects are stored. Using
tablespaces, users can choose to place some objects on really expansive/
really fast storage, and other (not so hot) objects on a slower/cheaper
storage.

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Decibel! <decibel(at)decibel(dot)org> writes:
> "cache priority" to me sounds like we're trying to influence caching
> behavior, which isn't what's happening. I do agree that we need a
> better way to tell the planner what tables are in memory.

What's been discussed in the past is per-tablespace settings for
random_page_cost and friends. That was meant to cover actual disk
hardware differences, but could be (ab)used to handle the case of
heavily and not so heavily used tables.

Per-table sounds kinda bogus to me; such settings would probably reflect
wishful thinking on the part of the DBA more than reality.

regards, tom lane

On Tue, 22 Apr 2008, PFC wrote:

> Example : let's imagine a "cache priority" setting.

Which we can presume the DBA will set incorrectly because the tools needed
to set that right aren't easy to use.

> An alternative would be for the background writer to keep some stats and do
> the thing for us :
> - begin bgwriter scan
> - setup hashtable of [relid => page count]
> - at each page that is scanned, increment "page count" for this
> relation...

I've already got a plan sketched out that does this I didn't manage to get
finished in time for 8.3. What I wanted it for was not for this purpose,
but for instrumentation of what's in the cache that admins can look at.
Right now you can get that out pg_buffercache, but that's kind of
intrusive because of the locks it takes. In many cases I'd be perfectly
happy with an approximation of what's inside the buffer cache, accumulated
while the page header is being locked anyway as the BGW passed over it.
And as you note having this data available can be handy for internal
self-tuning as well once it's out there.

Jim threw out that you can just look at the page hit percentages instead.
That's not completely true. If you've had some nasty query blow out your
buffer cache, or if the server has been up a looong time and the total
stas don't really reflect recent reality, what's in the buffer cache and
what the stats say have been historical cached can diverge.

> This would not examine whatever is in the OS' cache, though.

I don't know that it's too unrealistic to model the OS as just being an
extrapolated bigger version of the buffer cache. I can think of a couple
of ways those can diverge:

1) Popular pages that get high usage counts can end up with a higher
representation in shared_buffers than the OS

2) If you've being doing something like a bulk update, you can have lots
of pages that have been written recently in the OS cache that aren't
really accounted for fully in shared_buffers, because they never get a
high enough usage count to stay there (only used once) but can fill the OS
cache as they're spooled up to write.

I'm not sure that either of these cases are so strong they invalidate your
basic idea though. There's a pending 8.4 TODO to investigate whether
increasing the maximum usage count a buffer can get would be an
improvement. If that number got bumped up I could see (2) become more of
a problem.

I'd be a somewhat concerned about turning this mechanism on by default
though, at least at first. A hybrid approach that gives the DBA some
control might work well. Maybe have an "adjust estimates for cache
contents" knob that you can toggle on a per-session or per-table basis?

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

>> Example : let's imagine a "cache priority" setting.
>
> Which we can presume the DBA will set incorrectly because the tools
> needed to set that right aren't easy to use.

LOL, yes.

> Jim threw out that you can just look at the page hit percentages
> instead. That's not completely true. If you've had some nasty query
> blow out your buffer cache, or if the server has been up a looong time
> and the total stas don't really reflect recent reality, what's in the
> buffer cache and what the stats say have been historical cached can
> diverge.

Yes :
- perform huge query on table A
- table A is now in cache
- perform huge query on table B
- table B is now in cache, A isn't
- perform huge query on table A again
- postgres still thinks table A is cached and chooses a bad plan

>> This would not examine whatever is in the OS' cache, though.

Yeah, but now that shared_buffers can be set to a large part of physical
RAM, does it still matters ?
Point is, postgres knows what is in the shared_buffers, so it can make a
good decision. Postgres doesn't know what the OS has in cache, so it could
only make a wild guess. I would rather err on the side of safety...

>
> I don't know that it's too unrealistic to model the OS as just being an
> extrapolated bigger version of the buffer cache. I can think of a
> couple of ways those can diverge:
>
> 1) Popular pages that get high usage counts can end up with a higher
> representation in shared_buffers than the OS
>
> 2) If you've being doing something like a bulk update, you can have lots
> of pages that have been written recently in the OS cache that aren't
> really accounted for fully in shared_buffers, because they never get a
> high enough usage count to stay there (only used once) but can fill the
> OS cache as they're spooled up to write.

Especially on CHECKPOINT

> > The optimizer could then use a different (much lower) value of
> > random_page_cost for tables for which "cache priority" is set
> > highest since it would know.
>
> "cache priority" to me sounds like we're trying to influence caching
> behavior, which isn't what's happening. I do agree that we need a
> better way to tell the planner what tables are in memory.

I think overruling the cache manager to more aggressively cache certain
objects is a bad idea in general.
e.g. the above telling the planner can easily produce self fulfilling
prophecies. Instead, if we find situations where the cache is not
optimally used we should try to improve the cache algorithm.

A per tablespace random_page_cost might make more sense, as Tom already
said.

e.g. Informix had a command to lock a table into memory, but apparently
it was so often misused, that the feature has been removed again, and
replaced by a better caching algorithm.

Andreas

Decibel! wrote:
> we can just look at
> the hit rate for the object. But we'd also need stats for how often we
> find pages for a relation in the OS cache, which no one has come up with
> a good method for.

Makes me wonder if we could (optionally, I guess, since timing
stuff is apparently slow on some systems) also keep save the
average time it took for a block to get ready in pg_statio_all_tables.
Or, (if possible), save the averages for random and sequential pages
separately.

Then rather than using guessed values in the config files it seems
the plans could use the actual averages per table.

That would address both poor guesses on random_page_cost,
effective_cache_size, etc - as well as get things right
on systems where some tablespaces are fast and some are slow.

On Apr 23, 2008, at 12:31 AM, Greg Smith wrote:
> Jim threw out that you can just look at the page hit percentages
> instead. That's not completely true. If you've had some nasty
> query blow out your buffer cache, or if the server has been up a
> looong time and the total stas don't really reflect recent reality,
> what's in the buffer cache and what the stats say have been
> historical cached can diverge.

Yeah, I kinda hand-waved over that. Obviously we'd want to look at
recent stats, not beginning of time.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828