pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhy do optimizer parameters have to be set manually?
6+ messages / 4 participants
[nested] [flat]
* why do optimizer parameters have to be set manually?
@ 2003-12-18 18:44 Marinos J. Yannikos <mjy@geizhals.at>
2003-12-18 19:17 ` Re: why do optimizer parameters have to be set manually? Christopher Browne <cbbrowne@libertyrms.info>
2003-12-18 19:56 ` Re: why do optimizer parameters have to be set manually? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Marinos J. Yannikos @ 2003-12-18 18:44 UTC (permalink / raw)
To: pgsql-performance
Hi,
it seems to me that the optimizer parameters (like random_page_cost
etc.) could easily be calculated and adjusted dynamically be the DB
backend based on the planner's cost estimates and actual run times for
different queries. Perhaps the developers could comment on that?
I'm not sure how the parameters are used internally (apart from whatever
"EXPLAIN" shows), but if cpu_operator_cost is the same for all
operators, this should probably also be adjusted for individual
operators (I suppose that ">" is not as costly as "~*").
As far as the static configuration is concerned, I'd be interested in
other users' parameters and hardware configurations. Here's ours (for a
write-intensive db that also performs many queries with regular
expression matching):
effective_cache_size = 1000000 # typically 8KB each
#random_page_cost = 0.2 # units are one sequential page fetch cost
random_page_cost = 3 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
cpu_index_tuple_cost = 0.01 # (same) 0.1
#cpu_operator_cost = 0.0025 # (same)
cpu_operator_cost = 0.025 # (same)
other options:
shared_buffers = 240000 # 2*max_connections, min 16, typically 8KB each
max_fsm_relations = 10000 # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 10000000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 20 # min 10
wal_buffers = 128 # min 4, typically 8KB each
sort_mem = 800000 # min 64, size in KB
vacuum_mem = 100000 # min 1024, size in KB
checkpoint_segments = 80 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 300 # range 30-3600, in seconds
commit_delay = 100000 # range 0-100000, in microseconds
commit_siblings = 5 # range 1-1000
12GB RAM, dual 2,80GHz Xeon, 6x 10K rpm disks in a RAID-5, Linux 2.4.23
with HT enabled.
Regards,
Marinos
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: why do optimizer parameters have to be set manually?
2003-12-18 18:44 why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
@ 2003-12-18 19:17 ` Christopher Browne <cbbrowne@libertyrms.info>
1 sibling, 0 replies; 6+ messages in thread
From: Christopher Browne @ 2003-12-18 19:17 UTC (permalink / raw)
To: pgsql-performance
mjy@geizhals.at ("Marinos J. Yannikos") writes:
> it seems to me that the optimizer parameters (like random_page_cost
> etc.) could easily be calculated and adjusted dynamically be the DB
> backend based on the planner's cost estimates and actual run times for
> different queries. Perhaps the developers could comment on that?
Yes, it seems like a Small Matter Of Programming.
http://wombat.doc.ic.ac.uk/foldoc/foldoc.cgi?SMOP
In seriousness, yes, it would seem a reasonable idea to calculate some
of these values a bit more dynamically.
I would be inclined to start with something that ran a workload, and
provided static values based on how that workload went. That would
require NO intervention inside the DB server; it could be accomplished
simply by writing a database script. Feel free to contribute either a
script or a backend "hack"...
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: why do optimizer parameters have to be set manually?
2003-12-18 18:44 why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
@ 2003-12-18 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 13:53 ` Re: why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-12-18 19:56 UTC (permalink / raw)
To: Marinos J. Yannikos <mjy@geizhals.at>; +Cc: pgsql-performance
"Marinos J. Yannikos" <mjy@geizhals.at> writes:
> it seems to me that the optimizer parameters (like random_page_cost
> etc.) could easily be calculated and adjusted dynamically be the DB
> backend based on the planner's cost estimates and actual run times for
> different queries. Perhaps the developers could comment on that?
No, they are not that easy to determine. In particular I think the idea
of automatically feeding back error measurements is hopeless, because
you cannot tell which parameters are wrong.
> I'm not sure how the parameters are used internally (apart from whatever
> "EXPLAIN" shows), but if cpu_operator_cost is the same for all
> operators, this should probably also be adjusted for individual
> operators (I suppose that ">" is not as costly as "~*").
In theory perhaps, but in practice this is far down in the noise in most
situations.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: why do optimizer parameters have to be set manually?
2003-12-18 18:44 why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
2003-12-18 19:56 ` Re: why do optimizer parameters have to be set manually? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-19 13:53 ` Marinos J. Yannikos <mjy@geizhals.at>
2003-12-19 15:07 ` Re: why do optimizer parameters have to be set manually? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Marinos J. Yannikos @ 2003-12-19 13:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> No, they are not that easy to determine. In particular I think the idea
> of automatically feeding back error measurements is hopeless, because
> you cannot tell which parameters are wrong.
Isn't it just a matter of solving an equation system with n variables (n
being the number of parameters), where each equation stands for the
calculation of the run time of a particular query? I.e. something like
this for a sequential scan over 1000 rows with e.g. 2 operators used per
iteration that took 2 seconds (simplified so that the costs are actual
timings and not relative costs to a base value):
1000 * sequential_scan_cost + 1000 * 2 * cpu_operator_cost = 2.0 seconds
With a sufficient number of equations (not just n, since not all query
plans use all the parameters) this system can be solved for the
particular query mix that was used. E.g. with a second sequential scan
over 2000 rows with 1 operator per iteration that took 3 seconds you can
derive:
sequential_scan_cost = 1ms
cpu_operator_cost = 0.5ms
This could probably be implemented with very little overhead compared to
the actual run times of the queries.
Regard,
Marinos
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: why do optimizer parameters have to be set manually?
2003-12-18 18:44 why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
2003-12-18 19:56 ` Re: why do optimizer parameters have to be set manually? Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 13:53 ` Re: why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
@ 2003-12-19 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 17:44 ` Re: why do optimizer parameters have to be set manually? William Yu <wyu@talisys.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2003-12-19 15:07 UTC (permalink / raw)
To: Marinos J. Yannikos <mjy@geizhals.at>; +Cc: pgsql-performance
"Marinos J. Yannikos" <mjy@geizhals.at> writes:
> Tom Lane wrote:
>> No, they are not that easy to determine. In particular I think the idea
>> of automatically feeding back error measurements is hopeless, because
>> you cannot tell which parameters are wrong.
> Isn't it just a matter of solving an equation system with n variables (n
> being the number of parameters), where each equation stands for the
> calculation of the run time of a particular query?
If we knew all the variables involved, it might be (though since the
equations would be nonlinear, the solution would be more difficult than
you suppose). The real problems are:
1. There is lots of noise in any real-world measurement, mostly due to
competition from other processes.
2. There are effects we don't even try to model, such as the current
contents of kernel cache. Everybody who's done any work with Postgres
knows that for small-to-middling tables, running the same query twice in
a row will yield considerably different runtimes, because the second
time through all the data will be in kernel cache. But we don't have
any useful way to model that in the optimizer, since we can't see what
the kernel has in its buffers.
3. Even for the effects we do try to model, some of the equations are
pretty ad-hoc and might not fit real data very well. (I have little
confidence in the current correction for index order correlation, for
example.)
In short, if you just try to fit the present cost equations to real
data, what you'll get will inevitably be "garbage in, garbage out".
You could easily end up with parameter values that are much less
realistic than the defaults.
Over time we'll doubtless improve the optimizer's cost models, and
someday we might get to a point where this wouldn't be a fool's errand,
but I don't see it happening in the foreseeable future.
I think a more profitable approach is to set up special test code to try
to approximate the value of individual parameters measured in isolation.
For instance, the current default of 4.0 for random_page_cost was
developed through rather extensive testing a few years ago, and I think
it's still a decent average value (for the case where you are actually
doing I/O, mind you). But if your disks have particularly fast or slow
seek times, maybe it's not good for you. It might be useful to package
up a test program that repeats those measurements on particular systems
--- though the problem of noisy measurements still applies. It is not
easy or cheap to get a measurement that isn't skewed by kernel caching
behavior. (You need a test file significantly larger than RAM, and
even then you'd better repeat the measurement quite a few times to see
how much noise there is in it.)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: why do optimizer parameters have to be set manually?
2003-12-18 18:44 why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
2003-12-18 19:56 ` Re: why do optimizer parameters have to be set manually? Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 13:53 ` Re: why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
2003-12-19 15:07 ` Re: why do optimizer parameters have to be set manually? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-12-19 17:44 ` William Yu <wyu@talisys.com>
0 siblings, 0 replies; 6+ messages in thread
From: William Yu @ 2003-12-19 17:44 UTC (permalink / raw)
To: pgsql-performance
Tom Lane wrote:
> easy or cheap to get a measurement that isn't skewed by kernel caching
> behavior. (You need a test file significantly larger than RAM, and
> even then you'd better repeat the measurement quite a few times to see
> how much noise there is in it.)
I found a really fast way in Linux to flush the kernel cache and that is
to unmount the drive and then remount. Beats having to read though a
file > RAM everytime.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-12-19 17:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-18 18:44 why do optimizer parameters have to be set manually? Marinos J. Yannikos <mjy@geizhals.at>
2003-12-18 19:17 ` Christopher Browne <cbbrowne@libertyrms.info>
2003-12-18 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 13:53 ` Marinos J. Yannikos <mjy@geizhals.at>
2003-12-19 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-19 17:44 ` William Yu <wyu@talisys.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