Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-03-14 Thread Jeff Janes
On Mon, Mar 10, 2014 at 3:16 PM, Kevin Grittner kgri...@ymail.com wrote:

 Andres Freund and...@2ndquadrant.com wrote:
  On 2014-02-16 21:26:47 -0500, Robert Haas wrote:

  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.
 
  FWIW It's a good idea in my experience.

 This is more about the balance among the various cpu_* costs than
 the balance between cpu_* costs and the *_page costs.  I usually
 need to adjust the page costs, too; and given how heavily cached
 many machines are, I'm usually moving them down.  But if you think
 about the work involved in moving to a new tuple, do you really
 think it's only twice the cost of moving to a new index entry on an
 index scan?  Or only four times as expensive as executing an
 average operator function?


If the next tuple is already hinted and not compressed or toasted, I would
completely believe that.  In fact, unless the operator is integer or dp, I
would say it is less than 2 times as expensive.  If it is a text operator
and the collation is not C or POSIX, then moving to the next tuple is
likely less expensive than a single operator evaluation.

If your tuples are updated nearly as often as queried, the hint resolution
could be a big cost.  But in that case, probably the contention would be a
bigger issue than the pure CPU cost.

I don't know how compression and toast would affect the times.  Are your
tables heavily toasted?

If top down measurements and bottom up measurements aren't giving the same
results, then what is going on?  We know and document how caching needs to
be baked into the page costs parameters.  What unknown thing is throwing
off the cpu costs?



 In my experience setting cpu_tuple_cost
 higher tends to better model costs, and prevent CPU-sucking scans
 of large numbers of rows.

 I only have anecdotal evidence, though.  I have seen it help dozens
 of times, and have yet to see it hurt.  That said, most people on
 this list are probably capable of engineering a benchmark which
 will show whichever result they would prefer.  I would prefer to
 hear about other data points based on field experience with
 production systems.  I haven't offered the trivial patch because
 when I've raised the point before, there didn't seem to be anyone
 else who had the same experience.  It's good to hear that Andres
 has seen this, too.

 FWIW, even though I'm repeating something I've mentioned before,
 whenever raising this setting did help, 0.03 was high enough to see
 the benefit.  Several times I have also tried 0.05 just to test
 whether I was wandering near a tipping point for a bad choice from
 this.  I have never had 0.05 produce plans noticeably better or
 worse than 0.03.



Have you ever tried lowering the other two cpu cost parameters instead?
 That would be the more definitive indication that the benefit is not
coming just by moving the io costs closer to the cpu costs

Cheers,

Jeff


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-03-10 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote:
 On 2014-02-16 21:26:47 -0500, Robert Haas wrote:

 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.

 FWIW It's a good idea in my experience.

This is more about the balance among the various cpu_* costs than
the balance between cpu_* costs and the *_page costs.  I usually
need to adjust the page costs, too; and given how heavily cached
many machines are, I'm usually moving them down.  But if you think
about the work involved in moving to a new tuple, do you really
think it's only twice the cost of moving to a new index entry on an
index scan?  Or only four times as expensive as executing an
average operator function?  In my experience setting cpu_tuple_cost
higher tends to better model costs, and prevent CPU-sucking scans
of large numbers of rows.

I only have anecdotal evidence, though.  I have seen it help dozens
of times, and have yet to see it hurt.  That said, most people on
this list are probably capable of engineering a benchmark which
will show whichever result they would prefer.  I would prefer to
hear about other data points based on field experience with
production systems.  I haven't offered the trivial patch because
when I've raised the point before, there didn't seem to be anyone
else who had the same experience.  It's good to hear that Andres
has seen this, too.

FWIW, even though I'm repeating something I've mentioned before,
whenever raising this setting did help, 0.03 was high enough to see
the benefit.  Several times I have also tried 0.05 just to test
whether I was wandering near a tipping point for a bad choice from
this.  I have never had 0.05 produce plans noticeably better or
worse than 0.03.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-03-10 Thread Josh Berkus
On 03/10/2014 03:16 PM, Kevin Grittner wrote:
 I only have anecdotal evidence, though.  I have seen it help dozens
 of times, and have yet to see it hurt.  That said, most people on
 this list are probably capable of engineering a benchmark which
 will show whichever result they would prefer.  I would prefer to
 hear about other data points based on field experience with
 production systems.  I haven't offered the trivial patch because
 when I've raised the point before, there didn't seem to be anyone
 else who had the same experience.  It's good to hear that Andres
 has seen this, too.

The problem with cpu_tuple_cost is that it's used in several places by
the planner and makes it hard to model what the effect of any change
would be.  If we had a good general benchmark which actually gave the
query planner a workout, we could come up with some reasonable default
settings, but right now we can't.

Back in 2004-2006 era, when CPU speeds had leapfrogged ahead of disk
speeds (which were largely unchanged from 2000), I was routinely
*lowering* cpu_tuple_cost (and cpu_index_tuple_cost) to get better
plans.  This was baked into early versions of Greenplum for that reason.

So I'm not saying that we shouldn't change the default for
cpu_tuple_cost.  I'm saying that we currently don't have enough
information on *when* and *how much* to change it.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-27 Thread Craig Ringer
On 02/18/2014 12:19 AM, Andres Freund wrote:
 On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
 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!
 
 Actually, I object to increasing work_mem by default. In my experience
 most of the untuned servers are backing some kind of web application and
 often run with far too many connections. Increasing work_mem for those
 is dangerous.

Good point. Especially with pagination involved. Those OFFSET 4
LIMIT 100 queries can be a killer.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-25 Thread Robert Haas
On Mon, Feb 24, 2014 at 1:05 PM, Bruce Momjian br...@momjian.us wrote:
 On Mon, Feb 17, 2014 at 11:14:33AM -0500, Bruce Momjian wrote:
 On Sun, Feb 16, 2014 at 09:26:47PM -0500, Robert Haas wrote:
   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!

 OK, patch attached.

 Patch applied.

Thanks!

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-24 Thread Bruce Momjian
On Mon, Feb 17, 2014 at 11:14:33AM -0500, Bruce Momjian wrote:
 On Sun, Feb 16, 2014 at 09:26:47PM -0500, Robert Haas wrote:
   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!
 
 OK, patch attached.

Patch applied.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Tom Lane
Gavin Flower gavinflo...@archidevsys.co.nz writes:
 On 17/02/14 15:26, Robert Haas wrote:
 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!

The case where this is sensible is where your database mostly fits in
RAM, so that the cost of touching the underlying spinning rust isn't
so relevant.  The default cost settings are certainly not very good
for such scenarios.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Bruce Momjian
On Sun, Feb 16, 2014 at 09:26:47PM -0500, Robert Haas wrote:
  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!

OK, patch attached.

 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.

Robert, can you take the lead on these remaining possible changes?  We
don't have time for any controversial changes but things everyone can
agree on, like work_mem, should be implemented for 9.4.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
new file mode 100644
index e12778b..47bdebf
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*** include 'filename'
*** 1213,1219 
 para
  Specifies the amount of memory to be used by internal sort operations
  and hash tables before writing to temporary disk files. The value
! defaults to one megabyte (literal1MB/).
  Note that for a complex query, several sort or hash operations might be
  running in parallel; each operation will be allowed to use as much memory
  as this value specifies before it starts to write data into temporary
--- 1213,1219 
 para
  Specifies the amount of memory to be used by internal sort operations
  and hash tables before writing to temporary disk files. The value
! defaults to four megabytes (literal4MB/).
  Note that for a complex query, several sort or hash operations might be
  running in parallel; each operation will be allowed to use as much memory
  as this value specifies before it starts to write data into temporary
*** include 'filename'
*** 1239,1245 
  Specifies the maximum amount of memory to be used by maintenance
  operations, such as commandVACUUM/command, commandCREATE
  INDEX/, and commandALTER TABLE ADD FOREIGN KEY/.  It defaults
! to 16 megabytes (literal16MB/).  Since only one of these
  operations can be executed at a time by a database session, and
  an installation normally doesn't have many of them running
  concurrently, it's safe to set this value significantly larger
--- 1239,1245 
  Specifies the maximum amount of memory to be used by maintenance
  operations, such as commandVACUUM/command, commandCREATE
  INDEX/, and commandALTER TABLE ADD FOREIGN KEY/.  It defaults
! to 64 megabytes (literal64MB/).  Since only one of these
  operations can be executed at a time by a database session, and
  an installation normally doesn't have many of them running
  concurrently, it's safe to set this value significantly larger
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
new file mode 100644
index 86afde1..aa5a875
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*** static struct config_int ConfigureNamesI
*** 1773,1779 
  			GUC_UNIT_KB
  		},
  		work_mem,
! 		1024, 64, MAX_KILOBYTES,
  		NULL, NULL, NULL
  	},
  
--- 1773,1779 
  			GUC_UNIT_KB
  		},
  		work_mem,
! 		4096, 64, MAX_KILOBYTES,
  		NULL, NULL, NULL
  	},
  
*** static struct config_int ConfigureNamesI
*** 1784,1790 
  			GUC_UNIT_KB
  		},
  		maintenance_work_mem,
! 		16384, 1024, MAX_KILOBYTES,
  		NULL, NULL, NULL
  	},
  
--- 1784,1790 
  			GUC_UNIT_KB
  		},
  		maintenance_work_mem,
! 		65536, 1024, MAX_KILOBYTES,
  		NULL, NULL, NULL
  	},
  
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
new file mode 100644
index 480c9e9..07341e7
*** 

Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
 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!

Actually, I object to increasing work_mem by default. In my experience
most of the untuned servers are backing some kind of web application and
often run with far too many connections. Increasing work_mem for those
is dangerous.

 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.

FWIW It's a good idea in my experience.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote:
 On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
  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!
 
 Actually, I object to increasing work_mem by default. In my experience
 most of the untuned servers are backing some kind of web application and
 often run with far too many connections. Increasing work_mem for those
 is dangerous.

And I still disagree with this- even in those cases.  Those same untuned
servers are running dirt-simple queries 90% of the time and they won't
use any more memory from this, while the 10% of the queries which are
more complicated will greatly improve.

  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.
 
 FWIW It's a good idea in my experience.

I'm in favor of this also but I'm also in the camp of gee, more data
would be nice.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-17 11:31:56 -0500, Stephen Frost wrote:
 * Andres Freund (and...@2ndquadrant.com) wrote:
  On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
   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!
  
  Actually, I object to increasing work_mem by default. In my experience
  most of the untuned servers are backing some kind of web application and
  often run with far too many connections. Increasing work_mem for those
  is dangerous.
 
 And I still disagree with this- even in those cases.  Those same untuned
 servers are running dirt-simple queries 90% of the time and they won't
 use any more memory from this, while the 10% of the queries which are
 more complicated will greatly improve.

Uh. Paging.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Robert Haas
On Mon, Feb 17, 2014 at 11:19 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
 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!

 Actually, I object to increasing work_mem by default. In my experience
 most of the untuned servers are backing some kind of web application and
 often run with far too many connections. Increasing work_mem for those
 is dangerous.

I think you may be out-voted.  An awful lot of people have voiced
support for the idea of raising this value, and there is no rule that
our default should be the smallest value that anyone will ever find
useful.  We do tend to err on the side of conservatism and aim for a
relatively low-end machine, and I agree with that policy, but there is
such a thing as going overboard.  With the proposed defaults, a user
with one sort or hash in every session, each of which uses the
entirety of work_mem, is on the hook for 400MB.  If you're trying to
handle 100 connections on a machine that does not have 400MB of
working memory available, you are probably in for a bad time of it.

Now, if you're saying that people raise max_connections to say 1000
*and do nothing else* perhaps that makes the argument more plausible.
But I don't think it makes it very much more plausible.  Even a
high-end system is likely to deliver terrible performance if the user
has 1000 simultaneously-active connections; one with only a few GB of
memory is going to be crushed like a bug.

I'll note that in 9.3, we quadrupled the default size of
shared_buffers when we got out from under the POSIX shared memory
limits and AFAIK we've had zero complaints about that.  It is entirely
possible, even likely, that there is a machine out there somewhere for
which the old value of 32MB is preferable, and those people can
configure a smaller value.  But that's not typical.  And neither do I
believe that the typical PostgreSQL user wants a 2MB sort to spill to
disk.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-17 12:23:58 -0500, Robert Haas wrote:
 On Mon, Feb 17, 2014 at 11:19 AM, Andres Freund and...@2ndquadrant.com 
 wrote:
  On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
  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!
 
  Actually, I object to increasing work_mem by default. In my experience
  most of the untuned servers are backing some kind of web application and
  often run with far too many connections. Increasing work_mem for those
  is dangerous.
 
 I think you may be out-voted.

I realize that, but I didn't want to let the I don't think anyone
objected stand :)

 With the proposed defaults, a user with one sort or hash in every
 session, each of which uses the entirety of work_mem, is on the hook
 for 400MB.  If you're trying to handle 100 connections on a machine
 that does not have 400MB of working memory available, you are probably
 in for a bad time of it.

Sure, if that's all they do it's fine. But often enough queries aren't
that simple. Lots of the ORMs commonly used for web applications tend to
create lots of JOINs to gather all the data and also use sorting for paging.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes:
 On 2014-02-17 12:23:58 -0500, Robert Haas wrote:
 I think you may be out-voted.

 I realize that, but I didn't want to let the I don't think anyone
 objected stand :)

FWIW, I think we need to be pretty gradual about this sort of thing,
because push-back from the field is the only way to know if we've gone
too far for average users.  I'm OK with raising work_mem 4X in one go,
but I'd complain if it were 10X, or if we were also raising other
resource consumption limits in the same release.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Robert Haas
On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2014-02-17 11:31:56 -0500, Stephen Frost wrote:
 * Andres Freund (and...@2ndquadrant.com) wrote:
  On 2014-02-16 21:26:47 -0500, Robert Haas wrote:
   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!
 
  Actually, I object to increasing work_mem by default. In my experience
  most of the untuned servers are backing some kind of web application and
  often run with far too many connections. Increasing work_mem for those
  is dangerous.

 And I still disagree with this- even in those cases.  Those same untuned
 servers are running dirt-simple queries 90% of the time and they won't
 use any more memory from this, while the 10% of the queries which are
 more complicated will greatly improve.

 Uh. Paging.

What about it?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Andres Freund
On 2014-02-17 13:33:17 -0500, Robert Haas wrote:
 On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund and...@2ndquadrant.com 
 wrote:
  And I still disagree with this- even in those cases.  Those same untuned
  servers are running dirt-simple queries 90% of the time and they won't
  use any more memory from this, while the 10% of the queries which are
  more complicated will greatly improve.
 
  Uh. Paging.
 
 What about it?

It's often the source of a good portion of the queries and load in web
applications. Multiple joins and more than one row... I have several
time seen stats changes or bad to-be-sorted columns cause large amounts
of memory to be used.

Anyway, I've stated my opinion that I do not think it's a good idea to
raise that particular default (while agreeing with all the others) and I
know I am in the minority, so I don't think we need to argue this out...

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Bruce Momjian
On Mon, Feb 17, 2014 at 07:39:47PM +0100, Andres Freund wrote:
 On 2014-02-17 13:33:17 -0500, Robert Haas wrote:
  On Mon, Feb 17, 2014 at 11:33 AM, Andres Freund and...@2ndquadrant.com 
  wrote:
   And I still disagree with this- even in those cases.  Those same untuned
   servers are running dirt-simple queries 90% of the time and they won't
   use any more memory from this, while the 10% of the queries which are
   more complicated will greatly improve.
  
   Uh. Paging.
  
  What about it?
 
 It's often the source of a good portion of the queries and load in web
 applications. Multiple joins and more than one row... I have several
 time seen stats changes or bad to-be-sorted columns cause large amounts
 of memory to be used.

Perhaps we should have said there was general agreement to increase
work_mem and maintenence_work_mem by 4x, not that there was 100%
agreement.  It would be nice to have 100% agreement, but if we _require_
that then defaults would probably never be changed.

 Anyway, I've stated my opinion that I do not think it's a good idea to
 raise that particular default (while agreeing with all the others) and I
 know I am in the minority, so I don't think we need to argue this out...

OK, good.  If you did feel there was need for more discussion, we would
need to push this change to PG 9.5.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Gavin Flower

On 18/02/14 03:48, Tom Lane wrote:

Gavin Flower gavinflo...@archidevsys.co.nz writes:

On 17/02/14 15:26, Robert Haas wrote:

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!

The case where this is sensible is where your database mostly fits in
RAM, so that the cost of touching the underlying spinning rust isn't
so relevant.  The default cost settings are certainly not very good
for such scenarios.

regards, tom lane

Thanks.

That is obvious... once you pointed it out!


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


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Peter Geoghegan
On Mon, Feb 17, 2014 at 8:31 AM, Stephen Frost sfr...@snowman.net wrote:
 Actually, I object to increasing work_mem by default. In my experience
 most of the untuned servers are backing some kind of web application and
 often run with far too many connections. Increasing work_mem for those
 is dangerous.

 And I still disagree with this- even in those cases.  Those same untuned
 servers are running dirt-simple queries 90% of the time and they won't
 use any more memory from this, while the 10% of the queries which are
 more complicated will greatly improve.

+1


-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-17 Thread Jeff Janes
On Sun, Feb 16, 2014 at 6:26 PM, Robert Haas robertmh...@gmail.com wrote:



 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.


I don't quite understand the point of bgwriter_lru_maxpages in the first
place.  What is it supposed to protect us from?

I wonder if that isn't an artefact from when the checkpointer was the same
process as the background writer, to prevent the background writer
functionality from starving the checkpointer functionality.


Cheers,

Jeff


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-16 Thread Robert Haas
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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-16 Thread Josh Berkus
On 02/16/2014 09:26 PM, Robert Haas wrote:
 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.

In 10 years of tuning PostgreSQL professionally, I still don't have a
mathematical model for the interaction of the various *_cost parameters
with the speeds of CPU, RAM and IO.   If someone else has one, please
post it so that we can make some intelligent decisions on defaults.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-16 Thread Gavin Flower

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


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2014-02-13 Thread Bruce Momjian
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?

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-18 Thread Josh Berkus
All,

So, I did an informal survey last night a SFPUG, among about 30
PostgreSQL DBAs and developers.  While hardly a scientific sample, it's
a data point on what we're looking at for servers.

Out of the 30, 6 had one or more production instances of PostgreSQL
running on machines or VMs with less than 1GB of RAM.  Out of those 5
had already edited their PostgreSQL.conf extensively.  Perhaps more
importantly, for four out of the 6, the low-memory Postgres instance(s)
was an older version (8.2 to 9.0) which they did not expect to upgrade.
 Also, note that a couple of the 6 were consultants, so they were
speaking for dozens of customer servers.

As a second data point, Christophe and I did a quick survey of the
database of server information on our clients, which include a bunch of
cloud-hosted web companies.  We found two PostgreSQL VMs which did not
have 1GB or more RAM, out of a few hundred.

Now, obviously, there's some significant sample bias in the above, but I
think it gives support to the assertion that we shouldn't really be
worrying about PostgresQL running well out-of-the-box on machines with 
1GB of RAM.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Robert Haas
On Wed, Oct 16, 2013 at 5:14 PM, Josh Berkus j...@agliodbs.com wrote:
 On 10/16/2013 01:25 PM, Andrew Dunstan wrote:
 Andres has just been politely pointing out to me that my knowledge of
 memory allocators is a little out of date (i.e. by a decade or two), and
 that this memory is not in fact likely to be held for a long time, at
 least on most modern systems. That undermines completely my reasoning
 above.

 Except that Opensolaris and FreeBSD still have the old memory allocation
 behavior, as do older Linux kernels, many of which will remain in
 production for years.  I have no idea what Windows' memory management
 behavior is.

 So this is a case of needing to know considerably more than the
 available RAM to determine a good setting.

I agree, but I still think my previous proposal of increasing the
defaults for work_mem and maintenance_work_mem by 4X would serve many
more people well than it would serve poorly.  I haven't heard anyone
disagree with that notion.  Does anyone disagree?  Should we do it?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote:

 I still think my previous proposal of increasing the defaults for
 work_mem and maintenance_work_mem by 4X would serve many more
 people well than it would serve poorly.  I haven't heard anyone
 disagree with that notion.  Does anyone disagree?  Should we do
 it?

I think that it makes sense to do that.  Those are still reasonable
defaults for a machine with 2GB of RAM, maybe even with less. 
We're talking about putting this only in a release that will come
out in 2014.  How many machines used for a database server that new
will have less than that?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Joshua D. Drake


On 10/17/2013 08:55 AM, Kevin Grittner wrote:


Robert Haas robertmh...@gmail.com wrote:


I still think my previous proposal of increasing the defaults for
work_mem and maintenance_work_mem by 4X would serve many more
people well than it would serve poorly.  I haven't heard anyone
disagree with that notion.  Does anyone disagree?  Should we do
it?


I think that it makes sense to do that.  Those are still reasonable
defaults for a machine with 2GB of RAM, maybe even with less.
We're talking about putting this only in a release that will come
out in 2014.  How many machines used for a database server that new
will have less than that?


A lot. A whole lot, more than what most people have in production with 
more than that. You are forgetting a very large segment of the 
population who run... VMs.


Why don't we just have 3 default config files:

2GB memory
4GB memory
8GB memory

Have initdb detect how much memory is available on the machine in TOTAL 
and pick the most appropriate.


Joshua D. Drake


--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
   a rose in the deeps of my heart. - W.B. Yeats


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Josh Berkus
JD,

 A lot. A whole lot, more than what most people have in production with
 more than that. You are forgetting a very large segment of the
 population who run... VMs.

Actually, even a mini AWS instance has 1GB of RAM.  And nobody who
uses a micro is going to expect it to perform well under load.  I
think it's completely reasonable to tell people running on  1GB of ram
to tune PostgreSQL down.

4MB work_mem / 64MB maint_work_mem still works fine at 1GB.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Robert Haas
On Thu, Oct 17, 2013 at 12:03 PM, Joshua D. Drake j...@commandprompt.com 
wrote:
 On 10/17/2013 08:55 AM, Kevin Grittner wrote:
 Robert Haas robertmh...@gmail.com wrote:

 I still think my previous proposal of increasing the defaults for
 work_mem and maintenance_work_mem by 4X would serve many more
 people well than it would serve poorly.  I haven't heard anyone
 disagree with that notion.  Does anyone disagree?  Should we do
 it?


 I think that it makes sense to do that.  Those are still reasonable
 defaults for a machine with 2GB of RAM, maybe even with less.
 We're talking about putting this only in a release that will come
 out in 2014.  How many machines used for a database server that new
 will have less than that?

 A lot. A whole lot, more than what most people have in production with more
 than that. You are forgetting a very large segment of the population who
 run... VMs.

That's true, but are you actually arguing for keeping work_mem at 1MB?

Even on a VM with only 1GB of RAM, work_mem=4MB is not going to cause
any problems unless you're also trying to service a large number of
simultaneous connections.  And if you're doing that, you probably need
to rethink something anyway.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Joshua D. Drake


On 10/17/2013 09:49 AM, Robert Haas wrote:


A lot. A whole lot, more than what most people have in production with more
than that. You are forgetting a very large segment of the population who
run... VMs.


That's true, but are you actually arguing for keeping work_mem at 1MB?

Even on a VM with only 1GB of RAM, work_mem=4MB is not going to cause
any problems unless you're also trying to service a large number of
simultaneous connections.  And if you're doing that, you probably need
to rethink something anyway.


No. I am arguing for the multiple config file option.

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
   a rose in the deeps of my heart. - W.B. Yeats


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Jeff Janes
On Thu, Oct 17, 2013 at 9:03 AM, Joshua D. Drake j...@commandprompt.comwrote:


 On 10/17/2013 08:55 AM, Kevin Grittner wrote:


 Robert Haas robertmh...@gmail.com wrote:

  I still think my previous proposal of increasing the defaults for
 work_mem and maintenance_work_mem by 4X would serve many more
 people well than it would serve poorly.  I haven't heard anyone
 disagree with that notion.  Does anyone disagree?  Should we do
 it?


 I think that it makes sense to do that.  Those are still reasonable
 defaults for a machine with 2GB of RAM, maybe even with less.
 We're talking about putting this only in a release that will come
 out in 2014.  How many machines used for a database server that new
 will have less than that?


 A lot. A whole lot, more than what most people have in production with
 more than that. You are forgetting a very large segment of the population
 who run... VMs.

 Why don't we just have 3 default config files:

 2GB memory
 4GB memory
 8GB memory


But what would go in each of those files?  Once we agree on what would be
in them, why not just have a continuous knob that does that same thing?

Would your suggestion for the 2GB file have work_mem be at least 4 MB?

Cheers,

Jeff


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Joshua D. Drake


On 10/17/2013 10:33 AM, Jeff Janes wrote:


A lot. A whole lot, more than what most people have in production
with more than that. You are forgetting a very large segment of the
population who run... VMs.

Why don't we just have 3 default config files:

2GB memory
4GB memory
8GB memory


But what would go in each of those files?  Once we agree on what would
be in them, why not just have a continuous knob that does that same thing?


Because we should set defaults, not optimized parameters. Workloads vary 
and we can reasonably say this is what we want BY DEFAULT for something 
but we can not reasonably say, this is what will suit your needs.


Once you get above 8GB of memory you are dealing with workloads that 
vary widely and will almost always need some kind of indvidual 
attention. However, 8GB and below, we can set reasonable defaults that 
allow a user to likely but possibly not worry about changing the conf.


Joshua D. Drake

--
Command Prompt, Inc. - http://www.commandprompt.com/  509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
   a rose in the deeps of my heart. - W.B. Yeats


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-17 Thread Merlin Moncure
On Thu, Oct 17, 2013 at 7:22 AM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Oct 16, 2013 at 5:14 PM, Josh Berkus j...@agliodbs.com wrote:
 On 10/16/2013 01:25 PM, Andrew Dunstan wrote:
 Andres has just been politely pointing out to me that my knowledge of
 memory allocators is a little out of date (i.e. by a decade or two), and
 that this memory is not in fact likely to be held for a long time, at
 least on most modern systems. That undermines completely my reasoning
 above.

 Except that Opensolaris and FreeBSD still have the old memory allocation
 behavior, as do older Linux kernels, many of which will remain in
 production for years.  I have no idea what Windows' memory management
 behavior is.

 So this is a case of needing to know considerably more than the
 available RAM to determine a good setting.

 I agree, but I still think my previous proposal of increasing the
 defaults for work_mem and maintenance_work_mem by 4X would serve many
 more people well than it would serve poorly.  I haven't heard anyone
 disagree with that notion.  Does anyone disagree?  Should we do it?

One source of hesitation for me is that I have a hunch that the stock
assumptions that go into shared_buffers will probably change, possibly
by a lot.  For a lot of (I think very good-) reasons current policy is
to set s_b to max 2GB.  After we nail some of the outstanding
contention issues and make other optimizations I expect that the
optimal setting may be 50% of ram or higher.

Point being: I like the idea of an initdb time setting that takes in
the estimated amount of RAM that is going to be dedicated to the
database.  From there, we then estimate best settings for the various
configurations (perhaps taking in extra hypothetical parameters to
help that job along).  So the anchor should not be s_b, but user
specified.

merlin


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread MauMau

From: Andres Freund and...@2ndquadrant.com

I've seen several sites shutting down because of forgotten prepared
transactions causing bloat and anti-wraparound shutdowns.


From: Magnus Hagander mag...@hagander.net

I would say *using* an external transaction manager *is* the irregular
thing. The current default *is* friendly for normal users, for example
see the comments from Andres about what happens if you make a mistake.
So I definitely agree with your sentiment that we should be more
friendly for normal users - but in this case we are.

If I look through all the customers I've worked with, only a handful
have actually used a transaction manager. And of those, at least half
of them were using it even though they didn't need it, because they
didn't know what it was.


I understand that you mean by *irregular* that there are few people who use 
distributed transactions.  I guess so too: there are not many users who 
require distributed transactions in the real world.  I meant by *irregular* 
that almost all users should use distributed transactions through an 
external transaction manager incorporated in Java EE application servers or 
MSDTC.


The distributed transaction features like XA and Java Transaction API (JTA) 
are established.  They are not irregular for those who need them; they were 
developed and exist for a long time, because they were/are needed.


I don't think the default value of zero for max_prepared_transactions is 
friendly for normal and not-normal users.  Normal users, who properly use 
external transaction manager, won't be caught by the trouble Andres 
mentioned, because the external transaction manager soon resolves prepared 
(in-doubt) transactions.


Not-normal users, who uses PREPARE TRANSACTION statement or 
XAResource.prepare() directly from their applications without using external 
transaction manager or without need based on proper understanding, won't 
escape from Andres's concern.  They will see the following message and 
follow it blindly to make their applications succeed.


ERROR:  prepared transactions are disabled
HINT:  Set max_prepared_transactions to a nonzero value.

So, the current default of zero is not only unfriendly for normal users but 
also non-helpful for those who make mistakes.


Regards
MauMau



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Andrew Dunstan


On 10/09/2013 11:06 AM, Andrew Dunstan wrote:




The assumption that each connection won't use lots of work_mem is also 
false, I think, especially in these days of connection poolers.






Andres has just been politely pointing out to me that my knowledge of 
memory allocators is a little out of date (i.e. by a decade or two), and 
that this memory is not in fact likely to be held for a long time, at 
least on most modern systems. That undermines completely my reasoning above.


Given that, it probably makes sense for us to be rather more liberal in 
setting work_mem that I was suggesting.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Bruce Momjian
On Wed, Oct 16, 2013 at 04:25:37PM -0400, Andrew Dunstan wrote:
 
 On 10/09/2013 11:06 AM, Andrew Dunstan wrote:
 
 
 
 The assumption that each connection won't use lots of work_mem is
 also false, I think, especially in these days of connection
 poolers.
 
 
 
 
 Andres has just been politely pointing out to me that my knowledge
 of memory allocators is a little out of date (i.e. by a decade or
 two), and that this memory is not in fact likely to be held for a
 long time, at least on most modern systems. That undermines
 completely my reasoning above.
 
 Given that, it probably makes sense for us to be rather more liberal
 in setting work_mem that I was suggesting.

Ah, yes, this came up last year (MMAP_THRESHOLD):

http://www.postgresql.org/message-id/20120730161416.gb10...@momjian.us

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Claudio Freire
On Wed, Oct 16, 2013 at 5:30 PM, Bruce Momjian br...@momjian.us wrote:
 On Wed, Oct 16, 2013 at 04:25:37PM -0400, Andrew Dunstan wrote:

 On 10/09/2013 11:06 AM, Andrew Dunstan wrote:
 
 
 
 The assumption that each connection won't use lots of work_mem is
 also false, I think, especially in these days of connection
 poolers.
 
 


 Andres has just been politely pointing out to me that my knowledge
 of memory allocators is a little out of date (i.e. by a decade or
 two), and that this memory is not in fact likely to be held for a
 long time, at least on most modern systems. That undermines
 completely my reasoning above.

 Given that, it probably makes sense for us to be rather more liberal
 in setting work_mem that I was suggesting.

 Ah, yes, this came up last year (MMAP_THRESHOLD):

 http://www.postgresql.org/message-id/20120730161416.gb10...@momjian.us


Beware of depending on that threshold. It varies wildly among platforms.

I've seen implementations with the threshold well above 64MB.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-16 Thread Josh Berkus
On 10/16/2013 01:25 PM, Andrew Dunstan wrote:
 Andres has just been politely pointing out to me that my knowledge of
 memory allocators is a little out of date (i.e. by a decade or two), and
 that this memory is not in fact likely to be held for a long time, at
 least on most modern systems. That undermines completely my reasoning
 above.

Except that Opensolaris and FreeBSD still have the old memory allocation
behavior, as do older Linux kernels, many of which will remain in
production for years.  I have no idea what Windows' memory management
behavior is.

So this is a case of needing to know considerably more than the
available RAM to determine a good setting.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread MauMau

From: Magnus Hagander mag...@hagander.net

On Oct 12, 2013 2:13 AM, MauMau maumau...@gmail.com wrote:

I'm not sure if many use XA features, but I saw the questions and answer

a few times, IIRC.  In the trouble situation, PostgreSQL outputs an
intuitive message like increase max_prepared_transactions, so many users
might possibly have been able to change the setting and solve the problem
themselves without asking for help, feeling stress like Why do I have to
set this?  For example, max_prepared_transactions is called hideous
creature in the following page:


https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t


Anybody who follows that page is screwed anyway. I notice they recommend
running regular VACUUM FULL across the whole database, so it's obvious 
they

know nothing about postgresql. There's nothing we can do about what people
write on random pages around the Internet.


Regular VACUUM FULL is certainly overkill.  Apart from that, having to set 
max_prepared_transactions seems to make PostgreSQL difficult for people with 
that level of knowledge, doesn't it?  I wonder if there are other major 
DBMSs which require marameter configuration and server restart to use 
distributed transactions.





According to the below page, the amount of memory consumed for this is

(770 + 270 * max_locks_per_transaction) * max_prepared_transactions.
With the default setting of maxconnections=100 and
max_locks_per_transaction=64, this is only 180KB.  So the overhead is
negligible.

You are assuming memory is the only overhead. I don't think it is.


Having a quick look at the source code, just setting 
max_prepared_transactions to non-zero seems to produce almost no processing 
overhead.



If the goal is to make PostgreSQL more friendly and run smoothly without

frustration from the start and not perfect tuning, I think
max_prepared_transactions=max_connections is an easy and good item.  If 
the

goal is limited to auto-tuning memory sizes, this improvement can be
treated separately.

Frankly, I think we'd help 1000 times more users of we enabled a few wal
writers by default and jumped the wal level. Mainly so they could run one
off base backup. That's used by orders of magnitude more users than XA.


Agreed.  The default of non-zero max_wal_senders and wal_level  'archive' 
would be beneficial for more users.  Likewise, non-zero 
max_prepared_transactons would improve the impression of PostgreSQL (for 
limited number of users, though), and it wouldn't do any harm.


Regards
MauMau



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 21:41:18 +0900, MauMau wrote:
 Likewise, non-zero max_prepared_transactons would improve the
 impression of PostgreSQL (for limited number of users, though), and it
 wouldn't do any harm.

I've seen several sites shutting down because of forgotten prepared
transactions causing bloat and anti-wraparound shutdowns.

A big, big -1 for changing that default.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread MauMau

From: Dimitri Fontaine dimi...@2ndquadrant.fr

The reason why that parameter default has changed from 5 to 0 is that
some people would mistakenly use a prepared transaction without a
transaction manager. Few only people are actually using a transaction
manager that it's better to have them have to set PostgreSQL.


I guess this problem is not unique to PostgreSQL.  I think PostgreSQL can be 
more friendly for normal users (who use external transaction manager), and 
does not need to be too conservative because of people who do irregular 
things.


Regards
MauMau



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 2:47 PM, MauMau maumau...@gmail.com wrote:
 From: Dimitri Fontaine dimi...@2ndquadrant.fr

 The reason why that parameter default has changed from 5 to 0 is that
 some people would mistakenly use a prepared transaction without a
 transaction manager. Few only people are actually using a transaction
 manager that it's better to have them have to set PostgreSQL.


 I guess this problem is not unique to PostgreSQL.  I think PostgreSQL can be
 more friendly for normal users (who use external transaction manager), and
 does not need to be too conservative because of people who do irregular
 things.

I would say *using* an external transaction manager *is* the irregular
thing. The current default *is* friendly for normal users, for example
see the comments from Andres about what happens if you make a mistake.
So I definitely agree with your sentiment that we should be more
friendly for normal users - but in this case we are.

If I look through all the customers I've worked with, only a handful
have actually used a transaction manager. And of those, at least half
of them were using it even though they didn't need it, because they
didn't know what it was.

But the argument about being friendly for new users should definitely
have us change wal_level and max_wal_senders.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Josh Berkus
On 10/15/2013 05:52 AM, Magnus Hagander wrote:
 But the argument about being friendly for new users should definitely
 have us change wal_level and max_wal_senders.

+1 for having replication supported out-of-the-box aside from pg_hba.conf.

To put it another way: users are more likely to care about replication
than they are about IO overhead on a non-replicated server.  And for the
users who care about IO overhead, they are more likely to much about in
pg.conf *anyway* in order to set a slew of performance-tuning settings.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 10:19:06 -0700, Josh Berkus wrote:
 On 10/15/2013 05:52 AM, Magnus Hagander wrote:
  But the argument about being friendly for new users should definitely
  have us change wal_level and max_wal_senders.
 
 +1 for having replication supported out-of-the-box aside from pg_hba.conf.
 
 To put it another way: users are more likely to care about replication
 than they are about IO overhead on a non-replicated server.  And for the
 users who care about IO overhead, they are more likely to much about in
 pg.conf *anyway* in order to set a slew of performance-tuning settings.

But it will hurt people restoring backups using pg_restore -j. I think
people might be rather dissapointed if that slows down by a factor of
three.

I think we really need to get to the point where we increase the wal
level ondemand...

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote:
 On 2013-10-15 10:19:06 -0700, Josh Berkus wrote:
 On 10/15/2013 05:52 AM, Magnus Hagander wrote:
  But the argument about being friendly for new users should definitely
  have us change wal_level and max_wal_senders.

 +1 for having replication supported out-of-the-box aside from pg_hba.conf.

 To put it another way: users are more likely to care about replication
 than they are about IO overhead on a non-replicated server.  And for the
 users who care about IO overhead, they are more likely to much about in
 pg.conf *anyway* in order to set a slew of performance-tuning settings.

 But it will hurt people restoring backups using pg_restore -j. I think
 people might be rather dissapointed if that slows down by a factor of
 three.

 I think we really need to get to the point where we increase the wal
 level ondemand...

Yeha, there are really two things.

If we can increase wal_level on demand, that would solve one of them.
Turning that into a SIGHUP parameter would be great. I have no idea
how hard it would be. In theory, couldn't we let it be sighup and then
just have do_pg_start_backup() block until all backends have
acknowledged that they are on the new WAL level somehow? (Yes, I
realize this might be a big simplification, but I'm allowed to hope,
no?)

The other problem is max_wal_senders. I think that's a much smaller
problem - setting that one to 5 or so by default shouldn't have a big
impact. But without the wal_level changes, it would also be mostly
pointless...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Andres Freund
On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote:
 On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com wrote:
  On 2013-10-15 10:19:06 -0700, Josh Berkus wrote:
  On 10/15/2013 05:52 AM, Magnus Hagander wrote:
   But the argument about being friendly for new users should definitely
   have us change wal_level and max_wal_senders.
 
  +1 for having replication supported out-of-the-box aside from pg_hba.conf.
 
  To put it another way: users are more likely to care about replication
  than they are about IO overhead on a non-replicated server.  And for the
  users who care about IO overhead, they are more likely to much about in
  pg.conf *anyway* in order to set a slew of performance-tuning settings.
 
  But it will hurt people restoring backups using pg_restore -j. I think
  people might be rather dissapointed if that slows down by a factor of
  three.
 
  I think we really need to get to the point where we increase the wal
  level ondemand...
 
 Yeha, there are really two things.
 
 If we can increase wal_level on demand, that would solve one of them.
 Turning that into a SIGHUP parameter would be great. I have no idea
 how hard it would be. In theory, couldn't we let it be sighup and then
 just have do_pg_start_backup() block until all backends have
 acknowledged that they are on the new WAL level somehow? (Yes, I
 realize this might be a big simplification, but I'm allowed to hope,
 no?)

Depends on what you want to support. For basebackups, that should be
doable with some pullups.
It's unfortunately more complex than that for streaming rep - we really
need persistent standby registration there. Otherwise the wal_level will
fall back to minimal when the standby disconnects which will obviously
break the standby.

 The other problem is max_wal_senders. I think that's a much smaller
 problem - setting that one to 5 or so by default shouldn't have a big
 impact. But without the wal_level changes, it would also be mostly
 pointless...

Well, you currently cannot even set it when the wal_level isn't set
appropriately, but that that should be easy enough to change.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-15 Thread Magnus Hagander
On Tue, Oct 15, 2013 at 7:32 PM, Andres Freund and...@2ndquadrant.com wrote:
 On 2013-10-15 19:29:50 +0200, Magnus Hagander wrote:
 On Tue, Oct 15, 2013 at 7:26 PM, Andres Freund and...@2ndquadrant.com 
 wrote:
  On 2013-10-15 10:19:06 -0700, Josh Berkus wrote:
  On 10/15/2013 05:52 AM, Magnus Hagander wrote:
   But the argument about being friendly for new users should definitely
   have us change wal_level and max_wal_senders.
 
  +1 for having replication supported out-of-the-box aside from pg_hba.conf.
 
  To put it another way: users are more likely to care about replication
  than they are about IO overhead on a non-replicated server.  And for the
  users who care about IO overhead, they are more likely to much about in
  pg.conf *anyway* in order to set a slew of performance-tuning settings.
 
  But it will hurt people restoring backups using pg_restore -j. I think
  people might be rather dissapointed if that slows down by a factor of
  three.
 
  I think we really need to get to the point where we increase the wal
  level ondemand...

 Yeha, there are really two things.

 If we can increase wal_level on demand, that would solve one of them.
 Turning that into a SIGHUP parameter would be great. I have no idea
 how hard it would be. In theory, couldn't we let it be sighup and then
 just have do_pg_start_backup() block until all backends have
 acknowledged that they are on the new WAL level somehow? (Yes, I
 realize this might be a big simplification, but I'm allowed to hope,
 no?)

 Depends on what you want to support. For basebackups, that should be
 doable with some pullups.
 It's unfortunately more complex than that for streaming rep - we really
 need persistent standby registration there. Otherwise the wal_level will
 fall back to minimal when the standby disconnects which will obviously
 break the standby.

I was actually thinking the easier step might not be to do it
dynamically as the standby registers - just allow it to be a SIGHUP
parameter. So you'd still change it in postgresql.conf, but it would
be ok with a reload rather than a restart.

Yes, fully dynamic would be better, so if we could combined those two,
that would make us require nothing for pg_basebackup, and just a
reload for replication slaves. The point being that we wouldn't need
a *restart* at any point - and that alond would be a big improvement.


 The other problem is max_wal_senders. I think that's a much smaller
 problem - setting that one to 5 or so by default shouldn't have a big
 impact. But without the wal_level changes, it would also be mostly
 pointless...

 Well, you currently cannot even set it when the wal_level isn't set
 appropriately, but that that should be easy enough to change.

Yes, it would be a trivial change to allow that parametre to be set
and then just give an error if you try to initiate streaming in that
case.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-14 Thread Robert Haas
On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander mag...@hagander.net wrote:
 On Oct 11, 2013 10:23 PM, 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

 Uh, those are there to limit io and not memory, right? More memory isn't the
 reason to increase them, more io is. For people deploying on modern server
 hardware then yes it's often low, but for all those deploying in virtualized
 environments with io performance reminding you of the 1990ies, I'm not so
 sure it is...

bgwriter_lru_maxpages is clearly related to the size of
shared_buffers, although confusingly it is expressed as a number of
buffers, while shared_buffers is expressed as a quantity of memory.  I
think we might have done better to call the GUC
bgwriter_lru_maxpercent and make it a percentage of shared buffers.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-14 Thread Jim Nasby

On 10/14/13 8:18 AM, Robert Haas wrote:

On Sat, Oct 12, 2013 at 3:07 AM, Magnus Hagander mag...@hagander.net wrote:

On Oct 11, 2013 10:23 PM, 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


Uh, those are there to limit io and not memory, right? More memory isn't the
reason to increase them, more io is. For people deploying on modern server
hardware then yes it's often low, but for all those deploying in virtualized
environments with io performance reminding you of the 1990ies, I'm not so
sure it is...


bgwriter_lru_maxpages is clearly related to the size of
shared_buffers, although confusingly it is expressed as a number of
buffers, while shared_buffers is expressed as a quantity of memory.  I
think we might have done better to call the GUC
bgwriter_lru_maxpercent and make it a percentage of shared buffers.



Also, more memory generally means more filesystem cache which means you can do 
more vacuum work per round.

FWIW, on our 512G servers...

cnuapp_p...@postgres11.obr=# select name, setting from pg_settings where name ~ 
'vacuum_cost';
 name | setting
--+-
 autovacuum_vacuum_cost_delay | 10
 autovacuum_vacuum_cost_limit | -1
 vacuum_cost_delay| 10
 vacuum_cost_limit| 2000
 vacuum_cost_page_dirty   | 10
 vacuum_cost_page_hit | 1
 vacuum_cost_page_miss| 10
(7 rows)

The page_hit cost is intentionally the same as the page_dirty limit because 
writes to the SAN are generally far cheaper than reads that actually hit 
spindles. Of course with the amount of FS cache we have (512G-8G shared buffers 
at most) reads are often very likely to hit the FS cache, but tuning of these 
settings while watching IO stats has shown these settings to be minimally 
disruptive.
--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-13 Thread Dimitri Fontaine
MauMau maumau...@gmail.com writes:
 I understand this problem occurs only when the user configured the
 application server to use distributed transactions, the application server
 crashed between prepare and commit/rollback, and the user doesn't recover
 the application server.  So only improper operation produces the problem.

The reason why that parameter default has changed from 5 to 0 is that
some people would mistakenly use a prepared transaction without a
transaction manager. Few only people are actually using a transaction
manager that it's better to have them have to set PostgreSQL.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-13 Thread Dimitri Fontaine
Magnus Hagander mag...@hagander.net writes:
 Frankly, I think we'd help 1000 times more users of we enabled a few wal
 writers by default and jumped the wal level. Mainly so they could run one
 off base backup. That's used by orders of magnitude more users than XA.

+1, or += default max_wal_senders actually ;-)

My vote would be to have default to at least 3, so that you can run both
a pg_basebackup -Xs (stream) and a standby or a pg_receivexlog in
parallel. Maybe 5 is an even better default.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-13 Thread Andres Freund
On 2013-10-12 09:04:55 +0200, Magnus Hagander wrote:
 Frankly, I think we'd help 1000 times more users of we enabled a few wal
 writers by default and jumped the wal level. Mainly so they could run one
 off base backup. That's used by orders of magnitude more users than XA.

Yes, I've thought about that several times as well. I think it might
actually not be too hard to allow increasing the WAL level dynamically
similar to what we do with full_page_writes. If we then would allow
starting wal senders with a lower wal_level we would get there without
breaking older installations which rely on TRUNCATE  COPY
optimizations et al.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-12 Thread Magnus Hagander
On Oct 12, 2013 2:13 AM, MauMau maumau...@gmail.com wrote:

 From: Bruce Momjian br...@momjian.us

 On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote:

 Although this is not directly related to memory, could you set
 max_prepared_transactions = max_connections at initdb time?  People
 must feel frustrated when they can't run applications on a Java or
 .NET application server and notice that they have to set
 max_prepared_transactions and restart PostgreSQL.  This is far from
 friendly.


 I think the problem is that many users don't need prepared transactions
 and therefore don't want the overhead.  Is that still accurate?


 I'm not sure if many use XA features, but I saw the questions and answer
a few times, IIRC.  In the trouble situation, PostgreSQL outputs an
intuitive message like increase max_prepared_transactions, so many users
might possibly have been able to change the setting and solve the problem
themselves without asking for help, feeling stress like Why do I have to
set this?  For example, max_prepared_transactions is called hideous
creature in the following page:

 https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t

Anybody who follows that page is screwed anyway. I notice they recommend
running regular VACUUM FULL across the whole database, so it's obvious they
know nothing about postgresql. There's nothing we can do about what people
write on random pages around the Internet.

 According to the below page, the amount of memory consumed for this is
(770 + 270 * max_locks_per_transaction) * max_prepared_transactions.
 With the default setting of maxconnections=100 and
max_locks_per_transaction=64, this is only 180KB.  So the overhead is
negligible.

You are assuming memory is the only overhead. I don't think it is.

 If the goal is to make PostgreSQL more friendly and run smoothly without
frustration from the start and not perfect tuning, I think
max_prepared_transactions=max_connections is an easy and good item.  If the
goal is limited to auto-tuning memory sizes, this improvement can be
treated separately.

Frankly, I think we'd help 1000 times more users of we enabled a few wal
writers by default and jumped the wal level. Mainly so they could run one
off base backup. That's used by orders of magnitude more users than XA.

/Magnus


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-12 Thread Magnus Hagander
On Oct 11, 2013 10:23 PM, 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

Uh, those are there to limit io and not memory, right? More memory isn't
the reason to increase them, more io is. For people deploying on modern
server hardware then yes it's often low, but for all those deploying in
virtualized environments with io performance reminding you of the 1990ies,
I'm not so sure it is...

/Magnus


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Magnus Hagander
On Thu, Oct 10, 2013 at 9:41 PM, Christopher Browne cbbro...@gmail.com wrote:
 On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote:
 How do we handle the Python dependency, or is this all to be done in
 some other language?  I certainly am not ready to take on that job.

 I should think it possible to reimplement it in C.  It was considerably
 useful to start by implementing in Python, as that evades various sorts
 of efforts needed in C (e.g. - memory allocation, picking a hash table
 implementation), and allows someone to hack on it without needing to
 run through a recompile every time something is touched.

I think in the context of this problem, reimplementing that from
python to C is the easiest part. Actually figuring out what the tool
should *do* and how it should do it is the hard part.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 10:20:36PM -0700, Josh Berkus wrote:
 Robert,
 
  The counter-proposal to auto-tuning is just to raise the default for
  work_mem to 4MB or 8MB.  Given that Bruce's current formula sets it at
  6MB for a server with 8GB RAM, I don't really see the benefit of going
  to a whole lot of code and formulas in order to end up at a figure only
  incrementally different from a new static default.
  
  Agreed.  But what do you think the value SHOULD be on such a system?
 
 That's the problem: It Depends.
 
 One thing in particular which is an issue with calculating against
 max_connections is that users who don't need 100 connections seldom
 *reduce* max_connections.  So that developer laptop which only needs 3
 connections is still going to have a max_connections of 100, just like
 the DW server where m_c should probably be 30.
 
  I guess the point I'm making here is that raising the default value is
  not mutually exclusive with auto-tuning.  We could quadruple the
  current defaults for work_mem and maintenance_work_mem and be better
  off right now, today.  Then, we could improve things further in the
  future if and when we agree on an approach to auto-tuning.  And people
  who don't use the auto-tuning will still have a better default.
 
 Seems fine to me.

I think we are nearing a conclusion on these issues, and I thank
everyone for the vigorous discussion.  When Josh showed disappointment
at the small increases in work_mem and maintenance_work_mem from
autotuning, I realized the complexity of autotuning just wasn't
warranted here.  Andrew's concern about the risks of having a work_mem
too high was also sobering.  Effective_cache_size has neither of these
issues, and hence was logical for auto-tuning.  I know Robert originally
suggested just improving the work_mem default --- I now agree with him,
and am sorry it took me so long to realize he was right.

One other problem with auto-tuning is that it really relies not only on
allocated_memory, but also on max_connections and
autovacuum_max_workers, which are going to be rather arbitrary and hard
for a user to set good enough to help auto-tuning.  Josh might be right
that auto-tuning of work_mem has to be more dynamic, perhaps based on
the number of _active_ backends or number of backends who have allocate
or are currently using work_mem.  Our new dynamic shared memory
allocation routines might help here in allocationg memory that can be
easily purged from the process address space.  I am now seeing a pattern
that per-backend allocations really need run-time tuning, rather than
being based on fixed GUC values.

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?

I will try to think some more about work_mem dynamic/runtime tuning and
return to it later.  I know Kevin has also thought about it.

I am also interesting in working on a server-side function that will
make configuration suggestions or use ALTER SYSTEM to set values.  I
could do it in PL/pgSQL, but PL/Perl would allow me to run operating
system commands to probe for OS information.  The function could look at
statistics and pg_buffercache output, and would be run during a typical
workload.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Josh Berkus
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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread Kevin Grittner
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.

Regarding the settings which have been the center of attention for
most of this thread, I have had very good luck with starting
work_mem at machine RAM * 0.25 / max_connections.  I get the
impression that Josh regards that as too low.  My guess is that he
deals more with data warehouse reporting systems than I do, where
larger settings are both more beneficial and less likely to cause
memory exhaustion than the typical systems I've worked with.  That
is the big problem with auto-configuration -- it depends so much on
the workload.  In the long run, an admission control policy and/or
adaptive configuration based on the observed workload seems like
what we *really* need.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread MauMau

From: Bruce Momjian br...@momjian.us

On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote:

Although this is not directly related to memory, could you set
max_prepared_transactions = max_connections at initdb time?  People
must feel frustrated when they can't run applications on a Java or
.NET application server and notice that they have to set
max_prepared_transactions and restart PostgreSQL.  This is far from
friendly.


I think the problem is that many users don't need prepared transactions
and therefore don't want the overhead.  Is that still accurate?


I'm not sure if many use XA features, but I saw the questions and answer a 
few times, IIRC.  In the trouble situation, PostgreSQL outputs an intuitive 
message like increase max_prepared_transactions, so many users might 
possibly have been able to change the setting and solve the problem 
themselves without asking for help, feeling stress like Why do I have to 
set this?  For example, max_prepared_transactions is called hideous 
creature in the following page:


https://community.jboss.org/wiki/InstallPostgreSQLOnFedora?_sscc=t

According to the below page, the amount of memory consumed for this is (770 
+ 270 * max_locks_per_transaction) * max_prepared_transactions.  With the 
default setting of maxconnections=100 and max_locks_per_transaction=64, this 
is only 180KB.  So the overhead is negligible.


http://www.postgresql.org/docs/9.2/static/kernel-resources.html

If the goal is to make PostgreSQL more friendly and run smoothly without 
frustration from the start and not perfect tuning, I think 
max_prepared_transactions=max_connections is an easy and good item.  If the 
goal is limited to auto-tuning memory sizes, this improvement can be treated 
separately.


Regards
MauMau









--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-11 Thread MauMau

From: Dimitri Fontaine dimi...@2ndquadrant.fr

MauMau maumau...@gmail.com writes:

Although this is not directly related to memory, could you set
max_prepared_transactions = max_connections at initdb time?  People must


You really need to have a transaction manager around when issuing
prepared transaction as failing to commit/rollback them will prevent
VACUUM and quickly lead you to an interesting situation.


I understand this problem occurs only when the user configured the 
application server to use distributed transactions, the application server 
crashed between prepare and commit/rollback, and the user doesn't recover 
the application server.  So only improper operation produces the problem. 
Just setting max_prepared_transactions to non-zero doesn't cause the 
problem.  Is this correct?


Regards
MauMau



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Wed, Oct 9, 2013 at 11:35 PM, Peter Geoghegan p...@heroku.com wrote:
 On Wed, Oct 9, 2013 at 8:20 PM, Bruce Momjian br...@momjian.us wrote:
 I am not sure that having that external to the backend really makes
 sense because I am concerned people will not use it.  We can certainly
 add it to change our defaults, of course.  Also consider many installs
 are automated.

 Sure.

 I was imagining that we'd want to write the tool with the idea in mind
 that it was usually run immediately after initdb. We'd reach out to
 packagers to have them push it into the hands of users where that's
 practical.

 If you think that sounds odd, consider that on at least one popular
 Linux distro, installing MySQL will show a ncurses interface where the
 mysql password is set. We wouldn't need anything as fancy as that.

I actually had the thought that it might be something we'd integrate
*into* initdb.  So you'd do initdb --system-memory 8GB or something
like that and it would do the rest.  That'd be slick, at least IMHO.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander mag...@hagander.net wrote:
 I think it would be even simpler, and more reliable, to start with the
 parameter to initdb - I like that. But instead of having it set a new
 variable based on that and then autotune off that, just have *initdb*
 do these calculations you're suggesting, and write new defaults to the
 files (preferably with a comment).

 That way if the user *later* comes in and say changes shared_buffers,
 we don't dynamically resize the work_mem into a value that might cause
 his machine to die from swapping which would definitely violate the
 principle of least surprise..

+1 for all of that.  I completely agree.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Peter Geoghegan (p...@heroku.com) wrote:
 On Wed, Oct 9, 2013 at 7:11 PM, Stephen Frost sfr...@snowman.net wrote:
  There is definitely something to be said for simplicity and just up'ing
  the default would have a more dramatic impact with a setting like
  work_mem than it would with shared_buffers, imv.
 
 Simplicity for us or for our users? 

My thinking was 'both', really.

 I wonder if we should just ship something like pgtune (in /bin, not in
 /contrib) that can be optionally used at initdb time. Making something
 like wal_buffers self-tuning is really compelling, but work_mem is
 quite different.

I'm coming around to agree with this also- doing this at initdb time
really makes more sense than during server start-up based on some
(mostly) unrelated value.

 I hear a lot of complaints about the first 15 minutes experience of
 Postgres. It's easy to scoff at this kind of thing, but I think we
 could do a lot better there, and at no real cost - the major blocker
 to doing something like that has been fixed (of course, I refer to the
 SysV shared memory limits). Is the person on a very small box where
 our current very conservative defaults are appropriate? Why not ask a
 few high-level questions like that to get inexperienced users started?

There are certainly challenges here wrt asking questions during install,
as was mentioned elsewhere, but I agree that we could do better.

 The tool could even have a parameter that allows a packager to pass
 total system memory without bothering the user with that, and without
 bothering us with having to figure out a way to make that work
 correctly and portably.

Agreed.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Magnus Hagander (mag...@hagander.net) wrote:
 I think it would be even simpler, and more reliable, to start with the
 parameter to initdb - I like that. But instead of having it set a new
 variable based on that and then autotune off that, just have *initdb*
 do these calculations you're suggesting, and write new defaults to the
 files (preferably with a comment).

Agreed; I especially like having the comment included.

 That way if the user *later* comes in and say changes shared_buffers,
 we don't dynamically resize the work_mem into a value that might cause
 his machine to die from swapping which would definitely violate the
 principle of least surprise..

+1

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread MauMau

From: Bruce Momjian br...@momjian.us

I will work on auto-tuning temp_buffers next.  Any other suggestions?
wal_buffers is already auto-tuned.


Great work.  I'm looking forward to becoming able to fully utilize system 
resources right after initdb.


Although this is not directly related to memory, could you set 
max_prepared_transactions = max_connections at initdb time?  People must 
feel frustrated when they can't run applications on a Java or .NET 
application server and notice that they have to set 
max_prepared_transactions and restart PostgreSQL.  This is far from 
friendly.


Regards
MauMau





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote:

 I actually had the thought that it might be something we'd integrate
 *into* initdb.  So you'd do initdb --system-memory 8GB or something
 like that and it would do the rest.  That'd be slick, at least IMHO.

How would you handle the case that the machine (whether physical or
a VM) later gets more RAM?  That's certainly not unheard of with
physical servers, and with VMs I'm not sure that the database
server would necessarily go through a stop/start cycle for it.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread MauMau

From: Robert Haas robertmh...@gmail.com
On Thu, Oct 10, 2013 at 1:23 AM, Magnus Hagander mag...@hagander.net 
wrote:

I think it would be even simpler, and more reliable, to start with the
parameter to initdb - I like that. But instead of having it set a new
variable based on that and then autotune off that, just have *initdb*
do these calculations you're suggesting, and write new defaults to the
files (preferably with a comment).

That way if the user *later* comes in and say changes shared_buffers,
we don't dynamically resize the work_mem into a value that might cause
his machine to die from swapping which would definitely violate the
principle of least surprise..


+1 for all of that.  I completely agree.


I vote for this idea completely, too.  It's nice to be able to specify 
usable RAM with something like initdb --system-memory 8GB, because it 
provides flexibility for memory allocation --- use the whole machine for one 
PostgreSQL instance, or run multiple instances on one machine with 50% of 
RAM for instance-A and 25% of RAM for instance B and C, etc.  But what is 
the default value of --system-memory?  I would like it to be the whole RAM.


I hope something like pgtune will be incorporated into the core, absorbing 
the ideas in:


- pgtune
- https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
- the book PostgreSQL 9.0 High Performance by Greg Smith

Then initdb calls the tool.  Of course, DBAs can use the tool later.  Like 
pgtune, the tool would be nice if it and initdb can accept --system-type 
or --workload with arguments {OLTP | DW | mixed}.


Regards
MauMau




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:01:52PM +0900, MauMau wrote:
 From: Bruce Momjian br...@momjian.us
 I will work on auto-tuning temp_buffers next.  Any other suggestions?
 wal_buffers is already auto-tuned.
 
 Great work.  I'm looking forward to becoming able to fully utilize
 system resources right after initdb.
 
 Although this is not directly related to memory, could you set
 max_prepared_transactions = max_connections at initdb time?  People
 must feel frustrated when they can't run applications on a Java or
 .NET application server and notice that they have to set
 max_prepared_transactions and restart PostgreSQL.  This is far from
 friendly.

I think the problem is that many users don't need prepared transactions
and therefore don't want the overhead.  Is that still accurate?

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Dimitri Fontaine
MauMau maumau...@gmail.com writes:
 Although this is not directly related to memory, could you set
 max_prepared_transactions = max_connections at initdb time?  People must

You really need to have a transaction manager around when issuing
prepared transaction as failing to commit/rollback them will prevent
VACUUM and quickly lead you to an interesting situation.

It used to have a default of 5 and is now properly defaulting to 0.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote:
 Robert Haas robertmh...@gmail.com wrote:
 
  I actually had the thought that it might be something we'd integrate
  *into* initdb.  So you'd do initdb --system-memory 8GB or something
  like that and it would do the rest.  That'd be slick, at least IMHO.
 
 How would you handle the case that the machine (whether physical or
 a VM) later gets more RAM?  That's certainly not unheard of with
 physical servers, and with VMs I'm not sure that the database
 server would necessarily go through a stop/start cycle for it.

Yes, going from a non-dedicated to a dedicated database server, adding
RAM, or moving the cluster to another server could all require an initdb
to change auto-tuned values.  This is why I think we will need to
auto-tune in the backend, rather than via initdb.  I do think an
available_mem parameter for initdb would help though, to be set in
postgresql.conf.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote:
 On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote:
  Robert Haas robertmh...@gmail.com wrote:
   I actually had the thought that it might be something we'd integrate
   *into* initdb.  So you'd do initdb --system-memory 8GB or something
   like that and it would do the rest.  That'd be slick, at least IMHO.
  
  How would you handle the case that the machine (whether physical or
  a VM) later gets more RAM?  That's certainly not unheard of with
  physical servers, and with VMs I'm not sure that the database
  server would necessarily go through a stop/start cycle for it.
 
 Yes, going from a non-dedicated to a dedicated database server, adding
 RAM, or moving the cluster to another server could all require an initdb
 to change auto-tuned values.  This is why I think we will need to
 auto-tune in the backend, rather than via initdb.  I do think an
 available_mem parameter for initdb would help though, to be set in
 postgresql.conf.

For this case, I think the suggestion made by MauMau would be better-
tell the user (in the postgresql.conf comments) a command they can run
with different memory settings to see what the auto-tuning would do.
Perhaps even have a way to enable use of those new variables, but I
don't really care for the idea of making a GUC that isn't anything
except a control for defaults of *other* GUCs.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Wed, Oct  9, 2013 at 09:34:16PM -0400, Robert Haas wrote:
 But your auto-tuned value can easily be too low or too high, too.
 Consider someone with a system that has 64GB of RAM.   EnterpriseDB
 has had customers who have found that with, say, a 40GB database, it's
 best to set shared_buffers to 40GB so that the database remains fully
 cached.  Your latest formula will auto-tune work_mem to roughly 100MB.
  On the other hand, if the same customer has a 400GB database, which
 can't be fully cached no matter what, a much lower setting for
 shared_buffers, like maybe 8GB, is apt to perform better.  Your
 formula will auto-tune shared_buffers to roughly 20MB.
 
 In other words, when there's only 24GB of memory available for
 everything-except-shared-buffers, your formula sets work_mem five
 times higher than when there's 48GB of memory available for
 everything-except-shared-buffers.  That surely can't be right.

Let me walk through the idea of adding an available_mem setting, that
Josh suggested, and which I think addresses Robert's concern about
larger shared_buffers and Windows servers.

The idea is that initdb would allow you to specify an available_mem
parameter, which would set a corresponding value in postgresql.conf. 
This could be later changed by the user.  (See my other email about why
we shouldn't do the tuning in initdb.)

shared_buffers would auto-tune to 25% of that, except on Windows, and
perhaps capped at 8GB,   Here is another case where not tuning
directly on shared_buffers is a win.

All other calculations would be based on available_mem - shared_buffers,
so if shared_buffers is manually or auto-tuned high or low, other tuning
would still be accurate.

work_mem would tune to (available_mem - shared_buffers) / 16 /
max_connections, so even if you used all max_connections, and 3x of
work_mem in each, you would still only match the size of shared_buffers.
maintenance_work_mem would key on autovacuum_max_workers.

effective_cache_size would be available_mem minus all of the values
above.

Now, how to handle changes?  available_mem could only be changed by a
server restart, because shared_buffers is based on it, and the rest of
the parameters are based on available_mem - shared_buffers.  Though
users can change work_mem in postgresql.conf and per-session,
auto-tuning would not be affected by these changes.  Calculating only
with available_mem - shared_buffers would give stability and
predicability to the auto-tuning system.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote:
 * Bruce Momjian (br...@momjian.us) wrote:
  On Thu, Oct 10, 2013 at 07:24:26AM -0700, Kevin Grittner wrote:
   Robert Haas robertmh...@gmail.com wrote:
I actually had the thought that it might be something we'd integrate
*into* initdb.  So you'd do initdb --system-memory 8GB or something
like that and it would do the rest.  That'd be slick, at least IMHO.
   
   How would you handle the case that the machine (whether physical or
   a VM) later gets more RAM?  That's certainly not unheard of with
   physical servers, and with VMs I'm not sure that the database
   server would necessarily go through a stop/start cycle for it.
  
  Yes, going from a non-dedicated to a dedicated database server, adding
  RAM, or moving the cluster to another server could all require an initdb
  to change auto-tuned values.  This is why I think we will need to
  auto-tune in the backend, rather than via initdb.  I do think an
  available_mem parameter for initdb would help though, to be set in
  postgresql.conf.
 
 For this case, I think the suggestion made by MauMau would be better-
 tell the user (in the postgresql.conf comments) a command they can run
 with different memory settings to see what the auto-tuning would do.
 Perhaps even have a way to enable use of those new variables, but I
 don't really care for the idea of making a GUC that isn't anything
 except a control for defaults of *other* GUCs.

Well, you then have two places you are doing the tuning --- one in
initdb, and another in the tool, and you can have cases where they are
not consistent.  You could have a mode where initdb re-writes
postgresql.conf, but that has all sorts of oddities about changing a
config file.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote:
 On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote:
  For this case, I think the suggestion made by MauMau would be better-
  tell the user (in the postgresql.conf comments) a command they can run
  with different memory settings to see what the auto-tuning would do.
  Perhaps even have a way to enable use of those new variables, but I
  don't really care for the idea of making a GUC that isn't anything
  except a control for defaults of *other* GUCs.
 
 Well, you then have two places you are doing the tuning --- one in
 initdb, and another in the tool, and you can have cases where they are
 not consistent.  You could have a mode where initdb re-writes
 postgresql.conf, but that has all sorts of oddities about changing a
 config file.

Not necessairly..  Have initdb call the tool to get the values to use
when first writing out the config file (or make the logic into a library
that initdb and the tool both use, or just both #include the same .h;
it's not like it's going to be terribly complicated), and then the tool
would be responsible for later changes to the postgresql.conf file, or
we just tell the user how to make the changes recommended by the tool.
I would *not* have initdb doing that, that's not its job.

If the user is expected to be modifying the postgresql.conf file in this
scenario anyway, I hardly see that having one-parameter-to-rule-them-all
is actually better than having 3 or 4.  If we're trying to get away from
the user modifying postgresql.conf, then we're going to need a tool to
do that (or use ALTER SYSTEM WHATEVER).  For my part, I'm really much
more interested in the first 15 minutes, as was mentioned elsewhere,
than how to help users who have been using PG for a year and then
discover they need to tune it a bit.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 11:45:41AM -0400, Stephen Frost wrote:
 * Bruce Momjian (br...@momjian.us) wrote:
  On Thu, Oct 10, 2013 at 11:18:46AM -0400, Stephen Frost wrote:
   For this case, I think the suggestion made by MauMau would be better-
   tell the user (in the postgresql.conf comments) a command they can run
   with different memory settings to see what the auto-tuning would do.
   Perhaps even have a way to enable use of those new variables, but I
   don't really care for the idea of making a GUC that isn't anything
   except a control for defaults of *other* GUCs.
  
  Well, you then have two places you are doing the tuning --- one in
  initdb, and another in the tool, and you can have cases where they are
  not consistent.  You could have a mode where initdb re-writes
  postgresql.conf, but that has all sorts of oddities about changing a
  config file.
 
 Not necessairly..  Have initdb call the tool to get the values to use
 when first writing out the config file (or make the logic into a library
 that initdb and the tool both use, or just both #include the same .h;
 it's not like it's going to be terribly complicated), and then the tool
 would be responsible for later changes to the postgresql.conf file, or
 we just tell the user how to make the changes recommended by the tool.
 I would *not* have initdb doing that, that's not its job.
 
 If the user is expected to be modifying the postgresql.conf file in this
 scenario anyway, I hardly see that having one-parameter-to-rule-them-all
 is actually better than having 3 or 4.  If we're trying to get away from
 the user modifying postgresql.conf, then we're going to need a tool to
 do that (or use ALTER SYSTEM WHATEVER).  For my part, I'm really much
 more interested in the first 15 minutes, as was mentioned elsewhere,
 than how to help users who have been using PG for a year and then
 discover they need to tune it a bit.

Well, I like the idea of initdb calling the tool, though the tool then
would need to be in C probably as we can't require python for initdb. 
The tool would not address Robert's issue of someone increasing
shared_buffers on their own.  In fact, the other constants would still
be hard-coded in from initdb, which isn't good.

I think the big win for a tool would be to query the user about how they
are going to be using Postgres, and that can then spit out values the
user can add to postgresql.conf, or to a config file that is included at
the end of postgresql.conf.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
* Bruce Momjian (br...@momjian.us) wrote:
 Well, I like the idea of initdb calling the tool, though the tool then
 would need to be in C probably as we can't require python for initdb. 
 The tool would not address Robert's issue of someone increasing
 shared_buffers on their own.

I'm really not impressed with this argument.  Either the user is going
to go and modify the config file, in which case I would hope that they'd
at least glance around at what they should change, or they're going to
move off PG because it's not performing well enough for them- which is
really what I'm trying to avoid happening during the first 15m.

 In fact, the other constants would still
 be hard-coded in from initdb, which isn't good.

Actually, it *is* good, as Magnus pointed out.  Changing a completely
unrelated parameter shouldn't make all of your plans suddenly change.
This is mollified, but only a bit, if you have a GUC that's explicitly
this changes other GUCs, but I'd much rather have a tool that can do a
better job to begin with and which helps the user understand what
parameters are available to change and why there's more than one.

 I think the big win for a tool would be to query the user about how they
 are going to be using Postgres, and that can then spit out values the
 user can add to postgresql.conf, or to a config file that is included at
 the end of postgresql.conf.

Agreed.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote:
 * Bruce Momjian (br...@momjian.us) wrote:
  Well, I like the idea of initdb calling the tool, though the tool then
  would need to be in C probably as we can't require python for initdb. 
  The tool would not address Robert's issue of someone increasing
  shared_buffers on their own.
 
 I'm really not impressed with this argument.  Either the user is going
 to go and modify the config file, in which case I would hope that they'd
 at least glance around at what they should change, or they're going to
 move off PG because it's not performing well enough for them- which is
 really what I'm trying to avoid happening during the first 15m.

Well, they aren't going around and looking at other parameters now or we
would not feel a need to auto-tune many of our defaults.

How do we handle the Python dependency, or is this all to be done in
some other language?  I certainly am not ready to take on that job.

One nice thing about a tool is that you can see your auto-tuned defaults
right away, while doing this in the backend, you have to start the
server to see the defaults.  I am not even sure how I could allow users
to preview their defaults for different available_mem settings.

  In fact, the other constants would still
  be hard-coded in from initdb, which isn't good.
 
 Actually, it *is* good, as Magnus pointed out.  Changing a completely
 unrelated parameter shouldn't make all of your plans suddenly change.
 This is mollified, but only a bit, if you have a GUC that's explicitly
 this changes other GUCs, but I'd much rather have a tool that can do a
 better job to begin with and which helps the user understand what
 parameters are available to change and why there's more than one.

Well, the big question is how many users are going to use the tool, as
we are not setting this up for experts, but for novices.

I think one big risk is someone changing shared_buffers and not having
an accurate available_memory.  That might lead to some very inaccurate
defaults.  Also, what happens if available_memory is not supplied at
all?  Do we auto-tune just from shared_buffers, or not autotune at all,
and then what are the defaults?  We could certainly throw an error if
shared_buffers  available_memory.  We might just ship with
available_memory defaulting to 256MB and auto-tune everything from
there.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Andrew Dunstan


On 10/10/2013 12:28 PM, Bruce Momjian wrote:


How do we handle the Python dependency, or is this all to be done in
some other language?  I certainly am not ready to take on that job.



Without considering any wider question here, let me just note this:

Anything that can be done in this area in Python should be doable in 
Perl fairly simply. I don't think we should be adding any Python 
dependencies. For good or ill Perl has been used for pretty much all our 
complex scripting (pgindent, MSVC build system etc.)



cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote:
 
 On 10/10/2013 12:28 PM, Bruce Momjian wrote:
 
 How do we handle the Python dependency, or is this all to be done in
 some other language?  I certainly am not ready to take on that job.
 
 
 Without considering any wider question here, let me just note this:
 
 Anything that can be done in this area in Python should be doable in
 Perl fairly simply. I don't think we should be adding any Python
 dependencies. For good or ill Perl has been used for pretty much all
 our complex scripting (pgindent, MSVC build system etc.)

Yes, but this is a run-time requirement, not build-time, and we have not
used Perl in that regard.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Andrew Dunstan


On 10/10/2013 12:45 PM, Bruce Momjian wrote:

On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote:

On 10/10/2013 12:28 PM, Bruce Momjian wrote:

How do we handle the Python dependency, or is this all to be done in
some other language?  I certainly am not ready to take on that job.


Without considering any wider question here, let me just note this:

Anything that can be done in this area in Python should be doable in
Perl fairly simply. I don't think we should be adding any Python
dependencies. For good or ill Perl has been used for pretty much all
our complex scripting (pgindent, MSVC build system etc.)

Yes, but this is a run-time requirement, not build-time, and we have not
used Perl in that regard.




Nor Python. If we want to avoid added dependencies, we would need to use C.

cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 12:59:39PM -0400, Andrew Dunstan wrote:
 
 On 10/10/2013 12:45 PM, Bruce Momjian wrote:
 On Thu, Oct 10, 2013 at 12:39:04PM -0400, Andrew Dunstan wrote:
 On 10/10/2013 12:28 PM, Bruce Momjian wrote:
 How do we handle the Python dependency, or is this all to be done in
 some other language?  I certainly am not ready to take on that job.
 
 Without considering any wider question here, let me just note this:
 
 Anything that can be done in this area in Python should be doable in
 Perl fairly simply. I don't think we should be adding any Python
 dependencies. For good or ill Perl has been used for pretty much all
 our complex scripting (pgindent, MSVC build system etc.)
 Yes, but this is a run-time requirement, not build-time, and we have not
 used Perl in that regard.
 
 
 
 Nor Python. If we want to avoid added dependencies, we would need to use C.

Yeah.  :-(  My crazy idea would be, because setting setting
available_mem without a restart would not be supported, to allow the
backend to output suggestions, e.g.:

test= SHOW available_mem = '24GB';
Auto-tuning values:
shared_buffers = 6GB
work_mem = 10MB
...
ERROR:  parameter available_mem cannot be changed without restarting 
the server

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus

 Because 'maintenance' operations were rarer, so we figured we could use
 more memory in those cases.

Once we brought Autovacuum into core, though, we should have changed that.

However, I agree with Magnus that the simple course is to have an
autovacuum_worker_memory setting which overrides maint_work_mem if set.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
On 10/09/2013 02:15 PM, Bruce Momjian wrote:
 and for shared_buffers of 2GB:
 
   test= show shared_buffers;
shared_buffers
   
2GB
   (1 row)
   
   test= SHOW work_mem;
work_mem
   --
6010kB
   (1 row)

Huh?  Only 6MB work_mem for 8GB RAM?  How'd you get that?

That's way low, and frankly it's not worth bothering with this if all
we're going to get is an incremental increase.  In that case, let's just
set the default to 4MB like Robert suggested.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Bruce Momjian
On Thu, Oct 10, 2013 at 10:20:02AM -0700, Josh Berkus wrote:
 On 10/09/2013 02:15 PM, Bruce Momjian wrote:
  and for shared_buffers of 2GB:
  
  test= show shared_buffers;
   shared_buffers
  
   2GB
  (1 row)
  
  test= SHOW work_mem;
   work_mem
  --
   6010kB
  (1 row)
 
 Huh?  Only 6MB work_mem for 8GB RAM?  How'd you get that?

 That's way low, and frankly it's not worth bothering with this if all
 we're going to get is an incremental increase.  In that case, let's just
 set the default to 4MB like Robert suggested.

Uh, well, 100 backends at 6MB gives us 600MB, and if each backend uses
3x work_mem, that gives us 1.8GB for total work_mem.  This was based on
Andrew's concerns about possible over-commit of work_mem.  I can of
course adjust that.

Consider 8GB of shared memory is 21MB.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
All,

We can't reasonably require user input at initdb time, because most
users don't run initdb by hand -- their installer does it for them.  So
any tuning which initdb does needs to be fully automated.

So, the question is: can we reasonably determine, at initdb time, how
much RAM the system has?

I also think this is where the much-debated ALTER SYSTEM SET suddenly
becomes valuable.  With it, it's reasonable to run a tune-up tool on
the client side.  I do think it's reasonable to tell a user:

Just installed PostgreSQL?  Run this command to tune your system:

Mind you, the tuneup tool I'm working on makes use of Python,
configuration directory, and Jinga2, so it's not even relevant to the
preceeding.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Stephen Frost
Bruce,

* Bruce Momjian (br...@momjian.us) wrote:
 On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote:
  I'm really not impressed with this argument.  Either the user is going
  to go and modify the config file, in which case I would hope that they'd
  at least glance around at what they should change, or they're going to
  move off PG because it's not performing well enough for them- which is
  really what I'm trying to avoid happening during the first 15m.
 
 Well, they aren't going around and looking at other parameters now or we
 would not feel a need to auto-tune many of our defaults.

I think you're confusing things here.  There's a huge difference between
didn't configure anything and got our defaults and went and changed
only one thing in postgresql.conf.  For one thing, we have a ton of the
former.  Perhaps there are some of the latter as well, but I would argue
it's a pretty small group.

 How do we handle the Python dependency, or is this all to be done in
 some other language?  I certainly am not ready to take on that job.

I agree that we can't add a Python (or really, perl) dependency, but I
don't think there's anything terribly complicated in what pgtune is
doing that couldn't be pretty easily done in C..

 One nice thing about a tool is that you can see your auto-tuned defaults
 right away, while doing this in the backend, you have to start the
 server to see the defaults.  I am not even sure how I could allow users
 to preview their defaults for different available_mem settings.

Agreed.

  Actually, it *is* good, as Magnus pointed out.  Changing a completely
  unrelated parameter shouldn't make all of your plans suddenly change.
  This is mollified, but only a bit, if you have a GUC that's explicitly
  this changes other GUCs, but I'd much rather have a tool that can do a
  better job to begin with and which helps the user understand what
  parameters are available to change and why there's more than one.
 
 Well, the big question is how many users are going to use the tool, as
 we are not setting this up for experts, but for novices.

The goal would be to have the distros and/or initdb use it for the
initial configuration..  Perhaps by using debconf or similar to ask the
user, perhaps by just running it and letting it do whatever it wants.

 I think one big risk is someone changing shared_buffers and not having
 an accurate available_memory.  That might lead to some very inaccurate
 defaults.  Also, what happens if available_memory is not supplied at
 all?  Do we auto-tune just from shared_buffers, or not autotune at all,
 and then what are the defaults?  We could certainly throw an error if
 shared_buffers  available_memory.  We might just ship with
 available_memory defaulting to 256MB and auto-tune everything from
 there.

These questions are less of an issue if we simply don't have this
available_memory GUC (which strikes me as just adding more confusion
for users anyway, not less).  If no '--available-memory' (or whatever)
option is passed to initdb then we should have it assume some default,
yes, but my view on what that is depends on what the specific results
are.  It sounds like --avail-memory=256MB would end up setting things to
about what we have now for defaults, which is alright for
shared_buffers, imv, but not for a default work_mem (1MB is *really*
small...).

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Jeff Janes
On Wed, Oct 9, 2013 at 8:06 AM, Andrew Dunstan and...@dunslane.net wrote:


 On 10/09/2013 10:45 AM, Bruce Momjian wrote:

 On Wed, Oct  9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:

  Effectively, if every session uses one full work_mem, you end up
 with
  total work_mem usage equal to shared_buffers.

  We can try a different algorithm to scale up work_mem, but it seems
 wise
  to auto-scale it up to some extent based on shared_buffers.


 In my experience a optimal value of work_mem depends on data and load,
 so I
 prefer a work_mem as independent parameter.

 But it still is an independent parameter.  I am just changing the default.


 The danger with work_mem especially is that setting it too high can lead
 to crashing postgres or your system at some stage down the track, so
 autotuning it is kinda dangerous, much more dangerous than autotuning
 shared buffers.



Is this common to see?  I ask because in my experience, having 100
connections all decide to do large sorts simultaneously is going to make
the server fall over, regardless of whether it tries to do them in memory
(OOM) or whether it does them with tape sorts (stuck spin locks, usually).



 The assumption that each connection won't use lots of work_mem is also
 false, I think, especially in these days of connection poolers.


I don't follow that.  Why would using a connection pooler change the
multiples of work_mem that a connection would use?

Cheers,

Jeff


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
Bruce,

 That's way low, and frankly it's not worth bothering with this if all
 we're going to get is an incremental increase.  In that case, let's just
 set the default to 4MB like Robert suggested.
 
 Uh, well, 100 backends at 6MB gives us 600MB, and if each backend uses
 3x work_mem, that gives us 1.8GB for total work_mem.  This was based on
 Andrew's concerns about possible over-commit of work_mem.  I can of
 course adjust that.

That's worst-case-scenario planning -- the 3X work-mem per backend was:
a) Solaris and
b) data warehousing

In a normal OLTP application each backend averages something like 0.25 *
work_mem, since many queries use no work_mem at all.

It also doesn't address my point that, if we are worst-case-scenario
default-setting, we're going to end up with defaults which aren't
materially different from the current defaults.  In which case, why even
bother with this whole exercise?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus

 It also doesn't address my point that, if we are worst-case-scenario
 default-setting, we're going to end up with defaults which aren't
 materially different from the current defaults.  In which case, why even
 bother with this whole exercise?

Oh, and let me reiterate: the way to optimize work_mem is through an
admission control mechanism.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote:
 On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote:
 * Bruce Momjian (br...@momjian.us) wrote:
  Well, I like the idea of initdb calling the tool, though the tool then
  would need to be in C probably as we can't require python for initdb.
  The tool would not address Robert's issue of someone increasing
  shared_buffers on their own.

 I'm really not impressed with this argument.  Either the user is going
 to go and modify the config file, in which case I would hope that they'd
 at least glance around at what they should change, or they're going to
 move off PG because it's not performing well enough for them- which is
 really what I'm trying to avoid happening during the first 15m.

 Well, they aren't going around and looking at other parameters now or we
 would not feel a need to auto-tune many of our defaults.

 How do we handle the Python dependency, or is this all to be done in
 some other language?  I certainly am not ready to take on that job.

I don't see why it can't be done in C.  The server is written in C,
and so is initdb.  So no matter where we do this, it's gonna be in C.
Where does Python enter into it?

What I might propose is that we have add a new binary tunedb, maybe
compiled out of the src/bin/initdb.c directory.  So you can say:

initdb --available-memory=32GB

...and it will initialize the cluster with appropriate settings.  Or
you can say:

tunedb --available-memory=32GB

...and it will print out a set of proposed configuration settings.  If
we want a mode that rewrites the configuration file, we could have:

tunedb --available-memory=32GB --rewrite-config-file=$PATH

...but that might be overkill, at least for version 1.

 One nice thing about a tool is that you can see your auto-tuned defaults
 right away, while doing this in the backend, you have to start the
 server to see the defaults.  I am not even sure how I could allow users
 to preview their defaults for different available_mem settings.

Yep, agreed.  And agreed that not being able to preview settings is a problem.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus j...@agliodbs.com wrote:
 So, the question is: can we reasonably determine, at initdb time, how
 much RAM the system has?

As long as you are willing to write platform-dependent code, yes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 11:41 AM, Robert Haas robertmh...@gmail.com wrote:
 I don't see why it can't be done in C.  The server is written in C,
 and so is initdb.  So no matter where we do this, it's gonna be in C.
 Where does Python enter into it?

I mentioned that pgtune was written in Python, but as you say that's
wholly incidental. An equivalent C program would only be slightly more
verbose.


-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Thu, Oct 10, 2013 at 11:43 AM, Robert Haas robertmh...@gmail.com wrote:
 On Thu, Oct 10, 2013 at 1:37 PM, Josh Berkus j...@agliodbs.com wrote:
 So, the question is: can we reasonably determine, at initdb time, how
 much RAM the system has?

 As long as you are willing to write platform-dependent code, yes.

That's why trying to give the responsibility to a packager is compelling.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Eisentraut
On 10/10/13 11:31 AM, Bruce Momjian wrote:
 Let me walk through the idea of adding an available_mem setting, that
 Josh suggested, and which I think addresses Robert's concern about
 larger shared_buffers and Windows servers.

I think this is a promising idea.  available_mem could even be set
automatically by packages.  And power users could just set available_mem
= -1 to turn off all the magic.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Robert Haas
On Thu, Oct 10, 2013 at 2:45 PM, Josh Berkus j...@agliodbs.com wrote:
 On 10/10/2013 11:41 AM, Robert Haas wrote:
 tunedb --available-memory=32GB

 ...and it will print out a set of proposed configuration settings.  If
 we want a mode that rewrites the configuration file, we could have:

 tunedb --available-memory=32GB --rewrite-config-file=$PATH

 ...but that might be overkill, at least for version 1.

 Given that we are talking currently about ALTER SYSTEM SET *and*
 configuration directories, we should not be rewriting any existing
 config file.  We should be adding an auto-generated one, or using ALTER
 SYSTEM SET.

 In fact, why don't we just do this though ALTER SYSTEM SET?  add a
 plpgsql function called pg_tune().

That's another way to do it, for sure.  It does require the ability to
log in to the database.  I imagine that could be less convenient in
some scripting environments.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Magnus Hagander
On Thu, Oct 10, 2013 at 8:41 PM, Robert Haas robertmh...@gmail.com wrote:
 On Thu, Oct 10, 2013 at 12:28 PM, Bruce Momjian br...@momjian.us wrote:
 On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote:
 * Bruce Momjian (br...@momjian.us) wrote:
  Well, I like the idea of initdb calling the tool, though the tool then
  would need to be in C probably as we can't require python for initdb.
  The tool would not address Robert's issue of someone increasing
  shared_buffers on their own.

 I'm really not impressed with this argument.  Either the user is going
 to go and modify the config file, in which case I would hope that they'd
 at least glance around at what they should change, or they're going to
 move off PG because it's not performing well enough for them- which is
 really what I'm trying to avoid happening during the first 15m.

 Well, they aren't going around and looking at other parameters now or we
 would not feel a need to auto-tune many of our defaults.

 How do we handle the Python dependency, or is this all to be done in
 some other language?  I certainly am not ready to take on that job.

 I don't see why it can't be done in C.  The server is written in C,
 and so is initdb.  So no matter where we do this, it's gonna be in C.
 Where does Python enter into it?

 What I might propose is that we have add a new binary tunedb, maybe
 compiled out of the src/bin/initdb.c directory.  So you can say:

 initdb --available-memory=32GB

 ...and it will initialize the cluster with appropriate settings.  Or
 you can say:

 tunedb --available-memory=32GB

 ...and it will print out a set of proposed configuration settings.  If
 we want a mode that rewrites the configuration file, we could have:

 tunedb --available-memory=32GB --rewrite-config-file=$PATH

 ...but that might be overkill, at least for version 1.

I like this. And I agree that the edit-in-place might be overkill. But
then, if/when we get the ability to programatically modify the config
files, that's probably not a very complicated thing to add once the
rest is done.


 One nice thing about a tool is that you can see your auto-tuned defaults
 right away, while doing this in the backend, you have to start the
 server to see the defaults.  I am not even sure how I could allow users
 to preview their defaults for different available_mem settings.

 Yep, agreed.  And agreed that not being able to preview settings is a problem.

I'd even say it would be a *big* problem.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Josh Berkus
On 10/10/2013 11:41 AM, Robert Haas wrote:
 tunedb --available-memory=32GB
 
 ...and it will print out a set of proposed configuration settings.  If
 we want a mode that rewrites the configuration file, we could have:
 
 tunedb --available-memory=32GB --rewrite-config-file=$PATH
 
 ...but that might be overkill, at least for version 1.

Given that we are talking currently about ALTER SYSTEM SET *and*
configuration directories, we should not be rewriting any existing
config file.  We should be adding an auto-generated one, or using ALTER
SYSTEM SET.

In fact, why don't we just do this though ALTER SYSTEM SET?  add a
plpgsql function called pg_tune().

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Geoghegan
On Wed, Oct 9, 2013 at 10:21 PM, Magnus Hagander mag...@hagander.net wrote:
 Well, the Postgres defaults won't really change, because the default
 vacuum_work_mem will be -1, which will have vacuum defer to
 maintenance_work_mem. Under this scheme, vacuum only *prefers* to get
 bound working memory size from vacuum_work_mem. If you don't like
 vacuum_work_mem, you can just ignore it.

 While unrelated to the main topic of this thread, I think this is very
 important as well. I often have to advice people to remember to cap
 their maintenance_work_mem because of autovacuum, and to remember to
 re-tune maintenance_wokr_mem when they change the number of autovacuum
 workers.

I'll code that up at some point, then.

-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Auto-tuning work_mem and maintenance_work_mem

2013-10-10 Thread Peter Eisentraut
On 10/10/13 11:45 AM, Bruce Momjian wrote:
 I think the big win for a tool would be to query the user about how they
 are going to be using Postgres, and that can then spit out values the
 user can add to postgresql.conf, or to a config file that is included at
 the end of postgresql.conf.

I think such a tool would actually make the initial experience worse for
many people.  Quick, how are you going to use your PostgreSQL server:

- OLTP
- web
- mixed

Uh, all of the above?  This sort of thing can quickly turn the first 15
minutes into the first 2 hours, as users are forced to analyze the
different settings, have second thoughts, wonder about how to change
them back, etc.  The fewer decisions people have to make initially, the
better.  The initdb phase already has too many required decisions that
can cause regret later (e.g., locale, encoding, checksums).



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


  1   2   >