pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re-ordering .CONF params ... questions for this list
9+ messages / 5 participants
[nested] [flat]

* Re-ordering .CONF params ... questions for this list
@ 2003-06-10 18:01 Josh Berkus <josh@agliodbs.com>
  2003-06-10 19:05 ` Re: Re-ordering .CONF params ... questions for this list Richard Huxton <richardh@archonet.com>
  2003-06-13 22:31 ` Re: Re-ordering .CONF params ... questions for this list Nick Fankhauser <nickf@ontko.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Josh Berkus @ 2003-06-10 18:01 UTC (permalink / raw)
  To: pgsql-performance

Folks,

We've been discussing this for a while on HACKERS.  However, I haven't been 
getting much feedback on the specific order proposed.

Attached is an outline of my proposed re-ordering of postgresql.conf.sample.   
Please send me comments.  I need to submit a patch by Thursday, so don't take 
too long.

This is an effort to make the order of run-time params in 
postgresql.conf.sample and in the docs more logical and less baffling to the 
new DBA.

Questions:
1) Should "enable_implicit_from" go in the "Version/Platform Compatibility" 
section where I have it now, or in "CLIENT CONNECTIONS-Statement Behavior", 
or somewhere else?

2) Where should "preload_libraries" go?   I'm very reluctant to start a 
"Misc." section.  Perhaps I should start a "LIBRARIES" section?

3) I have re-ordered each subsection somewhat.   The fixed ordering is based 
on:
        a) My guess at the frequency with which that option will be changed, 
with more common options toward the top of the subsection;
        b) Grouping for tightly related options and for options that cascade;
        c) where (a) and (b) are unclear, alpha order.
Does this order make sense looking at the file?

3) Should we use indenting in PostgreSQL.conf.sample?   I tend to think it 
would make the file easier to read, but I'm not sure what effect it would 
have, if any, on parsing the file and whether other people would find it easy 
to read.



-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Attachments:

re_order_conf (text/plain, 2.4K) download #CONNECTIONS AND AUTHENTICATION #Connection Settings tcpip_socket max_connections superuser_reserved_connections port unix_socket_directory unix_socket_group unix_socket_permissions #Security & Authentication authentication_timeout ssl krb_server_keyfile virtual_host db_user_namespace #RESOURCE USAGE (except WAL) #Memory shared_buffers sort_mem vacuum_mem #Free Space Map max_fsm_pages max_fsm_relations #Disk Usage max_files_per_process #WRITE AHEAD LOG fsync wal_sync_method wal_buffers checkpoint_segments checkpoint_timeout checkpoint_warning commit_delay commit_siblings #QUERY TUNING #Planner Method Enabling enable_hashagg enable_hashjoin enable_indexscan enable_mergejoin enable_nestloop enable_seqscan enable_sort enable_tidscan #Planner Cost Constants effective_cache_size random_page_cost cpu_tuple_cost cpu_index_tuple_cost cpu_operator_cost default_statistics_target #Genetic Estimate Query Optimizer geqo geqo_threshold geqo_selection_bias geqo_pool_size geqo_effort geqo_generations geqo_random_seed #Other Query Modifiers explain_pretty_print from_collapse_limit join_collapse_limit max_expr_depth #CLIENT CONNECTION DEFAULTS #Statement Behaviour autocommit search_path default_transaction_isolation default_transaction_read_only statement_timeout #Locale and Formatting client_encoding australian_timezones datestyle timezone lc_messages lc_monetary lc_time lc_numeric #Other Defaults dynamic_library_path password_encryption #LOGGING & DEBUGGING #Debugging/Logging Levels server_min_messages client_min_messages log_min_error_statement debug_print_parse debug_print_rewritten debug_print_plan debug_pretty_print debug_assertions silent_mode #Additional Info to Log log_connections log_duration log_pid log_statement log_timestamp hostname_lookup show_source_port #Syslog syslog syslog_facility syslog_ident #STATISTICS #Statistics monitoring show_parser_stats show_planner_stats show_executor_stats show_statement_stats #Query/Index Statistics Collector stats_start_collector stats_reset_on_server_start stats_command_string stats_row_level stats_block_level #LOCK MANAGEMENT deadlock_timeout max_locks_per_transaction #VERSION/PLATFORM COMPATIBILITY OPTIONS enable_implicit_from have_rendezvous regex_flavor sql_inheritance transform_null_equals
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
@ 2003-06-10 19:05 ` Richard Huxton <richardh@archonet.com>
  2003-06-10 20:05   ` Re: Re-ordering .CONF params ... questions for this list Joe Conway <mail@joeconway.com>
  2003-06-10 20:57   ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  1 sibling, 2 replies; 9+ messages in thread

From: Richard Huxton @ 2003-06-10 19:05 UTC (permalink / raw)
  To: josh@agliodbs.com; pgsql-performance

On Tuesday 10 Jun 2003 7:01 pm, Josh Berkus wrote:
> Folks,
>
> We've been discussing this for a while on HACKERS.  However, I haven't been
> getting much feedback on the specific order proposed.
>
> Attached is an outline of my proposed re-ordering of
> postgresql.conf.sample. Please send me comments.  I need to submit a patch
> by Thursday, so don't take too long.
>
> This is an effort to make the order of run-time params in
> postgresql.conf.sample and in the docs more logical and less baffling to
> the new DBA.
>
> Questions:
> 1) Should "enable_implicit_from" go in the "Version/Platform Compatibility"
> section where I have it now, or in "CLIENT CONNECTIONS-Statement Behavior",
> or somewhere else?

Version compatibility I'd vote for (hesitantly)

> 2) Where should "preload_libraries" go?   I'm very reluctant to start a
> "Misc." section.  Perhaps I should start a "LIBRARIES" section?

No useful ideas - sorry.

> 3) I have re-ordered each subsection somewhat.   The fixed ordering is
> based on:
>         a) My guess at the frequency with which that option will be
> changed, with more common options toward the top of the subsection;
>         b) Grouping for tightly related options and for options that
> cascade; c) where (a) and (b) are unclear, alpha order.
> Does this order make sense looking at the file?

Looks good, I'd suggest the following perhaps:

Logging & Debugging
I'd like this near the top, but then I use syslogging. With a new install I go 
in and check tcpip_socket etc, fix the logging and just see if everything is 
working. Then I go in and do a little tuning.
Actually, maybe the syslog sub-section should go above the others - say where 
you'll log to, and then what you'll log. Of course, I'm biased since I use 
syslog.

Client Connection Defaults/Other/password_encryption
This should probably go in the security section. Actually, looking at it 
"dynamic_librar_path" is in the wrong place too - cut & past error?

Query Tuning/Planner Method Enabling
I'm in two minds here - obviously it is more "basic" than the "cost 
constraints" section, but that's the one people will be tinkering with first. 
Nope - thinking about it, you've got it right.

> 3) Should we use indenting in PostgreSQL.conf.sample?   I tend to think it
> would make the file easier to read, but I'm not sure what effect it would
> have, if any, on parsing the file and whether other people would find it
> easy to read.

Not sure it would help that much - the comments need a URL to the relevant 
page in the online docs though. A couple more lines of comments too:

# Syslog
#   To log to syslog, use something like
#   syslog = 2, syslog_facility = 'LOCAL0', syslog_ident = 'postgres'
#   Don't forget to update your syslog.conf then too.
#
...etc

Otherwise, looks good to me.

-- 
  Richard Huxton
  Archonet Ltd



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

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-10 19:05 ` Re: Re-ordering .CONF params ... questions for this list Richard Huxton <richardh@archonet.com>
@ 2003-06-10 20:05   ` Joe Conway <mail@joeconway.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Joe Conway @ 2003-06-10 20:05 UTC (permalink / raw)
  To: Richard Huxton <richardh@archonet.com>; +Cc: josh@agliodbs.com; pgsql-performance

Richard Huxton wrote:
>>2) Where should "preload_libraries" go?   I'm very reluctant to start a
>>"Misc." section.  Perhaps I should start a "LIBRARIES" section?
> 
> No useful ideas - sorry.

Sorry, I missed this earlier. This is a performance tuning option.

Joe




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

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-10 19:05 ` Re: Re-ordering .CONF params ... questions for this list Richard Huxton <richardh@archonet.com>
@ 2003-06-10 20:57   ` Josh Berkus <josh@agliodbs.com>
  2003-06-10 21:28     ` Re: Re-ordering .CONF params ... questions for this list Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2003-06-10 20:57 UTC (permalink / raw)
  To: Richard Huxton <richardh@archonet.com>; pgsql-performance

Richard,

> Logging & Debugging
> I'd like this near the top, but then I use syslogging. With a new install I 
go 
> in and check tcpip_socket etc, fix the logging and just see if everything is 
> working. Then I go in and do a little tuning.
> Actually, maybe the syslog sub-section should go above the others - say 
where 
> you'll log to, and then what you'll log. Of course, I'm biased since I use 
> syslog.

I have no objection to moving the syslog section.  Any other opinions?

> Client Connection Defaults/Other/password_encryption
> This should probably go in the security section. Actually, looking at it 
> "dynamic_librar_path" is in the wrong place too - cut & past error?

Not the way I read the docs; according to the docs:

password_encryption is whether or not the statement "ALTER USER joe_schmoe 
WITH PASSWORD 'xxxyyy'" is encrypted by default even if you don't use the 
"WITH ENCRYPTION" option.   And it is SET-able on each client connection, by 
regular users.  So it goes in "CLIENT CONNECTION SETTINGS".

"dynamic_library_path", while less obvious, is also SETable on each client 
connection.   I'd be happy to revise this if someone understands/uses this 
option and has a better idea where to put it.

> Not sure it would help that much - the comments need a URL to the relevant 
> page in the online docs though. A couple more lines of comments too:

Given that we're running out of time, I wasn't going to touch any of the 
comments in PostgreSQL.conf.sample.   Instead, I was going to leave the 
comments as-is, and post extensive comments on Techdocs before 7.4 beta.  
Then, in 7.5 or 8.0 we can re-comment .conf.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-10 19:05 ` Re: Re-ordering .CONF params ... questions for this list Richard Huxton <richardh@archonet.com>
  2003-06-10 20:57   ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
@ 2003-06-10 21:28     ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-06-10 21:29       ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2003-06-10 21:28 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: Richard Huxton <richardh@archonet.com>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> Given that we're running out of time, I wasn't going to touch any of the 
> comments in PostgreSQL.conf.sample.   Instead, I was going to leave the 
> comments as-is, and post extensive comments on Techdocs before 7.4
> beta.  

I doubt anyone would object to improving the comments during beta; so
you don't need to consider that part something that has to be done
before feature freeze.

			regards, tom lane



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

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-10 19:05 ` Re: Re-ordering .CONF params ... questions for this list Richard Huxton <richardh@archonet.com>
  2003-06-10 20:57   ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-10 21:28     ` Re: Re-ordering .CONF params ... questions for this list Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-06-10 21:29       ` Josh Berkus <josh@agliodbs.com>
  2003-06-10 21:45         ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2003-06-10 21:29 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <richardh@archonet.com>; pgsql-performance

Tom,

> I doubt anyone would object to improving the comments during beta; so
> you don't need to consider that part something that has to be done
> before feature freeze.

Oh, cool.  OK, then ... the hard part is just deciding on what comments to 
include.   We'll work on that in this list.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




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

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-10 19:05 ` Re: Re-ordering .CONF params ... questions for this list Richard Huxton <richardh@archonet.com>
  2003-06-10 20:57   ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-10 21:28     ` Re: Re-ordering .CONF params ... questions for this list Tom Lane <tgl@sss.pgh.pa.us>
  2003-06-10 21:29       ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
@ 2003-06-10 21:45         ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Josh Berkus @ 2003-06-10 21:45 UTC (permalink / raw)
  To: pgsql-performance; +Cc: pgsql-hackers@postgresql.org

Folks,

Revised ordering of options, based on information and suggestions received 
here on both mailing lists.

-- 
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology 	josh@agliodbs.com
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco

Attachments:

re_order_conf (text/plain, 2.6K) download #CONNECTIONS AND AUTHENTICATION #Connection Settings tcpip_socket max_connections superuser_reserved_connections port unix_socket_directory unix_socket_group unix_socket_permissions #Security & Authentication authentication_timeout ssl krb_server_keyfile virtual_host db_user_namespace #RESOURCE USAGE (except WAL) #Memory shared_buffers sort_mem vacuum_mem #Free Space Map max_fsm_pages max_fsm_relations #Disk Resource Usage max_files_per_process preload_libraries #WRITE AHEAD LOG fsync wal_sync_method wal_buffers checkpoint_segments checkpoint_timeout checkpoint_warning commit_delay commit_siblings #QUERY TUNING #Planner Method Enabling enable_hashagg enable_hashjoin enable_indexscan enable_mergejoin enable_nestloop enable_seqscan enable_sort enable_tidscan #Planner Cost Constants effective_cache_size random_page_cost cpu_tuple_cost cpu_index_tuple_cost cpu_operator_cost default_statistics_target #Genetic Estimate Query Optimizer geqo geqo_threshold geqo_selection_bias geqo_pool_size geqo_effort geqo_generations geqo_random_seed #Other Query Modifiers explain_pretty_print from_collapse_limit join_collapse_limit max_expr_depth #LOGGING & DEBUGGING #Syslog syslog syslog_facility syslog_ident #Debugging/Logging Levels server_min_messages client_min_messages log_min_error_statement debug_print_parse debug_print_rewritten debug_print_plan debug_pretty_print debug_assertions silent_mode #Additional Info to Log log_connections log_duration log_pid log_statement log_timestamp hostname_lookup show_source_port #CLIENT CONNECTION DEFAULTS #Statement Behaviour XXXXXautocommitXXXXX REMOVED! search_path default_transaction_isolation default_transaction_read_only statement_timeout #Locale and Formatting datestyle timezone australian_timezones extra_float_digits lc_messages lc_monetary lc_time lc_numeric client_encoding #Other Defaults password_encryption dynamic_library_path #STATISTICS #Statistics monitoring show_parser_stats show_planner_stats show_executor_stats show_statement_stats default_statistics_target #Query/Index Statistics Collector stats_start_collector stats_reset_on_server_start stats_command_string stats_row_level stats_block_level #LOCK MANAGEMENT deadlock_timeout max_locks_per_transaction #VERSION/PLATFORM COMPATIBILITY OPTIONS #Previous Postgres Versions enable_implicit_from regex_flavor sql_inheritance #Compatibility with Platforms & Clients have_rendezvous transform_null_equals
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
@ 2003-06-13 22:31 ` Nick Fankhauser <nickf@ontko.com>
  2003-06-13 22:51   ` Re: Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Nick Fankhauser @ 2003-06-13 22:31 UTC (permalink / raw)
  To: josh@agliodbs.com; pgsql-performance

Josh- I took a quick look at your proposal on conf ordering-

The groupings are great.

Without a clear notion of dependencies, and only based on what I think
people are likely to tweak the most, I'd suggest promoting the "client
connection defaults", "version/platform compatibility" & "logging/debugging"
groups to positions 2,3 & 4 respectively.

Here's the thinking-

You'd have all of the options that a neophyte might need to set to perform a
particular task in a given environment in the first three groups. Problems
encountered while setting these up might require the adventurous beginner to
dip into logging/debugging to gather basic diagnostic info.

With this ordering, everything you might have to touch in order to get a
basic system up & running lives in the top 4 groups. (This also helps soften
the dilemma of where enable_implicit_from should go by putting the two
possible groups next to one another.)

Below the top four groups are the tuning parameters best not messed with
until one passes from neophyte to DB-Geek level. (And probably not worth
messing with even then.) These are only needed when you've passed over from
getting it running to needing it to run better.

-Nick



> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Josh Berkus
> Sent: Tuesday, June 10, 2003 1:02 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Re-ordering .CONF params ... questions for this list
>
>
> Folks,
>
> We've been discussing this for a while on HACKERS.  However, I
> haven't been
> getting much feedback on the specific order proposed.
>
> Attached is an outline of my proposed re-ordering of
> postgresql.conf.sample.
> Please send me comments.  I need to submit a patch by Thursday,
> so don't take
> too long.
>
> This is an effort to make the order of run-time params in
> postgresql.conf.sample and in the docs more logical and less
> baffling to the
> new DBA.
>
> Questions:
> 1) Should "enable_implicit_from" go in the "Version/Platform
> Compatibility"
> section where I have it now, or in "CLIENT CONNECTIONS-Statement
> Behavior",
> or somewhere else?
>
> 2) Where should "preload_libraries" go?   I'm very reluctant to start a
> "Misc." section.  Perhaps I should start a "LIBRARIES" section?
>
> 3) I have re-ordered each subsection somewhat.   The fixed
> ordering is based
> on:
>         a) My guess at the frequency with which that option will
> be changed,
> with more common options toward the top of the subsection;
>         b) Grouping for tightly related options and for options
> that cascade;
>         c) where (a) and (b) are unclear, alpha order.
> Does this order make sense looking at the file?
>
> 3) Should we use indenting in PostgreSQL.conf.sample?   I tend to
> think it
> would make the file easier to read, but I'm not sure what effect it would
> have, if any, on parsing the file and whether other people would
> find it easy
> to read.
>
>
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>




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

* Re: Re-ordering .CONF params ... questions for this list
  2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
  2003-06-13 22:31 ` Re: Re-ordering .CONF params ... questions for this list Nick Fankhauser <nickf@ontko.com>
@ 2003-06-13 22:51   ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Josh Berkus @ 2003-06-13 22:51 UTC (permalink / raw)
  To: nickf@ontko.com; pgsql-performance

Nick,

> Without a clear notion of dependencies, and only based on what I think
> people are likely to tweak the most, I'd suggest promoting the "client
> connection defaults", "version/platform compatibility" & "logging/debugging"
> groups to positions 2,3 & 4 respectively.

I like your ideas, but there's two problems with them:

1) I mess around with postgresql.conf constantly, and seldom touch anything in 
the "client connection defaults" section.  I do, however, mess with the stuff 
in the "resource usage" section, as to most of the people on this list.

2) I just spent 4.5 hours re-arranging the Runtime-config docs page last 
night, and am very reluctant to do it again.


-- 
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology 	josh@agliodbs.com
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco




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


end of thread, other threads:[~2003-06-13 22:51 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-10 18:01 Re-ordering .CONF params ... questions for this list Josh Berkus <josh@agliodbs.com>
2003-06-10 19:05 ` Richard Huxton <richardh@archonet.com>
2003-06-10 20:05   ` Joe Conway <mail@joeconway.com>
2003-06-10 20:57   ` Josh Berkus <josh@agliodbs.com>
2003-06-10 21:28     ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-10 21:29       ` Josh Berkus <josh@agliodbs.com>
2003-06-10 21:45         ` Josh Berkus <josh@agliodbs.com>
2003-06-13 22:31 ` Nick Fankhauser <nickf@ontko.com>
2003-06-13 22:51   ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox