pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Changing the random_page_cost default (was: cpu_tuple_cost)
5+ messages / 4 participants
[nested] [flat]

* Re: Changing the random_page_cost default (was: cpu_tuple_cost)
@ 2005-03-15 12:35 Greg Sabino Mullane <greg@turnstep.com>
  2005-03-15 13:31 ` Re: Changing the random_page_cost default (was: cpu_tuple_cost) Jeff Hoffmann <jeff@propertykey.com>
  2005-03-15 15:22 ` Re: Changing the random_page_cost default (was: cpu_tuple_cost) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 5+ messages in thread

From: Greg Sabino Mullane @ 2005-03-15 12:35 UTC (permalink / raw)
  To: pgsql-performance


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Josh Berkus wrote:
> I don't agree.  The defaults are there for people who aren't going to read
> enough of the documentation to set them.  As such, conservative for the
> defaults is appropriate.

Sure, but I would argue that 4 is *too* conservative. We've certainly changed
other values over the years. I see it as those most affected by this change
are those who are least likely to have the know-how to change the default,
and are also the majority of our users. I've often had to reduce this, working
on many databases, on many versions of PostgreSQL. Granted, I don't work on
any huge, complex, hundreds of gig databases, but that supports my point -
if you are really better off with a /higher/ (than 3) random_page_cost, you
already should be tweaking a lot of stuff yourself anyway. Tom Lane has a
good point about tweaking other default parameters as well, and that's a
worthy goal, but I don't think extended searching for a "sweet spot" should
prevent us from making a small yet important (IMO!) change in the default
of this one variable.

N.B. My own personal starting default is 2, but I thought 3 was a nice
middle ground more likely to reach consensus here. :)

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200503141727
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCNhFAvJuQZxSWSsgRAgZiAJ9947emxFoMMXKooJHi2ZPIQr9xGACgjaFf
hBCPTuHZwGFzomf1Z1TDpVo=
=KX9t
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changing the random_page_cost default (was: cpu_tuple_cost)
  2005-03-15 12:35 Re: Changing the random_page_cost default (was: cpu_tuple_cost) Greg Sabino Mullane <greg@turnstep.com>
@ 2005-03-15 13:31 ` Jeff Hoffmann <jeff@propertykey.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Jeff Hoffmann @ 2005-03-15 13:31 UTC (permalink / raw)
  To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-performance


On Mar 15, 2005, at 6:35 AM, Greg Sabino Mullane wrote:

> Granted, I don't work on
> any huge, complex, hundreds of gig databases, but that supports my 
> point -
> if you are really better off with a /higher/ (than 3) 
> random_page_cost, you
> already should be tweaking a lot of stuff yourself anyway.

I think this is a good point.  The people that tend to benefit from the 
lower cost are precisely the people least likely to know to change it.  
It's the "install & go" crowd with smaller databases and only a few 
users/low concurrency that expect it to "just work".  The bigger 
installations are more like to have dedicated DB admins that understand 
tuning.

Wasn't there an idea on the table once to ship with several different 
configuration files with different defaults for small, medium, large, 
etc. installs?  Wouldn't it make sense to ask the user during initdb to 
pick from one of the default config files?  Or even have a few simple 
questions like "How much memory do you expect to be available to 
PostgreSQL?" and "How many concurrent users do you expect to have?".  
It's one thing to know how much memory is in a machine, it quite 
another thing to know how much the user wants dedicated to PostgreSQL.  
A couple of questions like that can go a long way to coming up with 
better ballpark figures.

--
Jeff Hoffmann
jeff@propertykey.com




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changing the random_page_cost default (was: cpu_tuple_cost)
  2005-03-15 12:35 Re: Changing the random_page_cost default (was: cpu_tuple_cost) Greg Sabino Mullane <greg@turnstep.com>
@ 2005-03-15 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-15 22:44   ` Re: Changing the random_page_cost default (was: Mark Kirkwood <markir@paradise.net.nz>
  2005-03-16 01:03   ` Re: Changing the random_page_cost default (was: cpu_tuple_cost) Greg Sabino Mullane <greg@turnstep.com>
  1 sibling, 2 replies; 5+ messages in thread

From: Tom Lane @ 2005-03-15 15:22 UTC (permalink / raw)
  To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-performance

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> N.B. My own personal starting default is 2, but I thought 3 was a nice
> middle ground more likely to reach consensus here. :)

Your argument seems to be "this produces nice results for me", not
"I have done experiments to measure the actual value of the parameter
and it is X".  I *have* done experiments of that sort, which is where
the default of 4 came from.  I remain of the opinion that reducing
random_page_cost is a band-aid that compensates (but only partially)
for problems elsewhere.  We can see that it's not a real fix from
the not-infrequent report that people have to reduce random_page_cost
below 1.0 to get results anywhere near local reality.  That doesn't say
that the parameter value is wrong, it says that the model it's feeding
into is wrong.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changing the random_page_cost default (was:
  2005-03-15 12:35 Re: Changing the random_page_cost default (was: cpu_tuple_cost) Greg Sabino Mullane <greg@turnstep.com>
  2005-03-15 15:22 ` Re: Changing the random_page_cost default (was: cpu_tuple_cost) Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-15 22:44   ` Mark Kirkwood <markir@paradise.net.nz>
  1 sibling, 0 replies; 5+ messages in thread

From: Mark Kirkwood @ 2005-03-15 22:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Sabino Mullane <greg@turnstep.com>; pgsql-performance

Tom Lane wrote:
> "Greg Sabino Mullane" <greg@turnstep.com> writes:
> 
>>N.B. My own personal starting default is 2, but I thought 3 was a nice
>>middle ground more likely to reach consensus here. :)
> 
> 
> Your argument seems to be "this produces nice results for me", not
> "I have done experiments to measure the actual value of the parameter
> and it is X".  I *have* done experiments of that sort, which is where
> the default of 4 came from.  I remain of the opinion that reducing
> random_page_cost is a band-aid that compensates (but only partially)
> for problems elsewhere.  We can see that it's not a real fix from
> the not-infrequent report that people have to reduce random_page_cost
> below 1.0 to get results anywhere near local reality.  That doesn't say
> that the parameter value is wrong, it says that the model it's feeding
> into is wrong.
> 

I would like to second that. A while back I performed a number of 
experiments on differing hardware and came to the conclusion that *real* 
random_page_cost was often higher than 4 (like 10-15 for multi-disk raid 
  systems).

However I have frequently adjusted Pg's random_page_cost to be less than 
4 - if it helped queries perform better.

So yes, it looks like the model is the issue - not the value of the 
parameter!

regards

Mark




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Changing the random_page_cost default (was: cpu_tuple_cost)
  2005-03-15 12:35 Re: Changing the random_page_cost default (was: cpu_tuple_cost) Greg Sabino Mullane <greg@turnstep.com>
  2005-03-15 15:22 ` Re: Changing the random_page_cost default (was: cpu_tuple_cost) Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-16 01:03   ` Greg Sabino Mullane <greg@turnstep.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Greg Sabino Mullane @ 2005-03-16 01:03 UTC (permalink / raw)
  To: pgsql-performance


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Your argument seems to be "this produces nice results for me", not
> "I have done experiments to measure the actual value of the parameter
> and it is X".  I *have* done experiments of that sort, which is where
> the default of 4 came from.  I remain of the opinion that reducing
> random_page_cost is a band-aid that compensates (but only partially)
> for problems elsewhere.  We can see that it's not a real fix from
> the not-infrequent report that people have to reduce random_page_cost
> below 1.0 to get results anywhere near local reality.  That doesn't say
> that the parameter value is wrong, it says that the model it's feeding
> into is wrong.

Good points: allow me to rephrase my question then:

When I install a new version of PostgreSQL and start testing my
applications, one of the most common problems is that many of my queries
are not hitting an index. I typically drop random_page_cost to 2 or
lower and this speeds things very significantly. How can I determine a
better way to speed up my queries, and why would this be advantageous
over simply dropping random_page_cost? How can I use my particular
situation to help develop a better model and perhaps make the defaults
work better for my queries and other people with databaes like mine.
(fairly simple schema, not too large (~2 Gig total), SCSI, medium to
high complexity queries, good amount of RAM available)?

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200503150600
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCNsCbvJuQZxSWSsgRAs0sAJwLFsGApzfYNV5jPL0gGVW5BH37hwCfRSW8
ed3sLnMg1UOTgN3oL9JSIFo=
=cZIe
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-03-16 01:03 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-15 12:35 Re: Changing the random_page_cost default (was: cpu_tuple_cost) Greg Sabino Mullane <greg@turnstep.com>
2005-03-15 13:31 ` Jeff Hoffmann <jeff@propertykey.com>
2005-03-15 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-15 22:44   ` Re: Changing the random_page_cost default (was: Mark Kirkwood <markir@paradise.net.nz>
2005-03-16 01:03   ` Greg Sabino Mullane <greg@turnstep.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