X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B2C0C53239 for ; Mon, 11 Apr 2005 15:29:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23451-06 for ; Mon, 11 Apr 2005 18:29:16 +0000 (GMT) Received: from vms044pub.verizon.net (vms044pub.verizon.net [206.46.252.44]) by svr1.postgresql.org (Postfix) with ESMTP id 17C0E5295F for ; Mon, 11 Apr 2005 15:29:15 -0300 (ADT) Received: from jfradkin ([63.162.100.127]) by vms044.mailsrvcs.net (Sun Java System Messaging Server 6.2 HotFix 0.04 (built Dec 24 2004)) with ESMTPA id <0IES00FFIOOR9FJ0@vms044.mailsrvcs.net> for pgsql-performance@postgresql.org; Mon, 11 Apr 2005 13:29:16 -0500 (CDT) Date: Mon, 11 Apr 2005 14:29:22 -0400 From: "Joel Fradkin" Subject: Re: Is there somthing I need to do on my production server? In-reply-to: <002c01c53eb9$ee07af00$797ba8c0@jfradkin> To: "'PostgreSQL Perform'" Message-id: <000001c53ec4$625336e0$797ba8c0@jfradkin> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook, Build 10.0.6626 Content-type: text/plain; charset=us-ascii Content-transfer-encoding: quoted-printable Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/219 X-Sequence-Number: 11616 Here is the config for the AS4 server. # ----------------------------- # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name =3D value # # (The '=3D' is optional.) White space may be used. Comments are = introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default = values. # # Please note that re-commenting a setting is NOT sufficient to revert = it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=3Don'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload". Some settings, such as listen_address, require # a postmaster shutdown and restart to take effect. #------------------------------------------------------------------------= --- # FILE LOCATIONS #------------------------------------------------------------------------= --- # The default values of these variables are driven from the -D command = line # switch or PGDATA environment variable, represented here as ConfigDir. # data_directory =3D 'ConfigDir' # use data in another = directory #data_directory =3D '/pgdata/data' # hba_file =3D 'ConfigDir/pg_hba.conf' # the host-based = authentication file # ident_file =3D 'ConfigDir/pg_ident.conf' # the IDENT configuration = file # If external_pid_file is not explicitly set, no extra pid file is = written. # external_pid_file =3D '(none)' # write an extra pid file #------------------------------------------------------------------------= --- # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------= --- # - Connection Settings - #listen_addresses =3D 'localhost' # what IP interface(s) to listen on; # defaults to localhost, '*' =3D any listen_addresses =3D '*' port =3D 5432 max_connections =3D 100 # note: increasing max_connections costs about 500 bytes of = shared # memory per connection slot, in addition to costs from shared_buffers # and max_locks_per_transaction. #superuser_reserved_connections =3D 2 #unix_socket_directory =3D '' #unix_socket_group =3D '' #unix_socket_permissions =3D 0777 # octal #rendezvous_name =3D '' # defaults to the computer name # - Security & Authentication - #authentication_timeout =3D 60 # 1-600, in seconds #ssl =3D false #password_encryption =3D true #krb_server_keyfile =3D '' #db_user_namespace =3D false #------------------------------------------------------------------------= --- # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------= --- # - Memory - shared_buffers =3D 12288 #5000 min 16, at least max_connections*2, 8KB = each #work_mem =3D 1024 # min 64, size in KB work_mem =3D 16384 # 8192 #maintenance_work_mem =3D 16384 # min 1024, size in KB #max_stack_depth =3D 2048 # min 100, size in KB # - Free Space Map - max_fsm_pages =3D 100000 #30000 # min max_fsm_relations*16, 6 bytes = each max_fsm_relations =3D 1500 #1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process =3D 1000 # min 25 #preload_libraries =3D '' # - Cost-Based Vacuum Delay - #vacuum_cost_delay =3D 0 # 0-1000 milliseconds #vacuum_cost_page_hit =3D 1 # 0-10000 credits #vacuum_cost_pagE_miss =3D 10 # 0-10000 credits #vacuum_cost_page_dirty =3D 20 # 0-10000 credits #vacuum_cost_limit =3D 200 # 0-10000 credits # - Background writer - #bgwriter_delay =3D 200 # 10-10000 milliseconds between rounds #bgwriter_percent =3D 1 # 0-100% of dirty buffers in each = round #bgwriter_maxpages =3D 100 # 0-1000 buffers max per round #------------------------------------------------------------------------= --- # WRITE AHEAD LOG #------------------------------------------------------------------------= --- # - Settings - fsync =3D true # turns forced synchronization on or = off wal_sync_method =3D open_sync# fsync # the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync wal_buffers =3D 2048#8 # min 4, 8KB each #commit_delay =3D 0 # range 0-100000, in microseconds #commit_siblings =3D 5 # range 1-1000 # - Checkpoints - checkpoint_segments =3D 100 #3 # in logfile segments, min 1, 16MB = each #checkpoint_timeout =3D 300 # range 30-3600, in seconds #checkpoint_warning =3D 30 # 0 is off, in seconds # - Archiving - #archive_command =3D '' # command to use to archive a logfile segment #------------------------------------------------------------------------= --- # QUERY TUNING #------------------------------------------------------------------------= --- # - Planner Method Configuration - #enable_hashagg =3D true #enable_hashjoin =3D true #enable_indexscan =3D true #enable_mergejoin =3D false #enable_nestloop =3D true #enable_seqscan =3D true #enable_sort =3D true #enable_tidscan =3D true # - Planner Cost Constants - effective_cache_size =3D 262144 #40000 typically 8KB each #random_page_cost =3D 4 # units are one sequential page fetch = cost random_page_cost =3D 2 #cpu_tuple_cost =3D 0.01 # (same) #cpu_index_tuple_cost =3D 0.001 # (same) #cpu_operator_cost =3D 0.0025 # (same) # - Genetic Query Optimizer - #geqo =3D true #geqo_threshold =3D 12 #geqo_effort =3D 5 # range 1-10 #geqo_pool_size =3D 0 # selects default based on effort #geqo_generations =3D 0 # selects default based on effort #geqo_selection_bias =3D 2.0 # range 1.5-2.0 # - Other Planner Options - default_statistics_target =3D 250#10 # range 1-1000 #from_collapse_limit =3D 8 #join_collapse_limit =3D 8 # 1 disables collapsing of explicit = JOINs #------------------------------------------------------------------------= --- # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------= --- # - Where to Log - #log_destination =3D 'stderr' # Valid values are combinations of = stderr, # syslog and eventlog, depending on # platform. # This is relevant when logging to stderr: redirect_stderr =3D true # Enable capturing of stderr into log = files. # These are only relevant if redirect_stderr is true: log_directory =3D 'pg_log' # Directory where log files are written. # May be specified absolute or relative to PGDATA log_filename =3D 'postgresql-%a.log' # Log file name pattern. # May include strftime() escapes log_truncate_on_rotation =3D true # If true, any existing log file of = the # same name as the new log file will be truncated # rather than appended to. But such = truncation # only occurs on time-driven rotation, # not on restarts or size-driven rotation. # Default is false, meaning append to = existing # files in all cases. log_rotation_age =3D 1440 # Automatic rotation of logfiles will = happen after # so many minutes. 0 to disable. log_rotation_size =3D 0 # Automatic rotation of logfiles will = happen after # so many kilobytes of log output. 0 to disable. # These are relevant when logging to syslog: #syslog_facility =3D 'LOCAL0' #syslog_ident =3D 'postgres' # - When to Log - #client_min_messages =3D notice # Values, in order of decreasing = detail: # debug5, debug4, debug3, debug2, = debug1, # log, notice, warning, error #log_min_messages =3D notice # Values, in order of decreasing = detail: # debug5, debug4, debug3, debug2, = debug1, # info, notice, warning, error, log, fatal, # panic #log_error_verbosity =3D default # terse, default, or verbose messages #log_min_error_statement =3D panic # Values in order of increasing = severity: # debug5, debug4, debug3, debug2, = debug1, # info, notice, warning, error, panic(off) #log_min_duration_statement =3D -1 # -1 is disabled, in milliseconds. #silent_mode =3D false # DO NOT USE without syslog or redirect_stderr # - What to Log - #debug_print_parse =3D false #debug_print_rewritten =3D false #debug_print_plan =3D false #debug_pretty_print =3D false #log_connections =3D false #log_disconnections =3D false #log_duration =3D false #log_line_prefix =3D '' # e.g. '<%u%%%d> ' # %u=3Duser name %d=3Ddatabase name # %r=3Dremote host and port # %p=3DPID %t=3Dtimestamp %i=3Dcommand = tag # %c=3Dsession id %l=3Dsession line = number # %s=3Dsession start timestamp = %x=3Dtransaction id # %q=3Dstop here in non-session = processes # %%=3D'%' #log_statement =3D 'none' # none, mod, ddl, all #log_hostname =3D false ______________________________ #------------------------------------------------------------------------= --- # RUNTIME STATISTICS #------------------------------------------------------------------------= --- # - Statistics Monitoring - #log_parser_stats =3D false #log_planner_stats =3D false #log_executor_stats =3D false #log_statement_stats =3D false # - Query/Index Statistics Collector - #stats_start_collector =3D true #stats_command_string =3D false #stats_block_level =3D false #stats_row_level =3D false #stats_reset_on_server_start =3D true #------------------------------------------------------------------------= --- # CLIENT CONNECTION DEFAULTS #------------------------------------------------------------------------= --- # - Statement Behavior - #search_path =3D '$user,public' # schema names #default_tablespace =3D '' # a tablespace name, or '' for default #check_function_bodies =3D true #default_transaction_isolation =3D 'read committed' #default_transaction_read_only =3D false #statement_timeout =3D 0 # 0 is disabled, in milliseconds # - Locale and Formatting - #datestyle =3D 'iso, mdy' #timezone =3D unknown # actually, defaults to TZ environment setting #australian_timezones =3D false #extra_float_digits =3D 0 # min -15, max 2 #client_encoding =3D sql_ascii # actually, defaults to database = encoding # These settings are initialized by initdb -- they might be changed lc_monetary =3D 'en_US.UTF-8' # locale for monetary = formatting lc_numeric =3D 'en_US.UTF-8' # locale for number formatting lc_time =3D 'en_US.UTF-8' # locale for time formatting # - Other Defaults - #explain_pretty_print =3D true #dynamic_library_path =3D '$libdir' #------------------------------------------------------------------------= --- # LOCK MANAGEMENT #------------------------------------------------------------------------= --- #deadlock_timeout =3D 1000 # in milliseconds #max_locks_per_transaction =3D 64 # min 10, ~200*max_connections bytes = each #------------------------------------------------------------------------= --- # VERSION/PLATFORM COMPATIBILITY #------------------------------------------------------------------------= --- # - Previous Postgres Versions - #add_missing_from =3D true #regex_flavor =3D advanced # advanced, extended, or basic #sql_inheritance =3D true #default_with_oids =3D true # - Other Platforms & Clients - #transform_null_equals =3D false