Re: Postgre SQL 7.1 cygwin performance issue. - Mailing list pgsql-performance

Ravindran G - TLS, Chennai. wrote:
> Hi,
>
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000 (2 GB Memory,
> P4).
>

I would strongly suggest moving to native 8.1 :). You will find your
life much better.


Joshua D. Drake


> We understand that the maximum connections that can be set is 64 in
> Postgresql 7.1 version.
>
> The performance is very slow and some time the database is not getting
> connected from our application because of this.
>
> Please advise us on how to increase the performance by setting any
> attributes in configuration files ?.
>
> Find enclosed the configuration file.
>
> Thanks and regards,
> Ravi
>
>
> To post a message to the mailing list, send it to
>   pgsql-performance@postgresql.org
>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]
> Sent: Tuesday, August 22, 2006 5:32 PM
> To: ravig3
> Subject: 7E88-5CD9-AD0E : CONFIRM from pgsql-performance (subscribe)
>
>
> __
> The following request
>
>   "subscribe pgsql-performance ravig3 <ravindran_g@hcl.in>"
>
> was sent to
> by ravig3 <ravindran_g@hcl.in>.
>
> To accept or reject this request, please do one of the following:
>
> 1. If you have web browsing capability, visit
>
> <http://mail.postgresql.org/mj/mj_confirm/domain=postgresql.org?t=7E88-5CD9-
> AD0E>
>    and follow the instructions there.
>
> 2. Reply to majordomo@postgresql.org
>    with one of the following two commands in the body of the message:
>
>     accept
>     reject
>
>    (The number 7E88-5CD9-AD0E must be in the Subject header)
>
> 3. Reply to majordomo@postgresql.org
>    with one of the following two commands in the body of the message:
>
>     accept 7E88-5CD9-AD0E
>     reject 7E88-5CD9-AD0E
>
> Your confirmation is required for the following reason(s):
>
>   The subscribe_policy rule says that the "subscribe" command
>   must be confirmed by the person affected by the command.
>
>
> If you do not respond within 4 days, a reminder will be sent.
>
> If you do not respond within 7 days, this token will expire,
> and the request will not be completed.
>
> If you would like to communicate with a person,
> send mail to pgsql-performance-owner@postgresql.org.
> DISCLAIMER
> The contents of this e-mail and any attachment(s) are confidential and intended for the
>
> named recipient(s) only. It shall not attach any liability on the originator or HCL or its
>
> affiliates. Any views or opinions presented in this email are solely those of the author and
>
> may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction,
>
> dissemination, copying, disclosure, modification, distribution and / or publication of this
>
> message without the prior written consent of the author of this e-mail is strictly
>
> prohibited. If you have received this email in error please delete it and notify the sender
>
> immediately. Before opening any mail and attachments please check them for viruses and
>
> defect.
>
>
> ------------------------------------------------------------------------
>
> #
> # PostgreSQL configuration file
> # -----------------------------
> #
> # This file consists of lines of the form
> #
> #   name = value
> #
> # (The `=' is optional.) White space is collapsed, comments are
> # introduced by `#' 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.
>
> # Any option can also be given as a command line switch to the
> # postmaster, e.g., 'postmaster -c log_connections=on'. Some options
> # can be changed at run-time with the 'SET' SQL command.
>
>
> #========================================================================
>
>
> #
> #    Connection Parameters
> #
> tcpip_socket = true
> #ssl = false
>
> max_connections = 64
>
> #port = 5432
> #hostname_lookup = false
> #show_source_port = false
>
> #unix_socket_directory = ''
> #unix_socket_group = ''
> #unix_socket_permissions = 0777
>
> #virtual_host = ''
>
> #krb_server_keyfile = ''
>
>
> #
> #    Shared Memory Size
> #
> shared_buffers = 20000        # 2*max_connections, min 16
> #max_fsm_relations = 100    # min 10, fsm is free space map
> max_fsm_pages = 20000      # min 1000, fsm is free space map
> #max_locks_per_transaction = 64 # min 10
> #wal_buffers = 8            # min 4
>
> #
> #    Non-shared Memory Sizes
> #
> #sort_mem = 512             # min 32
> #vacuum_mem = 8192          # min 1024
>
>
> #
> #    Write-ahead log (WAL)
> #
> #wal_files = 0 # range 0-64
> wal_sync_method = open_sync   # the default varies across platforms:
> #               # fsync, fdatasync, open_sync, or open_datasync
> #wal_debug = 0             # range 0-16
> #commit_delay = 0          # range 0-100000
> #commit_siblings = 5       # range 1-1000
> #checkpoint_segments = 3   # in logfile segments (16MB each), min 1
> #checkpoint_timeout = 300  # in seconds, range 30-3600
> #fsync = true
>
>
> #
> #    Optimizer Parameters
> #
> #enable_seqscan = true
> #enable_indexscan = true
> #enable_tidscan = true
> #enable_sort = true
> #enable_nestloop = true
> #enable_mergejoin = true
> #enable_hashjoin = true
>
> #ksqo = false
>
> effective_cache_size = 5000  # default in 8k pages
> #random_page_cost = 4
> #cpu_tuple_cost = 0.01
> #cpu_index_tuple_cost = 0.001
> #cpu_operator_cost = 0.0025
>
>
> #
> #    GEQO Optimizer Parameters
> #
> #geqo = true
> #geqo_selection_bias = 2.0 # range 1.5-2.0
> #geqo_threshold = 11
> #geqo_pool_size = 0        # default based on #tables in query, range 128-1024
> #geqo_effort = 1
> #geqo_generations = 0
> #geqo_random_seed = -1     # auto-compute seed
>
>
> #
> #    Debug display
> #
> #silent_mode = false
>
> log_connections = true
> log_timestamp = true
> #log_pid = false
>
> #debug_level = 0 # range 0-16
>
> debug_print_query = true
> #debug_print_parse = false
> #debug_print_rewritten = false
> #debug_print_plan = false
> #debug_pretty_print = false
>
> # requires USE_ASSERT_CHECKING
> #debug_assertions = true
>
>
> #
> #    Syslog
> #
> # requires ENABLE_SYSLOG
> #syslog = 0 # range 0-2
> #syslog_facility = 'LOCAL0'
> #syslog_ident = 'postgres'
>
>
> #
> #    Statistics
> #
> #show_parser_stats = false
> #show_planner_stats = false
> #show_executor_stats = false
> #show_query_stats = false
>
> # requires BTREE_BUILD_STATS
> #show_btree_build_stats = false
>
>
> #
> #    Access statistics collection
> #
> #stats_start_collector = true
> #stats_reset_on_server_start = true
> #stats_command_string = false
> #stats_row_level = false
> #stats_block_level = false
>
>
> #
> #    Lock Tracing
> #
> #trace_notify = false
>
> # requires LOCK_DEBUG
> #trace_locks = false
> #trace_userlocks = false
> #trace_lwlocks = false
> #debug_deadlocks = false
> #trace_lock_oidmin = 16384
> #trace_lock_table = 0
>
>
> #
> #    Misc
> #
> #dynamic_library_path = '$libdir'
> #australian_timezones = false
> #authentication_timeout = 60    # min 1, max 600
> #deadlock_timeout = 1000
> #default_transaction_isolation = 'read committed'
> #max_expr_depth = 10000         # min 10
> #max_files_per_process = 1000   # min 25
> #password_encryption = false
> #sql_inheritance = true
> #transform_null_equals = false
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend


--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/