pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanner constants for RAM resident databases
6+ messages / 4 participants
[nested] [flat]
* Planner constants for RAM resident databases
@ 2005-07-02 01:59 Emil Briggs <emil@baymountain.com>
0 siblings, 3 replies; 6+ messages in thread
From: Emil Briggs @ 2005-07-02 01:59 UTC (permalink / raw)
To: pgsql-performance
I'm working with an application where the database is entirely resident in RAM
(the server is a quad opteron with 16GBytes of memory). It's a web
application and handles a high volume of queries. The planner seems to be
generating poor plans for some of our queries which I can fix by raising
cpu_tuple_cost. I have seen some other comments in the archives saying that
this is a bad idea but is that necessarily the case when the database is
entirely resident in RAM?
Emil
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Planner constants for RAM resident databases
@ 2005-07-02 02:08 Steve Atkins <steve@blighty.com>
parent: Emil Briggs <emil@baymountain.com>
2 siblings, 0 replies; 6+ messages in thread
From: Steve Atkins @ 2005-07-02 02:08 UTC (permalink / raw)
To: pgsql-performance
On Fri, Jul 01, 2005 at 09:59:38PM -0400, Emil Briggs wrote:
> I'm working with an application where the database is entirely resident in RAM
> (the server is a quad opteron with 16GBytes of memory). It's a web
> application and handles a high volume of queries. The planner seems to be
> generating poor plans for some of our queries which I can fix by raising
> cpu_tuple_cost. I have seen some other comments in the archives saying that
> this is a bad idea but is that necessarily the case when the database is
> entirely resident in RAM?
If I'm understanding correctly that'll mostly increase the estimated
cost of handling a row relative to a sequential page fetch, which
sure sounds like it'll push plans in the right direction, but it
doesn't sound like the right knob to twiddle.
What do you have random_page_cost set to?
Cheers,
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Planner constants for RAM resident databases
@ 2005-07-02 02:40 John A Meinel <john@arbash-meinel.com>
parent: Emil Briggs <emil@baymountain.com>
2 siblings, 0 replies; 6+ messages in thread
From: John A Meinel @ 2005-07-02 02:40 UTC (permalink / raw)
To: emil@baymountain.com; +Cc: pgsql-performance
Emil Briggs wrote:
>I'm working with an application where the database is entirely resident in RAM
>(the server is a quad opteron with 16GBytes of memory). It's a web
>application and handles a high volume of queries. The planner seems to be
>generating poor plans for some of our queries which I can fix by raising
>cpu_tuple_cost. I have seen some other comments in the archives saying that
>this is a bad idea but is that necessarily the case when the database is
>entirely resident in RAM?
>
>Emil
>
>
>
Generally, the key knob to twiddle when everything fits in RAM is
random_page_cost. If you truly have everything in RAM you could set it
almost to 1. 1 means that it costs exactly the same to go randomly
through the data then it does to go sequential. I would guess that even
in RAM it is faster to go sequential (since you still have to page and
deal with L1/L2/L3 cache, etc). But the default random_page_cost of 4 is
probably too high for you.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Planner constants for RAM resident databases
@ 2005-07-02 03:14 John A Meinel <john@arbash-meinel.com>
parent: Emil Briggs <emil@baymountain.com>
2 siblings, 1 reply; 6+ messages in thread
From: John A Meinel @ 2005-07-02 03:14 UTC (permalink / raw)
To: pgsql-performance
Emil Briggs wrote:
>>I just mentioned random_page_cost, but you should also tune
>>effective_cache_size, since that is effectively most of your RAM. It
>>depends what else is going on in the system, but setting it as high as
>>say 12-14GB is probably reasonable if it is a dedicated machine. With
>>random_page_cost 1.5-2, and higher effective_cache_size, you should be
>>doing pretty well.
>>John
>>=:->
>>
>>
>
>I tried playing around with these and they had no effect. It seems the only
>thing that makes a difference is cpu_tuple_cost.
>
>
>
I'm surprised. I know cpu_tuple_cost can effect it as well, but usually
the recommended way to get indexed scans is the above two parameters.
When you do "explain analyze" of a query that you have difficulties
with, how are the planner's estimates. Are the estimated number of rows
about equal to the actual number of rows?
If the planner is mis-estimating, there is a whole different set of
tuning to do to help it estimate correctly.
John
=:->
PS> Use reply-all so that your comments go to the list.
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Planner constants for RAM resident databases
@ 2005-07-02 13:44 Emil Briggs <emil@baymountain.com>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 6+ messages in thread
From: Emil Briggs @ 2005-07-02 13:44 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
> When you do "explain analyze" of a query that you have difficulties
> with, how are the planner's estimates. Are the estimated number of rows
> about equal to the actual number of rows?
Some of them are pretty far off. For example
-> Merge Left Join (cost=9707.71..13993.52 rows=1276 width=161) (actual
time=164.423..361.477 rows=49 loops=1)
I tried setting enable_merge_joins to off and that made the query about three
times faster. It's using a hash join instead.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Planner constants for RAM resident databases
@ 2005-07-05 21:33 Josh Berkus <josh@agliodbs.com>
parent: Emil Briggs <emil@baymountain.com>
0 siblings, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2005-07-05 21:33 UTC (permalink / raw)
To: emil@baymountain.com; +Cc: John A Meinel <john@arbash-meinel.com>; pgsql-performance
Emil,
> -> Merge Left Join (cost=9707.71..13993.52 rows=1276 width=161)
> (actual time=164.423..361.477 rows=49 loops=1)
That would indicate that you need to either increase your statistical
sampling (SET STATISTICS) or your frequency of running ANALYZE, or both.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-07-05 21:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-02 01:59 Planner constants for RAM resident databases Emil Briggs <emil@baymountain.com>
2005-07-02 02:08 ` Steve Atkins <steve@blighty.com>
2005-07-02 02:40 ` John A Meinel <john@arbash-meinel.com>
2005-07-02 03:14 ` John A Meinel <john@arbash-meinel.com>
2005-07-02 13:44 ` Emil Briggs <emil@baymountain.com>
2005-07-05 21:33 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox