Tuning Postgresql on FreeBSD 5.1
Paul Pathiakis
paul at pathiakis.com
Mon Aug 25 06:30:14 PDT 2003
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'
More information about the freebsd-performance
mailing list