On 17/02/14 15:26, Robert Haas wrote:
On Thu, Feb 13, 2014 at 3:34 PM, Bruce Momjian <br...@momjian.us> wrote:
On Fri, Oct 11, 2013 at 03:39:51PM -0700, Kevin Grittner wrote:
Josh Berkus <j...@agliodbs.com> wrote:
On 10/11/2013 01:11 PM, Bruce Momjian wrote:
In summary, I think we need to:
* decide on new defaults for work_mem and maintenance_work_mem
* add an initdb flag to allow users/packagers to set shared_bufffers?
* add an autovacuum_work_mem setting?
* change the default for temp_buffers?
If we're changing defaults, bgwriter_lru_maxpages and vacuum_cost_limit
could also use a bump; those thresholds were set for servers with < 1GB
of RAM.
+1 on those.
Also, I have often had to bump cpu_tuple_cost into the 0.03 to 0.05
range to get a good plan. In general, this makes the exact
settings of *_page_cost less fussy, and I have hit situations where
I was completely unable to get a good plan to emerge without
bumping cpu_tuple_cost relative to the other cpu costs. I know that
it's possible to engineer a workload that shows any particular cost
adjustment to make things worse, but in real-life production
environments I have never seen an increase in this range make plan
choice worse.
So, would anyone like me to create patches for any of these items before
we hit 9.4 beta? We have added autovacuum_work_mem, and increasing
work_mem and maintenance_work_mem by 4x is a simple operation. Not sure
about the others. Or do we just keep this all for 9.5?
I don't think anyone objected to increasing the defaults for work_mem
and maintenance_work_mem by 4x, and a number of people were in favor,
so I think we should go ahead and do that. If you'd like to do the
honors, by all means!
The current bgwriter_lru_maxpages value limits the background writer
to a maximum of 4MB/s. If one imagines shared_buffers = 8GB, that
starts to seem rather low, but I don't have a good feeling for what a
better value would be.
The current vacuum cost delay settings limit autovacuum to about
2.6MB/s. I am inclined to think we need a rather large bump there,
like 10x, but maybe it would be more prudent to do a smaller bump,
like say 4x, to avoid changing the default behavior too dramatically
between releases. IOW, I guess I'm proposing raising
vacuum_cost_limit from 200 to 800.
I don't really know about cpu_tuple_cost. Kevin's often advocated
raising it, but I haven't heard anyone else advocate for that. I
think we need data points from more people to know whether or not
that's a good idea in general.
Processors have been getting faster, relative to spinning rust, over the
years. So it puzzles me why anybody would want to raise the
cpu_tuple_cost! Possibly, the various costs should change if the
database is on SSD's? Of course, I have the implicit assumption that
cost factors like 'cpu_tuple_cost' have more than just a vague relation
to the semantics implied by their naming!
It would be good, if can we get some clarity on what these various cost
factors are actually meant to do and how they relate to each other.
Cheers,
Gavin
Cheers,
Gavin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
