Tuning Postgresql on FreeBSD 5.1

From: Paul Pathiakis (paul_at_pathiakis.com)
Date: 08/25/03

  • Next message: Sean Chittenden: "Re: Tuning Postgresql on FreeBSD 5.1" 
    To: freebsd-database@freebsd.org, freebsd-performance@freebsd.org
Date: Mon, 25 Aug 2003 09:29:32 -0400

    Hi,

            I'd like to thank everyone for their help so far. I'm implement most of the
    enhancements and changes on the database so far. I now have a bigger
    problem... the machine is generating some large reports (term used by the DB
    people here) and the processes start and instantly drop off to no
    utilization... they sit there and hang... seemingly resource starved. I'd
    like know if someone could help me. I've enclosed the postgresql.conf file,
    the systctl.conf file, loader.conf and the Kernel memory parameters.
            Again, the machine is a twin 2.8 Xeon HTT machine. HTT is turned on and the
    machine sees 4 cpus. It has 4 GB of RAM and I'm starting to put on SCSI
    drives as the machine had the IDE drives maxed out at 100% utilization at all
    times. (Again, I didn't order this machine, otherwise it would have had dual
    U320 channels etc on its I/O system) Presently, due to scavenging a PCI SCSI
    card (ADAPTEC U2W), disks (U320 10K rpm) and an enclosure for 4 disks, I have
    the following:

    /dev/ar1s1d 114244630 479980 104625080 0% /usr/local
    /dev/da0s1d 138860928 81448860 46303194 64% /usr/local/pgsql
    /dev/md0 1031916 4 949360 0%
    /usr/local/pgsql/data/base/16978/pgsql_tmp
    procfs 4 4 0 100% /proc
    /dev/da1s1d 138860928 131202 127620852 0%
    /usr/local/pgsql/data/pg_xlog
    /dev/da2s1d 142801720 3277472 128100112 2% /pg_index
    /dev/da3s1d 142801720 1049616 130327968 1% /pg_table

    noatime is configured on the pgsql hierarchy and related links to disks on
    /pg_* . I configured a MD as the pgsql_tmp directory, etc. I've created
    UFS2+S filesystems with block and frag sizes of 8K as this is optimal for PG.
    I hope this makes sense all the way around. I'm not a DBA, just a UNIX
    admin.
            
            Anyhow, I'm looking at the postgresql.conf file and I don't see a whole lot
    that makes sense to me. Please help! I've got a bunch of people saying
    Linux just runs faster and the DB group is using a Linux config file on the
    FreeBSD machine. (Don't get me going) It is my belief that a BSD DB is
    going to run faster on it's platform of choice for development (FreeBSD) than
    another OS. Please help!

            Thanks!

            Paul Pathiakis
            

    sysctl.conf:

    kern.maxfiles=10000
    kern.ipc.shm_use_phys=1
    kern.ipc.shmall=524288
    kern.ipc.shmmax=1073741824
    vfs.vmiodirenable=1

    loader.conf

    kern.maxfiles=32768
    kern.nbuf=16384

    GENERIC SMP:

    options SYSVMSG #SYSV-style message queues
    options SYSVSHM #SYSV-style shared memory
    options SYSVSEM #SYSV-style semaphores
    options SHMMAXPGS=4096
    options SHMSEG=256
    options SEMMNI=256
    options SEMMNS=512
    options SEMMNU=256
    options SEMMAP=256

    Postgresql.conf:

    #
    # PostgreSQL configuration file
    # -----------------------------
    #
    # This file consists of lines of the form:
    #
    # name = value
    #
    # (The '=' 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.
    #
    # 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.
    #
    # 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".

    #========================================================================

    #
    # Connection Parameters
    #
    #tcpip_socket = false
    #ssl = false

    max_connections = 128
    #superuser_reserved_connections = 2

    #port = 5432
    #hostname_lookup = false
    #show_source_port = false

    #unix_socket_directory = ''
    #unix_socket_group = ''
    #unix_socket_permissions = 0777 # octal

    #virtual_host = ''

    #krb_server_keyfile = ''

    #
    # Shared Memory Size
    #
    shared_buffers = 48000 # min max_connections*2 or 16, 8KB each
    #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
    #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
    #max_locks_per_transaction = 64 # min 10
    #wal_buffers = 8 # min 4, typically 8KB each

    #
    # Non-shared Memory Sizes
    #
    sort_mem = 32768 # min 64, size in KB
    #vacuum_mem = 8192 # min 1024, size in KB

    #
    # Write-ahead log (WAL)
    #
    #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
    #checkpoint_timeout = 300 # range 30-3600, in seconds
    #
    #commit_delay = 0 # range 0-100000, in microseconds
    #commit_siblings = 5 # range 1-1000
    #
    #fsync = true
    #wal_sync_method = fsync # the default varies across platforms:
    # # fsync, fdatasync, open_sync, or
    open_datasync
    #wal_debug = 0 # range 0-16

    #
    # Optimizer Parameters
    #
    #enable_seqscan = true
    #enable_indexscan = true
    #enable_tidscan = true
    #enable_sort = true
    #enable_nestloop = true
    #enable_mergejoin = true
    #enable_hashjoin = true

    #effective_cache_size = 1000 # typically 8KB each
    #random_page_cost = 4 # units are one sequential page fetch cost
    #cpu_tuple_cost = 0.01 # (same)
    #cpu_index_tuple_cost = 0.001 # (same)
    #cpu_operator_cost = 0.0025 # (same)

    #default_statistics_target = 10 # range 1-1000

    #
    # 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 statement,
                                    # range 128-1024
    #geqo_effort = 1
    #geqo_generations = 0
    #geqo_random_seed = -1 # auto-compute seed

    #
    # Message display
    #
    #server_min_messages = notice # Values, in order of decreasing detail:
                                    # debug5, debug4, debug3, debug2, debug1,
                                    # info, notice, warning, error, log, fatal,
                                    # panic
    #client_min_messages = notice # Values, in order of decreasing detail:
                                    # debug5, debug4, debug3, debug2, debug1,
                                    # log, info, notice, warning, error
    #silent_mode = false

    #log_connections = false
    #log_pid = false
    #log_statement = false
    #log_duration = false
    #log_timestamp = false

    #log_min_error_statement = panic # Values in order of increasing severity:
                                     # debug5, debug4, debug3, debug2, debug1,
                                     # info, notice, warning, error, panic(off)

    #debug_print_parse = false
    #debug_print_rewritten = false
    #debug_print_plan = false
    #debug_pretty_print = false

    #explain_pretty_print = true

    # requires USE_ASSERT_CHECKING
    #debug_assertions = true

    #
    # 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_statement_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
    #
    #autocommit = true
    #dynamic_library_path = '$libdir'
    #search_path = '$user,public'
    #datestyle = 'iso, us'
    #timezone = unknown # actually, defaults to TZ environment setting
    #australian_timezones = false
    #client_encoding = sql_ascii # actually, defaults to database encoding
    #authentication_timeout = 60 # 1-600, in seconds
    #deadlock_timeout = 1000 # in milliseconds
    #default_transaction_isolation = 'read committed'
    #max_expr_depth = 10000 # min 10
    #max_files_per_process = 1000 # min 25
    #password_encryption = true
    #sql_inheritance = true
    #transform_null_equals = false
    #statement_timeout = 0 # 0 is disabled, in milliseconds
    #db_user_namespace = false

    #
    # Locale settings
    #
    # (initialized by initdb -- may be changed)
    LC_MESSAGES = 'C'
    LC_MONETARY = 'C'
    LC_NUMERIC = 'C'
    LC_TIME = 'C'

    _______________________________________________
    freebsd-performance@freebsd.org mailing list
    http://lists.freebsd.org/mailman/listinfo/freebsd-performance
    To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org"

  • Next message: Sean Chittenden: "Re: Tuning Postgresql on FreeBSD 5.1"

    Relevant Pages

    • Re: Typed DataSets in VS2005
      ... The hard-coded values are pointing to our development database. ... Or are you talking about another config file? ... I always taken the connection string via ... hradcoded to create a typed dataset for you.. ...
      (microsoft.public.dotnet.languages.csharp)
    • Re: Typed DataSets in VS2005
      ... Ar u using "Microsoft.ApplicationBlocks.Data" to access your database.. ... SqlConnection by reading the values from the config file and assign it to ... The table adapter created with the typed dataset has a Connection ... connection string via the config file and use it in the dataaccess ...
      (microsoft.public.dotnet.languages.csharp)
    • Re: Config file to turn on/off features ?
      ... > places you could store configuration are in the registry or a database. ... XML files for configuration are easy for users to edit, ... > the user to corrupt the config file. ...
      (microsoft.public.dotnet.framework)
    • Re: (spam)secure mailform
      ... As the manual mentioned:"The address of the page which referred the user agent to the current page. ... But whats the best way to prevent using the mail form outside my domain. ... look up the selected value in your database or config file and send the mail. ...
      (comp.lang.php)