Re: [HACKERS] Overhauling GUCS

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Not surprising really. It is a simple adjustment to make and it also is
> easy to spot when its a problem. However it is not trivial to test for
> (in terms of time and effort). I know 10 is wrong and so do you.

Sure.  But what is right?  I'm afraid to just push it to (say) 100
because of the possibility of O(N^2) behavior in eqjoinsel.  Somebody
needs to do some measurements on somewhat realistic 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] Overhauling GUCS

[Joshua D. Drake]

On Fri, 2008-06-06 at 20:19 -0400, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:

> Actually, the reason it's still 10 is that the effort expended to get it
> changed has been *ZERO*.  I keep asking for someone to make some
> measurements, do some benchmarking, anything to make a plausible case
> for a specific higher value as being a reasonable place to set it.

> The silence has been deafening.

Not surprising really. It is a simple adjustment to make and it also is
easy to spot when its a problem. However it is not trivial to test for
(in terms of time and effort). I know 10 is wrong and so do you. If you
don't I am curious why I see so many posts from you saying, "Your
estimates are off, what is your default_statistics_target?" with yet
even more responses saying, "Uhh 10." 


Sincerely,

Joshua D. Drake




-- 
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] Overhauling GUCS

[Greg Smith]

On Fri, 6 Jun 2008, Tom Lane wrote:


Well, you can't see the default or reset values in pg_settings, only the
current value.  However, I fail to see the use of either of those for
a configure wizard.


I'm under the impression that the primary reason to put the default in 
there is to make it easier for a file generator program to be decoupled a 
bit from the internal representation.  Regardless, these values should be 
exposed for tool writers.  If you build a prototype interface for an 
interactive settings changing tool, you quickly discover that showing the 
default, range, and recommended setting are all valuable things people 
would like to see when deciding what the change a setting to.  And there's 
no reason accumulating all that info should be the responsibility of a 
tool writer when it's easy to expose and keep up to date inside the 
database itself.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
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] Overhauling GUCS

[Tom Lane]

Greg Smith <[EMAIL PROTECTED]> writes:
> On Fri, 6 Jun 2008, Gregory Stark wrote:
>> "Greg Smith" <[EMAIL PROTECTED]> writes:
>>> 1) Is it worthwhile to expand the information stored in the GUC structure to
>>> make it better capable of supporting machine generation and to provide more
>>> information for tool authors via pg_settings?  The exact fields that should 
>>> or
>>> shouldn't be included remains controversial; consider "default value",
>>> "per-session/runtime/restart", and "enum lists" as the list of things that 
>>> are
>>> most needed there.
>> 
>> Isn't that a list of what's *already* there?

> I should have been clearer there.  Some of the items suggested are already 
> in the structure, but aren't visible via pg_settings.

Well, you can't see the default or reset values in pg_settings, only the
current value.  However, I fail to see the use of either of those for
a configure wizard.  It'll presumably be attached to a fresh connection
so the reset value is not different from the current; and any decent
wizard is going to know perfectly well what the defaults are.

> ... In others (like the 
> suggestion to add a URL to the documentation) it is actually a new field 
> being added as well as its corresponding entry in the settings view.

Offhand I would argue that we should choose the URLs in such a way that
they can be derived automatically if you know the PG version number and
GUC variable name.  (We're almost there already, except that you have
to know which section of the chapter it's in.)  So the need to have a
pg_settings column seems questionable --- seems like it would mostly
be clutter.  Perhaps instead of a view column, it would be useful to
encapsulate the algorithm as a function?  pg_variable_help_url(name)

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] Overhauling GUCS

[Tom Lane]

Robert Treat <[EMAIL PROTECTED]> writes:
> There is a saying, something like "The accumulation of annecdotes is not 
> data".  Well, we seem to have a high bar on what proof we need to actually 
> change a default GUC settings. default_statistics_target is a prime example, 
> where almost no one i know has ever recommended 10 as a default, or suggests 
> setting it to 10 as an way to improve performance, but the effort to get it 
> changed to something more reasonable has been monumental. 

Actually, the reason it's still 10 is that the effort expended to get it
changed has been *ZERO*.  I keep asking for someone to make some
measurements, do some benchmarking, anything to make a plausible case
for a specific higher value as being a reasonable place to set it.

The silence has been deafening.

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] Overhauling GUCS

[Joshua D. Drake]

On Sat, 2008-06-07 at 01:30 +0200, Andreas Pflug wrote:
> Gregory Stark wrote:
> > "Andreas Pflug" <[EMAIL PROTECTED]> writes:

> I think I made my point very clear when stating "not a file, but via
> SQL". Though I'm not a native English speaker, and I'm sure you
> understood. I must assume you're polluting this thread deliberately in
> order to sabotage the original intention of this thread. I find this
> disgusting.
> 
> Ok, trying to contribute gui tools for pgsql is for masochists. We have
> vi, sed and grep, no more tools required, right?

Hold on guys, let's not get all out of whack. No one in their right mind
is going to suggest that vi, sed and grep are the only tools we need to
do this.

However a GUI wizard isn't really a way to go either. SQL? Yes it would
be very nice to be able to:

BEGIN;
SET shared_buffers to 65536 COMMENT IS 'Since I have 4G of ram I want
512 megs of shared buffers';
SET effective_cache_size to 262144 COMMENT IS 'Since I have 4G of ram I
want 1 gig of effective_cache';
COMMIT;

When that is done, it would write out the postgresql.conf.

Regardless of all of this, this thread is way out of hand. We went from
overhauling the configuration file to wizards, sample postgresql.conf
files and possibly even half of us migrating to MySQL :P.

Can we refine the topic a bit?

Joshua D. Drake




-- 
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] Overhauling GUCS

[Andreas Pflug]

Gregory Stark wrote:
> "Andreas Pflug" <[EMAIL PROTECTED]> writes:
> 
>> I personally wouldn't even think about starting such a wizard, unless I have 
>> an
>> idea how to push the result into the database. No, not a file, but via SQL! 
>> So
>> your statement you won't react unless a wizard is almost ready is 
>> prohibitive,
>> apart from the fact that not only wizards (featuring AI) are interesting, but
>> simple config tools as well.
> 
> Well there's a perfectly good place to start today. Dump out a config file

I think I made my point very clear when stating "not a file, but via
SQL". Though I'm not a native English speaker, and I'm sure you
understood. I must assume you're polluting this thread deliberately in
order to sabotage the original intention of this thread. I find this
disgusting.

Ok, trying to contribute gui tools for pgsql is for masochists. We have
vi, sed and grep, no more tools required, right?

Regards,
Andreas

-- 
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] Overhauling GUCS

[Chris Browne]

[EMAIL PROTECTED] (Greg Smith) writes:
> On Fri, 6 Jun 2008, Heikki Linnakangas wrote:
>
>> Or perhaps we should explicitly mark the settings the tool has
>> generated, and comment out:
>>
>> #shared_buffers = 32MB   # commented out by wizard on 2008-06-05
>> shared_buffers = 1024MB  # automatically set by wizard on 2008-06-05
>
> What I would like to do is make the tool spit out a revision history
> in the same way I find all "big IT" shops handling this already:  by
> putting a revision history style commentary above the current setting.
> Here's a sample:
>
> # 2008-03-02 : 32MB : postgres : Database default
> # 2008-05-02 : 512MB : pg_autotune : Wizard update
> # 2008-05-15 : 1024MB : gsmith : Increased after benchmark tests
> shared_buffers = 1024MB
>
> If the first tuning tool that comes into existance used this format,
> and the format was reasonable, I think it would be possible to get
> people making manual edits to adopt it as well.

My first reaction to this is...

 "And why is it that you seem to imagine it a good idea to recreate
  RCS, badly?"

While that may be an unkind reaction, I don't think it's a
particularly wrong one!

Getting into "comment formatting" certainly *does* seem like an
attempt to do, poorly, what RCS does.  And I am pointing at a tool
from 1985 as opposed to "modern" ones to point out that there were
better approaches to managing versioning of simple text documents
*THIRTY YEARS AGO*.

Indeed, CVS, which we are still using doesn't fundamentally do
anything, in this context, that RCS didn't.

I kind of wish that there was some sort of library for "doing RCS";
even in the absence of that, it would be perfectly logical for a tool
that manages this to DEMAND that there be a "diff" utility or some
equivalent available.

In the absence of that, let me suggest that it would be preferable to
follow VMS's lead and, if you "automagically" make any changes, create
a version called "postgresql.conf;[n]" where "[n]" is either:
  a) 1, if "postgresql.conf;1" does not exist, or
  b) 1 + the largest integer found.

Replace ";" with some other character as need be.

Add a line (or a few lines) at the top of the current version that
indicates some bit of commented-out-metadata that is obviously
formatted and easily recognizable such as:

### [pgauto] postgresql.conf-autotuner - ran 2008-07-02
### [pgauto] little bit of safely-turfable header
### [pgauto] further little bit of safely-turfable header

In effect, what it does is thus:

 - Keep the old copy as postgresql.conf;24  (because we have some 24 elder 
versions)
 - Strips off any leading lines that begin with "### [pgauto]"
 - Puts any interesting comments in a set of leading lines that begin with "### 
[pgauto]"

Merits, over some wacky rewrite-comment-formatting:
 - This requires only the thinnest bit of "special handling" of comments.
 - It doesn't force in a dependancy on [pick-someone's-pet-SCM that you happen 
to hate]
 - Even in the absence of an SCM, it keeps versioning information around
 - If you have a "diff," it'll work perfectly well, and be reasonably 
informative
 - It doesn't throw ANY old data away!

Sorry, that's more than enough rant for today!  :-)
-- 
"cbbrowne","@","linuxdatabases.info"
http://cbbrowne.com/info/rdbms.html
Rules of the Evil Overlord  #220. "Whatever my one vulnerability is, I
will fake a  different one. For example, ordering  all mirrors removed
from the palace, screaming and flinching whenever someone accidentally
holds up a mirror, etc. In the climax when the hero whips out a mirror
and thrusts it at my face,  my reaction will be ``Hmm...I think I need
a shave.''"  

-- 
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] New DTrace probes proposal

[Robert Lor]

Robert Treat wrote:


While it would be nice to have a clean merge of the two, it's probably simple enough to just 
re-implement the differences into your patch (since yours already compiles on 8.4).

Should be straightforward ... I can do the merge.
  As far as naming scheme, I'm not particularly wedded to either... is 
there a dtrace naming convention that could be followed? 
  
Yep, and the probes I submitted  pretty much follow the suggested naming 
convention.


-Robert


--
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] log_filename()

[Alvaro Herrera]

Joshua D. Drake wrote:
> We got the comment on the docs:
> 
> log_filename(string) is misleading, since it really doesn't use a
> strftime pattern, but instead a reimplementation of strftime, in order
> to be cross-platform. There is no documentation on this except to look
> in src/timezone/strftime.c (the function is called pg_strftime)

Correct.  I propose the attached patch.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: config.sgml
===
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.179
diff -c -p -r1.179 config.sgml
*** config.sgml	19 May 2008 18:08:15 -	1.179
--- config.sgml	6 Jun 2008 21:52:45 -
*** local0.*/var/log/postgresql
*** 2428,2435 
  log_filename were server_log,
  then the chosen file name would be server_log.1093827753
  for a log starting at Sun Aug 29 19:02:33 2004 MST.
! This parameter can only be set in the postgresql.conf
! file or on the server command line.
 
 
  If CSV-format output is enabled in log_destination,
--- 2428,2435 
  log_filename were server_log,
  then the chosen file name would be server_log.1093827753
  for a log starting at Sun Aug 29 19:02:33 2004 MST.
! Note that the system's strftime is not used
! directly, so platform-specific (nonstandard) extensions do not work.
 
 
  If CSV-format output is enabled in log_destination,
*** local0.*/var/log/postgresql
*** 2440,2445 
--- 2440,2449 
  In the case of the example above, the CSV
  file name will be server_log.1093827753.csv.
 
+
+ This parameter can only be set in the postgresql.conf
+ file or on the server command line.
+

   
  

-- 
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] TODO, FAQs to Wiki?

[Alvaro Herrera]

Bruce Momjian wrote:

> Magnus has started moving the Developer's FAQ to a wiki.  I am thinking
> we should move the main FAQ and the TODO list to a wiki as well if the
> community is in agreement.

Discussion with you and Magnus indicated that you were both committed to
having the TODO on the wiki, but each was waiting on the other for
anything to happen.  Now that the PGCon dust has been settled for quite
a while, should we proceed with that plan?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Overhauling GUCS

[Greg Smith]

On Fri, 6 Jun 2008, Gregory Stark wrote:


"Greg Smith" <[EMAIL PROTECTED]> writes:


1) Is it worthwhile to expand the information stored in the GUC structure to
make it better capable of supporting machine generation and to provide more
information for tool authors via pg_settings?  The exact fields that should or
shouldn't be included remains controversial; consider "default value",
"per-session/runtime/restart", and "enum lists" as the list of things that are
most needed there.


Isn't that a list of what's *already* there?


I should have been clearer there.  Some of the items suggested are already 
in the structure, but aren't visible via pg_settings. In those cases it's 
just exporting information that's already there.  In others (like the 
suggestion to add a URL to the documentation) it is actually a new field 
being added as well as its corresponding entry in the settings view.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
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] Overhauling GUCS

[Robert Lor]

Robert Treat wrote:

On Wednesday 04 June 2008 22:04:54 Greg Smith wrote:
  

I was just talking to someone today about building a monitoring tool for
this.  Not having a clear way to recommend people monitor use of work_mem
and its brother spilled to disk sorts is an issue right now, I'll whack
that one myself if someone doesn't beat me to it before I get time.



I remember *years* ago, someone wrote a perl script to poll pgsql_tmp and 
print out anytime something showed up... you could probably find that in the 
archives if you look around. 


of course to me this sounds like an excellent idea for a dtrace probe ;-)

  


Actually, you can find out from the sort-end probe now whether or not 
the sort spilled to disk and number of disk blocks used. This is one of 
the probes from Simon.


TRACE_POSTGRESQL_SORT_END(state->tapeset,
   (state->tapeset ? 
LogicalTapeSetBlocks(state->tapeset) :
   (state->allowedMem - state->availMem + 1023) / 
1024));


-Robert

--
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] Overhauling GUCS

[Andreas 'ads' Scherbaum]

On Thu, 05 Jun 2008 12:53:55 -0700 Ron Mayer wrote:

> Steve Atkins wrote:
> > ... cross-platform (Windows, Linux, Solaris, OS X as a bare
> > minimum) 
> 
> I wonder how cross-platform the tuning algorithm itself is.
> 
> I could also imagine that decisions like "do I let the OS page
> cache, or postgres's buffer cache get most of the memory" are
> extremely OS dependent.

But you can hide most of the internal stuff from the user, either by
generating the config file for the platform the tool is running on or
by an option like "generate config for platform xyz".

If you have cross-platform already in place, this should not be much
overhead.


Kind regards

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group

-- 
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] Overhauling GUCS

[Gregory Stark]

"Greg Smith" <[EMAIL PROTECTED]> writes:

> 1) Is it worthwhile to expand the information stored in the GUC structure to
> make it better capable of supporting machine generation and to provide more
> information for tool authors via pg_settings?  The exact fields that should or
> shouldn't be included remains controversial; consider "default value",
> "per-session/runtime/restart", and "enum lists" as the list of things that are
> most needed there.

Isn't that a list of what's *already* there?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS 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] Overhauling GUCS

[Ron Mayer]

Joshua D. Drake wrote:

Tom Lane wrote:

Peter Eisentraut <[EMAIL PROTECTED]> writes:

- If we know better values, why don't we set them by default?


The problem is: better for what?


That is where some 80% solution sample config files come in.

+1.

At work I use 3 templates.
* One for salespeople's demo laptops.
* One for a developer's desktop.
* One for our bigger production servers.

The old old default postgresql.conf used to be nice for
the first group.  The newer set of defaults is nicer
for the second group.  Emailing the lists here's the
current best way of tuning for that last case.

I wonder if the fastest way to generate the configurator
would be to simply ask everyone to post their tuned
postgresql.conf files along with a brief description of
the use case for that file.  The we could group the
use-cases into various classes; and average the values
of the submitted files.  Then the configurator's one
question "choose which use case most closely matches
yours from this list".


--
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] New DTrace probes proposal

[Robert Treat]

On Friday 06 June 2008 14:32:27 Robert Lor wrote:
> Robert Treat wrote:
> > certainly by the time 8.4 ships, these should work with freebsd I'd
> > think. ideally we would need to confirm this by release time, certainly
> > getting a bsd buildfarm member to compile with them would be a start (and
> > very unlikely to cause issues)
>
> As soon as the DTrace port is working on FreeBSD, I will confirm that
> the probes are working properly, and it's definitely a good idea to get
> a buildfarm machine building with --enable-dtrace.
>
> > One thing I didnt understand after looking at this was...
> >
> >> * Probes to measure query time
> >> query-parse-start (int, char *)
> >
> > I would have guessed that the arguments might be pid and query string,
> > but looking at the probes, I see it defined as:
> >
> >  TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);
> >
> > which doesn't seem to match up... can you explain that piece?
>
> Having the pid passed as an argument was my original intention, but it's
> actually redundant since the pid is readily available from the script,
> so I will fix the other probes with pid as args.
>
> > Overall, I like the probes you have breaking down query
> > parsing/planning/executing, though I like ours for measuring autovacuum
> > pieces, so I think the end game should be to just merge the two patches
> > together (barring any place there is direct conflict)... do you see any
> > issues with that?
>
> Yes, to avoid confusion, the probes should be merged and resubmitted as
> one patch. Have yours been ported to 8.4 yet? We also need to make sure
> the names and arg types are consistent, probably should work on this
> offline.
>

We haven't ported our probes to 8.4 yet; the focus of our work has been to 
help people currently running postgres in production, and will probably 
remain with that focus untill closer to feature freeze. (Granted, if we get 
something into 8.4, we may just overhaul our to match that.)  While it would 
be nice to have a clean merge of the two, it's probably simple enough to just 
re-implement the differences into your patch (since yours already compiles on 
8.4).  As far as naming scheme, I'm not particularly wedded to either... is 
there a dtrace naming convention that could be followed? 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Overhauling GUCS

[Alvaro Herrera]

Robert Treat wrote:

> One idea I have been kicking around is having every guc have a anchor in the 
> website (rahter than anchoring on parameter family).  It might be enough to 
> just populate the search bot with every guc anchored to family though... 

+1 on the anchor per variable.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Overhauling GUCS

[Greg Smith]

On Fri, 6 Jun 2008, Tom Lane wrote:


I grow weary of this thread.


If we keep it up for, oh, another three years, then maybe you'll be as 
weary as I am of struggling with problems in this area.  Strinking a 
balance between the wants and needs of people who want a fancy GUI tool 
for configuring database settings with those who want to edit things 
manually is a difficult problem that is not going away.  If this didn't 
keep coming back to haunt me all the time I'd like to forget about it 
myself.


I will say it once more: I do not believe for one instant that the 
current formatting of postgresql.conf is the major impediment, or even a 
noticeable impediment, to producing a useful configuration wizard.


Arguments about formatting change to postgresql.conf are a tangent to the 
central questions here, and having just closed some open comments on that 
I am with you on ignoring those as off-topic the same way I keep 
minimizing "what are the parameters to tune?" comments.


Here are the relevant questions around since the first message that are 
not attracting discussion:


1) Is it worthwhile to expand the information stored in the GUC structure 
to make it better capable of supporting machine generation and to provide 
more information for tool authors via pg_settings?  The exact fields that 
should or shouldn't be included remains controversial; consider "default 
value", "per-session/runtime/restart", and "enum lists" as the list of 
things that are most needed there.


2) Should the sample postgresql.conf file be replaced by a program that 
generates it using that beefed up structure instead, therefore removing 
one file that has to be manually kept in sync with the rest of the code 
base right now?


3) What now makes sense for a way to update database parameters for users 
whose primary (or only in some cases) access to the server is over the 
database port, given the other changes have improved automatic config file 
generation?


If you wish to prove otherwise, provide a complete wizard except for the 
parts that touch the config file, and I will promise to finish it.


You do realize that if I provided you with such a sample, the not 
implemented yet "config API" stubs it needs to work would be exactly what 
are suggested to add in the proposal page, right?  I (and Josh) didn't 
just make them all up out of nowhere you know.  I wrote a message here 
already about what the seemingly inevitable path the budding "wizard tool 
hacker" follows and why that leads into some of the changes suggested.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
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] Overhauling GUCS

[Robert Treat]

On Friday 06 June 2008 08:35:00 Peter Eisentraut wrote:
> Am Mittwoch, 4. Juni 2008 schrieb Tom Lane:
> > * Can we present the config options in a more helpful way (this is 99%
> > a documentation problem, not a code problem)?
>
> ack
>
> > * Can we build a "configuration wizard" to tell newbies what settings
> > they need to tweak?
>
> Some questions to clarify this:
>
> - What settings do "newbies" (or anyone else) typically need to change?
> Please post a list.
>

i do have a defined set (sort of a "top ten you need to know") that i tell 
people about in my postgres 101 tutorials... off the top of my head it looks 
like this:

shared_buffers
effective_cache_size
default_stats_target
work_mem
maintainance_work_mem
listen_address
max_connections
the fsm parameters
checkpoint_segements
random_page_cost

i think others close to the list are constraint_exclusion (unless it defaults 
to on now), max_prepared_transactions (set it to 0 unless you use it... i may 
stop recommending this after noticing its effects on max # system locks 
allowed), and then the logging parameters (which is to say, you need to set 
up logging that works, however you want to do it). 

There are a couple more that might go on the list, like synchronous_commit, 
but its on the fence for now. 

> - What values would you set those settings to?  Please provide a
> description for arriving at a value, which can later be transformed into
> code.  Note that in some cases, not even the documentation provides more
> than handwaving help.
>

I can provide this if you want, will need to look over my notes. one issue 
we've faced in the past with this is something like shared_buffers, where the 
settings is based on 1) dedicated server?, 2) available ram, 3) amount of 
red/write traffic, 4) disk subsystem.  Those types of input are hard to 
quantify in code. 

> - If we know better values, why don't we set them by default?
>

There is a saying, something like "The accumulation of annecdotes is not 
data".  Well, we seem to have a high bar on what proof we need to actually 
change a default GUC settings. default_statistics_target is a prime example, 
where almost no one i know has ever recommended 10 as a default, or suggests 
setting it to 10 as an way to improve performance, but the effort to get it 
changed to something more reasonable has been monumental. 

> Another orthogonal stumbling block on the way to making all of this
> automatic is that the surely criticial shared_buffers setting will in any
> useful configuration require messing around with kernel settings that no
> PostgreSQL tool can really help with.

yep.  seems it might be possible to just compare the shared_buffer setting 
with the kernel parameters before making the change though... not sure in 
which way you would slant the output though. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Proposal: New LWLockmode LW_OWNER

[Jignesh K. Shah]

Tom Lane wrote:

"Jignesh K. Shah" <[EMAIL PROTECTED]> writes:
  

Tom Lane wrote:


This seems rather crazy, and you haven't actually given a single
convincing use-case.
  


  
One area that I find it useful is where it will be useful is in 
ProcArrayEndTransaction where it uses exclusive to update  proc array 
structure where right now it uses EXCLUSIVE and most commit transactions 
are updating their own proc array structure this lock semantic can be 
useful..



That is exactly a place where you CAN'T use this, because it will break
transactional semantics, specifically serialization of commits relative
to snapshots.  See all the discussions around the last refactoring of
ProcArray locking, and particularly the summary in
src/backend/access/transam/README.


  


Quoting from the README that you mentioned:

Formally, the correctness requirement is "if a snapshot A considers 
transaction X as committed, and any of transaction X's snapshots 
considered transaction Y as committed, then snapshot A must consider 
transaction Y as committed".


What we actually enforce is strict serialization of commits and 
rollbacks with snapshot-taking: we do not allow any transaction to exit 
the set of running transactions while a snapshot is being taken.  (This 
rule is stronger than necessary for consistency, but is relatively 
simple to enforce, and it assists with some other issues as explained 
below.)  The implementation of this is that GetSnapshotData takes the 
ProcArrayLock in shared mode (so that multiple backends can take 
snapshots in parallel), but ProcArrayEndTransaction must take the 
ProcArrayLock in exclusive mode while clearing MyProc->xid at 
transaction end (either commit or abort).


ProcArrayEndTransaction also holds the lock while advancing the shared 
latestCompletedXid variable.  This allows GetSnapshotData to use 
latestCompletedXid + 1 as xmax for its snapshot: there can be no 
transaction >= this xid value that the snapshot needs to consider as 
completed.


Quote End

What I understand is the rule of serializations comes into play when 
Snapshot is being taken... Snapshot is being taken only when SHARED lock 
has already been acquired. If EX_OWNER is being used in this scenario, 
it still works as designed. If EX_OWNER has been acquired, NOBODY can 
get SHARED Lock which means the rule is still satisfied. Of course I am 
still worried about latestCompletedXid being written at the same time 
(maybe use some atomic compare and swap function for it to solve the 
problem) but I dont understand why you think that this is the place 
where it cannot be used?


Simon,  Scalability increases 50% from what I see  (throughput increases 
from 200k tpm to 300k tpm and system utilization went from 50% to 80% or 
so) and is a step forward in overall performance and system utilization. 
Also response time on high loads also falls drastically to something 
similar to low load response times.



Regards,
Jignesh









--
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] Overhauling GUCS

[Robert Treat]

On Thursday 05 June 2008 15:15:14 Greg Smith wrote:
> (1) is in that proposal but is strictly optional as something to put in
> the configuration file itself.  The idea behind (2) is to enable tool
> authors to have an easier way to suggest where to head for more
> information.  I'd like for it to be trivial for a tool to say "Suggested
> value for  is ; see
> http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html
> for more information".  I know what most of the settings I tinker with do,
> but even I'd like it to be easier to find the right spot in the manual;
> for newbies it's vital.  You are correct that (2) isn't strictly necessary
> here, but it's valuable and will be easier to wrap into this than to bolt
> on later.
>

One idea I have been kicking around is having every guc have a anchor in the 
website (rahter than anchoring on parameter family).  It might be enough to 
just populate the search bot with every guc anchored to family though... 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Overhauling GUCS

[Greg Smith]

On Fri, 6 Jun 2008, Heikki Linnakangas wrote:

Or perhaps we should explicitly mark the settings the tool has generated, and 
comment out:


#shared_buffers = 32MB   # commented out by wizard on 2008-06-05
shared_buffers = 1024MB  # automatically set by wizard on 2008-06-05


What I would like to do is make the tool spit out a revision history in 
the same way I find all "big IT" shops handling this already:  by putting 
a revision history style commentary above the current setting.  Here's a 
sample:


# 2008-03-02 : 32MB : postgres : Database default
# 2008-05-02 : 512MB : pg_autotune : Wizard update
# 2008-05-15 : 1024MB : gsmith : Increased after benchmark tests
shared_buffers = 1024MB

If the first tuning tool that comes into existance used this format, and 
the format was reasonable, I think it would be possible to get people 
making manual edits to adopt it as well.


The exact details of how this should look are off-topic for the main 
discussion here, though, so I'd prefer if this whole line of discussion 
died off.  Anyone who wants to comment on this whole area, feel free to 
contact me off-list or edit the Wiki page (which has a section on this 
topic now) to hash out suggestions in this area, I'm trying to keep this 
somewhat thread focused now.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
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] Overhauling GUCS

[Gregory Stark]

"Andreas Pflug" <[EMAIL PROTECTED]> writes:

> I personally wouldn't even think about starting such a wizard, unless I have 
> an
> idea how to push the result into the database. No, not a file, but via SQL! So
> your statement you won't react unless a wizard is almost ready is prohibitive,
> apart from the fact that not only wizards (featuring AI) are interesting, but
> simple config tools as well.

Well there's a perfectly good place to start today. Dump out a config file
with the settings your wizard or config tool wants to change from their
current values.

We could come up with a canonical filename for such a file and include an
include directive for it in the standard configuration file or you could just
include instructions saying what include line to add to your config file in
the installation instructions for your tool.

Great, I'm glad we've resolved that issue.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

-- 
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] Overhauling GUCS

[Steve Atkins]

On Jun 6, 2008, at 12:22 PM, Greg Smith wrote:


On Fri, 6 Jun 2008, Peter Eisentraut wrote:

- What settings do "newbies" (or anyone else) typically need to  
change?

Please post a list.
- What values would you set those settings to?  Please provide a  
description
for arriving at a value, which can later be transformed into code.   
Note that
in some cases, not even the documentation provides more than  
handwaving help.


Josh's spreadsheet at http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc 
 provides five different models for setting the most critical  
parameters based on different types of workloads.  Everyone can  
quibble over the fine tuning, but having a good starter set of  
reasonable settings for these parameters is a solved problem.



 It's just painful to build a tool to apply the available expert  
knowledge that is already around.


I hope to have something in a week or so that's a first cut at that  
tool. I'm aiming at a GUI tool at first, as I think that's the main  
need, though once the basics are done an interactive or non- 
interactive CLI version shouldn't be a big deal.


Cheers,
  Steve


--
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] Overhauling GUCS

[Robert Treat]

On Wednesday 04 June 2008 22:04:54 Greg Smith wrote:
> On Wed, 4 Jun 2008, Aidan Van Dyk wrote:
> > * Are we always spilling small amounts of data to disk for sorting?  A
> >  a small work_mem increase might help...
>
> I was just talking to someone today about building a monitoring tool for
> this.  Not having a clear way to recommend people monitor use of work_mem
> and its brother spilled to disk sorts is an issue right now, I'll whack
> that one myself if someone doesn't beat me to it before I get time.
>

I remember *years* ago, someone wrote a perl script to poll pgsql_tmp and 
print out anytime something showed up... you could probably find that in the 
archives if you look around. 

of course to me this sounds like an excellent idea for a dtrace probe ;-)

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Overhauling GUCS

[Greg Smith]

On Fri, 6 Jun 2008, Peter Eisentraut wrote:


- What settings do "newbies" (or anyone else) typically need to change?
Please post a list.
- What values would you set those settings to?  Please provide a description
for arriving at a value, which can later be transformed into code.  Note that
in some cases, not even the documentation provides more than handwaving help.


Josh's spreadsheet at 
http://pgfoundry.org/docman/view.php/1000106/84/calcfactors.sxc provides 
five different models for setting the most critical parameters based on 
different types of workloads.  Everyone can quibble over the fine tuning, 
but having a good starter set of reasonable settings for these parameters 
is a solved problem.  It's just painful to build a tool to apply the 
available expert knowledge that is already around.



- If we know better values, why don't we set them by default?


Because there's not enough information available; the large differences 
between how you tune for different workloads is one example.  Another is 
that people tune for peak and projected activity rather than just what's 
happening right now.  Every model suggested for a tuning wizard recognizes 
you need to ask some set of questions to nail things down.  I continue to 
repeat in broken-record style, exactly what a tuning tool will ask about 
and what settings it will suggest is not important, and getting into that 
is an entirely different discussion (one that gets hashed out every single 
day on pgsql-performance).  The fact that writing such a tool is harder 
than it should be is the issue here.


Another orthogonal stumbling block on the way to making all of this 
automatic is that the surely criticial shared_buffers setting will in 
any useful configuration require messing around with kernel settings 
that no PostgreSQL tool can really help with.


Yes.  So?  All you can do is point this out to users.

--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
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] New DTrace probes proposal

[Tom Lane]

Robert Lor <[EMAIL PROTECTED]> writes:
> As soon as the DTrace port is working on FreeBSD, I will confirm that 
> the probes are working properly, and it's definitely a good idea to get 
> a buildfarm machine building with --enable-dtrace.

I'm pretty certain one of the OS X build critters is already testing
that.  Of course it's only testing that it builds, not that it does
anything useful ...

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] Overhauling GUCS

[Robert Treat]

On Monday 02 June 2008 10:12:06 Tom Lane wrote:
> I have no objection to providing alternative ways to edit the
> configuration data, but the primary source of the settings is
> going to continue to be an editable text file.  Any proposals for
> alternatives-to-a-text-editor have to work within that reality.
>

I think there is some disagreement that using text files needs to be 
the "primary" way to edit configurations.  Some people are of the opinion 
that we should focus on text files as a secondary method, generally to be 
used only when a more sql/gui oriented way wont work (ie. your shared buffer 
restart scenario).  I think most of the user base would like to approach 
administration from that point-of-view, and as of yet I haven't seen a 
technical reason why that world view is wrong, only philosphical ones. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Overhauling GUCS

[Andreas Pflug]

Tom Lane wrote:

I grow weary of this thread.  I will say it once more: I do not believe
for one instant that the current formatting of postgresql.conf is the
major impediment, or even a noticeable impediment, to producing a useful
configuration wizard.  If you wish to prove otherwise, provide a
complete wizard except for the parts that touch the config file, and
I will promise to finish it.

I will not read or respond to any further discussion of changing the
config file format.  It's a waste of bandwidth.
  


Your statement doesn't really surprise me. Apparently you kind of 
celebrate misunderstanding my point, which isn't primarily about the 
file format, but about config param accessibility via api/SQL.


I personally wouldn't even think about starting such a wizard, unless I 
have an idea how to push the result into the database. No, not a file, 
but via SQL! So your statement you won't react unless a wizard is almost 
ready is prohibitive, apart from the fact that not only wizards 
(featuring AI) are interesting, but simple config tools as well.


Regards,
Andrads


--
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] Proposal: New LWLockmode LW_OWNER

[Simon Riggs]

On Fri, 2008-06-06 at 12:39 -0400, Tom Lane wrote:
> "Jignesh K. Shah" <[EMAIL PROTECTED]> writes:
> > New Lock Mode Proposed: LW_EX_OWNER  (input on better name will be 
> > appreciated).
> 
> This seems rather crazy, and you haven't actually given a single
> convincing use-case.  Shouldn't you be trying to break down a lock
> into multiple locks instead of inventing new lock semantics that
> nobody really understands?

I understand why Jignesh has approached it this way, having talked some
at PGCon about this.

Splitting ProcArray into multiple pieces is likely to slow down access
to the ProcArray for everyone, since shared accessors want the whole
thing, but we should try that also as you suggest. 

Allowing a lock mode where the individual pieces are accessible as a
whole or individually does make some sense. This is a different
situation than buffer and lock table access, where there was no common
workload that needed access to all partitions.

So I think its a reasonable idea, with a complex sounding name. The main
issue is proving it helps the target workload, and doesn't hinder other
workloads. We should do that before we think of a better name. There are
other possibilities as well, but my feeling is that we should explore
them all - so lets give this idea enough space to show its worth, if
any.

Personally, I don't see it being applicable to WAL buffers though. That
is a different situation again and we have a couple of workable ideas on
the table already. That doesn't detract from this idea's possible worth.
Unique and important situations need unique solutions.

So, please can we see some perf results? Big gains justify extra code.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and 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] Overhauling GUCS

[Andreas Pflug]

Gregory Stark wrote:
  

Text config files are NOT friendly for beginner and mediocre users. IMHO the
current restriction on GUC changes is a major obstacle towards pgsql tuning
tools, e.g. written as a Google SoC project. Graphic tools aren't too popular
at pgsql-hackers, but please contemplate a little how much pgadmin may have
contributed to the pgsql usage boost, esp. on windows.



Like it or not computers actually have to store state when you're done
entering it via the GUI. If you design around the GUI you end up with system
that can *only* be used via a GUI and spend years trying to work around that
(witness Windows which is only now with a lot of effort recovering from that
mistake).
  


I never advocated a file format that isn't editable any more; au 
contraire. And the statement that a GUI configuration contradicts manual 
editing is plain wrong, even with most windows software (if written 
nicely to the registry, you could dump the key, edit and reload it, or 
use regedit).

OTOH, you can't make most windows users happy with a text file version only.

Regards,
Andreas


--
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] New DTrace probes proposal

[Robert Lor]

Robert Treat wrote:


certainly by the time 8.4 ships, these should work with freebsd I'd think. 
ideally we would need to confirm this by release time, certainly getting a 
bsd buildfarm member to compile with them would be a start (and very unlikely 
to cause issues)
  
As soon as the DTrace port is working on FreeBSD, I will confirm that 
the probes are working properly, and it's definitely a good idea to get 
a buildfarm machine building with --enable-dtrace.
One thing I didnt understand after looking at this was... 
  

* Probes to measure query time
query-parse-start (int, char *)

I would have guessed that the arguments might be pid and query string, but 
looking at the probes, I see it defined as:


 TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);

which doesn't seem to match up... can you explain that piece? 
  
Having the pid passed as an argument was my original intention, but it's 
actually redundant since the pid is readily available from the script, 
so I will fix the other probes with pid as args.


Overall, I like the probes you have breaking down query 
parsing/planning/executing, though I like ours for measuring autovacuum 
pieces, so I think the end game should be to just merge the two patches 
together (barring any place there is direct conflict)... do you see any 
issues with that? 
  
Yes, to avoid confusion, the probes should be merged and resubmitted as 
one patch. Have yours been ported to 8.4 yet? We also need to make sure 
the names and arg types are consistent, probably should work on this 
offline.
One other questions would be what to do with the dtrace scripts. I think 
having a set of these available is a large boon for dtrace users, but do you 
see that as something that needs to be distriubuted with the core?
I don't see the need to include the scripts with core now, maybe some 
point in the future if it makes sense.
 I'd lean 
towards reviving the dtrace project on pgfoundry, but it might be worth 
expanding the dynamic tracing chapter to include more examples and a pointer 
to pgfoundry.  
  
Agreed on both. I will add the new scripts to the dtrace project on 
PgFoundry and add more info to the doc. I think you guys have some 
interesting scripts as well that folks will find useful.


-Robert


--
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] Proposal: New LWLockmode LW_OWNER

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> "Jignesh K. Shah" <[EMAIL PROTECTED]> writes:
>>> New Lock Mode Proposed: LW_EX_OWNER  (input on better name will be 
>>> appreciated).

> We do something like this in the sinval code -- see SIGetDataEntry.

Yeah, that analogy occurred to me later --- EX_OWNER would be a close
match to what sinval is doing.  However, adding a third mode to LWLocks
would certainly introduce extra cycles into what is already a hotspot,
and one use-case that is already working fine without it doesn't seem
like much of an argument.  (ProcArray isn't a use-case because of the
commit interlock problem, and I didn't see any other proposed uses
that weren't mere hand-waving.)

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] Proposal: New LWLockmode LW_OWNER

[Tom Lane]

"Jignesh K. Shah" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> This seems rather crazy, and you haven't actually given a single
>> convincing use-case.

> One area that I find it useful is where it will be useful is in 
> ProcArrayEndTransaction where it uses exclusive to update  proc array 
> structure where right now it uses EXCLUSIVE and most commit transactions 
> are updating their own proc array structure this lock semantic can be 
> useful..

That is exactly a place where you CAN'T use this, because it will break
transactional semantics, specifically serialization of commits relative
to snapshots.  See all the discussions around the last refactoring of
ProcArray locking, and particularly the summary in
src/backend/access/transam/README.

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] Proposal: New LWLockmode LW_OWNER

[Alvaro Herrera]

Tom Lane wrote:
> "Jignesh K. Shah" <[EMAIL PROTECTED]> writes:
> > New Lock Mode Proposed: LW_EX_OWNER  (input on better name will be 
> > appreciated).
> 
> This seems rather crazy, and you haven't actually given a single
> convincing use-case.  Shouldn't you be trying to break down a lock
> into multiple locks instead of inventing new lock semantics that
> nobody really understands?

We do something like this in the sinval code -- see SIGetDataEntry.  We
use LW_SHARED for it.  Obviously it has the implication that a backend
can never grab only SHARED and examine the status of other backends, but
that's not needed in this code.  Perhaps the other pieces of code that
Jignesh wants to improve can be treated similarly?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] Proposal: New LWLockmode LW_OWNER

[Jignesh K. Shah]

Tom Lane wrote:

"Jignesh K. Shah" <[EMAIL PROTECTED]> writes:
  
New Lock Mode Proposed: LW_EX_OWNER  (input on better name will be 
appreciated).



This seems rather crazy, and you haven't actually given a single
convincing use-case.  Shouldn't you be trying to break down a lock
into multiple locks instead of inventing new lock semantics that
nobody really understands?




One area that I find it useful is where it will be useful is in 
ProcArrayEndTransaction where it uses exclusive to update  proc array 
structure where right now it uses EXCLUSIVE and most commit transactions 
are updating their own proc array structure this lock semantic can be 
useful.. However I havent figured out on the last line where it updates 
ShmemVariableCache->latestCompletedXid which might require still an 
EXCLUSIVE lock and hence did not propose the use case.


http://doxygen.postgresql.org/procarray_8c-source.html#l00231

The whole concept of a single Exclusive lock to me is more flawed  than 
the proposed idea. Single Exclusive locks are artificial bottlenecks in 
PostgreSQL and thats why a new lock semantic is helpful in opening 
people's mind beyond exclusive lock and suddenly people will start doing 
more parallel work where possible and LW_EX_OWNER allows that to work. 
Which infact will allow somebody else to innovate on 
ProcArrayEndTransaction and solve the problem that I could not figure 
regarding latestCompletedXid.



In my sample test where putting LW_EX_OWNER or LW_OWNER as I have it my 
code in ProcArrayEndTransaction  the throughput of HEAD CVS went from 
200,000 transactions per minute to 300,000 transactions per minute but 
though in my case latestCompletedXid is unsafe. If thats solved, there 
is a potential upswing in scalability in PostgreSQL core.


Once that function is implemented correctly, it will highlight other 
places where such lock semantics could prove to be useful.. (My money on 
WALInsertLock)



-Jignesh




--
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] Overhauling GUCS

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> Bugger... Now we only need to make postgresql check postmaster.conf
> into git everytime it makes a change...

Been there, wrote that. (for postgresql.conf anyway).

- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200806061351
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkhJeXQACgkQvJuQZxSWSshS3QCfQMC092pMGm/FiRJkYmu9j68e
VDYAoM051BtIF8Hsemsrs//jSv3dkGel
=otzg
-END PGP SIGNATURE-



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

[HACKERS] We have a launch abort ... PG update releases will be delayed

[Tom Lane]

This report:
http://archives.postgresql.org/pgsql-general/2008-06/msg00208.php
shows that there is a nasty oversight in my patch of awhile back:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00081.php
which might cause pg_dump output to fail to reload.  This is a
regression compared to the current releases in the pre-8.2 branches.
Accordingly, core has determined that it is inadvisable to release
the current code on Monday as was previously announced.  Although
the planned fix is quite simple, we will push the release date out
a few days to give the packagers adequate time to do their work.
(The exact new release date isn't determined yet.)

Since the updated tarballs were already spun and might have been
downloaded by a few people, we will generate new ones with new
version numbers (8.3.3, etc) to avoid confusion.

Sorry about that :-(

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] Overhauling GUCS

[Joshua D. Drake]

On Fri, 2008-06-06 at 13:07 -0400, Aidan Van Dyk wrote:
> * David E. Wheeler <[EMAIL PROTECTED]> [080606 12:22]:
>  
> > I guess that could be a feature. Personally, I use a vcs system for  
> > that.
> 
> Bugger... Now we only need to make postgresql check postmaster.conf
> into git everytime it makes a change...

Just work with the FAM api to automate it :P

Joshua D. Drake


> 
> ;-)
> 
> 


-- 
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] Overhauling GUCS

[Aidan Van Dyk]

* David E. Wheeler <[EMAIL PROTECTED]> [080606 12:22]:
 
> I guess that could be a feature. Personally, I use a vcs system for  
> that.

Bugger... Now we only need to make postgresql check postmaster.conf
into git everytime it makes a change...

;-)


-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] Overhauling GUCS

[Robert Treat]

On Saturday 31 May 2008 17:34:27 David E. Wheeler wrote:
> On May 31, 2008, at 12:36, Gregory Stark wrote:
> > What this sounds like is a sly way to try to get rid of
> > postgresql.conf
> > entirely and replace it with parameters stored in the database so
> > admins would
> > adjust the parameters using an SQL syntax rather than a text file.
> >
> > There are pros and cons of such a system but I think for newbie
> > admins that
> > would be a thousand times *more* baffling. You would have to learn new
> > commands and have no holistic view of what parameters had been set,
> > what
> > related parameters might exist. You also have no way to keep the
> > file in a
> > version control system or sync across servers etc.
>
> FWIW, this has not been a barrier to MySQL adoption.
>

agreed. using sql like syntax is often a more familiar operation than editing 
a text file for DBAs.  Consider the number of DBA's that manage other DB's 
strictly through graphical interfaces (something we are rather lacking in 
ability to do comparativly).  consider also that if this is built atop the 
current pg_settings, all the normal sql tools can still help... ie. 
pg_dump -t if you want to check something into svn. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Overhauling GUCS

[Robert Treat]

On Wednesday 04 June 2008 15:48:47 Andrew Dunstan wrote:
> simply remove all the comment lines from your
> config file. 

+1. That would clear up a lot of confusion on it's own. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] Proposal: New LWLockmode LW_OWNER

[Tom Lane]

"Jignesh K. Shah" <[EMAIL PROTECTED]> writes:
> New Lock Mode Proposed: LW_EX_OWNER  (input on better name will be 
> appreciated).

This seems rather crazy, and you haven't actually given a single
convincing use-case.  Shouldn't you be trying to break down a lock
into multiple locks instead of inventing new lock semantics that
nobody really understands?

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] Overhauling GUCS

[David E. Wheeler]

On Jun 6, 2008, at 01:50, Andreas Pflug wrote:


Two heretical questions:
Do we need user generated comments at all?
I can't remember ever having used any comment in postgresql.conf.


That's a valid point. I've used comments to note by whom and when when  
a setting was changed.


Why do so many people here insist on editing postgresql.conf as  
primary means of changing config params?
Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as  
sed'ing postgresql.conf or doing it manually?


I think that there has been enough pushback against housing all the  
settings in the database, not to mention that it calls for an API,  
that just starting with something simpler to parse the file and  
rewrite it from the command-line might be a better first step.


Looking around for different approaches, network appliances come to  
my mind, e.g. Cisco routers and PIX. You have 3 ways to configure a  
pix:
- use a command line (using ssh or telnet, eqivalent to psql); WRITE  
MEMORY to make the changes survive a reboot.

- use a web interface (or similar tool)
- use tftp to up/download the complete config in and out, editing  
the file. User comments will be lost, with the exception of those  
that have been applied with special comment commands (equivalent to  
"comment on").


I think the first option there is the one that's been getting the most  
support here.


Best,

David


--
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] Overhauling GUCS

[David E. Wheeler]

On Jun 5, 2008, at 23:08, Heikki Linnakangas wrote:

What comments do we consider machine-generated? Just the ones used  
to comment out settings, like


#shared_buffers = 32MB

or something else?


Those and documentation comments.

If the automatic tool lets alone all other kind of comments, I think  
we're fine. In fact, it wouldn't necessarily need to modify those  
comments either, it could simply add a new setting line below that:


#shared_buffers = 32MB
shared_buffers = 1024MB


Well, we've been talking about having varying levels of documentation  
in the comments of the file based on the options passed to the  
configuration program. I think that these are the primary concern,  
though Greg, please do correct me if I'm mistaken.


For extra safety, it could comment out old settings, perhaps with  
something like this:


#shared_buffers = 32MB
#shared_buffers = 1024MB  # commented out by wizard on 2008-06-05
shared_buffers = 2048MB

This would preserve a full change history in the file. It would  
become quite messy after a lo of changes, of course, but a user can  
trim the history by hand if he wants to.


I guess that could be a feature. Personally, I use a vcs system for  
that.


Best,

David

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

[HACKERS] Proposal: New LWLockmode LW_OWNER

[Jignesh K. Shah]

Currently there are two modes of LWLock : SHARED and EXCLUSIVE
Mostly you need to have EXCLUSIVE lock mode to make any changes, add, 
delete and SHARED if you are just reading it.  Multiple backends can 
grab SHARED mode simultaneously while only one Backend can grab 
EXCLUSIVE at a time. There are situations when there are opportunities 
that certain values are typically changed by the same backend also in 
such cases when multiple backend  wants to change similarly values that 
they themselves typically change there is no lockmode which allows 
simultaneous updates in such cases and the result is sequential changes 
to the values which can be done simultaneously


New Lock Mode Proposed: LW_EX_OWNER  (input on better name will be 
appreciated).


So now there will be three modes SHARED, EXCLUSIVE, EX_OWNER
They will still be all mutually exclusive in the sense at any given time 
there can be only one mode active. However there is a marked difference 
while values of SHARED can be 0..N and EXCLUSIVE can be 0..1, the new 
lock mode EX_OWNER can be again 0..N.


This is primarily important so that we can carry out tasks of updates 
which not necessarily will be modified by any other backend.. Protection 
is guranteed since mostly old code will still have EXCLUSIVE lock so 
they will still need to work as guranteed. However advantage is for 
certain operations where we can still allow others to write into their 
own "area" with this EX_OWNER lock mode which allows multiple backend 
into shared area that they own. The area is not guaranteed by the lock 
mode but the procedure themselves and lock framework need not worry 
about that.


I wrote a prototype which needs changes in lwlock.h and lwlock.c and 
modifies lwlock.c which also awakes all SHARED together and if the first 
waiter is EX_OWNER it awakes all EX_OWNER together and if it EXCLUSIVE 
then just wakes the EXCLUSIVE waiter..



The potential for this new lock mode  can be used in various scenarios 
though it will need separate proposals on how to handle them since all 
bases are not covered yet but just as examples here:
1.  Proc array structure: Many times specific proc array structure is 
modified by the same backend.
2. WAL Buffers themselves: One way to make wal_buffer scalable is to 
have areas defined and have certain backend go against such areas rather 
than get the whole buffer lock. EXCLUSIVE is still used for most of them 
except certain identified parts..

3. Many other not identified yet.


Right now this proposal is only for the new Lock mode. Since thats a 
Lock framework enhancements which can give rise to multiple uses later


Regards,
Jignesh


--
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] Overhauling GUCS

[Joshua D. Drake]

Peter Eisentraut wrote:

Am Freitag, 6. Juni 2008 schrieb Joshua D. Drake:

That is where some 80% solution sample config files come in.


Considering that writing a sample configuration file is trivial, yet I haven't 
seen a single one posted in the six or more years of GUC, I have no faith in 
this plan until I actually see it implemented.


I fail to see why anyone would bother with the amount of noise on this 
thread. If people start leaning toward an actual solution, I am sure the 
work will get done.


Joshua D. Drake



--
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] New DTrace probes proposal

[Robert Treat]

On Saturday 17 May 2008 22:33:01 Robert Lor wrote:
> (Resending since it didn't work the first time. Not sure if attaching a
> tar file was the culprit.)
>
> I'd like to propose adding the following probes (some of which came from
> Simon) to 8.4.
>

+1

> I think these probe provide very useful data. Although some of the data
> can be collected now, the main advantages with probes, among others, are
> (1) they are always available and can be enabled only when needed
> especially in production (2) different combinations of probes can be
> used together to collect interesting data.
>
> They work on OS X Leopard & Solaris now, and hopefully on FreeBSD soon.
>

certainly by the time 8.4 ships, these should work with freebsd I'd think. 
ideally we would need to confirm this by release time, certainly getting a 
bsd buildfarm member to compile with them would be a start (and very unlikely 
to cause issues)

> Preliminary patch attached along with sample DTrace scripts.
>

One thing I didnt understand after looking at this was... 

> * Probes to measure query time
> query-parse-start (int, char *)

I would have guessed that the arguments might be pid and query string, but 
looking at the probes, I see it defined as:

 TRACE_POSTGRESQL_QUERY_PARSE_START(query_string);

which doesn't seem to match up... can you explain that piece? 


Overall, I like the probes you have breaking down query 
parsing/planning/executing, though I like ours for measuring autovacuum 
pieces, so I think the end game should be to just merge the two patches 
together (barring any place there is direct conflict)... do you see any 
issues with that? 

One other questions would be what to do with the dtrace scripts. I think 
having a set of these available is a large boon for dtrace users, but do you 
see that as something that needs to be distriubuted with the core? I'd lean 
towards reviving the dtrace project on pgfoundry, but it might be worth 
expanding the dynamic tracing chapter to include more examples and a pointer 
to pgfoundry.  

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

-- 
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] orafce does NOT build with Sun Studio compiler

[Jonah H. Harris]

On Fri, Jun 6, 2008 at 10:35 AM, Bjorn Munch <[EMAIL PROTECTED]> wrote:
> Have you tried with Studio 12?  I have a vague recollection that it
> might treat this differently (in order words, accept it), but I may be
> wrong...

It may work, but it's still unportable code.  Correcting the root
problem is the real fix, and one Pavel seemed to agree with.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
Edison, NJ 08837 | http://www.enterprisedb.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] Overhauling GUCS

[Peter Eisentraut]

Am Freitag, 6. Juni 2008 schrieb Joshua D. Drake:
> That is where some 80% solution sample config files come in.

Considering that writing a sample configuration file is trivial, yet I haven't 
seen a single one posted in the six or more years of GUC, I have no faith in 
this plan until I actually see it implemented.

-- 
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] Overhauling GUCS

[Joshua D. Drake]

Peter Eisentraut wrote:

Am Freitag, 6. Juni 2008 schrieb Tom Lane:

Peter Eisentraut <[EMAIL PROTECTED]> writes:

- If we know better values, why don't we set them by default?

The problem is: better for what?  In particular, I'm uncomfortable with
any changes in the direction of trying to make Postgres take over the
entire machine by default.  I'd want some fairly explicit permission
from the user for that ...


Yes, those are decisions we are going to have to make, eventually.  But recall 
the three step process:


1. What values need changing?


shared_buffers
work_mem
maintenance_work_mem
checkpoint_segments
wal_sync_method
effective_cache_size

I haven't seen a proposal for item 1 yet, so the rest is idle discussion at 
this time.


I think those cover the biggest low hanging fruit, async_commit is arguable.

Sincerely,

Joshua D. Drake




--
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] Overhauling GUCS

[Peter Eisentraut]

Am Freitag, 6. Juni 2008 schrieb Tom Lane:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > - If we know better values, why don't we set them by default?
>
> The problem is: better for what?  In particular, I'm uncomfortable with
> any changes in the direction of trying to make Postgres take over the
> entire machine by default.  I'd want some fairly explicit permission
> from the user for that ...

Yes, those are decisions we are going to have to make, eventually.  But recall 
the three step process:

1. What values need changing?

2. What to change them to?

3. Could that be the new default value?

I haven't seen a proposal for item 1 yet, so the rest is idle discussion at 
this time.

-- 
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] Overhauling GUCS

[Joshua D. Drake]

Tom Lane wrote:

Peter Eisentraut <[EMAIL PROTECTED]> writes:

- If we know better values, why don't we set them by default?


The problem is: better for what?  In particular, I'm uncomfortable with
any changes in the direction of trying to make Postgres take over the
entire machine by default.  I'd want some fairly explicit permission
from the user for that ...


That is where some 80% solution sample config files come in.

Joshua D. Drake



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] Overhauling GUCS

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> - If we know better values, why don't we set them by default?

The problem is: better for what?  In particular, I'm uncomfortable with
any changes in the direction of trying to make Postgres take over the
entire machine by default.  I'd want some fairly explicit permission
from the user for that ...

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] Overhauling GUCS

[Tom Lane]

Andreas Pflug <[EMAIL PROTECTED]> writes:
> Text config files are NOT friendly for beginner and mediocre users. IMHO 
> the current restriction on GUC changes is a major obstacle towards pgsql 
> tuning tools, e.g. written as a Google SoC project. Graphic tools aren't 
> too popular at pgsql-hackers, but please contemplate a little how much 
> pgadmin may have contributed to the pgsql usage boost, esp. on windows.

I grow weary of this thread.  I will say it once more: I do not believe
for one instant that the current formatting of postgresql.conf is the
major impediment, or even a noticeable impediment, to producing a useful
configuration wizard.  If you wish to prove otherwise, provide a
complete wizard except for the parts that touch the config file, and
I will promise to finish it.

I will not read or respond to any further discussion of changing the
config file format.  It's a waste of bandwidth.

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] Overhauling GUCS

[Gregory Stark]

"Peter Eisentraut" <[EMAIL PROTECTED]> writes:

> And note that one of the major advances in X.org over XFree86 was that all 
> the 
> useless garbage was removed from the configuration file, so that the final 
> and usable configuration fits on one screen, and you can even write it from 
> memory if you dare to.

This is a good point, though I think the big advance wasn't in actually
removing the garbage so much as making it irrelevant. That is, the advances
were in making the server actually work automatically with less manual
configuration.

That's always a noble goal. I would be interested in what parameters people
thought we could eliminate from the initial config file because the server
normally gets it right anyways and users shouldn't have to adjust it anyways.

(I'm specifically *not* referring to sections like the kerberos parameters
which even if most users can leave them alone those who do use that feature
*do* need to adjust those parameters because the server does *not* get them
right automatically)

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS 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] orafce does NOT build with Sun Studio compiler

[Bjorn Munch]

On 05/06 10.44, Mayuresh Nirhali wrote:
> Sun Studio does not like array declarations with null as dimenstion.
> So, In pipe.c we have,
> 
> typedef struct
> {
>LWLockId shmem_lock;
>pipe *pipes;
>alert_event *events;
>alert_lock *locks;
>size_t size;
>unsigned int sid;
>char data[];   /* line 149 */
> } sh_memory;

Have you tried with Studio 12?  I have a vague recollection that it
might treat this differently (in order words, accept it), but I may be
wrong...

- Bjorn

-- 
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] Overhauling GUCS

[Gregory Stark]

"Andreas Pflug" <[EMAIL PROTECTED]> writes:

> Gregory Stark wrote:
>> So all you have is our existing file except with an additional layer of
>> quoting to deal with, a useless SET keyword to annoy users, and a file that
>> you need a bison parser 
>
> Don't you think that's a little over the top, throwing bison at the simple 
> task
> to extend postgresql.conf scanning so it accepts --, /**/ and SET?

Well you're the one who proposed generalising the simple text file format to
SET commands. 

The whole point of a machine-editable format would be to make it more
restricted and predictable, not less so. We had a proposal to do a
postgresql.conf.auto which was automatically generated included from a
free-form postgresql.conf. That file could generated in a simple format
without worrying about formatting, comments, ordering, etc.

> Text config files are NOT friendly for beginner and mediocre users. IMHO the
> current restriction on GUC changes is a major obstacle towards pgsql tuning
> tools, e.g. written as a Google SoC project. Graphic tools aren't too popular
> at pgsql-hackers, but please contemplate a little how much pgadmin may have
> contributed to the pgsql usage boost, esp. on windows.

Like it or not computers actually have to store state when you're done
entering it via the GUI. If you design around the GUI you end up with system
that can *only* be used via a GUI and spend years trying to work around that
(witness Windows which is only now with a lot of effort recovering from that
mistake).

People got all focused on comments and sed, but in fact there are a host of
reasons to want to be able to directly edit the state stored in a simple text
file. You might like to group together related options, for example, perhaps
grouping together the options that your site have to adjust between dev and
production -- and probably have instructions on how to make the transition in
a commented section.

I happen to think being presented with 200 options is less confusing than
being presented with an empty file and being told to go read the documentation
to find out whether I might want to put anything in it. The documentation is a
good reference but isn't good as a "here's what you'll want to check before
you start" guide.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS 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] Overhauling GUCS

[Aidan Van Dyk]

* Peter Eisentraut <[EMAIL PROTECTED]> [080606 08:25]:
> Am Mittwoch, 4. Juni 2008 schrieb Aidan Van Dyk:
> > > When reading this thread, I'm wondering if anybody ever saw a config
> > > file for a complex software product that was easily editable and
> > > understandable. I don't know one. If there was one, it'd be nice to know
> > > it so we can learn from it.
> >
> > PostreSQL, Apache, X.org
> 
> And note that one of the major advances in X.org over XFree86 was that all 
> the 
> useless garbage was removed from the configuration file, so that the final 
> and usable configuration fits on one screen, and you can even write it from 
> memory if you dare to.

Ya, and unfortunately, I haven't got my dual-head to work with recent
distro X.org either, probably because I haven't found how how/where/what
to set in Xorg.conf for new version (and my old one doesn't "just work"
on new X.org either).  So I've got an old x.org version pinned, even
though I'm pretty sure that a newer X.org is probably better, dispite my
lack of ability to configure it.

Go figure ;-)

-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] Overhauling GUCS

[Aidan Van Dyk]

* Andreas Pflug <[EMAIL PROTECTED]> [080606 04:50]:
> David E. Wheeler wrote:
> >
> >How about a simple rule, such as that machine-generated comments start 
> >with "##", while user comments start with just "#"? I think that I've 
> >seen such a rule used before. At any rate, I think that, unless you 
> >have some sort of line marker for machine-generated comments, there 
> >will be no way to tell them apart from user comments. 
> 
> Two heretical questions:
> Do we need user generated comments at all?
> I can't remember ever having used any comment in postgresql.conf.

Well, I have, mainly to leave traces as to what and why I've changed
something from a default/previous value, based on "chagne, hope, and
test" style tuning.

And the one that Greg brought up earlier:
## Don't make this too high, or linux OOM will kill ther server!!!

I'm guessing that comment was put in for a reason too.

> Why do so many people here insist on editing postgresql.conf as primary 
> means of changing config params?
> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing 
> postgresql.conf or doing it manually?

I would guess main for 3 reasons:

1) It's always been that way, it's the traditional "unix" way, and many
   admins are comfortable being able to comment/make quick test
   changes with simple text files.
2) Their postgresql.conf are distrubuted/synced/generated from central
   provisioning/SCM system
3) PostgreSQL the server isn't even running

> Looking around for different approaches, network appliances come to my 
> mind, e.g. Cisco routers and PIX. You have 3 ways to configure a pix:
> - use a command line (using ssh or telnet, eqivalent to psql); WRITE 
> MEMORY to make the changes survive a reboot.
> - use a web interface (or similar tool)
> - use tftp to up/download the complete config in and out, editing the 
> file. User comments will be lost, with the exception of those that have 
> been applied with special comment commands (equivalent to "comment on").

And, of course, other server software comes to mind too:
apache, bind, postfix, sendmail, dhcpd, sshd, cron, xinetd... ;-)

a.
-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature

Re: [HACKERS] Overhauling GUCS

[Peter Eisentraut]

Am Donnerstag, 5. Juni 2008 schrieb Tom Lane:
> How far could we get with the answers to just three questions:
>
> * How many concurrent queries do you expect to have?
>
> * How much RAM space are you willing to let Postgres use?
>
> * How much "overhead" disk space are you willing to let Postgres use?

This is surely a good start.  We could optimize this even more by saying, disk 
space is cheap, so let's just use a much higher default setting for 
checkpoint_segments.  (If PostgreSQL is installed but not actually used, not 
all the space is actually going to be used anyway.)  Then, increase 
max_connections a bit; that should be OK for most users.  Then you are left 
with the memory settings, and those need kernel tuning in most cases, so any 
automation tool loses.  Hmm.

-- 
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] Overhauling GUCS

[Peter Eisentraut]

Am Mittwoch, 4. Juni 2008 schrieb Tom Lane:
> * Can we present the config options in a more helpful way (this is 99%
> a documentation problem, not a code problem)?

ack

> * Can we build a "configuration wizard" to tell newbies what settings
> they need to tweak?

Some questions to clarify this:

- What settings do "newbies" (or anyone else) typically need to change?  
Please post a list.

- What values would you set those settings to?  Please provide a description 
for arriving at a value, which can later be transformed into code.  Note that 
in some cases, not even the documentation provides more than handwaving help.

- If we know better values, why don't we set them by default?

Another orthogonal stumbling block on the way to making all of this automatic 
is that the surely criticial shared_buffers setting will in any useful 
configuration require messing around with kernel settings that no PostgreSQL 
tool can really help with.

-- 
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] Overhauling GUCS

[Peter Eisentraut]

Am Mittwoch, 4. Juni 2008 schrieb Aidan Van Dyk:
> > When reading this thread, I'm wondering if anybody ever saw a config
> > file for a complex software product that was easily editable and
> > understandable. I don't know one. If there was one, it'd be nice to know
> > it so we can learn from it.
>
> PostreSQL, Apache, X.org

And note that one of the major advances in X.org over XFree86 was that all the 
useless garbage was removed from the configuration file, so that the final 
and usable configuration fits on one screen, and you can even write it from 
memory if you dare to.

-- 
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] Overhauling GUCS

[Andreas Pflug]

Gregory Stark wrote:

So all you have is our existing file except with an additional layer of
quoting to deal with, a useless SET keyword to annoy users, and a file that
you need a bison parser 
Don't you think that's a little over the top, throwing bison at the 
simple task to extend postgresql.conf scanning so it accepts --, /**/ 
and SET?



to deal instead of a simple keyword-value syntax that
sed can manipulate.

  
sed seems to be THE killer application... Apart from the fact that sed 
easily could identify SET, my answer would be to use psql to modify the 
config, not sed.


Text config files are NOT friendly for beginner and mediocre users. IMHO 
the current restriction on GUC changes is a major obstacle towards pgsql 
tuning tools, e.g. written as a Google SoC project. Graphic tools aren't 
too popular at pgsql-hackers, but please contemplate a little how much 
pgadmin may have contributed to the pgsql usage boost, esp. on windows.


Regards,
Andreas




--
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] Overhauling GUCS

[Gregory Stark]

"Andreas Pflug" <[EMAIL PROTECTED]> writes:

> Gregory Stark wrote:
>> "Andreas Pflug" <[EMAIL PROTECTED]> writes:
>>   
>>> Why do so many people here insist on editing postgresql.conf as primary 
>>> means
>>> of changing config params?
>>> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
>>> postgresql.conf or doing it manually?
>>
>> no, it's awful.
>
> So I wonder why you accept it when configuring schemas. What's the big
> difference between setting a config param, and creating a table?

Yes, this is a big impedence mismatch between the old-school DBMS (with
emphasis on the MS part of the acronym) architecture and more modern
approaches. You don't configure your web site with "ALTER PAGE home.html SET
FORM contactus INPUT TYPE SUBMIT" either...

This is partly out of necessity though as those database objects contain
*data*. So merely replacing them with new data objects doesn't give the system
enough information to understand what to do with that data. The DDL commands
which modify the schema give that kind of data mutating instruction.

And I'll note that DBAs go to *great* lengths to do exactly the same kind of
thing I described the CISCO DBAs doing. Usually that means doing an ALTER
command and separately editing a creation script and trying to keep the two in
sync. It's a huge burden.

> And ultimately, the config param file format may well look like an SQL command
> file, restricted to SET only.

So all you have is our existing file except with an additional layer of
quoting to deal with, a useless SET keyword to annoy users, and a file that
you need a bison parser to deal instead of a simple keyword-value syntax that
sed can manipulate.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres 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] Overhauling GUCS

[Andreas Pflug]

Gregory Stark wrote:

"Andreas Pflug" <[EMAIL PROTECTED]> writes:

  

Why do so many people here insist on editing postgresql.conf as primary means
of changing config params?
Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
postgresql.conf or doing it manually?



no, it's awful.

  


So I wonder why you accept it when configuring schemas. What's the big 
difference between setting a config param, and creating a table?


And ultimately, the config param file format may well look like an SQL 
command file, restricted to SET only.





And in every major installation I've seen people use the last option. They
treat the original text file which is kept elsewhere -- normally checked into
some revision control system, tracked and managed like source code -- as the
canonical and authoritative version.
  


That's how you'd have to manage the schema sources too, no? Your 
comments are lost as well after schema creation scripts are executed, 
and manual changes may interfere with that.


Regards,
Andreas




--
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] Overhauling GUCS

[Gregory Stark]

"Andreas Pflug" <[EMAIL PROTECTED]> writes:

> Why do so many people here insist on editing postgresql.conf as primary means
> of changing config params?
> Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing
> postgresql.conf or doing it manually?

no, it's awful.

> Looking around for different approaches, network appliances come to my mind,
> e.g. Cisco routers and PIX. You have 3 ways to configure a pix:
> - use a command line (using ssh or telnet, eqivalent to psql); WRITE MEMORY to
> make the changes survive a reboot.
> - use a web interface (or similar tool)
> - use tftp to up/download the complete config in and out, editing the file.
> User comments will be lost, with the exception of those that have been applied
> with special comment commands (equivalent to "comment on").

And in every major installation I've seen people use the last option. They
treat the original text file which is kept elsewhere -- normally checked into
some revision control system, tracked and managed like source code -- as the
canonical and authoritative version.

The fact that you have to go through an especially complicated and awkward
process to load it onto the switches is just a technical detail to be worked
around. And the fact that somebody could have altered the configuration
directly on the switch and forgotten to write it to the configuration file is
a danger to be worked around with policy, not a benefit.

Essentially what good cisco network admins are doing here is working around
the awkward interface using policy and replacing it with a static text
configuration file kept elsewhere.

Experience with other attempts at automatic tools to edit configuration files
like webmin etc are that editing text files which are also under user control
is just a terrible idea. You can make it almost work but it's always fragile
and can't keep up with the flexibility of free-form text and the creativity of
users.

What I would suggest is going back to the plan when we added include files.
Have an automatically generated config file which is freshly re-generated each
time and doesn't try to preserve any user-formatting. Users then include that
file at the top of postgresql.conf and override any of the settings by setting
them in postgresql.conf after the include.

The important thing here is to separate files which are "under postgres
control" and those "under user control". That doesn't necessarily mean we have
to ban users from touching the automatically generated config file or switch
formats, but it relieves us of any responsibility for maintaining free-form
text.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS 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] Overhauling GUCS

[Andreas Pflug]

David E. Wheeler wrote:


How about a simple rule, such as that machine-generated comments start 
with "##", while user comments start with just "#"? I think that I've 
seen such a rule used before. At any rate, I think that, unless you 
have some sort of line marker for machine-generated comments, there 
will be no way to tell them apart from user comments. 


Two heretical questions:
Do we need user generated comments at all?
I can't remember ever having used any comment in postgresql.conf.

Why do so many people here insist on editing postgresql.conf as primary 
means of changing config params?
Isn't a psql -c "SET foo=bar; MAKE PERSISTENT" just as good as sed'ing 
postgresql.conf or doing it manually?



Looking around for different approaches, network appliances come to my 
mind, e.g. Cisco routers and PIX. You have 3 ways to configure a pix:
- use a command line (using ssh or telnet, eqivalent to psql); WRITE 
MEMORY to make the changes survive a reboot.

- use a web interface (or similar tool)
- use tftp to up/download the complete config in and out, editing the 
file. User comments will be lost, with the exception of those that have 
been applied with special comment commands (equivalent to "comment on").




Regards,
Andreas



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